【SQL】完成數據平移，LEAD()和LAG()函數用法一次看

---

前言

今天要跟大家分享，M作者前陣子考SQL白板題時，遇到顧客留存率(customer retention)的題目。

由於考試當下比較緊張，因此使用JOIN的方式「暴力解題」，事後與考官討論才想起原來可以使用SQL的LEAD/LAG函數。而LEAD/LAG函數除了用來計算顧客留存率之外，平時寫SQL若要計算成長率、比較前後時間的數字變化等都可以使用。

那麼，如果你準備好就跟我們一起來看今天的SQL分享吧！

---

LEAD/LAG函數基礎介紹

LEAD/LAG函數屬於SQL中的Window Function(視窗函數)，這兩個函數的主要功能是協助數據平移。

聽起來很抽象嗎? 讓我舉個簡單的例子說明，假設有張buyer表，欄位分別是月份(month)、買家人數(buyer_count)，如下圖：

如果你想再新增一欄，查看上個月的買家人數，那麼你可以使用LAG函數。以較直觀的方式來解釋，可以說LAG函數把數據向下平移（見下圖箭頭）。

寫成SQL的語法如下：

SELECT *
, LAG(buyer_count,1,0) OVER (ORDER BY buyer_cnt_last_month) AS buyer_count_last_month
FROM buyer

以2022/11/1為例，這樣你可以快速對比這個月的買家人數(3300人)，以及上個月(10月)的買家人數(300人)。

---

反之，如果你想查看下個月的買家人數，那麼可以使用LEAD函數。同樣以直觀的方式解釋，可以說LEAD函把數據向上平移（見下圖箭頭）。

寫成SQL的語法如下：

SELECT *
, LEAD(buyer_count,1,0) OVER (ORDER BY buyer_count_last_month) AS buyer_count_next_month
FROM buyer

如此一來，你就可以在結果中比較前後一個月的數字了。

你發現了嗎? LEAD/LAG函數最常被使用的情景是用來進行前後時間對比。下面我們會分別介紹這兩種函數要怎麼使用，以及它的基礎、進階使用方式。

語法

由於LEAD/LAG函數的語法相同，只是平移方向不同，因此這邊我們只列出LEAD函數的語法（LAG函數的語法只要替換LEAD就可以囉）。

• LEAD(指定函數,平移列數, 預設值)：指定函數是放要被平移的欄位；位移平移列數若不寫默認為1；預設值為若平移後沒有東西，所填入的值。
• 搭配ORDER BY 使用: LEAD () OVER (ORDER BY clause)
• 搭配PARTITION BY 使用: LEAD () OVER (PARTITION BY clause ORDER BY clause)

---

---

LEAD/LAG函數怎麼使用

基礎用法1：前後時間對比 ORDER BY

LEAD/LAG函數的基礎用法會搭配ORDER BY使用，ORDER BY指的是「排序」。

從下方buyer表來看，如果下ORDER BY month 代表按照月份升序(ascending)排序，ORDER BY month DESC則代表按照降序(descending)來進行排序。

小提醒：ORDER BY後面若不寫ASC或是DESC會默認為ASC(升序)，因此如果要降序排序記得要放上DESC喔!

因此，如果要看本月、上個月、下個月的對應購買人數，SQL可以這麼寫：

SELECT *
, LEAD(buyer_count,1,0) OVER (ORDER BY buyer_cnt_last_month) AS buyer_count_next_month
, LAG(buyer_count,1,0) OVER (ORDER BY buyer_cnt_last_month) AS buyer_count_last_month
FROM buyer

可以得到以下結果：

---

進階用法1：使用 PARTITION BY

LEAD/LAG函數的進階用法除了搭配前面提到的ORDER BY之外，也會跟PARTITION BY一起使用。下面幫大家整理下用法：

• PARTITION BY: 用於分群。以下方這張cluster表為例，如果是PARTITION BY cluster，代表將不同cluster進行分群（FMCG一群、Fashion一群）。
• ORDER BY: 用於排序。以同張表為例，如果是ORDER BY month，代表按照不同月份升序排序。

如果綜合以上兩者，使用PARTITION BY cluster ORDER BY month，那就是先針對cluster進行分群，再按照時間排序。

看到這裡，你可能會問：如果我只下ORDER BY month，不下PARTITION BY 會發生甚麼情況呢? 這樣後續在使用LEAD/LAG函數抓取月份時，SQL就不知道你要抓的是FMCG的月份還是fashion的月分。

寫成SQL語法如下：

SELECT *
, LAD(buyer_count,1,0) OVER (PARTITION BY cluster ORDER BY month) AS last_month_buyer
, LEAD(buyer_count,1,0) OVER (PARTITION BY cluster ORDER BY month) AS next_month_buyer
FROM buyer

最後會得到以下結果：

附帶一提，如果要計算成長率，下面提供幾點作法：

• 使用上面的SQL寫法，導出數字後將兩者相除-1
• 從SQL語法進行調整：

-- growth_rate為計算本月的成長率(上個月買家數/本月買家數-1)
SELECT *
, LAD(buyer_count,1,0) OVER (PARTITION BY cluster ORDER BY month) AS last_month_buyer
, LEAD(buyer_count,1,0) OVER (PARTITION BY cluster ORDER BY month) AS next_month_buyer
, buyer_count / LAD(buyer_count,1,0) OVER (PARTITION BY cluster ORDER BY month) -1 AS growth_rate
FROM buyer

---

---

進階用法2：計算顧客留存率

最後，我們要來計算今天文章的重點，也就是顧客留存率(customer retention rate)。

這邊定義一下什麼是顧客留存率，假設上個月到店裡購買的人數有100人，在這100人當中，有30個人這個月又來店裡消費，這時你可以說本月的顧客留存率為30%(30/100=30%)。

下面這張buyer表，列出各buyer_id購買的時間，那麼要如何求出各月的顧客留存率呢?（連續兩個月都有買才算）

下面的query我們拆成幾步來看：

1. 子查詢(subquery)：用LAG函數求出每個buyer_id上次的購買時間。
2. CASE WHEN：利用last_month = grass_month – interval ‘1’ month，求出本月&上個月都有買的人。
3. 最後將CASE WHEN算出的人數除以本月購買的人就可以計算出顧客留存率。

SELECT 
    grass_month
    , COUNT(DISTINCT buyer_id) AS current_buyer
    , COUNT(DISTINCT CASE WHEN last_month = grass_month - interval '1' month THEN buyer_id END) AS last_month_buyer
    , COUNT(DISTINCT CASE WHEN last_month = grass_month - interval '1' month THEN buyer_id END) / COUNT(DISTINCT buyer_id) AS rate
FROM 
    (
    SELECT
        grass_month
        , buyer_id
        , LAG(grass_month) over (PARTITION BY buyer_id ORDER BY grass_month) AS last_month
    FROM buyer        
    )
GROUP BY 1

以上是本次的SQL函數分享，後續這個系列除了會分享給各位小讀者SQL語法，也算是M作者日常工作的小筆記。

因此，如果你有更好的方法，或是對於文中的語法有建議的地方，都歡迎來信(themurphymofei@gmail.com)與我進一步討論！