如何用SQL自动生成月度快照：计算过去一年平均数量

需求说明 #

• 为每个物品生成月度快照，计算每月月末日期对应的过去一年平均数量（例如2023-09-30的平均值范围为2022-09-30至2023-09-30）
• 需通过DISTINCT去除重复的WorkId数据
• 替代手动修改日期范围的方式，自动生成多月度的完整快照结果

测试数据 #

CREATE TABLE #TEMP (
    WorkId int, 
    ItemId int, 
    Quantity float, 
    WorkDate date
)

INSERT INTO #TEMP (WorkId, ItemId, Quantity, WorkDate) VALUES
(12,1,10,'2022-05-30'),
(12,1,10,'2022-06-30'),
(10,1,8,'2022-07-30'),
(10,1,8,'2022-08-30'),
(1,1,1,'2022-09-30'),
(2,1,1,'2022-10-30'),
(2,1,3,'2022-11-30'),
(2,1,3,'2022-12-30'),
(3,1,5,'2023-04-30'),
(3,1,5,'2023-05-30'),
(4,1,2,'2023-06-30'),
(4,1,2,'2023-07-30'),
(5,1,5,'2023-08-30'),
(6,1,6,'2023-09-30')
GO

解决方案SQL #

WITH MonthEnds AS (
    -- 生成所有需要计算的月末日期（基于数据中的WorkDate范围）
    SELECT DISTINCT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, WorkDate), -1) AS EndOfMonth
    FROM #TEMP
    -- 可选：如果需要限制日期范围，添加WHERE条件，例如：
    -- WHERE WorkDate >= '2022-09-01' AND WorkDate <= '2023-09-30'
),
DistinctWorkData AS (
    -- 先对原始数据去重，保留唯一的WorkId记录
    SELECT DISTINCT
        WorkId,
        ItemId,
        Quantity,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, WorkDate), -1) AS RecordMonthEnd
    FROM #TEMP
)
SELECT
    me.EndOfMonth,
    dwd.ItemId,
    AVG(dwd.Quantity) AS AvgQuantityPastYear
FROM MonthEnds me
JOIN DistinctWorkData dwd
    ON dwd.RecordMonthEnd BETWEEN DATEADD(MONTH, -12, me.EndOfMonth) AND me.EndOfMonth
GROUP BY me.EndOfMonth, dwd.ItemId
ORDER BY me.EndOfMonth, dwd.ItemId

关键逻辑说明 #

1. MonthEnds CTE：从原始数据中提取所有唯一的月末日期，作为快照的目标日期；也可以手动指定日期范围（比如只生成近12个月的月末）。
2. DistinctWorkData CTE：提前对原始数据去重，确保每个WorkId只保留一条记录，同时将WorkDate转换为对应月份的月末日期，方便后续时间范围匹配。
3. 关联计算：将每个目标月末日期与过去12个月内的去重数据关联，按目标月末和物品ID分组计算平均值，自动生成所有月度的快照结果。

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