SQL如何实现分组数据的跨行比较_使用窗口函数LEAD与LAG分析-SQL-PHP中文网

lead()和lag()必须配合over子句使用，且over中至少需order by，分组需加partition by；默认边界返回null，可用第三参数设默认值或case when过滤；排序字段须有索引以避免性能问题。

sql如何实现分组数据的跨行比较_使用窗口函数lead与lag分析

LEAD 和 LAG 函数怎么写才不报错

直接用 LEAD() 或 LAG() 却提示“窗口函数必须带 OVER 子句”？这是最常见的入门卡点。这两个函数不是独立表达式，必须配合 OVER() 定义窗口范围，否则语法直接失败。

典型错误写法：SELECT name, LAG(price) FROM sales; —— 缺少 OVER，必然报错 ERROR: window function requires an OVER clause。

OVER 至少要包含 ORDER BY：窗口依赖顺序，没排序就无法定义“上一行”或“下一行”
如果还要分组比较（比如每个用户单独看价格变化），得加 PARTITION BY user_id，否则全表混在一起算
别在 WHERE 中引用 LAG() 结果：窗口函数在逻辑执行顺序中晚于 WHERE，只能放在 SELECT 或 HAVING（配合聚合）里

跨行比较时 NULL 值怎么处理才合理

LAG() 查第一行的“前一行”，LEAD() 查最后一行的“后一行”，默认返回 NULL。但业务上往往需要区分“真缺失”和“边界值”。比如订单时间序列中，首单的“上一笔订单时间”为 NULL 是合理的；但若想计算间隔天数，直接 current_time - LAG(time) 会得到 NULL，整列变空。

用第三个参数指定默认值：LAG(order_time, 1, '1970-01-01') OVER (ORDER BY order_time)，避免空值污染后续计算
对时间差等场景，更稳妥的是用 CASE WHEN 过滤：CASE WHEN LAG(order_time) OVER (ORDER BY order_time) IS NOT NULL THEN order_time - LAG(order_time) OVER (ORDER BY order_time) END
注意数据类型匹配：LAG() 返回值类型和原列一致，不能拿 LAG(status) 直接跟字符串 'completed' 比较而不考虑大小写或空格

分组内比较必须用 PARTITION BY，但容易漏掉 ORDER BY

想查“每个用户的订单金额是否比上一笔高”，只写 PARTITION BY user_id 不够。如果没有 ORDER BY order_date，数据库无法确定哪一行是“上一笔”——同一用户的多笔订单可能按插入顺序、主键顺序或完全无序返回，结果不可控。

常见反模式：LAG(amount) OVER (PARTITION BY user_id) —— 缺少 ORDER BY，PostgreSQL 允许但行为未定义；MySQL 8.0+ 会报错；SQL Server 强制要求。

分组 + 排序必须同时存在：LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date, id)，末位补 id 防止时间相同时序不确定
排序字段要是能唯一标识行位置的组合，避免因重复值导致 LAG() 跳行或滞留
如果业务只要最新两笔对比，可先用子查询或 CTE 限制每组最多取 2 行，再用 LAG，避免全量窗口开销

性能隐患：ORDER BY 字段没索引时窗口函数很慢

当表有千万级订单，又按 user_id 分组、按 created_at 排序查 LEAD()，而 created_at 没索引，数据库就得对每组做全排序，I/O 和 CPU 消耗陡增。

关键索引应覆盖 PARTITION BY + ORDER BY 字段，例如：CREATE INDEX idx_user_created ON orders(user_id, created_at);
避免在 ORDER BY 中用函数，如 ORDER BY DATE(created_at)，会导致索引失效
如果只是检测相邻两行是否相等（如连续登录），可考虑用 ROW_NUMBER() 配合自连接，有时比窗口函数更快，尤其在低选择性场景下

真正难的不是写出 LAG，而是确认它跑在正确的分组和有序上下文中——顺序错了，结果就全偏了；索引缺了，查一次就卡住。