LEAD与LAG函数的基础概念
LEAD和LAG是SQL标准中定义的窗口函数，主要用于在查询结果集中获取当前行相邻行的数据，无需进行表自连接操作。其中LAG函数用于获取当前行之前（向上）指定偏移量的数据，LEAD函数用于获取当前行之后（向下）指定偏移量的数据，二者都支持结合PARTITION BY子句实现分组内的跨行比较。
基础语法说明
LAG函数的语法结构如下：
LAG(目标列, 偏移量, 默认值) OVER (
PARTITION BY 分组列
ORDER BY 排序列
)
LEAD函数的语法结构与LAG类似：
LEAD(目标列, 偏移量, 默认值) OVER (
PARTITION BY 分组列
ORDER BY 排序列
)
参数说明：
- 目标列：需要获取的相邻行的列名
- 偏移量：可选参数，默认值为1，表示向上或向下偏移的行数，必须为非负整数
- 默认值：可选参数，当偏移后没有对应行时返回的默认值，不指定则返回NULL
- PARTITION BY：可选子句，用于指定分组的列，相同分组内的数据才会进行跨行比较
- ORDER BY：必选子句，用于指定分组内数据的排序规则，排序结果决定跨行比较的相邻关系
分组场景下的跨行比较实现
假设我们有一张用户订单表user_order，表结构如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| user_id | INT | 用户ID |
| order_id | INT | 订单ID |
| order_amount | DECIMAL | 订单金额 |
| order_time | DATETIME | 下单时间 |
现在需要统计每个用户的相邻两笔订单的时间间隔，以及相邻两笔订单的金额差值，就可以结合PARTITION BY按用户分组，ORDER BY按下单时间排序，使用LAG和LEAD函数实现。
示例1：计算同用户相邻订单的时间差
使用LAG函数获取每个用户上一笔订单的下单时间，再与当前订单的下单时间计算差值：
SELECT
user_id,
order_id,
order_time,
-- 获取同用户上一笔订单的下单时间，没有上一笔则返回NULL
LAG(order_time, 1) OVER (PARTITION BY user_id ORDER BY order_time) AS prev_order_time,
-- 计算当前订单与上一笔订单的时间差，单位为天
TIMESTAMPDIFF(
DAY,
LAG(order_time, 1) OVER (PARTITION BY user_id ORDER BY order_time),
order_time
) AS day_diff
FROM user_order
ORDER BY user_id, order_time;
示例2：计算同用户相邻订单的金额变化
使用LEAD函数获取每个用户下一笔订单的金额，再计算当前订单金额与下一笔订单金额的差值：
SELECT
user_id,
order_id,
order_amount,
-- 获取同用户下一笔订单的金额，没有下一笔则返回0
LEAD(order_amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS next_order_amount,
-- 计算当前订单金额与下一笔订单金额的差值
order_amount - LEAD(order_amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS amount_diff
FROM user_order
ORDER BY user_id, order_time;
使用注意事项
在使用LEAD和LAG函数进行分组跨行比较时，需要注意以下几点：
- 必须指定ORDER BY子句，否则分组内的数据顺序不明确，跨行比较的结果没有实际意义
- 偏移量参数只能为非负整数，如果需要获取当前行前第2行的数据，使用LAG(列名,2)，而不是LAG(列名,-2)
- 当偏移后没有对应数据时，若不指定默认值会返回NULL，进行数值计算时需要提前处理NULL值，避免结果异常
- 不同数据库对窗口函数的支持版本不同，比如MySQL从8.0版本开始支持LEAD和LAG函数，使用时需要确认数据库版本
LEAD和LAG函数的执行效率远高于传统的表自连接写法，尤其是在数据量较大的分组场景下，能够有效减少查询的IO消耗，提升查询性能。
常见应用场景扩展
除了上述订单场景，LEAD和LAG函数还可以应用于很多其他分组跨行比较场景：
- 统计同部门员工相邻两次绩效评分的变化情况
- 计算同商品连续两天的销量差值
- 检测同设备连续两次上报数据的异常波动
只要是需要分组内相邻行数据对比的需求，都可以优先考虑使用LEAD和LAG函数实现，能够大幅简化SQL逻辑，提升开发效率。