在 SQL 中，偏移類窗口函數(shù) LAG() 和 LEAD() 用于訪問當(dāng)前行的前幾行或后幾行的值。

1.LAG()函數(shù)

LAG() 函數(shù)返回當(dāng)前行的前幾行的數(shù)據(jù)。

LAG(Expression, OffSetValue, DefaultVar) OVER (
    PARTITION BY [Expression]
    ORDER BY Expression [ASC|DESC]
);

• expression??: 你想要獲取的列或表達(dá)式。
• offset?? (可選): 你希望向前偏移的行數(shù)。默認(rèn)是 1，表示獲取前一行的數(shù)據(jù)。
• default_value?? (可選): 如果當(dāng)前行之前沒有足夠的行，返回的默認(rèn)值。默認(rèn)是 NULL，如果沒有設(shè)置 default_value，且當(dāng)前行是窗口的第一行或沒有前幾行數(shù)據(jù)時(shí)，返回 NULL。
• PARTITION BY?? (可選): 按某列分組計(jì)算窗口函數(shù)，類似于 GROUP BY。如果沒有此項(xiàng)，整個(gè)數(shù)據(jù)集視為一個(gè)窗口。
• ORDER BY??: 按照某列排序，確定偏移的順序。

Demo????????????：

表格數(shù)據(jù)??

sales 表，表結(jié)構(gòu)和數(shù)據(jù)如下：

Demo????：基礎(chǔ)用法

使用 LAG() 函數(shù)來獲取按月排序后的“revenue”列的前一行的值。

SELECT  id, 
		month, 
		revenue, 
		LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales;

Tips????：

• 第一行沒有前一行，所以 prev_revenue 為 NULL。
• 第二行的 prev_revenue 為第一行的 revenue 值（100）。
• 第三行的 prev_revenue 為第二行的 revenue 值（150）。

Demo????：帶偏移量的LAG()函數(shù)

使用 LAG() 函數(shù)，并指定偏移量為 2，獲取兩行之前的“revenue”值。

SELECT  id, 
		month, 
		revenue, 
		LAG(revenue, 2) OVER (ORDER BY month) AS prev_revenue
FROM sales;

Tips????：

• 第一行和第二行都沒有兩行之前的記錄，所以 prev_revenue 為 NULL。
• 第三行的 prev_revenue 為第一行的 revenue 值（100）。

Demo????：帶默認(rèn)值的LAG()函數(shù)

使用 LAG() 函數(shù)，并指定默認(rèn)值為 0，當(dāng)無法獲取前一行的值時(shí)返回默認(rèn)值。

SELECT  id, 
		month, 
		revenue, 
		LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales;

Tips????：

• 使用 LAG(revenue, 1, 0) 來獲取前一行的“revenue”值，如果沒有前一行則返回默認(rèn)值 0。
• 第一行沒有前一行，所以 prev_revenue 為 0。
• 第二行的 prev_revenue 為第一行的 revenue 值（100）。
• 第三行的 prev_revenue 為第二行的 revenue 值（150）。

Demo????：LAG()函數(shù)，比較每一天的銷售額與前一天的銷售額的差異。

SELECT
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS difference
FROM sales;

• LAG(amount, 1, 0)：這行的 LAG 函數(shù)表示獲取前一天（前一行）的 amount 列的值，如果前一天沒有數(shù)據(jù)（例如第一行），則返回 0。
• 通過 ORDER BY sale_date，確保按日期順序排列數(shù)據(jù)。

2.LEAD()函數(shù)

LEAD() 函數(shù)與 LAG() 類似，但它返回的是當(dāng)前行的后幾行的數(shù)據(jù)。

LEAD(Expression, OffSetValue, DefaultVar) OVER (
    PARTITION BY [Expression]
    ORDER BY Expression [ASC|DESC]
);

• expression??: 你想要獲取的列或表達(dá)式。
• offset?? (可選): 你希望向前偏移的行數(shù)。默認(rèn)是 1，表示獲取前一行的數(shù)據(jù)。
• default_value?? (可選): 如果當(dāng)前行之前沒有足夠的行，返回的默認(rèn)值。默認(rèn)是 NULL，如果沒有設(shè)置 default_value，且當(dāng)前行是窗口的第一行或沒有前幾行數(shù)據(jù)時(shí)，返回 NULL。
• PARTITION BY?? (可選): 按某列分組計(jì)算窗口函數(shù)，類似于 GROUP BY。如果沒有此項(xiàng)，整個(gè)數(shù)據(jù)集視為一個(gè)窗口。
• ORDER BY??: 按照某列排序，確定偏移的順序。

Demo????：基礎(chǔ)用法

