SQL Server中计算过去12个月累计值的SQL语句求解

问题描述 #

现有SQL Server表包含Year、Month、Value三列，需要计算ValueOfLastTwelveMonths列，该列代表当前记录对应月份往前推12个月内（包含当前月）的所有Value总和（表中存在月份缺失的情况，缺失月份的Value视为0，不参与求和）。预期结果如下：

错误尝试代码 #

当前尝试的SQL语句无法实现需求，因为PARTITION BY Year, Month会按年和月分组，每个分组仅包含当前行，求和结果与Value一致：

SELECT Year,
        Month,
        Value,
        SUM (Value) OVER (PARTITION BY Year, Month)
FROM MyTable

正确解决方案 #

方案一：使用窗口函数（推荐） #

通过将年份和月份转换为连续的月份编号，利用窗口函数的范围求和，自动忽略缺失的月份：

WITH MonthNumberCTE AS (
    SELECT 
        Year,
        Month,
        Value,
        -- 将年月转换为连续的数字（如2021年1月=2021*12+1=24253）
        Year * 12 + Month AS MonthNumber
    FROM MyTable
)
SELECT 
    Year,
    Month,
    Value,
    -- 计算当前月份编号往前11个范围内的所有Value总和
    SUM(Value) OVER (
        ORDER BY MonthNumber
        RANGE BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS ValueOfLastTwelveMonths
FROM MonthNumberCTE
ORDER BY MonthNumber;

方案二：使用关联查询 #

通过左连接筛选过去12个月的记录，再分组求和：

SELECT 
    t1.Year,
    t1.Month,
    t1.Value,
    SUM(ISNULL(t2.Value, 0)) AS ValueOfLastTwelveMonths
FROM MyTable t1
LEFT JOIN MyTable t2 ON 
    -- 筛选t2的月份在t1月份往前11个月到当前月之间
    DATEFROMPARTS(t2.Year, t2.Month, 1) >= DATEADD(MONTH, -11, DATEFROMPARTS(t1.Year, t1.Month, 1))
    AND DATEFROMPARTS(t2.Year, t2.Month, 1) <= DATEFROMPARTS(t1.Year, t1.Month, 1)
GROUP BY t1.Year, t1.Month, t1.Value
ORDER BY t1.Year, t1.Month;

说明 #

• 方案一使用窗口函数性能更优，适合数据量较大的场景；
• 两种方案都会自动忽略表中缺失的月份，因为缺失月份没有对应记录，求和时不会计入；
• DATEFROMPARTS函数要求SQL Server 2012及以上版本，若使用更低版本可通过CAST(Year AS VARCHAR) + '-' + CAST(Month AS VARCHAR) + '-01'转换为日期字符串再转日期类型。

内容的提问来源于stack exchange，提问作者Salim Aissaoui Tlemcani