SQL筆記(5)，依狀態序列統計，以物流貨態 log 為例

在我使用各種資料的經驗中，除了遇到各種以人、交易為單位的表格與檔案外，也有一種以紀錄檔案中特定欄位變動的 log 檔，換句話說，當追蹤的欄位被建立、變更甚至刪除時，就會留下一筆變動紀錄。

像這樣的 log 檔，很常用來追蹤交易的結帳狀態、金物流狀態、貨物的上架狀態等，且相較於記錄現況的主檔案，log 檔提供了整個狀態變化的歷程，這不僅提供新類型的分析，像是：把每個狀態當常節點的路徑分析；也可以用來校正，像是：「現況」是認證賣家的人，從 log 檔可以發現他說不定大部分的時間其實都是停權狀態。

範例 — 物流狀態

物流的貨態是用來說明 log 檔的好例子，以下是一份關於物流 log 檔的範例：

log 資料示意

那麼，像這樣的資料可以怎麼應用呢？比如說好了，我想研究有多少交易因為「逾期取貨」而被退件，但從最終/目前的貨態卻無法精準地統計，因此我需要使用物流貨態的 log 檔，統計有多少交易踩過「逾期取貨」的狀態。要達成這件事的 SQL 如下：

SELECT T_ID,
  listagg(status, '-') within group(order by log_time) as status_log
FROM ship_log s
GROUP BY T_ID

那麼這段 SQL（以 oracle 為例）基本上就是以交易代碼 T_ID 分組並利用 listagg() 彙整，而 listagg() 的彙整則是把指定欄位的字串依指定順序排序，並以分隔符串接。

• 同筆交易內，依 log 時間排序，within group(order by log_time) 。
• 將貨態欄位 status 以 分隔符 - 串接在一起，listagg(status, '-') 。

log 資料彙整後的結果

最終撈取資料的結果如上。有了這樣的資料後，就可以再做後續的分析，像是：用 status_log like '%逾期未取%'，去篩選踩過「逾期未取」狀態的人，或是用 status_log 再去 GROUP BY，統計看看正常的貨態路徑佔多少比例、有無異常的貨態路徑發生。

最後，MySQL 的可以參考 GROUP_CONCAT()

SELECT T_ID, 
 GROUP_CONCAT(status ORDER BY log_time SEPARATOR '-')
FROM ship_log_test
GROUP BY tid

Reference

1. w3school MySQL，group_concat() 說明