SQL筆記(6)，辨別連續事件與工作階段
久未更新，這次想來談個 SQL tricks — 如何從事件資料中定義出滿足某條件的一段期間，像是：連續每天簽到的期間、持續和網頁互動的期間（工作階段）。
連續觸發事件的期間 — 以每日簽到為例¹
給定我們有會員的簽到資料 (Fig.1 左)，我們想統計會員通常會連續簽到幾天 (像是會員 aaa 在 1/1–1/3 期間連簽了3天)，或是此時此刻有多少人已經連簽7天了，藉此定義活躍會員或是設計獎勵方案。原始的簽到紀錄不好直接回答此問題，但如果有每個「連續簽到期間」的組別 ID 就會簡單很多 — 以該組別 ID GROUP BY 或是作組內排序即可 (Fig.1 右)。
with T1 as (
select xdate, pid,
row_number() over(partition by pid order by xdate) re_index
from signed_record
)
select xdate, pid
concat(pid, datediff(xdate,'2021-12-31') - global_index) as consec_id
from T1想找出「連續簽到期間」的思路在於，設法找出這個期間，在某種運算下不變的值。首先，我們為每個人計算了簽到的次序 within_index 以及簽到日的絕對日期次序 absolute_index，後者可以是簽到日和任意日期的間隔天數，或是透過 unix_time 轉換為某個整數。這時我們可以觀察到，在連續簽到其間內，簽到次序和絕對日期次序的差距不變，這是因為你每次連續簽到，簽到次序和絕對日期次序同樣都會 +1。利用這點並結合會員 ID，我們就可以定義出每次連續簽到期間的 ID consec_id。
知曉這個做法後，用 SQL 實踐並不困難，大致上就是用 row_number() 算出每個會員的簽到次序 (會員內排序所以是 partition by pid，而排序依據是簽到日期 order by xdate)，接著用 date_diff() 算出絕對日期次序。兩者相減後再用 concat() 和會員 ID 結合成連續簽到期間的 ID。
辨別工作階段 Session
這次我們想要從事件流資料 (先只考慮頁面瀏覽 pageview) 中定義出工作階段 (Session)，如 Fig.3 的 session_id。所謂工作階段可以理解為和網站或某項服務互動的一段期間，當用戶在短時間內瀏覽了首頁、商品頁、購物車頁，這段期間的所有行為都應該被歸在同一個工作階段內 (session_id=1)，另外，間隔太久或閒置的話 (如：30分鐘)，就會被認定成不同的工作階段，像是 Fig.3 中的 session_id=1 和 2。關於工作階段，也可以參考 Google Analytics 4 的說明。²
with T1 as (
select pid, xtime,
lead(xtime) over(partition by pid order by xtime) as next_xdt,
lead(xtime) over(partition by pid order by xtime) - xtime as time_spent
from pv_record
),
T2 as (
select pid, xtime,
case when time_spent>1800 or time_spent is null then 1
else 0 end as if_leave
from T1
)
select pid, xtime,
sum(if_leave) over(partition by pid order by xtime
range between current row and unbounded following) as session_id -- 以下累積
from T2找出工作階段的思路在於：先標記出工作階段的開始或結束，再透過某種計算將這個標記轉換成工作階段的 ID。
所謂工作階段結束，就是你下一筆互動的時間跟目前互動的時間差了很久 (這裡用30分鐘為準)，或是查不到下一筆互動紀錄的時間。這部分我們透過 T1 的 lead(xdt) 去取得下一筆互動的時間，並在 T2 的 case 進行判斷。當標記出工作階段結束的互動時 session_end，我們在 sum() over() 做了一個看起來有點複雜的以下累積：
partition by pid：依照pid分組進行以下操作order by xtime：依照xtime排序range between current row and unbounded following：每筆資料加總的區間 (視窗) 為該列至該列以下，也就是以下累積
請留意，目前的 session_id 僅用於區辨個人的工作階段，倘若整份資料有其他人的話，工作街段應該是 pid與 session_id 的組合，換句話說若要統計不重複的工作階段數的話，應該是 distinct(pid, session_id) 。最後，這招也可以用於處理前段提到的連續簽到問題，各位可以試試。
Reference
[1] 參考自 https://stackoverflow.com/questions/26117179/sql-count-consecutive-days
[2] Google Analytic 4 關於工作階段的說明：關於定義；關於以工作階段定義的指標與維度
