[數據分析#32] 3 個寫法：搞懂 SQL 視窗函數 Window Function

視窗函數 (Window Function) 是什麼？

• 視窗函數 (Window Function) 是 SQL 在做資料處理的時候，跟 Excel 還有 Google Sheet 最大的不同！視窗函數 (Window Function) 可以在一個儲存格裡面儲存一串數字，並且對這一串數字做運算，不像一般的 Excel，如果需要運算，一個儲存格裡面只能儲存一個數值
• 視窗函數可以對資料集做排名、可以分群後運算，在某些情況下很實用，待會會一一舉例很實用的幾個情境

為什麼叫視窗函數 (Window Function)？函數的基本架構是什麼？

• 我們在做資料處理的時候，沒有直接的改變原有資料，而是開一個新的視窗去處理
• 視窗函數 (Window Function) 的基本架構會包含：運算函數( ) OVER (PARTITION BY XXX ORDER BY XXX)
  • OVER ( )：( ) 裡面就像視窗一樣，對資料集先做分群處理
    • PARTITION BY XXX：針對資料做分群
    • ORDER BY XXX：是針對分群後的資料作排序
    • PARTITION BY & ORDER BY 處理過的資料，會儲存在 OVER ( ) 這個視窗裡面
  • 運算函數 ( )：針對 OVER ( ) 這個視窗裡的資料，做運算，例如：加總、平均、貼上名次、或是其他進階的運算函數

--說明：視窗函數的架構
--來自：https://prestodb.io/docs/current/functions/window.html
function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

視窗函數 (Window Function) 怎麼用？

視窗函數 (Window Function) 的應用場景有哪些？

1. 依照條件，貼上排序：RANK ( )
2. 累積加總：SUM ( )
   1. 除了加總以外，其他運算也很好用，像是 AVG ( )
3. 查找前一個或是後一個值：LEAD ( )、LAG ( )
4. 在同個資料集中，做排序、加總等等：PARTITION BY & ORDER BY

範例資料集介紹：customer

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

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

寫法一：基礎視窗函數

• 題目：將資料集貼上號碼
  • 第一步：用 * 取出資料集所有的欄位
  • 第二步：
    • 以 row_number( ) 這個函數，為每一行貼上號碼
    • OVER ( ) 內放的是所有資料集
    • 小提醒：這裡沒有用 PARTITION BY & ORDER BY 的話，就是沒有幫資料集排序，純粹取出所有資料放到 OVER ( ) 這個視窗裡面

SELECT *, row_number() OVER () AS row_num
FROM customer

寫法二：在視窗內用 ORDER BY 排序後做不同的資料處理

• 題目：用視窗函數取出客戶訂單排行、前面一名、後面一名、然後用 percentile 分出 3 個群體
  • 第一步：先對資料集處理，取出每個客戶的訂單總數
  • 第二步：
    • 客戶訂單排行：用 row_number( )，over (order by orders) 內的資料集處理就是直接以 orders 做排序
    • 取出前一名以及後一名：可以用 lag(id, 1) 跟 lead(id, 1) 函數，詳情教學可以看我另外一篇的教學：SQL LEAD() LAG() 函數怎麼寫？5 種用法教學，這個函數算是比較進階，但是商業上處理資料很實用，很推薦學一下！
    • 分出 3 個群體：用 ntile ( ) 這個函數，要分三群的話就寫 ntile(3)
    • 小提醒： SQL 預設的 ORDER BY 順序是由小到大，如果想要從大到小，就寫 over (order by orders desc)，desc 就是 descending，下降的意思

with 

customer_agg as (
    select 
    distinct id
    , sum(orders) as orders
    from customer
    group by 1
)

--視窗函數的應用場景
select id
, row_number() over (order by orders) as rank -- 貼上排行
, lag(id, 1) over (order by orders) as previous --查找前一個值
, lead(id, 1) over (order by orders) as next --查找下一個值
, ntile(3) over (order by orders) as customer_group --用 percentile 取出分群
from customer_agg

寫法三：在視窗內用 ORDER BY、PARTITION BY 分群+排序後再做資料處理

• 題目：用視窗函數以性別做分群，取出每個性別內的訂單排名、還有後面一名是誰
  • 第一步：先對資料集處理，取出每個客戶的訂單總數、性別
  • 第二步：取出性別跟 id
  • 第三步：
    • 取出每個性別內的訂單排名：row_number() over (partition by gender order by orders)
      • partition by gender 就是以性別做分群，男生一群，女生一群，無性別一群
      • order by orders 就是在分群過後，再以 orders 做排序
    • 還有後面一名是誰：lag(id, 1) over (partition by gender order by orders)
      • 詳情教學可以看我另外一篇的教學：SQL LEAD() LAG() 函數怎麼寫？5 種用法教學
    • 小提醒： SQL 預設的 ORDER BY 順序是由小到大，如果想要從大到小，就寫 over (order by orders desc)，desc 就是 descending，下降的意思

with customer_agg as (
    select 
    distinct id
    , CASE
        WHEN gender = 2 THEN 'Male'
        WHEN gender = 4 THEN 'Female'
        WHEN gender = 1 THEN 'Unknown'
		ELSE 'Unknown'
    END AS gender
    , sum(orders) as orders
    from customer
    group by 1,2
)

select 
gender
, id
, row_number() over (partition by gender order by orders) as gender_rank
, lag(id, 1) over (partition by gender order by orders) as gender_previous
from customer_agg

總結

• 在正常的情況下，用最基礎的 SQL 函數就可以處理一般的商業情況：最白話的 SQL 語法教學攻略：10 個我最常用的
• 視窗函數 Window Function 可以跟一般好用的聚合函數 (聚合函數就是像 COUNT( )、AVG( ) 這類的函數) 做出很棒的配合，達到 1+1 > 2 的效果
• 再來簡單回顧一下視窗函數的幾個商業情境：
  • 在同個資料集中，做分群跟排序：PARTITION BY & ORDER BY
  • 依照條件，貼上排序、名次：RANK ( )、ROＷ_NUMBER()
  • 累積加總：SUM ( )，之後會出累積加總的文章給大家參考
  • 除了加總以外，其他聚合函數也很好用，像是 AVG ( )
  • 查找前一個或是後一個值，可以做顧客購買前後行為等等：LEAD ( )、LAG ( )
  • 資料集練習以及寫法結果 (output)

如何練習 SQL 函數？

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

更多學習資源推薦

• 視窗函數教學，這適合對 SQL 有基本了解：Advanced T-SQL: Window Functions for Advanced Querying

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

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

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