[數據分析#31] SQL CASE WHEN 5 種最實用寫法

什麼情境下會使用 CASE WHEN 函數？

當我們需要針對不同條件，回傳不同的結果，就會需要用到 CASE WHEN，CASE WHEN 跟 Excel 或是 Google Sheet 裡面的 IF 函數很像，對比一下就知道：

• IF(條件 = ?, THEN 回傳 A, ELSE 回傳 B)，在 Excel 或是 Google Sheet 中，可以寫如果儲存格裡的值是 Female，就回傳女生，不是的話，就回傳男生
  • IF(第一行 = “Female”, “女生”, “男生”)
• CASE WHEN 條件 = ? THEN 回傳 A ELSE 回傳 B，在 SQL 語法中，可以寫如果值是 Female，就回傳女生，不是的話，就回傳男生
  • CASE WHEN 第一行 = “Female” THEN “女生” ELSE “男生” END

所以其實 CASE WHEN 跟 IF 是一樣的，只要在 Excel 中有學過 IF，SQL 裡的 CASE WHEN 應該就能輕易上手

CASE WHEN 函數怎麼寫？5 種商業常用舉例

範例資料集介紹：customer

• customer 這份顧客資料集，包含以下資料：
  • 日期 (grass_date)
  • 顧客編號 (id)
  • 年齡 (age)：以數字儲存
  • 性別 (gender)：以數字儲存，2 為男性，4 為女性，1 為未知
  • 訂單數量 (orders)：以數字儲存，購買訂單數量

grass_date	id	age	gender	orders

• 資料集、所有寫法的結果、小撇步都會在：這邊，點一下就可以拷貝出來使用囉！

寫法一：基礎 CASE WHEN

--CASE WHEN v1
SELECT 
distinct id
, CASE
    WHEN gender = 2 THEN 'Male'
    WHEN gender = 4 THEN 'Female'
    WHEN gender = 1 THEN 'Unknown'
		ELSE 'Unknown'
    END AS gender
, CASE
    WHEN age >= 0 and age < 40 THEN 'Young'
    WHEN age >= 40 THEN 'Elderly'
    ELSE 'Unknown'
    END AS age_range
FROM customer
ORDER BY gender, age_range ASC

寫法 2：CASE WHEN (1,0) 與 SUM() 與 GROUP BY

• 題目：我們想要知道不同性別的重度使用者有多少？
  • 第一步：先處理資料集，將資料集縮小成性別、顧客編號、加總訂單數
    • 如果想要知道如何處理子查詢，可以看看這篇文章
  • 第二步：選許資料集裡面的性別
  • 第三步：CASE WHEN 訂單數 > 10，回傳 1，其他就回傳 0，這個欄位判定顧客是不是重度使用者 (heavy_user)
  • 第四步：只要出現沒有計算的公式，都要 GROUP BY，因此 GROUP BY gender，也可以寫作 GROUP BY 1

SELECT 
gender,
SUM (CASE
    WHEN orders > 10 THEN 1
    ELSE 0
    END) AS heavy_user
FROM (
		--先處理資料集變成子查詢
    SELECT gender
    , id
    , sum(orders) as orders
    FROM customer
    GROUP BY 1,2
)
GROUP BY 1
ORDER BY 1

寫法 3：CASE WHEN (回傳原欄位) 與 SUM() 與 GROUP BY

• 題目：我們想要知道每個顧客在第一段活動期間 (1/1~1/4)、第二段活動期間 (1/5-1/9) 的訂單
  • 選取出顧客編號、性別的唯一值，用 DISTINCT 可以從資料庫中把唯一值取出
  • 第一段活動期間 (1/1~1/4)：CASE WHEN 在這段活動期間，就回傳原欄位中的 orders，不在活動期間就回傳 0，因此所有回傳的 orders 都是在第一段活動期間的，最後再把所以有回傳的 orders 加總
  • 第二段活動期間 (1/5~1/9)：CASE WHEN 在這段活動期間，就回傳原欄位中的 orders，不在活動期間就回傳 0，因此所有回傳的 orders 都是在第二段活動期間的，最後再把所以有回傳的 orders 加總

SELECT
distinct id
, gender
, SUM(CASE 
    WHEN grass_date >= date('2022-01-01') and grass_date < date('2022-01-05')
    THEN orders
    ELSE 0
    END) AS first_campaign_gmv
, SUM(CASE 
    WHEN grass_date >= date('2022-01-05') and grass_date < date('2022-01-10')
    THEN orders
    ELSE 0
    END) AS second_campaign_gmv
FROM customer
group by 1,2

寫法 4：寫法 3 的分解寫法

• 題目：我們想要知道各個性別在第一段活動期間 (1/1~1/4)、第二段活動期間 (1/5-1/9) 的訂單
  • 第一步：子查詢中處理第一段活動期間 (1/1~1/4) 與 第二段活動期間 (1/5~1/9) 的訂單
  • 第二步：把性別取出、加總第一段活動期間訂單、加總第二段活動期間訂單

SELECT
gender
, SUM(first_campaign_gmv) AS first_campaign_gmv
, SUM(second_campaign_gmv) AS second_campaign_gmv
FROM (
        SELECT
        id
        , gender
        , CASE 
            WHEN grass_date >= date('2022-01-01') and grass_date < date('2022-01-05')
            THEN orders
            ELSE 0
            END AS first_campaign_gmv
        , CASE 
            WHEN grass_date >= date('2022-01-05') and grass_date < date('2022-01-10')
            THEN orders
            ELSE 0
            END AS second_campaign_gmv
        FROM customer
)
group by 1

寫法 5：級距的寫法

• 題目：我們想要知道訂單以 5 為單位，每一個級距的客戶人數
  • 第一步：子查詢中處理每個客人的訂單總數
  • 第二步：以 CASE WHEN 來區分級距，並且計算出客戶人數
  • 小撇步：如果覺得級距很多，在 SQL 裡面一個一個寫很累的話，可以用 Excel 或是 Google Sheet 快速寫出整段 CASE WHEN，一樣在

SELECT
CASE 
    when orders <= 5 then '<5'
    when orders > 5 and orders <= 10 then '5-10'
    when orders > 10 and orders <= 15 then '10-15'
    when orders > 15 and orders <= 20 then '15-20'
    when orders > 20  then '>20' end as orders_tier
, COUNT(distinct id) count_user
FROM (
    SELECT
    id
    , sum(orders) orders
    from customer
    group by 1
)
GROUP BY 1

總結

CASE WHEN 在處理以下這幾種狀況時，特別實用：

1. 資料命名 (將數字轉為有意義的文字，像是性別轉換)：寫法一
2. 資料定義 (符合日期條件，才回傳值)：寫法二三四
3. 定義級距或是分群：寫法五

希望以上的舉例，對理解 CASE WHEN 的實際情況有幫助！

如何練習 SQL 函數？

1. 使用 Google BigQuery 沙盒功能
2. 匯入 Google Sheet 裡的資料集，之前有寫過一篇教學，如果不會的話可以看這裡：Google BigQuery 教學：3 分鐘完成資料匯入跟 SQL 查詢
3. 練習上面的所有寫法，output 出結果！

更多學習資源推薦

網路上的 SQL 學習資源很多，這裡會推薦我有使用過的 SQL 學習資源：

• Coursera：SQL for Data Science：英文課程，當初上完這門課之後，足夠應付 SQL 公司的上機考
• SQL 的五十道練習：初學者友善的資料庫入門：中文課程，老師在台大進修部教很多年 (都是數據分析相關的課程)，我的 R 也是跟老師學的，這次推出搭配很多練習題的 SQL 線上課程
• Google Data Analytics Professional Certificate：Google 認證的資料分析師證照課程有幫助嗎？ 誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！

如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 SQL 教學：