lag和lead是位移函数而非聚合函数，仅取相邻行指定字段值；必须配合partition by和order by使用，用于计算环比、差值等，首行/末行越界返回null，需用coalesce等显式处理。
LEAD 和 LAG 本质不是聚合函数，不能直接跨行“聚合”
很多人一看到“跨行计算”就默认要用聚合，但 LEAD 和 LAG 是位移函数（value shifting），只取相邻行的某个字段值，不参与求和、计数等聚合运算。想实现“分组内上一行销售额 × 1.2”可以，但“分组内上三行到当前行的平均销售额”就得组合 AVG() + ROWS BETWEEN 窗口帧，而不是靠 LAG。
正确写法：先用 LAG/LEAD 取值，再在外部或子查询中做运算
典型场景是计算环比、差值、变化率。例如按日期排序，取每个分组（如 product_id）内上一条记录的 price：
SELECT product_id, sale_date, price, LAG(price) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_price, price - LAG(price) OVER (PARTITION BY product_id ORDER BY sale_date) AS price_diff FROM sales;
注意点：
-
LAG(price, 1)和LAG(price)等价，默认 offset=1；LAG(price, 2)才是取上两行 - 必须显式写
PARTITION BY，否则全表视为一个组，跨 product_id 拿错值 - ORDER BY 缺失会报错（多数数据库强制要求），且顺序错会导致
LAG结果完全不可信 - 首行无前驱值，
LAG返回 NULL —— 如果要补 0，得套COALESCE(LAG(...), 0)
常见错误：把 LAG 当成 GROUP BY 后的聚合结果来 JOIN 或 WHERE
比如试图这样写：
SELECT * FROM ( SELECT product_id, LAG(price) OVER (...) AS prev_p FROM sales ) t WHERE prev_p > 100; -- ❌ 错！prev_p 是每行的位移值，不是分组统计值
这种写法逻辑没问题，但容易误以为 prev_p 是“该 product_id 的上期均价”，其实它只是“本行上一条记录的 price”。如果你真需要分组级的参考值（如“每个 product_id 最早一笔 price”），该用 FIRST_VALUE(price) OVER (PARTITION BY product_id ORDER BY sale_date)，而不是硬套 LAG。
性能与兼容性：OFFSET 超大或 ORDER BY 表达式复杂时要小心
当 LAG(price, 1000) 或 ORDER BY parse_json(metadata)->>'timestamp' 出现在大表窗口计算中，PostgreSQL 和 MySQL 8.0+ 通常能下推优化，但 ClickHouse 或旧版 Hive 可能触发全分区 materialize，内存暴涨。实操建议：
- 避免在
ORDER BY里用高开销函数；优先在 WHERE 中过滤后再开窗 - 如果只需要首尾偏移（如第一行、最后一行），用
FIRST_VALUE/LAST_VALUE更语义清晰，部分引擎对其有额外优化 - SQL Server 的
LAG不支持IGNORE NULLS，而 BigQuery 和 PostgreSQL 支持 —— 若数据含空值且业务逻辑要求跳过，得提前用COALESCE或子查询清理
真正难的不是写出 LAG，而是想清楚你到底要“取邻值”还是“算区间聚合”——这两个需求底层执行计划、资源消耗、结果语义都完全不同。
