SQL窗口函数中如何实现动态条件范围指定？

问题背景 #

需要计算每月对应的3个月数值总和，原本用窗口函数实现过去3个月求和，但存在需求：当缺少前序行时，改用后续行或前后结合的行凑够3个数据。尝试在窗口函数的ROWS子句中使用CASE语句，但SQL Server不支持这种语法，且希望避免重复计算逻辑。

示例数据 #

预期输出 #

原尝试代码（无法运行） #

SELECT
    [month],
    ROUND(
        SUM(
            ISNULL(
                CAST( val AS DECIMAL(10, 2) ),
                0
            )
        ) OVER (
            ORDER BY [month]
            ROWS BETWEEN
                CASE
                    WHEN [month] = 1
                    THEN CURRENT ROW AND 2 FOLLOWING
                    WHEN [month] = 2
                    THEN 1 PRECEDING AND 1 FOLLOWING
                    ELSE 2 PRECEDING AND CURRENT ROW
                END
        ) / 3,
        2
    ) AS sum_val
FROM
    myTable
;

可行实现方案 #

方案一：用LAG/LEAD函数精准选取数据点 #

由于SQL Server窗口函数的ROWS/RANGE子句不支持动态CASE条件，我们可以用LAG和LEAD函数直接获取需要的3个数据点，通过CASE分支处理不同月份的取值逻辑，避免重复计算。

SELECT
    [month],
    ROUND(
        (
            CASE
                -- 1月：无前置行，取当前+后2个月
                WHEN [month] = 1 THEN val + LEAD(val,1) OVER(ORDER BY [month]) + LEAD(val,2) OVER(ORDER BY [month])
                -- 2月：仅1条前置行，取前1+当前+后1个月
                WHEN [month] = 2 THEN LAG(val,1) OVER(ORDER BY [month]) + val + LEAD(val,1) OVER(ORDER BY [month])
                -- 11-12月：无足够后置行，取前2+当前个月
                WHEN [month] IN (11,12) THEN LAG(val,2) OVER(ORDER BY [month]) + LAG(val,1) OVER(ORDER BY [month]) + val
                -- 3-10月：有完整前置行，取标准过去3个月
                ELSE LAG(val,2) OVER(ORDER BY [month]) + LAG(val,1) OVER(ORDER BY [month]) + val
            END
        ) / 3.0,
        2
    ) AS sum_val
FROM
    myTable
ORDER BY [month];

代码说明

• LAG(val, n)：获取当前行之前第n行的val值
• LEAD(val, n)：获取当前行之后第n行的val值
• 用3.0确保除法结果为小数，再通过ROUND保留2位小数，和原需求逻辑一致

方案二：通用动态窗口范围（适配任意月份数量） #

如果数据月份不固定（比如不是刚好12个月），可以通过行序号和总行数动态计算窗口范围，无需硬编码月份：

WITH ranked_data AS (
    SELECT
        [month],
        val,
        ROW_NUMBER() OVER(ORDER BY [month]) AS rn,
        COUNT(*) OVER() AS total_rows
    FROM myTable
)
SELECT
    [month],
    ROUND(
        SUM(val) OVER(
            ORDER BY rn
            ROWS BETWEEN 
                -- 计算起始行：若当前行前2行不存在，从第1行开始；否则从当前行前2行开始
                CASE WHEN rn - 2 < 1 THEN 0 ELSE rn - 2 END PRECEDING
                AND
                -- 计算结束行：若需要的后置行超出总行数，到最后一行；否则取对应后置行
                CASE WHEN rn + (2 - (rn - 1)) > total_rows THEN total_rows - rn ELSE 2 - (rn - 1) END FOLLOWING
        ) / 3.0,
        2
    ) AS sum_val
FROM ranked_data
ORDER BY [month];

这个方案通过行序号动态调整窗口的起始和结束位置，确保每次都能取到3行数据（若总行数不足3行则取所有行），通用性更强。

内容的提问来源于stack exchange，提问作者GettingItDone