使用 LEAD() 函數(shù)來獲取按月排序后的“revenue”列的后一行的值。

SELECT  id, 
		month, 
		revenue, 
		LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales;

Tips????：

• 第一行的 next_revenue 為第二行的 revenue 值（150）。
• 第二行的 next_revenue 為第三行的 revenue 值（200）。
• 第三行沒有后續(xù)行，所以 next_revenue 為 NULL。

Demo????：帶偏移量的LEAD()函數(shù)

使用 LEAD() 函數(shù)，并指定偏移量為 2，獲取兩行之后的“revenue”值。

SELECT  id, 
		month,
		revenue, 
		LEAD(revenue, 2) OVER (ORDER BY month) AS next_revenue
FROM sales;

Tips????：

• 使用 LEAD(revenue, 2) 來獲取兩行之后的“revenue”值。
• 第一行的 next_revenue 為第三行的 revenue 值（200）。
• 第二行和第三行都沒有兩行之后的記錄，所以 next_revenue 為 NULL。

Demo????：帶默認(rèn)值的LEAD()函數(shù)

使用 LEAD() 函數(shù)，并指定默認(rèn)值為 0，當(dāng)無法獲取后一行的值時(shí)返回默認(rèn)值。

SELECT id, month, revenue, LEAD(revenue, 1, 0) OVER (ORDER BY month) AS next_revenue
FROM sales;

Tips????：

• 使用 LEAD(revenue, 1, 0) 來獲取后一行的“revenue”值，如果沒有后一行則返回默認(rèn)值 0。
• 第一行的 next_revenue 為第二行的 revenue 值（150）。
• 第二行的 next_revenue 為第三行的 revenue 值（200）。
• 第三行沒有后一行，所以 next_revenue 為 0。

Demo????：LEAD()函數(shù)，比較每一天的銷售額與下一天的銷售額的差異。

SELECT
    sale_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_day_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY sale_date) - amount AS difference
FROM sales;

• LEAD(amount, 1, 0)：這行的 LEAD 函數(shù)表示獲取下一天（下一行）的 amount 列的值。如果下一天沒有數(shù)據(jù)（例如最后一行），則返回 0。
• 通過 ORDER BY sale_date，確保按日期順序排列數(shù)據(jù)。

最后再來一個(gè)小練習(xí)（lc會(huì)員題）：查找電影院所有連續(xù)可用的座位。

WITH t1 AS (
    SELECT
        seat_id,  -- 選擇座位ID
        free,  -- 選擇當(dāng)前座位的空閑狀態(tài)
        lag(free, 1, 999) OVER() AS pre,  -- 獲取當(dāng)前座位前一個(gè)座位的空閑狀態(tài)，默認(rèn)值為 999
        lead(free, 1, 999) OVER() AS next  -- 獲取當(dāng)前座位后一個(gè)座位的空閑狀態(tài)，默認(rèn)值為 999
    FROM Cinema  -- 從 Cinema 表中選擇數(shù)據(jù)
)

SELECT
    seat_id  -- 返回座位ID
FROM t1  -- 從 t1 子查詢中選擇數(shù)據(jù)
WHERE 
    free = 1  -- 當(dāng)前座位為空閑
    AND (pre = 1 OR next = 1)  -- 前一個(gè)座位或后一個(gè)座位為空閑
ORDER BY seat_id;  -- 按座位ID升序排序

思路：

1. lag(free, 1, 999) 和 lead(free, 1, 999):

   • lag(free, 1, 999) 用于獲取當(dāng)前座位前一個(gè)座位的 free 值（默認(rèn)為 999，表示沒有前一個(gè)座位）。
   • lead(free, 1, 999) 用于獲取當(dāng)前座位后一個(gè)座位的 free 值（默認(rèn)為 999，表示沒有后一個(gè)座位）。

2. free = 1 和 (pre = 1 OR next = 1):

   • 只選擇當(dāng)前座位是空閑的 (free = 1)。
   • 選擇那些前一個(gè)或后一個(gè)座位也是空閑的 (pre = 1 OR next = 1)，表示這些座位是連續(xù)空閑的。

3. ORDER BY seat_id:

   • 確保最終返回的結(jié)果按座位 ID 升序排序。

通過執(zhí)行查詢，得到的 t1 子查詢結(jié)果：

從 t1 中篩選出滿足 free = 1 且 (pre = 1 OR next = 1) 的行，得到的結(jié)果：

 到此這篇關(guān)于SQL偏移類窗口函數(shù) LAG、LEAD的用法小結(jié)的文章就介紹到這了,更多相關(guān)SQL偏移類窗口函數(shù) 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論