在SQL Server中用滚动月份实现月度平均值计算的除法公式

需求说明 #

计算每个年份的月度平均值，公式为截至当年最后一个已完成月份的总单位数 ÷ 已完成月份数，例如2022年1-3月单位数分别为20、40、10，最终年度平均值为(20+40+10)/3=23。

假设表结构 #

假设数据存储在UnitData表中，字段如下：

• Year：年份（INT类型）
• Month：月份（INT类型）
• UnitCount：当月单位数（INT类型）

实现方案 #

1. 创建示例数据（可选）

CREATE TABLE UnitData (
    Year INT,
    Month INT,
    UnitCount INT
);

INSERT INTO UnitData VALUES
(2022, 1, 20),
(2022, 2, 40),
(2022, 3, 10);

2. 核心计算SQL

使用窗口函数实现累计求和与累计月份统计，最后提取每个年份的最终平均值：

WITH MonthlyCumulative AS (
    SELECT
        Year,
        Month,
        -- 累计总单位数
        SUM(UnitCount) OVER (PARTITION BY Year ORDER BY Month) AS TotalUnits,
        -- 截至当前的已完成月份数
        COUNT(Month) OVER (PARTITION BY Year ORDER BY Month) AS CompletedMonths,
        -- 计算当月累计平均值（*1.0避免整数除法）
        SUM(UnitCount) OVER (PARTITION BY Year ORDER BY Month) * 1.0 / COUNT(Month) OVER (PARTITION BY Year ORDER BY Month) AS MonthlyAvg
    FROM UnitData
)
-- 取每个年份最后一个月的平均值作为年度结果
SELECT
    Year,
    ROUND(MonthlyAvg, 0) AS AnnualMonthlyAvg -- 按示例保留整数，可调整精度
FROM MonthlyCumulative
WHERE Month = (SELECT MAX(Month) FROM UnitData ud WHERE ud.Year = MonthlyCumulative.Year);

特殊场景处理 #

如果存在缺失月份数据的情况（比如某年份缺2月数据），若需要按自然月份数计算（而非实际存在的月份数），可以将COUNT(Month)替换为MAX(Month)：

WITH MonthlyCumulative AS (
    SELECT
        Year,
        Month,
        SUM(UnitCount) OVER (PARTITION BY Year ORDER BY Month) AS TotalUnits,
        MAX(Month) OVER (PARTITION BY Year) AS CompletedMonths, -- 改用最大月份数
        SUM(UnitCount) OVER (PARTITION BY Year ORDER BY Month) * 1.0 / MAX(Month) OVER (PARTITION BY Year) AS MonthlyAvg
    FROM UnitData
)
SELECT
    Year,
    ROUND(MonthlyAvg, 0) AS AnnualMonthlyAvg
FROM MonthlyCumulative
WHERE Month = (SELECT MAX(Month) FROM UnitData ud WHERE ud.Year = MonthlyCumulative.Year);

基于日期字段的适配 #

如果表中存储的是完整日期（如RecordDate DATETIME类型），可通过日期函数提取年份和月份：

WITH MonthlyCumulative AS (
    SELECT
        YEAR(RecordDate) AS Year,
        MONTH(RecordDate) AS Month,
        SUM(UnitCount) OVER (PARTITION BY YEAR(RecordDate) ORDER BY MONTH(RecordDate)) AS TotalUnits,
        COUNT(MONTH(RecordDate)) OVER (PARTITION BY YEAR(RecordDate) ORDER BY MONTH(RecordDate)) AS CompletedMonths,
        SUM(UnitCount) OVER (PARTITION BY YEAR(RecordDate) ORDER BY MONTH(RecordDate)) * 1.0 / COUNT(MONTH(RecordDate)) OVER (PARTITION BY YEAR(RecordDate) ORDER BY MONTH(RecordDate)) AS MonthlyAvg
    FROM UnitData
)
SELECT
    Year,
    ROUND(MonthlyAvg, 0) AS AnnualMonthlyAvg
FROM MonthlyCumulative
WHERE Month = (SELECT MAX(MONTH(RecordDate)) FROM UnitData ud WHERE YEAR(ud.RecordDate) = MonthlyCumulative.Year);

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