lag()必须配合over(partition by...order by...)显式定义窗口，否则跨组计算导致结果错误；需在select中使用，partition by与业务分组一致，order by须确定性排序，首行用coalesce处理，不可在group by后使用。
LAG函数在分组内计算前后行差值的正确用法
直接结论：LAG() 本身不支持“分组后自动重置”，必须配合 OVER (PARTITION BY ... ORDER BY ...) 显式定义窗口范围，否则跨组数据会混入计算，结果完全错误。
常见错误现象是：差值出现在不同用户、不同日期、不同设备 ID 之间，比如用户 A 的最后一条记录和用户 B 的第一条记录相减——这显然不是业务想要的“组内相邻变化”。
实操要点：
-
LAG()必须写在SELECT子句中，不能用于WHERE或GROUP BY -
PARTITION BY字段要和业务分组维度严格一致（如user_id、device_id、date::date） -
ORDER BY必须有确定性排序，推荐加上时间戳或自增 ID；仅用ORDER BY value可能因值重复导致排序不稳定 - 默认返回
NULL，需用COALESCE(LAG(...) OVER (...), 0)或类似方式处理首行
分组差分 vs 聚合后差分：别混淆两个阶段
很多人想先 GROUP BY 汇总，再对汇总结果做差，但这是两回事：LAG() 是窗口函数，作用于原始行级别；而 GROUP BY 是聚合阶段，二者不可互换。
典型误用场景：
- 想看每个用户每日登录时长的“相比昨日增长”，却先按
user_id, date分组求出日均时长，再试图在GROUP BY结果上套LAG()—— 这在语法上就非法（窗口函数不能出现在GROUP BY后的查询层级） - 正确路径只有一条：先用窗口函数算出差值（保持原始粒度），再按需
GROUP BY或过滤
示例（PostgreSQL）：
SELECT
user_id,
event_time::date AS day,
duration_sec,
duration_sec - COALESCE(LAG(duration_sec) OVER (
PARTITION BY user_id ORDER BY event_time
), 0) AS diff_from_prev
FROM user_sessions
WHERE event_time >= '2024-01-01';
性能与兼容性注意点
LAG() 在主流 SQL 引擎中都支持（PostgreSQL、Redshift、BigQuery、Snowflake、MySQL 8.0+、SQL Server 2012+），但 SQLite 和旧版 MySQL 不支持。
性能方面容易被忽略的问题：
- 未建索引的
ORDER BY字段会导致全表排序，尤其在大表上极慢；建议在(user_id, event_time)上建联合索引 - 如果
PARTITION BY字段基数极高（如百万级user_id），窗口计算开销会上升，此时可考虑先过滤再计算（如加WHERE user_id IN (...)） - 某些引擎（如 Hive）对复杂
OVER子句优化较差，避免嵌套窗口函数
差值为负/空时的业务含义必须人工确认
LAG() 计算的是“当前行减前一行”，但业务上可能需要“绝对变化量”或“是否上升”。数据库不会替你判断逻辑，比如：
- 温度传感器每分钟上报一次，
LAG()差值为负表示降温——这合理 - 但如果是“账户余额”，负值意味着支出，可能需要额外字段标识类型，不能只看数字
-
NULL不仅代表首行，也可能因ORDER BY字段为空或过滤条件导致缺失；建议用CASE WHEN LAG(...) IS NULL THEN 'first_record' ELSE 'delta'显式标记
最常被跳过的一步：没验证 PARTITION BY 分组是否真的把数据切开了。上线前务必抽样检查几个分组的前几行，确认 LAG() 返回值符合预期——这个动作比写十个 COALESCE 都重要。
