在SQL Server中如何使用窗口函数简化同比与环比分析-SQL-PHP中文网

直接用lag/lead算环比结果常为空，是因为未补全时间序列且排序不严格：日期缺失时lag会跳过空档取更早值（如2023-03的“上期”误为2023-01而非缺失的2023-02），正确做法是先用cte生成连续年月序列并left join补null，再在完整序列上执行lag(value, 1) over (order by year_month)。

在sql server中如何使用窗口函数简化同比与环比分析

为什么直接用 `LAG` 和 `LEAD` 就能算环比，但结果常为空？

因为 LAG 默认取上一行，而原始数据若未按时间严格排序或存在缺失月份，就会跳过空缺、导致错位。比如 2023-01、2023-03 连续两行，LAG(value, 1) OVER (ORDER BY date) 会让 2023-03 的“上期”误指 2023-01，而非预期的 2023-02（该月无数据）。

正确做法是先补全时间维度：

用 CTE 或临时表生成连续的年月序列（如 DATEFROMPARTS(y, m, 1) 构造每月首日）
再用 LEFT JOIN 把业务数据挂上去，缺失值自动为 NULL
最后在完整序列上跑 LAG(value, 1) OVER (ORDER BY year_month)，此时“上期”才真正对应上月

同比必须用 `DATEADD` 配合 `LAG` 吗？

不是必须，但最稳。有人尝试用 LAG(value, 12) OVER (ORDER BY year_month) 算同比，前提是数据严格按月对齐且无断层——一旦某年缺 2 月，第 13 行就不是去年同月。

更可靠的方式是把时间转换为可比键，再用 LAG 按键查找：

新增计算列：year_month = YEAR(date) * 100 + MONTH(date)（得 202301、202302…）
再用 LAG(value) OVER (ORDER BY year_month) 得到上月值
同比则用 LAG(value) OVER (ORDER BY year_month) FILTER (WHERE year_month % 100 = MONTH(date) AND YEAR(date) - 1 = YEAR(date) - 1) —— 不，这写法错；实际应先构造 last_year_month = (YEAR(date)-1)*100 + MONTH(date)，再用 JOIN 或 OUTER APPLY 关联，但太重
所以回归本质：用 DATEADD(MONTH, -12, date) 算出去年同期时间点，配合 LAG 不现实，改用 OUTER APPLY (SELECT TOP 1 value FROM t AS t2 WHERE t2.date = DATEADD(MONTH, -12, t1.date)) 更直观，但性能差；权衡之下，仍推荐补全时间轴后用 LAG(value, 12)

`PERCENT_RANK` 和 `NTILE` 在同比环比里有什么用？

它们不直接参与同比/环比计算，但常被误认为相关。真实场景中，它们用于“同期横向对比”：比如比较 2023 年各月销售额在近 3 年所有月份中的分位位置。

例如看 2023-06 的销售额是否处于历史高位：

SELECT 
  year_month,
  amount,
  PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank
FROM (
  SELECT YEAR(date)*100+MONTH(date) AS year_month, SUM(sales) AS amount
  FROM sales WHERE date >= '2021-01-01'
  GROUP BY YEAR(date)*100+MONTH(date)
) t

注意：PERCENT_RANK 返回的是相对排序位置（0～1），不是增长率；别把它和 (amount - LAG(amount))/LAG(amount) 混用。

SQL Server 2012+ 的窗口函数能替代自连接吗？

能，而且应该替代。传统同比常写成：

SELECT a.date, a.value, b.value AS last_year_value
FROM sales a
LEFT JOIN sales b ON YEAR(b.date) = YEAR(a.date)-1 AND MONTH(b.date) = MONTH(a.date)

问题在于：当存在多条同月记录时，JOIN 会爆炸式膨胀；且无法处理跨年闰日（如 2024-02-29 没有 2023 对应日）。

用窗口函数+补全时间轴后，逻辑扁平、可读性强、执行计划更优：

补全后的结果集只有一行/月，LAG 单次扫描即可完成所有偏移引用
SQL Server 优化器对 OVER (ORDER BY ...) 的排序复用做得较好，避免多次索引查找
但注意：若原始表无时间字段索引，补全步骤本身可能变瓶颈——务必确保 date 列有索引，且补全 CTE 尽量用 OPTION (MAXRECURSION 0) 控制递归深度

最易被忽略的一点：补全时间轴时用 DATEFROMPARTS 要求 SQL Server 2012+，低版本得用 DATEADD 拼接；而 LAG(..., 12) 在有断层时依然会错，必须依赖补全逻辑本身严谨——这里没捷径。