SQL偏移类窗口函数—— LAG（）、LEAD（）用法详解
SQL偏移类窗口函数：LAG() 和 LEAD() 用法详解
在 SQL 中，偏移类窗口函数 LAG() 和 LEAD() 用于访问当前行的前几行或后几行的值。
1. LAG() 函数
LAG() 函数返回当前行的前几行的数据。
LAG(Expression, OffSetValue, DefaultVar) OVER (PARTITION BY [Expression]ORDER BY Expression [ASC|DESC]
);
- expression🍔: 你想要获取的列或表达式。
- offset🍟 (可选): 你希望向前偏移的行数。默认是 1，表示获取前一行的数据。
- default_value🍿 (可选): 如果当前行之前没有足够的行，返回的默认值。默认是
NULL，如果没有设置default_value，且当前行是窗口的第一行或没有前几行数据时，返回NULL。 - PARTITION BY🥓 (可选): 按某列分组计算窗口函数，类似于
GROUP BY。如果没有此项，整个数据集视为一个窗口。 - ORDER BY🥩: 按照某列排序，确定偏移的顺序。
Demo🍕🍕🍕🍕🍕🍕：
表格数据😎
sales 表，表结构和数据如下：
| id | month | revenue |
|---|---|---|
| 1 | Jan | 100 |
| 2 | Feb | 150 |
| 3 | Mar | 200 |
Demo🍕🍕：基础用法
使用 LAG() 函数来获取按月排序后的“revenue”列的前一行的值。
SELECT id, month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales;
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | NULL |
| 2 | Feb | 150 | 100 |
| 3 | Mar | 200 | 150 |
Tips🍬🍬：
- 第一行没有前一行，所以
prev_revenue为NULL。 - 第二行的
prev_revenue为第一行的revenue值（100）。 - 第三行的
prev_revenue为第二行的revenue值（150）。
Demo🍕🍕：带偏移量的 LAG() 函数
使用 LAG() 函数，并指定偏移量为 2，获取两行之前的“revenue”值。
SELECT id, month, revenue, LAG(revenue, 2) OVER (ORDER BY month) AS prev_revenue
FROM sales;
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | NULL |
| 2 | Feb | 150 | NULL |
| 3 | Mar | 200 | 100 |
Tips🍬🍬：
- 第一行和第二行都没有两行之前的记录，所以
prev_revenue为NULL。 - 第三行的
prev_revenue为第一行的revenue值（100）。
Demo🍕🍕：带默认值的 LAG() 函数
使用 LAG() 函数，并指定默认值为 0，当无法获取前一行的值时返回默认值。
SELECT id, month, revenue, LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales;
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | 0 |
| 2 | Feb | 150 | 100 |
| 3 | Mar | 200 | 150 |
Tips🍬🍬：
- 使用
LAG(revenue, 1, 0)来获取前一行的“revenue”值，如果没有前一行则返回默认值0。 - 第一行没有前一行，所以
prev_revenue为0。 - 第二行的
prev_revenue为第一行的revenue值（100）。 - 第三行的
prev_revenue为第二行的revenue值（150）。
Demo🍕🍕： LAG() 函数，比较每一天的销售额与前一天的销售额的差异。
SELECTsale_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函数表示获取前一天（前一行）的amount列的值，如果前一天没有数据（例如第一行），则返回0。- 通过
ORDER BY sale_date，确保按日期顺序排列数据。
| sale_date | amount | previous_day_amount | difference |
|---|---|---|---|
| 2025-01-01 | 100 | 0 | 100 |
| 2025-01-02 | 150 | 100 | 50 |
| 2025-01-03 | 200 | 150 | 50 |
| 2025-01-04 | 180 | 200 | -20 |
2. LEAD() 函数
LEAD() 函数与 LAG() 类似，但它返回的是当前行的后几行的数据。
LEAD(Expression, OffSetValue, DefaultVar) OVER (PARTITION BY [Expression]ORDER BY Expression [ASC|DESC]
);
- expression🍔: 你想要获取的列或表达式。
- offset🍟 (可选): 你希望向前偏移的行数。默认是 1，表示获取前一行的数据。
- default_value🍿 (可选): 如果当前行之前没有足够的行，返回的默认值。默认是
NULL，如果没有设置default_value，且当前行是窗口的第一行或没有前几行数据时，返回NULL。 - PARTITION BY🥓 (可选): 按某列分组计算窗口函数，类似于
GROUP BY。如果没有此项，整个数据集视为一个窗口。 - ORDER BY🥩: 按照某列排序，确定偏移的顺序。
Demo🍕🍕：基础用法
使用 LEAD() 函数来获取按月排序后的“revenue”列的后一行的值。
SELECT id, month, revenue, LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales;
| id | month | revenue | next_revenue |
|---|---|---|---|
| 1 | Jan | 100 | 150 |
| 2 | Feb | 150 | 200 |
| 3 | Mar | 200 | NULL |
Tips🍬🍬：
- 第一行的
next_revenue为第二行的revenue值（150）。 - 第二行的
next_revenue为第三行的revenue值（200）。 - 第三行没有后续行，所以
next_revenue为NULL。
Demo🍕🍕：带偏移量的 LEAD() 函数
使用 LEAD() 函数，并指定偏移量为 2，获取两行之后的“revenue”值。
SELECT id, month,revenue, LEAD(revenue, 2) OVER (ORDER BY month) AS next_revenue
FROM sales;
| id | month | revenue | next_revenue |
|---|---|---|---|
| 1 | Jan | 100 | 200 |
| 2 | Feb | 150 | NULL |
| 3 | Mar | 200 | NULL |
Tips🍬🍬：
- 使用
LEAD(revenue, 2)来获取两行之后的“revenue”值。 - 第一行的
next_revenue为第三行的revenue值（200）。 - 第二行和第三行都没有两行之后的记录，所以
next_revenue为NULL。
Demo🍕🍕：带默认值的 LEAD() 函数
使用 LEAD() 函数，并指定默认值为 0，当无法获取后一行的值时返回默认值。
SELECT id, month, revenue, LEAD(revenue, 1, 0) OVER (ORDER BY month) AS next_revenue
FROM sales;
| id | month | revenue | next_revenue |
|---|---|---|---|
| 1 | Jan | 100 | 150 |
| 2 | Feb | 150 | 200 |
| 3 | Mar | 200 | 0 |
Tips🍬🍬：
- 使用
LEAD(revenue, 1, 0)来获取后一行的“revenue”值，如果没有后一行则返回默认值0。 - 第一行的
next_revenue为第二行的revenue值（150）。 - 第二行的
next_revenue为第三行的revenue值（200）。 - 第三行没有后一行，所以
next_revenue为0。
Demo🍕🍕：LEAD() 函数，比较每一天的销售额与下一天的销售额的差异。
SELECTsale_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函数表示获取下一天（下一行）的amount列的值。如果下一天没有数据（例如最后一行），则返回0。- 通过
ORDER BY sale_date，确保按日期顺序排列数据。
| sale_date | amount | next_day_amount | difference |
|---|---|---|---|
| 2025-01-01 | 100 | 150 | 50 |
| 2025-01-02 | 150 | 200 | 50 |
| 2025-01-03 | 200 | 180 | -20 |
| 2025-01-04 | 180 | 0 | -180 |
最后再来一个小练习（lc会员题）：查找电影院所有连续可用的座位。
WITH t1 AS (SELECTseat_id, -- 选择座位IDfree, -- 选择当前座位的空闲状态lag(free, 1, 999) OVER() AS pre, -- 获取当前座位前一个座位的空闲状态，默认值为 999lead(free, 1, 999) OVER() AS next -- 获取当前座位后一个座位的空闲状态，默认值为 999FROM Cinema -- 从 Cinema 表中选择数据
)SELECTseat_id -- 返回座位ID
FROM t1 -- 从 t1 子查询中选择数据
WHERE free = 1 -- 当前座位为空闲AND (pre = 1 OR next = 1) -- 前一个座位或后一个座位为空闲
ORDER BY seat_id; -- 按座位ID升序排序
思路：
-
lag(free, 1, 999)和lead(free, 1, 999):lag(free, 1, 999)用于获取当前座位前一个座位的free值（默认为 999，表示没有前一个座位）。lead(free, 1, 999)用于获取当前座位后一个座位的free值（默认为 999，表示没有后一个座位）。
-
free = 1和(pre = 1 OR next = 1):- 只选择当前座位是空闲的 (
free = 1)。 - 选择那些前一个或后一个座位也是空闲的 (
pre = 1 OR next = 1)，表示这些座位是连续空闲的。
- 只选择当前座位是空闲的 (
-
ORDER BY seat_id:- 确保最终返回的结果按座位 ID 升序排序。
| seat_id | free |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
通过执行查询，得到的 t1 子查询结果：
| seat_id | free | pre | next |
|---|---|---|---|
| 1 | 1 | 999 | 0 |
| 2 | 0 | 1 | 1 |
| 3 | 1 | 0 | 1 |
| 4 | 1 | 1 | 1 |
| 5 | 1 | 1 | 999 |
从 t1 中筛选出满足 free = 1 且 (pre = 1 OR next = 1) 的行，得到的结果：
| seat_id |
|---|
| 3 |
| 4 |
| 5 |