SQL Server中ROWS BETWEEN 1 PRECEDING实现环比计算

先说说一个常见误区：在SQL Server中计算环比时，许多用户的第一反应是使用 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW。结果得到一堆看似合理但实际完全错误的数据——因为该窗口帧取的是两行，而非一行。例如，SUM(sales) OVER (ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 返回的是“本月+上月”的累加和，并非单纯的上月值。环比需要的是单月数值，用于差值或除法运算，一旦用错，后续整个计算链都会出错。更隐蔽的是，这种错误在表面上难以察觉，排查起来非常耗时。

不能直接使用 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 进行环比计算——它取的是两行数据，而非上一行；若确实需要用窗口帧实现，必须写成 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING，但强烈建议改用 LAG() 函数。

为什么 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 无法计算环比

该范围包含当前行及其上一行，共两行数据。若对销售额执行 SUM(sales) OVER (...)，得到的是“本月 + 上月”的总和，而非上月单值。环比需要获取纯上月值，用于减法或除法。误用会导致整个计算链失效，且难以排查——表面上有数据，但语义完全错误。

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW → 2行窗口 → 适用于移动平均，不适用于获取上一行值
• ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING → 1行窗口 → 需配合 MAX() 或 MIN() 才能等效取出上一行值
• SQL Server 不允许在窗口帧内对同一列执行“当前值 - 聚合值”的直接运算（如 sales - SUM(sales) OVER (...)），会报错 Windowed functions cannot be used in the context of another windowed function

LAG() 是 SQL Server 中唯一可靠、可读且可维护的环比方案

SQL Server 2012 及以上版本完全支持 LAG()，语法清晰，行为确定，并能自动处理边界和空值逻辑。

• 基础写法：LAG(sales, 1) OVER (ORDER BY order_date) —— 明确获取上一行的 sales 值
• 首行兜底：LAG(sales, 1, 0) OVER (ORDER BY order_date) —— 首行返回 0 而非 NULL，避免后续计算异常
• 防除零：在分母位置使用 NULLIF(LAG(sales, 1), 0)，否则 / 0 会直接报错
• 排序必须唯一：ORDER BY order_date, sales_id —— 否则相同日期下 LAG() 返回的行不可控

真实生产环境中最容易崩溃的三个点

问题并非函数写法不熟，而在于数据与上下文未对齐。

• ORDER BY 字段无索引：SQL Server 会强制排序，大数据量下性能急剧下降；确保 order_date 或组合字段建有索引
• 时间粒度不统一：原始数据包含时分秒，但需要计算月度环比——必须先用 DATEFROMPARTS(YEAR(dt), MONTH(dt), 1) 归一化，否则同月多行会导致 LAG() 错位
• 缺失月份未补全：2024-02 数据缺失时，LAG(sales, 1) 会跳至 2024-01，导致 2024-03 的环比实际对比的是 2024-01——这不是函数 bug，而是数据问题，需在外层通过递归 CTE 或日历表 LEFT JOIN 补空

无需花费时间调整 ROWS BETWEEN 的边界，SQL Server 对窗口帧的聚合限制较为严格；应将精力集中在时间归一、排序唯一性和空值兜底这三方面，LAG() 即可稳定运行。