[SQL] 計算資料比數所佔的比例 @ MySQL 5.6

這個需求好像滿常見的？例如有一張表記錄著許多操作的流水帳，例如：

CREATE TABLE `play_action` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`play_result` INT (10) NOT NULL
)

想要以天為單位：

SELECT DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, play_result, count(*) FROM play_action GROUP BY d, play_result

就能得知一天當中，所有操作結果的次數，那如果是比例呢？只好要先 SUM 加總一下 XD 最後在 JOIN 一下即可。此例就用暫存表來處理，其中暫存表不予許同一個 SQL 內有多次存取，所以只好多開一張暫存表來處理，連續動作：

Step 1: 建立一張暫時表，以天為單位，根據播放後的狀態，依序是播放次數、播放日期、播放結果，其中播放結果 0 是正常的

CREATE TEMPORARY TABLE `play` (
`play_count` INT (10) NOT NULL,
`play_date` VARCHAR(32) NOT NULL,
`play_result` INT (10) NOT NULL,
UNIQUE KEY `play_date` (`play_date`, `play_result`)
);

Step 2: 從 play_action 撈資料來填補，此時可以用條件式挑選想要的資料，此例是 4 月之後的資料

INSERT INTO `play` (play_result, play_date, play_count) SELECT play_result, DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, count(*) as c FROM play_action WHERE timestamp >= '2016-04-01 00:00:00' GROUP BY play_result, d ORDER BY d ON DUPLICATE KEY UPDATE play_count=VALUES(play_count);

Step 3: 建立另一張暫存表，單純以天記錄總播放次數

CREATE TEMPORARY TABLE `play_sum` (
`play_count` INT (10) NOT NULL,
`play_date` VARCHAR(32) NOT NULL,
UNIQUE KEY `play_date` (`play_date`)
);

INSERT INTO `play_sum` (play_date, play_count) SELECT play_date, SUM(play_count) AS play_total FROM play GROUP BY play_date;

Step 4: 簡易運算，輸出為日期、播放成功次數的比例，總播放次數

SELECT play.play_date, play.play_result, play.play_count / play_sum.play_count * 100, play_sum.play_count AS play_total FROM play, play_sum WHERE play.play_date = play_sum.play_date;

+----------------+-------------+---------------------------------------------+------------+
| play_date      | play_result | play.play_count / play_sum.play_count * 100 | play_total |
+----------------+-------------+---------------------------------------------+------------+
| 2016-04-01 Fri |           0 |                                     54.9398 |        687 |
| 2016-04-02 Sat |           0 |                                     68.9445 |        972 |
| 2016-04-03 Sun |           0 |                                     75.6607 |       1748 |
| 2016-04-04 Mon |           0 |                                     71.3967 |       1112 |
| 2016-04-05 Tue |           0 |                                     70.2852 |        886 |
| 2016-04-06 Wed |           0 |                                     49.8085 |        880 |
| 2016-04-07 Thu |           0 |                                     53.2239 |        596 |
+----------------+-------------+---------------------------------------------+------------+

試用 Microsoft Azure Machine Learning / studio.azureml.net 服務

參加了 DevDays Asia 2016 活動，這次本來就看重於 Azure Machine learning 項目，雖然已經多少知道了，但看到講者或是導覽，只需將資料轉成 csv 以及在介面上拖拉一下就完成，還是滿驚豔的 :P

• http://interopevents.com/taipei2016#taipei2016-speakers
• https://www.microsoft.com/taiwan/events/devdays/agenda.htm

簡言之，使用者只需要將資料轉成 csv 格式（這邊其實是最多 know-how 的），接著完全用 http://studio.azureml.net/ 介面上傳資料、分割資料、挑選要用的演算法，在拉幾下，收工！

整個過程甚至可以方便地觀看 input 資料、觀看 traning 的結果，甚至在穿接成 web service api 來用都可以。這完全把資料分析中的：資料情境分析、資料科學家、資料工程師的工作，其中後兩者的工作都做完八九成了！

為何說資料情境分析還沒呢？當情境定義完後，而資料的前置處理才是最最重要的一塊，這幾乎還是無人能取代的，若真的說要有，大概就是用 AI 或是 try-and-error 把各種排列組合都自動化處理完，這樣也不是不可以啦 :P

筆記一下流程（建議直接上 studio.azureml.net 看導覽）：





接著，隨意打一些關鍵字，可以找尋到測資：



資料有了，接著就是分群，請用 split 關鍵字，依照填寫的比例，把一群拿去 train：



再來，就是要 train 了，可以挑選目標欄位：





接著，用 two class 關鍵字，可以找到一批免錢的演算法 XD



最後，再用 score 關鍵字，可以安排後續的動作：





再按個 Run 就會跑，跑完就可以看資料囉：









你以為就這樣而已嗎？其實還可以在串成 Web service api ... 這段就請直接看完導覽就知道了，在此單純文字紀錄上述流程。

透過 Git Submodule 和 CMake 使用 GoogleTest

大概兩年前有練習一下 [CPP] Unit Test for C++ Usage via Google Test，連假來複習一下，著重在 CMakefile 的使用：

$ cd /path/project
$ git submodule add https://github.com/google/googletest/tree/master/googletest 3rdParty/googletest

$ vim CMakeLists.txt
...

add_subdirectory(3rdParty/googletest)
add_executable(your_unit_test
your_unit_test.cpp
)
add_dependencies(your_unit_test gtest)
include_directories(${gtest_SOURCE_DIR}/include)
target_link_libraries(your_unit_test
gtest
)

Nginx 筆記 - 對於 requests 重導與 request_uri 變數 encode/decode 問題

事情是這樣的，有個服務應用配置 web server (www.localhost) 跟 app server (api.localhost) 時，有這這樣的特性：

