SQL 筆記(3)，分析有起訖時間的 session data，用 with as 生成日期序列並逐日彙整

這次我想要筆記如何對有起訖時間的 session data，逐時間點（如：逐日）做彙整。我這邊指涉的 session data 是以「一段期間」為單位的資料：每筆資料有起訖時間，期間內為某個狀態或有某項特質，例如：廣告投放期間、優惠活動期間等。而我曾分析過的勞保異動資料也是這種形式 —每筆資料是每個人投保狀態未更動的「一段期間」。假如說我從 202101 加保、202103 調薪、202107 退保，那麼我的資料就會記錄如下：

Fig.1 session data

我最終的目的是希望將 session data 轉變為以 date 為單位的資料，並逐日做投保人數、累積折扣之類的統計。要做到這件事大致會經過幾個步驟：

1. query 觀察區間內， 逐日的時間序列，如：2021–01–01 2021-01-02,...,2021-01-31 。
2. 合併時間序列與 session data，並進行彙整。其彙整方法有兩種，分別是「用不等式 Join並彙整資料」以及「以起訖日為流量，並透過以上累計轉換成存量」。

產生時間序列

Query 一段日期序列在 python 或某些程式語言上非常容易，但我覺得在 SQL 上並沒有那麼直觀。做了些搜尋後，我大致有找到幾種可用的語法

1. 藉由 connect by 產生階層式的資料，而日期的序列是其中一種應用（oracle）。
2. 藉由 with … as 、union all 遞迴地 query（oracle、MySQL，但語法有點差異）。¹

這邊我會先在 MySQL 上使用 with 的作法，範例如下。首先，with ... as ()叫做 common table expression（CTE），大致上就是可以讓你在後續的 query 中重複使用某段 query，像是這範例中的 d_seq ，這可以讓 SQL 語法更加可讀。再來，這裡藉由遞迴地使用 CTE 來產生日期序列 with recursive ... as() ，而 CTE 內部包含了幾個元素：

• 起始第一列的 query
• union all、 SELECT ... FROM d_seq：前列與下一列的關係（加一日）
• 終止條件 WHERE

以下例來說，我會產生欄位d，其值為2021-11-01 到 2021-11-30 的一串日期序列。

-- MySQL version (with as)
WITH RECURSIVE d_seq (d) AS
( 
  # 起始
  SELECT str_to_date('2021-11-01', '%Y-%m-%d') as d FROM dual
  
  # 遞迴、WHERE 終止條件
  UNION ALL
  SELECT d + 1 FROM d_seq
    WHERE d + 1 <= str_to_date('2021-11-30', '%Y-%m-%d')
)
SELECT * FROM d_seq

彙整 session data — JOIN

Fig.2 折扣檔期資料，彙整前後

假設我們要彙整的 session data 是 Fig.2 左的折扣檔期資料（disc_event），我們期待資料可以彙整成以日期為單位，統計每日的總折扣%數。首先，我使用上段 recursive CTE 產生的日期序列。接著，我將日期序列 d_seq 和折扣檔期資料合併 disc_event（條件為：當日期在折扣檔期的時間區間內）。² 最後，我再依日期進行彙整，呈現的資料如 Fig.2 右。

-- 逐日彙整折扣%數
WITH RECURSIVE d_seq AS
(
  SELECT str_to_date('2021-11-01', '%Y-%m-%d') as d FROM dual
  UNION ALL
  SELECT d + 1 FROM d_seq
    WHERE d + 1 <= str_to_date('2021-11-30', '%Y-%m-%d')
)
SELECT d, sum(discount) total_discount 
FROM d_seq
JOIN disc_event t1 on d>=t1.start_date and d<=t1.end_date
GROUP BY d;

彙整 session data — 累計加總 (202403 增)

相較於使用接近 Cross Join 並篩選合適資料的方法，另一個不那麼暴力的做法是把區間的起迄時間當成是類似「入帳」、「扣帳」事件或所謂「流量」資料，那麼我們的任務就是將「流量」資料轉換成「存量」資料，其方法就是對流量資料做以上累計：

-- 逐日彙整折扣%數
WITH RECURSIVE d_seq AS
(
  SELECT str_to_date('2021-11-01', '%Y-%m-%d') as d FROM dual
  UNION ALL
  SELECT d + 1 FROM d_seq
    WHERE d + 1 <= str_to_date('2021-11-30', '%Y-%m-%d')
),
st_event as (
select start_date, sum(discount) disc_plus
from disc_event
group by start_date
),
end_event as (
select end_date + interval 1 day as end_date, sum(discount) disc_subtract
from disc_event
group by end_date + interval 1 day
),
-- 流量資料
flow as (
select d, 
 coalesce(disc_plus,0) disc_plus, 
 coalesce(disc_subtract,0) disc_subtract, 
 coalesce(disc_plus,0)-coalesce(disc_subtract,0) disc_net
from d_seq
left join st_event st on d_seq.d=st.start_date
left join end_event ed on d_seq.d=ed.end_date
)
-- 轉換成每個時間點的存量資料
select d,
 sum(disc_net) over(order by d) as daily_disc_stock
from f;

下圖 Fig-3 可以更好的表示，我們怎麼得到最終結果。我們首先將期間資料依照 start_date end_date 進行彙整，得出每天的流量，也就是「開始折扣多少」、「停止折扣多少」；接著我們將其和日期序列合併並計算每日的淨流量；最後我們利用 sum() 搭配 window function 做以上累計，得到 Fig-3 的每日存量，也就是該天是用多少折扣額度。

Fig.3 折扣檔期資料，彙整前後 (方法2)

有個小地方要留意，因為假設折扣結束是發生在結束日的 23:59:59，因此折扣結束的事件其實是在隔日，因此我在 end_event 的計算上有再調整過日期。

另外一個要留意的地方是，這樣的算法假設資料起始的存量為 0，這顯然在許多情境並不合理，因此我們可以試著求取前一天的存量，再和後續幾天的流量結合。由於我們只要從 session data 中求取某一天的存量，這並不是一件太困難的事。³

[1] 更多 MySQL 上 with as 的用法可以參考官方文件。

[2] 另一種做法是 cross join 後，再用WHERE篩選日期在折扣檔期的時間區間內並做彙整。

[3] 這個方法是在和同事討論如何處理這類情境時逐漸釐清的，但其靈感應該是來自於同事D大的一張出帳儀表板。