MSSQL按多时间维度统计累计总计与上期差值的实现方法

原有代码失效原因 #

• 原始表为周粒度数据，代码未先做月度聚合，直接基于周明细计算，输出结果为周级数据，无法得到单月汇总的totla_number
• 窗口函数直接作用于周级数据，累计值、环比的计算粒度为周，和要求的月粒度统计规则不匹配
• SELECT子句相邻字段间缺少逗号，存在基础语法错误

正确实现SQL #

实现逻辑分两步：先按年、季度、月维度分组聚合得到月度汇总值，再基于聚合后的月度结果计算累计值与环比差值，代码如下：

WITH month_aggregate AS (
    SELECT
        year,
        quarter,
        month,
        SUM(totla_number) AS month_total
    FROM data
    -- 若需限定统计周期，可在此添加WHERE过滤条件，例如 WHERE (year=2021 AND quarter=4) OR (year=2022 AND quarter=1)
    GROUP BY year, quarter, month
)
SELECT
    year,
    quarter,
    month,
    month_total AS totla_number,
    SUM(month_total) OVER (
        PARTITION BY year 
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Grand,
    month_total - LAG(month_total) OVER (
        PARTITION BY year 
        ORDER BY month
    ) AS Diff
FROM month_aggregate
ORDER BY year, month;

逻辑说明 #

• 公共表达式month_aggregate完成周粒度到月粒度的聚合，输出结果为单月1行，匹配月度统计的粒度要求
• 累计值Grand按年分区，同一年度内按月份升序累加，跨年时累计值自动重置，符合2022年1月Grand重新从当月值开始计算的要求
• 环比Diff按年分区取上一个月的月度汇总值做差，每年度第一个统计月无前置周期数据，自动返回NULL，计算结果完全匹配给出的预期值：
  • 2021年11月：totla_number=914、Grand=914、Diff=NULL
  • 2021年12月：totla_number=1156、Grand=2070、Diff=242
  • 2022年1月：totla_number=987、Grand=987、Diff=NULL
  • 2022年2月：totla_number=464、Grand=1451、Diff=-523

内容的提问来源于stack exchange，提问作者陳冠儒