今天的文章要跟大家分享寫 SQL 遇到條件式判斷情境時,使用 CASE WHEN 函數寫法。什麼是條件式判斷?相信讀者們在使用 Excel 或 Google Sheet 都曾遇過 IF…THEN….ELSE… 的邏輯判斷,而 CASE WHEN 函數簡單來說就是 IF 條件判斷的概念。
那麼廢話不多說,接下來會跟大家介紹 CASE WHEN 的語法,並說明幾種寫 SQL 時常見的用法與情境。
在正式開始介紹前,你可以點選連結,選擇左上角的檔案>建立副本,copy 數據出來一起做練習。
CASE WHEN 語法
上面了解 CASE WHEN 的邏輯後,這邊要介紹 CASE WHEN 的語法,可以寫成以下兩種:
-- 寫法 1 墨菲習慣使用這種
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
[WHEN.......]
ELSE result
END;
-- 寫法 2
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
[WHEN.......]
ELSE result
END;
如果你省略 ELSE 子句,且沒有相符的條件,則會返回 NULL。下面我們直接帶大家看範例解釋。
基礎用法
附圖 user 表是某商店的用戶購買資料。假設我們想求:各性別對應的用戶人數,該怎麼做呢?
--寫法1
SELECT
CASE
WHEN gender = 1 THEN 'Male'
WHEN gender = 2 THEN 'Female'
WHEN gender = 3 THWN 'Others'
ELSE 'Others'
END AS gender
, COUNT(user_id) AS users
FROM user
GROUP BY 1
或者你也可以寫成:
--寫法2
SELECT
CASE gender
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 3 THWN 'Others'
ELSE 'Others'
END AS gender
, COUNT(user_id) AS users
FROM user
GROUP BY 1
這裡的 CASE WHEN 可以理解為 IF…THEN… 的意思,以白話說明則是:
- 當性別(gender)為 1 為男性(Male)
- 當性別(gender)為 2 為女性(Female)
- 當性別(gender)為 3 為其他(Others)
ELSE 代表如果性別非上述列舉的邏輯判斷(也就是它不是1或2或3),那麼則為其他 (Others);END 代表這一串邏輯判斷的欄位定義為 gender(性別)。
進階用法
CASE WHEN 級距用法
同樣使用上面的範例,如果我們想將用戶的年齡分成:18 歲以下、18~25 歲、26 ~ 40 歲、41~65 歲、65 歲以上這五個級距,分別查看對應級距的用戶人數該怎麼做呢?
--寫法 1
SELECT
CASE
WHEN age > 65 THEN 'age 65+'
WHEN age >= 41 THEN 'age 41~65'
WHEN age >= 26 THEN 'age 26~40'
WHEN age >= 18 THEN 'age 18~25'
ELSE 'age below 18'
END AS age
, COUNT(user_id) AS users
FROM user
GROUP BY 1
咦?看到這裡讀者可能會感到困惑:這樣寫不會重複計算嗎?
當我們寫出第一個 CASE WHEN age > 65 的子句,你可以想像 SQ L將 65 歲以上的人撈出來分為一類。那麼當你再寫出 WHEN age >= 41,因為 65 歲以上的人已經被撈出來了,所以 age >= 41 只剩下 41~65 歲,後面的子句就以此類推。
當然你也可以寫成:
--寫法 2
SELECT
CASE
WHEN age > 65 THEN 'age 65+'
WHEN age >= 41 AND age <= 65 THEN 'age 41~65'
WHEN age >= 26 AND age <= 40 THEN 'age 26~40'
WHEN age >= 18 AND age <= 25 THEN 'age 18~25'
ELSE 'age below 18'
END AS age
, COUNT(user_id) AS users
FROM user
GROUP BY 1
這兩種寫法在邏輯上是一樣的意思喔!
CASE WHEN 與 SUM() 合併
如果我們想知道:每個月各性別分別貢獻的 GMV 該怎麼做呢?(備註:此資料日期範圍是從 2023 1月 ~ 2023 3月)
SELECT
gender
, SUM(CASE
WHEN (grass_date BETWEEN date'2023-01-01' AND date'2023-01-31') THEN gmv
ELSE 0
END
) AS jan_gmv
, SUM(CASE
WHEN (grass_date BETWEEN date'2023-02-01' AND date'2023-02-28') THEN gmv
ELSE 0
END
) AS feb_gmv
, SUM(CASE
WHEN (grass_date BETWEEN date'2023-03-01' AND date'2023-03-31') THEN gmv
ELSE 0
END
) AS mar_gmv
FROM user
GROUP BY 1
假設今天的題目只要你求 1 月的 GMV 那你可以直接用 WHERE 的方式進行篩選,不過如果題目分別加了許多條件,想要一次求出就可以善用 CASE WHEN 與 SUM 合併使用。
小提醒:這個方法也適用於CASE WHEN和COUNT等AGGREGATE聚合函數的使用上喔
以上是本次文章 CASE WHEN 內容的介紹分享,如果你有任何問題都歡迎來信或是留言詢問交流喔!
Pingback: 莫非工具 | 文章導覽 - 墨菲莫非