SQL筆記(4)，用PIVOT做樞紐分析，以留存分析為例
SQL 的樞紐分析 PIVOT
這次要筆記的是 SQL 上的樞紐分析！樞紐分析簡言之就是將資料依多個變數或維度彙整，但呈現上通常將欲彙整的維度分成欄與列（寬表），如 Fig-1 分別將原資料彙整後（平均），再將 Name 和 Subject 這兩個維度分別彙整於欄與列。
說穿了，樞紐分析就是 GROUP BY 後，再將資料重新排列。但我們為什麼需要使用到樞紐分析呢？也許最常見的原因是為了將資料更好地呈現與解讀，後段提到的留存分析就是個好的例子。
PIVOT
以 Oracle 為例，樞紐分析的基本語法如下¹：
SELECT *
FROM (...)
PIVOT (
<agg_func>
FOR <col_var> IN (<col_v1>, <col_c2>, ...)
)<agg_func>要是一個聚合函數，像是AVG(<metric>)、COUNT(distinct(id))。FOR <col_var> IN (...)，表示要以<col_var>列舉在IN(...)的值分組作為「欄的維度」。以 Fig-1 的例子來說，就是FOR Subject IN ('Math','English','History')。- 最後，資料剩下的欄位（有可能不只一個），其值就會作為「列的維度」。而產出的樞紐分析表中，每一格即是依照欄與列的維度，以
<agg_func>彙整。以 Fig-1 的例子來說，就是A的Math平均分數為 80 分。 - 請注意，
IN(...)一定要明確表列，否則該值就不會被算入欄的維度，也就不會有代表該值的新欄位喔。
Example — 留存分析
我們以某個網站會員的留存分析為例，來實作 SQL 的樞紐分析吧！所謂的留存分析即是依世代 cohort（如：註冊日）、回訪期（如：註冊後的第 N 個禮拜）計算上站會員數，藉此觀察不同世代的會員隨時間推進，仍然活躍或流失的比例。使用資料如 Fig-2。
實際 SQL 如下（Oracle語法）。首先先看到內部的 SELECT ，我們將會員資料和上站紀錄合併，計算了回訪期 period（這邊算註冊後第 N 個禮拜），並且只追蹤註冊後 10 個禮拜的回訪。這邊我們基本已將樞紐分析 PIVOT 的資料準備妥當（沒有多餘或不足的欄位）。
再來我們設定樞紐分析表，首先這裡的彙整方式 COUNT(distinct(U_ID)) 即是去計算有多少上站會員²，而 FOR period IN (...) 則是代表我們欄的維度要依列舉的回訪期統計。最後資料的長相大致上會像是 Fig-3（稍作簡化的示意）。
SELECT *
FROM (
SELECT M.cdate cohort,
to_char(floor((DAU.vdate-M.cdate)/7)) period,
DAU.U_ID
FROM Member M
JOIN DAU ON M.U_ID=DAU.U_ID
WHERE floor((DAU.vdate-M.cdate)/7)<10
)
PIVOT (
COUNT(distinct(U_ID))
FOR period IN ('0','1','2','3','4','5','6','7','8','9')
)關於 Fig-3 的樞紐分析表（留存分析），由於第 1 期基本上反映了註冊會員，將第 N 期和第 1 期的上站人數相除便反映了註冊會員當中，有多少人到了第 N 期還活著。接著我們可以有幾種有趣的解讀方式：
- 從直欄跨世代比較某期的留存。如第 2 期的留存率逐世代下降，這可能代表整體網站的體驗是在下降，或是漸漸找不到喜歡本網站的客群了！
- 從橫列觀察某個世代，會員隨時間流失的情形。
- 從左下到右上拉一條線，大致能反應真實的時間，如：1/1註冊者的第 4 個禮拜、1/8 註冊者的第 3個禮拜，都是指向同個時間，這可以用來觀察現實中是否發生了特殊事件，如：網站壞掉或週年慶等。³
最後，實際上要怎麼定義世代與觀察期的長度（日、月等）；活躍與留存（有瀏覽、有貼文、有購物等）並沒有絕對的答案。但我覺得，與其為了讓指標沒有瑕疵而有過度複雜的定義，也許也該考量到這個指標設計是否簡潔且容易溝通。
[1] 儘管 MySQL 沒有現成的 pivot 語法，但仍然可以靠 CASE 搭配聚合函數去做條件彙整得到樞紐分析表。例如：avg(when Subject='Math' then Grade else null end) as Math。
[2] 我這邊計算會員數的方式是用 count(distinct())。因為會員可能會上站多天，而我的觀查期又是週，因此用 count() 的話很可能會重複計算同個會員。
[3] 如果世代和觀察期的時間長度不一致，定義與觀察時會有些 tricky。若世代和觀察期的時間長度一致，才能漂亮地看到，斜線經過的格子都指向同個真實時間。
[4] 我在 STATA 教學上也有一章在談 STATA 的樞紐分析，即 collapse 與 reshape 的應用。
