SQL LAG/LEAD 函数在环比增长率计算中的经典写法模板

lag需配合order by排序字段（如report_date），多值时加二级排序；用NULLif防除零，coalesce兜底；先开窗再where；跨门店等场景必须partition by；注意时间粒度与业务周期对齐。

LAG 函数怎么写才能正确算出上期值

直接用 LAG(column_name) 默认取前 1 行，但很多人漏掉 ORDER BY 子句，导致结果完全随机——窗口函数没排序就等于没定义“上期”。

• 必须在 OVER() 里明确指定排序字段，比如时间字段 ORDER BY report_date
• 如果存在多条同一天数据，要加二级排序（如 ORDER BY report_date, id），否则 LAG 返回哪一行不确定
• 默认返回 NULL，计算增长率时得用 COALESCE(LAG(...), 0) 或判断逻辑，否则 NULL / x 全是 NULL
• 别在 WHERE 里过滤时间后再套窗口函数——会先过滤再排序，可能把“上期”删掉；应先开窗再 WHERE

环比增长率公式里除零和 NULL 怎么处理

用 LAG 拿到上期值后，直接写 (cur - prev) / prev 很危险：上期为 0 就报错，上期为 NULL 就整个结果变 NULL。

• 优先用 NULLIF(prev, 0) 把除数为 0 的情况转成 NULL，再配合 COALESCE(..., 0) 统一兜底
• 更稳妥写法：CASE WHEN prev = 0 THEN 0 WHEN prev IS NULL THEN NULL ELSE (cur - prev) / prev::DECIMAL END（注意类型强转，避免整数除法截断）
• postgresql 中 / 对整数操作会截断，务必显式转 ::NUMERIC 或 ::DECIMAL(10,4)

LEAD 和 LAG 能不能混用在同一查询里算多期对比

能，而且很常见——比如既要同比（上月）、又要环比（上周），或者画趋势图需要前后各取一行。但要注意它们的排序逻辑必须一致。

• 同一个 OVER() 可复用，比如 OVER (ORDER BY dt)，LAG(val, 1) 和 LEAD(val, 1) 各取一边
• 不要给两个函数配不同 ORDER BY，否则窗口行为不可预测；也不要用不同 PARTITION BY，除非真要分组独立计算
• LAG(val, 2) 是取上上期，不是“上期的上期”——它直接跳过中间行，所以跨期数要核对业务定义是否匹配

分区（PARTITION BY）不加会出什么问题

不加 PARTITION BY 就是全表当一个分区排，一旦数据跨多个业务线、门店或用户，上期值就串了——A 店的今天可能拿 B 店的昨天当“上期”。

• 典型场景如按门店算日销售额环比：PARTITION BY store_id ORDER BY sale_date
• 时间字段必须严格单调递增且无重复，否则分区 + 排序后仍可能取错行；有重复时建议加唯一字段辅助排序
• mysql 8.0+、PostgreSQL、SQL Server 都支持，但 hive/spark SQL 对 PARTITION BY 后的排序稳定性要求更高，需确认执行引擎版本

实际写的时候最容易被忽略的是：排序字段的粒度和业务周期是否对齐。比如用小时字段做 ORDER BY 却想算“日环比”，那同一日内多条记录就会互相干扰——该用聚合后的日期维度，而不是原始时间戳。