SQL筆記(5),依狀態序列統計,以物流貨態 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, '-')
。
最終撈取資料的結果如上。有了這樣的資料後,就可以再做後續的分析,像是:用 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