網站首頁：http://www.localhost/
前端API：http://www.localhost/api/
真正API：http://api.localhost/

例如有一則 API 服務 http://www.localhost/api/helloworld 必須重導至 http://api.localhost/helloworld 才行。由於 web server 主要服務 static files 而 app server 則是服務 api ，但所有入口點都是在 web server，因此就需要將 requests 導向給後方的 app server：

upstream backend_hosts { server api.localhost:80; }

set $request_url $request_uri;
if ($uri ~ ^/api(.*)$ ) {
set $request_url $1;
}
location ^~ /api/ {
proxy_read_timeout 300;
proxy_set_header Host $http_host; proxy_redirect off;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_pass http://backend_hosts$request_url$is_args$args;
proxy_buffering off;
}

這樣，看似無限美好，但是有個關鍵要留意，上頭有做一次字串處理：set $request_url $1; 這一步把 /api/helloworld 轉成 /helloworld 沒錯，但 $request_url 變數連帶處理了解碼的問題，當整個 requests 只是常見的英數符號時，都是一切正常的，但如果是帶有中文字等需要做 URLEncode/Decode 時，就會出包啦。

$ sudo apt-get install nginx
$ sudo vim /etc/nginx/conf.d/www.conf
upstream backend_hosts { server 127.0.0.1:8000; }

# Web Server
server {
        listen 3000;

        set $request_url $request_uri;
        if ($uri ~ ^/api(.*)$ ) {
                set $request_url $1;
        }
        location ^~ /api/ {
                proxy_read_timeout 300;
                proxy_set_header Host $http_host; proxy_redirect off;
                proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
                proxy_pass http://backend_hosts$request_url$is_args$args;
                proxy_buffering off;
        }

        location ^~ / {
                add_header X-WWW-Request-URL $request_url;
                add_header X-WWW-Request-URI $request_uri;
                return 200;
        }
}
# APP Server
server {
        listen 8000;

        location ^~ / {
                add_header X-API-Request-URI $request_uri;
                return 200;
        }
}
$ sudo service nginx configtest && sudo service nginx restart

接著，使用 curl 來驗證：

$ curl -s -D - 'http://127.0.0.1:3000/'
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Content-Type: application/octet-stream
Content-Length: 0
Connection: keep-alive
X-WWW-Request-URL: /
X-WWW-Request-URI: /

看見 X-WWW-Request-URL 和 X-WWW-Request-URI 代表為 3000 port 服務。

$ curl -s -D - 'http://127.0.0.1:3000/api/helloworld?abc=123'
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Content-Type: application/octet-stream
Content-Length: 0
Connection: keep-alive
X-API-Request-URI: /helloworld?abc=123

可以看到 X-API-Request-URI: /helloworld?abc=123 時，代表有將 requests 導向到 8000 port 的服務。

接著，試試看URLEncode吧！

$ curl -s -D - 'http://127.0.0.1:3000/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A'          
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Content-Type: application/octet-stream
Content-Length: 0
Connection: keep-alive
X-WWW-Request-URL: /%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A
X-WWW-Request-URI: /%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A

在 3000 port 服務中，X-WWW-Request-URL 和 X-WWW-Request-URI 一致是非常正常，但是，換成 /api/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A 就會出包啦：

$ curl -s -D - 'http://127.0.0.1:3000/api/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A'
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Content-Type: application/octet-stream
Content-Length: 0
Connection: keep-alive
X-API-Request-URI: /%e4%bd%a0%e5%a5%bd%e5%97%8e%0d

仔細看會發現 X-API-Request-URI 缺少 %0A 的資料，並且看到 request_uri 有點像是被重新組合過，從原本的大寫變小寫了，但最重要的是有缺資料！

而解法？就是改用 rewrite 來避開了，但是對於這種 URLEncode 的，還必須有前置處理，例如定義新的服務網址： /api/urlencode/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A 用法，接著更新 nginx 設定檔：

upstream backend_hosts { server 127.0.0.1:8000; }

server {
        listen 3000;

        set $request_url $request_uri;

        if ($request_uri ~ ^/api(.*)$ ) {
                rewrite ^ $request_uri;
                rewrite ^/api(.*)$ $1;
                set  $request_url $1;
        }

        location ^~ / {

                add_header X-WWW-Request-URL $request_url;
                add_header X-WWW-Request-URI $request_uri;
                return 200;
        }

        location ^~ /urlencode {
                proxy_read_timeout 300;
                proxy_set_header Host $http_host; proxy_redirect off;
                proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
                proxy_pass http://backend_hosts$request_url;
                proxy_buffering off;
        }
}

server {
        listen 8000;

        location ^~ / {
                add_header X-API-Request-URI $request_uri;
                return 200;
        }
}

如此一來，就會看到 URLENCODE 資訊是沒有被解析過，完整的 pass 到 8000 port 服務囉

$ curl -s -D - 'http://127.0.0.1:3000/api/urlencode/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A'
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Content-Type: application/octet-stream
Content-Length: 0
Connection: keep-alive
X-API-Request-URI: /urlencode/%E4%BD%A0%E5%A5%BD%E5%97%8E%0D%0A

[Linux] 透過 du + sort 指令，查詢資料儲存最大量的目錄 @ Ubuntu 14.04

還滿常用 du -hd1 的指令，可以列出當下目錄的使用情況，且只列出一層子目錄。然而，列完後又想要排序，以此更快抓到問題 :P 發現 sort -h 指令頗好用：-h, --human-numeric-sort, compare human readable numbers (e.g., 2K 1G)

所以連續技：

$ du -hd1
...
13M     ./Cache
552M    ./Uploads
340K    ./css
1GB     .

$ du -hd1 | sort -h
...
340K    ./css
13M     ./Cache
552M    ./Uploads
1GB     .