[數據分析#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_dateidagegenderorders
  • 資料集、所有寫法的結果、小撇步都會在:這邊,點一下就可以拷貝出來使用囉!

寫法一:基礎 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 學習資源:

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

【1 對 1 免費諮詢】數據分析師 0~1 培訓班陪你一路到轉職成功!

hi 我是 Lisa

你也是 0 經驗、沒有相關背景,但也想要轉職數據分析師,或運用數據分析,提升在領域的專業度嗎?

-

你是否也覺得,數據分析的網路學習資源很多,但是多到不知道從哪裡開始?

學了很多工具,但學完了,問題才真正開始,不知道業界怎麼使用?又該如何面臨工作跟面試?

-

我在 2019 年從營運專員的職位轉職到數據分析師

在這之後,因為數據分析,也讓我快速建構不同領域的知識:行銷、營運、商業策略、產品、網頁

轉職過程中,花了很多時間成本摸索

我發現,學數據分析最快的方式是:

  1. 確認自己跟市場需求之間的差距:訂定轉職策略及了解數據分析師的工作方式
  2. 以業界應用的角度學習工具:包括數據工具及分析用思維架構,並且如何高效運用
  3. 建立一個商業上的真實專案,並且得到回饋:打造屬於你及貼合職缺需求的作品集和專案
  4. 打造一個高效履歷跟面談:透過高價值包裝術拿下面試、成功獲得數據行業的門票

當初如果我有這套系統思維,應該可以在短時間內轉職,馬上拿到進入業界的門票

-

現在,我想把這套完整課程分享給你

這是專門為了 0 經驗、沒有相關背景的轉職者所設計

不需要去考研究所、不需要花大錢準備出國讀書

可以用最快的時間準備轉職、拿到門票!

-

如果你正在準備轉職數據分析師,且試圖找過解決辦法卻始終治標不治本!歡迎跟我預約免費 1 對 1 諮詢,我會在諮詢時,了解你的需求並且評估我是否可以協助到你!

電子書 職場人必學的數據分析術

Lisa Wu

Hi 大家好!我是喜歡探索身心靈健康及平衡的數據分析師 Lisa Wu!這個網站會幫助你:利用數據分析 (理性) 及 身心靈平衡 (感性) 的系統,去達成人生目標!如果你對我的人生系統工具有興趣,歡迎聯繫我:couplehonest@gmail.com

This Post Has One Comment

Comments are closed.