基于SQL Server 2016实现按月份分组的3个月移动求和

嘿，这个需求用SQL Server 2016的窗口函数就能轻松搞定，咱们一步步来实现：

需求回顾 #

原始数据表（假设表名为DailyData）：

Target Date | Total #

2018-01-24 | 1
2018-02-28 | 1
2018-03-02 | 1
2018-03-08 | 1
2018-03-15 | 1
2018-03-30 | 1
2018-04-16 | 1
2018-04-18 | 1
2018-04-30 | 1

期望得到按月份分组的3个月移动求和结果：

Target Date | Total_Sum #

2018-01-01 | 1
2018-02-01 | 2
2018-03-01 | 6
2018-04-01 | 8

解决方案代码 #

-- 第一步：按月度汇总每日数据，得到每个月的总数量
WITH MonthlySummary AS (
    SELECT
        DATEFROMPARTS(YEAR([Target Date]), MONTH([Target Date]), 1) AS [Target Date],
        SUM(Total) AS Total_Month
    FROM DailyData
    GROUP BY DATEFROMPARTS(YEAR([Target Date]), MONTH([Target Date]), 1)
)
-- 第二步：对月度汇总数据应用3个月移动窗口求和
SELECT
    [Target Date],
    SUM(Total_Month) OVER (
        ORDER BY [Target Date]
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS Total_Sum
FROM MonthlySummary
ORDER BY [Target Date];

代码解释 #

1. 月度汇总CTE：MonthlySummary 把每日记录按当月第一天分组，用SUM(Total)统计每个月的总记录数（因为每条记录的Total都是1，这里等价于COUNT(*)）。DATEFROMPARTS函数用来将任意日期转换为当月的第一天，保证同一月份的所有记录都被归到同一个日期标识下。
2. 移动窗口求和：使用SUM() OVER()窗口函数，ORDER BY [Target Date]确保月份按时间顺序排列；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW指定窗口范围为当前月 + 前两个月，这样就自动计算出连续3个月的总和。

补充说明 #

如果你的数据中存在没有任何记录的月份（比如某月份没有数据，但需要在结果中显示该月且Total_Sum包含对应窗口的求和），可以先生成连续的月份序列，再与月度汇总表做左连接，之后再计算移动求和。

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