SQL 筆記(2),用CASE做條件彙整,以計算及格次數為例
條件彙整或聚合是個在分析資料上必備的小技巧,不管你習慣用哪個工具做資料分析,總有機會要產生較複雜的變數,像是:買家有幾次金額超過 2000 元的交易。這次我們要在 SQL 嘗試條件加總,也就是在 GROUP BY
上透過 CASE
添加條件,而 CASE
其實就是把條件對應到值的一個函數。以下是 CASE
的結構。¹ 請留意,CASE
的條件是由前往後適用的,如果條件 1、條件 2 同時適用的話,那麼最後就會被先指定成值 1。
CASE WHEN <條件1> THEN <值1>
WHEN <條件2> THEN <值2>
ELSE <其他值> END
條件彙整 (conditional aggregating)
以 Fig.1 左側的成績資料為例,假如我們希望逐 id 計算及格次數,那麼可以使用以下的 SQL 指令。這段 SQL 說穿了就是在彙整函數 sum()
內部塞入 CASE
,進而在條件下作加總,而產出結果如 Fig.1 右上所示。當然,除了 sum()
之外,也可以使用 avg()
、count()
、乃至 count(distinct())
等彙整函數。² 另外,當 sum()
用於 window function 時,也可以利用 CASE
做條件加總,結果如 Fig.1 右下。
最後,也許有人會想:為何不用 WHERE
直接篩選條件就好了呢?首先,不合條件的資料會直接排除,以 Fig.1 的資料為例,如果有人沒有半次及格的資料,他就會直接消失,而不是被彙整成 0 次及格;再來,我們可能會希望依多種不同的條件作彙整,但 WHERE
卻只能讓所有彙整函數適用一種條件。
-- 條件加總
SELECT id,
sum(CASE WHEN score>=60 THEN 1 ELSE 0 END) pass_times
FROM testcase1
GROUP BY id;
-- 條件加總 (window)
SELECT id,
sum(CASE WHEN score>=60 THEN 1 ELSE 0 END) over(partition by id) pass_times
FROM testcase1;
[1] 順帶一提,我的 STATA 教學中,STATA 上的資料探索也有提到條件加總。
[2] count(distinct())
這個鬼東西可以怎麼用在條件彙整上呢?我自己用的到的一個例子是:依不同註冊月份(cohort)計算,有多少人在註冊 7 天內完成了首次交易,而實際 SQL 大致長這樣 count(distinct(CASE WHEN trans_date — register_date<=7 THEN id ELSE null END))
,也就是數數看交易日在註冊日 7 天內的不重複 ID 有幾個。