SQL如何分析数据趋势变化_LEAD/LAG函数进阶应用-SQL-PHP中文网

lead/lag 返回null是设计行为，因边界外无对应行；需用第三参数指定默认值，且partition by须在over内正确位置，日期偏移应匹配自然日，order by字段须建索引以避免性能问题。

sql如何分析数据趋势变化_lead/lag函数进阶应用

LEAD/LAG 函数为什么返回 NULL 而不是预期值

常见现象是窗口函数结果里大量 NULL，尤其首尾几行。这不是 bug，而是设计行为：窗口函数在边界外无对应行时默认返回 NULL。

必须显式用第三个参数指定默认值，否则业务逻辑容易因 NULL 中断（比如做差值计算时整个表达式变 NULL）：

SELECT
  date,
  sales,
  LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_sales,
  sales - LAG(sales, 1, 0) OVER (ORDER BY date) AS delta
FROM sales_data;

LAG(sales, 1) → 第一行必为 NULL
LAG(sales, 1, 0) → 第一行取 0，避免 delta 失效
注意：默认值类型需与列一致，LAG(sales, 1, '0') 可能触发隐式转换报错

按分组计算趋势时 PARTITION BY 写错位置

想看每个商品的周环比，但结果混了不同商品的数据——根本原因是 PARTITION BY 没放在 OVER 子句里，或写在了 ORDER BY 后面。

正确结构只有这一种顺序：OVER (PARTITION BY product_id ORDER BY week)。漏掉 PARTITION BY 就变成全表排序，趋势线全乱。

错误写法：OVER (ORDER BY week PARTITION BY product_id) → 语法报错
错误写法：LAG(...) OVER (ORDER BY week) → 所有商品被拉成一条时间线
如果分组键含 NULL，该组内所有行会被归为同一分区，但 NULL 值本身不参与排序

LEAD/LAG 和日期间隔不匹配导致趋势误判

用 LAG(sales, 7) 算“上周销量”，但数据不是每天都有（比如只存工作日），结果会拿错行——它数的是“前7行”，不是“7天前”。

真正要对齐自然日，得先补全日期维度，再用 LEFT JOIN 或生成序列，否则 LAG 的偏移量毫无业务意义。

LAG(sales, 1) OVER (ORDER BY date) → 前1行，不保证是昨天
LAG(sales, 7) OVER (ORDER BY date) → 前7行，可能跨月甚至跨季
补日期后，再用 LAG 才可靠；否则优先考虑 JOIN 自关联（t1.date = t2.date - INTERVAL '7 days'）

性能问题：窗口函数在大数据量下变慢的两个主因

千万级表上 LEAD/LAG 查询突然卡住，通常不是函数本身慢，而是执行计划没走对索引，或内存不足触发落盘。

关键点：窗口函数依赖排序，而 ORDER BY 字段必须有索引，且不能被其他条件拖累（比如加了 WHERE status != 'deleted' 却没复合索引）。

确保 OVER 中的 ORDER BY 列建了索引（单列或前置复合索引）
避免在 OVER 子句里用函数，如 ORDER BY DATE(created_at) → 索引失效
PostgreSQL 中 work_mem 过小会导致排序落磁盘，查 EXPLAIN ANALYZE 看是否有 Sort Method: external merge

复杂点在于：趋势分析常要嵌套多层窗口、或和聚合混用，这时候 LAG 的执行时机（是先分组还是先排序）会直接影响中间结果集大小——稍不注意，10GB 临时空间就没了。