[數據分析#29] SQL LEAD() LAG() 函數怎麼寫？5 種用法教學

LEAD & LAG 可以說是視窗函數 (Window Function) 裡面，最好用的前幾名！這篇會跟大家分享，LEAD & LAG 要用在哪？

• 分享我在電商數據分析，最常用到的 2 個情境

LEAD & LAG 怎麼寫？

• 我會分享 5 種寫法，從簡單到進階
• 分享我的資料集跟練習題

LEAD & LAG 函數是什麼？

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

• LEAD & LAG 是 視窗函數 (Window Function) 的一種，SQL 函數主要分成 3 種：普通函數、聚合函數、視窗函數 3 種
  • 普通函數、聚合函數比較好理解，視窗函數稍微困難一點，不過在做分群分析的時候，是很好用的函數
  • 視窗函數 (Window Function) 簡單來說，他可以針對一個資料集做分割，再對分割後的資料做計算或是其他處理。例如：A 表中有 Lisa 的財務資料，財務資料中可以分成：吃、喝、玩、樂 4 種資料，Window Function 可以透過一段語法，就將吃、喝、玩、樂 4 種資料分割，再將吃的資料做排序或是再用聚合函數做計算
    • 視窗函數由 2 個主要函數組成：分割作用的 PARTITION BY 以及排序作用的 ORDER BY

LEAD & LAG 函數的作用是什麼？

• LEAD & LAG 函數會以使用者填入的 PARTITION BY 和 ORDER BY 進行分割跟排序
  • LAG 函數，例如 LAG(order)，就會利用 PARTITION BY 和 ORDER BY 進行分割跟排序後，排列好的資料集，列出前一筆 order 的數量
  • LEAD 函數，例如 LAG(order)，就會利用 PARTITION BY 和 ORDER BY 進行分割跟排序後，排列好的資料集，列出後一筆 order 的數量

PARTITION BY 和 ORDER BY 要做什麼？差別為何？

• PARTITION BY 用來分群，如果 PARTITION BY shop_id，就是把店家分成一群一群
• ORDER BY 用來排序，如果 PARTITION BY shop_id，然後 ORDER BY date，就是把店家分成一群一群，再以 date 排序
• 最後面會在進階寫法 4 說明這兩者的不同：
  • PARTITION BY shop_id ORDER BY date
  • ORDER BY shop_id, date
  • 如果可以懂這兩個的不同，接下來所有視窗函數 (Window Function) 都難不倒了！

什麼情境下會使用 LEAD & LAG 函數？

在處理電商的資料，以下幾個情境，都非常適合使用 LEAD & LAG 函數

• 前後時段的表現對比：算某一間店前後兩段時間的表現
  • 舉例：Lisa 咖啡店，我們已經算出1月、2月、3月的業績，我們可以用 LAG 函數取出這間店前一段時間的表現 (1月)，用 LEAD 函數取出這間店後一段時間的表現 (2月)，再列出現在這段時間的表現，就可以在 SQL 中就差異的處理 (Difference)

店家 (shop_id)	日期 (date)	業績
Lisa 咖啡店	1月	50萬
Lisa 咖啡店	2月	100萬
Lisa 咖啡店	3月	200萬

店家 (shop_id)	日期 (date)	現在業績 (current)	前一段時間業績 (previous)	後一段時間業績 (next)	差異 (Difference)
Lisa 咖啡店	2月	100萬	50萬	200萬	100萬-50萬

• 計算用戶頻次：在計算用戶頻次的時候，可以用 LEAD & LAG 函數帶出每個用戶上一次跟下一次的購買時間有多長
  • 舉例：Lisa 在 Lisa 咖啡店的消費情況
  • 最後，再算出 Lisa 的平均間隔天數就可以了，就會是 Lisa 平均 (31+16)/2=23.5，Lisa 平均 23.5 會去 Lisa 咖啡店消費一次

用戶 (user_id)	消費日期 (date)
Lisa	2022/1/1
Lisa	2022/1/31
Lisa	2022/2/15

用戶 (user_id)	消費日期 (date)	前一次消費日期 (date)	間隔天數
Lisa	2022/1/1	null	null
Lisa	2022/1/31	2022/1/1	31
Lisa	2022/2/15	2022/1/31	16

LEAD & LAG 函數怎麼寫？5 種好用的寫法舉例

範例資料集介紹：Sales

• Sales 這份資料集很簡單，只包含店家編號 (shop_id)、日期 (grass_date)、銷售訂單數量 (orders)

shop_id	grass_date	orders

最簡單的寫法：LEAD & LAG

• 取出某一天、前一天、後一天的訂單資料
• 用 LAG，ORDER BY 日期，可以取出前一天的訂單
• 用 LEAD，ORDER BY 日期，可以取出後一天的訂單

--最簡單的寫法
--分群的時候依照 grass_date, 依照日期帶出，前一次與下一次的業績
SELECT grass_date
, orders AS current_orders
, lag(orders) OVER (ORDER BY grass_date) AS previous_orders
, lead(orders) OVER (ORDER BY grass_date) AS next_orders
FROM sales

進階寫法 1：LEAD & LAG，ORDER BY 兩個條件以上

• 取出每一個店家 (先分一次群)、某一天、前一天、後一天 (再以日期做排序) 的訂單資料
• 用 LAG，ORDER BY 店家、日期，可以取出前一天的訂單
• 用 LEAD，ORDER BY 店家、日期，可以取出後一天的訂單

--進階寫法 1
--LEAD & LAG
--分群的時候依照 shop_id, grass_date 帶出，前一次與下一次的業績
SELECT shop_id
, grass_date
, lag(orders) OVER (ORDER BY shop_id, grass_date) AS previous_orders
, orders AS current_orders
, lead(orders) OVER (ORDER BY shop_id, grass_date) AS next_orders
FROM sales

店家編號	日期	前一天的訂單	今天的訂單	隔一天的訂單
shop_id	grass_date	previous_orders	current_orders	next_orders
37137599	2022/3/1		3267	4277
37137599	2022/3/2	3267	4277	9239
37137599	2022/3/3	4277	9239	2254

進階寫法 2：LEAD & LAG，ORDER BY 兩個條件後比對

• 取出每一個店家 (先分一次群)、某一天、前一天、後一天 (再以日期做排序) 的訂單資料
• 用 LAG，ORDER BY 店家、日期，可以取出前一天的訂單
• 用 LEAD，ORDER BY 店家、日期，可以取出後一天的訂單
• 計算每一個店家的成長狀況，拿某一天除以前一天的業績

--進階寫法 2
--LEAD & LAG
--分群的時候依照 shop_id, grass_date 帶出，前一次與下一次的業績
--最後再算出前後差 (difference) 以及 成長率 (growth)
SELECT shop_id
, grass_date
, LAG(orders) OVER (ORDER BY shop_id, grass_date) AS previous_orders
, orders AS current_orders
, orders - LAG(orders) OVER (ORDER BY shop_id, grass_date) AS difference
, (orders / LAG(orders) OVER (ORDER BY shop_id, grass_date)) -1 AS growth
FROM sales

shop_id	grass_date	previous_orders	current_orders	difference	growth
37137599	2022/3/1		3267
37137599	2022/3/2	3267	4277	1010	31%
37137599	2022/3/3	4277	9239	4962	116%
37137599	2022/3/4	9239	2254	-6985	-76%

進階寫法 3：LEAD & LAG，先分群 (CASE WHEN) 後再 ORDER BY 兩個條件

• 取出每一個店家、每一個月的業績分群
• 用 LAG，ORDER BY 店家、日期，可以取出前一天的業績分群
• 用 LEAD，ORDER BY 店家、日期，可以取出後一天的業績分群

--進階寫法 3
--LEAD & LAG & CASE WHEN
--子查詢裡面先算出這間店這個日期的訂單區間 (order_tier)
--最後再用 LEAD & LAG 列出這間店前後的訂單區間 (order_tier) 變化
SELECT shop_id
, grass_month
, order_tier AS current_order_tier
, lag(order_tier) OVER (ORDER BY shop_id, grass_month) AS previous_order_tier
FROM (
    SELECT shop_id
    , grass_month
    , CASE WHEN orders < 10 THEN 'order<1000'
            WHEN orders >= 10 THEN 'order>1000' ELSE 'no_order' END AS order_tier
    FROM sales
)

進階寫法 4：LEAD & LAG，PARTITION BY & ORDER BY 同時使用

• 下面兩段 SQL Query 的結果是一樣的，但是 SQL 在處理資料的時候使用 LEAD & LAG 的時候
• 只用 ORDER BY：不會先分群，直接排序
• 用 PARTITION BY & ORDER BY：先以 PARTITION BY 的指定欄位分群，在分群內以 ORDER BY 排序

--LEAD & LAG (只用 ORDER BY)
--進階寫法 2
SELECT shop_id
, grass_date
, LAG(orders) OVER (ORDER BY shop_id, grass_date) AS previous_orders
, orders AS current_orders
FROM sales

--LEAD & LAG (PARTITION BY & ORDER BY)
SELECT shop_id
, grass_date
, LAG(orders) OVER (PARTITION BY shop_id ORDER BY grass_date) AS previous_orders
, orders AS current_orders
, orders - LAG(orders) OVER (PARTITION BY shop_id ORDER BY grass_date) AS difference
FROM sales

如何練習 LEAD & LAG 函數？

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

更多學習資源推薦

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

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

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