lag和lead是分组内跨行比较最简洁可靠的方式，但必须同时指定partition by和order by，缺一不可：前者划分独立计算域，后者确定行序；漏partition by会导致跨组错连，漏order by则行为未定义或报错。
直接说结论：LAG 和 LEAD 是解决分组内跨行比较最简洁、最可靠的方式，但必须配对使用 OVER 子句中的 PARTITION BY 和 ORDER BY，漏掉任意一个就会导致逻辑错乱或报错。
为什么 LAG/LEAD 必须写全 OVER 子句
这两个函数不是普通聚合函数，它们依赖窗口定义来确定“上一行”“下一行”是谁。只写 ORDER BY 不加 PARTITION BY，所有数据被当成一个大组排序，用户 A 的最后一笔订单可能和用户 B 的第一笔订单相邻——跨用户比较毫无业务意义。反之，只写 PARTITION BY 不加 ORDER BY，数据库无法判断哪行是“前”哪行是“后”，直接报错：Window frame must have ORDER BY。
常见错误现象：
- 结果中
LAG值跳跃混乱，比如同一用户两次订单时间倒序，LAG取到的是更晚的订单金额 - 首尾几行大量出现
NULL，但实际数据有足够上下文——大概率是ORDER BY列存在重复值且未加二级排序
实操建议：
- 分组字段（如
user_id）必须进PARTITION BY - 排序字段（如
order_date）必须进ORDER BY，若存在并列，追加唯一字段（如order_id）避免非确定性 - 不要省略
OVER，哪怕只写OVER (ORDER BY id)也比不写强
LAG 和 LEAD 的参数差异与默认行为
两者语法完全一致：LAG(column, offset, default)，但语义相反：LAG 向前取，LEAD 向后取。关键区别在于你关注的是“已发生”还是“将发生”。
参数说明：
-
offset：偏移行数，默认为1。设为2就是取上上条或下下条；但注意，若当前行距窗口边界不足offset行，结果为NULL（除非指定了default） -
default：仅当无对应行时生效。例如用LAG(amount, 1, 0)替代默认的NULL，可避免后续计算中因NULL导致整列变NULL - MySQL 8.0+、PostgreSQL、Oracle、SQL Server 2012+ 均支持三参数形式；旧版 SQL Server 或某些 Hive 版本可能不支持
default参数，需用COALESCE(LAG(...), 0)替代
典型误用：
- 写成
LAG(amount, 0)—— 没有意义，偏移 0 行就是自己，直接引用列即可 - 在趋势判断中混用：比如用
LEAD算“上月环比”，方向反了，结果全错
分组内连续行为识别（如连续 3 天下单）
单纯用 LAG 或 LEAD 只能拿到单侧值，要判断“连续”必须组合使用。核心思路是：把当前行、前一行、前两行（或后两行）拉到同一行，再用条件判断三者是否满足连续条件。
例如查连续 3 天下单的用户：
SELECT user_id, order_date,
LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_date,
LAG(order_date, 2) OVER (PARTITION BY user_id ORDER BY order_date) AS prev2_date
FROM orders
WHERE order_date - LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) = 1
AND LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date)
- LAG(order_date, 2) OVER (PARTITION BY user_id ORDER BY order_date) = 1;
注意点：
- 日期相减是否等于 1，取决于数据库类型（PostgreSQL 支持
date - date得整数天，MySQL 需用DATEDIFF()） - 不能在
WHERE中直接用窗口函数别名（如prev_date），必须重复写完整表达式或套一层子查询 - 连续判断对排序精度极度敏感：如果
order_date含时分秒，而业务只关心“日粒度”，务必先用DATE(order_date)归一化
性能与兼容性容易被忽略的点
LAG/LEAD 本身不拖慢查询，但不当使用会让优化器失效。最常被忽视的是 ORDER BY 列缺乏索引。
影响场景：
- 大表（千万级以上）上执行
PARTITION BY user_id ORDER BY create_time，若(user_id, create_time)无联合索引，排序开销极大 - 在
GROUP BY后再套窗口函数，部分引擎（如旧版 MySQL）会强制物化中间结果，内存暴涨 - Hive 中
LEAD在ORDER BY含NULLS LAST时行为不稳定，建议显式用COALESCE(col, '9999-99-99')排序
真实代价往往不在函数本身，而在它所依赖的排序和分组动作是否被索引覆盖。上线前务必看执行计划里有没有 Sort 或 WindowAgg 节点落在大表上。
