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

以 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 有幾個。