LAG() 和 LEAD() 是 SQL 中常用的窗口函数，核心作用是在同一结果集中，根据指定排序规则，获取当前行“前面”或“后面”某行的数据，无需进行自连接，极大简化了“跨行取值”的逻辑。
一、核心定义与语法
两者语法结构完全一致，仅功能相反（LAG 取前，LEAD 取后）。
基本语法
|
LAG(目标字段, 偏移量, 默认值) OVER ( PARTITION BY 分组字段 -- 可选：按某字段分组，组内独立计算 ORDER BY 排序字段 [ASC/DESC] -- 必须：定义“前后”的排序规则 ) AS 别名 LEAD(目标字段, 偏移量, 默认值) OVER ( PARTITION BY 分组字段 -- 可选 ORDER BY 排序字段 [ASC/DESC] -- 必须 ) AS 别名 |
参数说明
|
参数 |
作用 |
|
目标字段 |
要获取的“前/后行”的字段（如金额、日期、姓名等） |
|
偏移量 |
可选，默认值为 1，表示“前 1 行”（LAG）或“后 1 行”（LEAD） |
|
默认值 |
可选，当“前/后行不存在”时返回的值（如第一行用 LAG(1) 会返回 NULL） |
|
PARTITION BY |
可选，按指定字段分组，组内单独计算“前后行”（如按部门分组取员工数据） |
|
ORDER BY |
必须，定义组内数据的排序顺序，决定“前”和“后”的方向 |
二、典型应用场景（附示例）
假设存在表 sales，存储每日销售数据，结构如下：
|
date |
product |
amount |
|
2024-01-01 |
A |
100 |
|
2024-01-02 |
A |
150 |
|
2024-01-03 |
A |
200 |
|
2024-01-01 |
B |
80 |
|
2024-01-02 |
B |
120 |
场景 1：获取“上一行/下一行”数据（基础用法）
需求：查询每个产品的每日销售额，并显示“前一天销售额”和“后一天销售额”。
|
SELECT date, product, amount, -- 获取“同一产品”前 1 天的销售额，无则返回 0 LAG(amount, 1, 0) OVER ( PARTITION BY product -- 按产品分组（不同产品不互相影响） ORDER BY date ASC -- 按日期升序，“前”即“前一天” ) AS prev_day_amount, -- 获取“同一产品”后 1 天的销售额，无则返回 0 LEAD(amount, 1, 0) OVER ( PARTITION BY product ORDER BY date ASC -- 按日期升序，“后”即“后一天” ) AS next_day_amount FROM sales; |
结果（清晰看到每行与前后行的关联）：
|
date |
product |
amount |
prev_day_amount |
next_day_amount |
|
2024-01-01 |
A |
100 |
0 |
150 |
|
2024-01-02 |
A |
150 |
100 |
200 |
|
2024-01-03 |
A |
200 |
150 |
0 |
|
2024-01-01 |
B |
80 |
0 |
120 |
|
2024-01-02 |
B |
120 |
80 |
0 |
场景 2：计算“相邻行差值”（如日环比）
需求：按产品计算每日销售额的“环比增长额”（当日销售额 - 前一日销售额）。
|
SELECT date, product, amount, -- 当日金额 - 前一天金额 = 环比增长额 amount - LAG(amount, 1, 0) OVER ( PARTITION BY product ORDER BY date ASC ) AS day_on_day_growth FROM sales; |
结果：
|
date |
product |
amount |
day_on_day_growth |
|
2024-01-01 |
A |
100 |
100 |
|
2024-01-02 |
A |
150 |
50 |
|
2024-01-03 |
A |
200 |
50 |
|
2024-01-01 |
B |
80 |
80 |
|
2024-01-02 |
B |
120 |
40 |
场景 3：获取“间隔多行”的数据（自定义偏移量）
需求：查询每个产品的销售额，并显示“前 2 天”的销售额（偏移量设为 2）。
|
SELECT date, product, amount, -- 偏移量=2：取“前 2 天”的数据，无则返回 NULL LAG(amount, 2) OVER ( PARTITION BY product ORDER BY date ASC ) AS prev_2day_amount FROM sales; |
结果（2024-01-03 的 A 产品，前 2 天是 2024-01-01 的 100）：
|
date |
product |
amount |
prev_2day_amount |
|
2024-01-01 |
A |
100 |
NULL |
|
2024-01-02 |
A |
150 |
NULL |
|
2024-01-03 |
A |
200 |
100 |
|
2024-01-01 |
B |
80 |
NULL |
|
2024-01-02 |
B |
120 |
NULL |
三、关键注意事项
ORDER BY 必须存在：LAG/LEAD 依赖排序规则定义“前后”，缺少 ORDER BY 会报错或结果混乱。
PARTITION BY 分组隔离：无 PARTITION BY 时，全表视为一个“组”，跨行取值会跨越所有数据（如产品 A 和 B 的数据会互相取前后行）。
偏移量与默认值：偏移量必须为非负整数；默认值不指定时，“前后行不存在”会返回 NULL（可根据需求设为 0 或其他值）。
与自连接的区别：传统“跨行取值”需用自连接（如 a.date = b.date + 1），但 LAG/LEAD 代码更简洁、性能更高（尤其大数据量场景）。
总结
LAG() 和 LEAD() 是“跨行数据关联”的高效工具，核心用于：
计算环比、同比（相邻时间数据对比）
补全缺失的前后关联信息（如前/后订单、前/后员工数据）
简化复杂的行与行之间的逻辑对比
核心逻辑：按分组、定排序、取前后，即可灵活应对各类“跨行取值”需求。
扫一扫
53
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
