SQL Server多维度按月聚合数据 填充缺失月份金额补0

核心逻辑 #

直接用日历表左连原表无法得到正确结果的根本原因是：缺少所有维度组合与所有统计月份的全量笛卡尔积骨架，关联时只会匹配到原表已存在的「维度+月份」组合，无法为每个维度组自动补全缺失月份。
实现分4步走：

• 从预置Calendar表筛选目标年份的12个月度周期，作为时间维度全集
• 从原表提取ActiveMark/Account/Company/Currency四个字段的去重值，作为业务维度全集
• 对时间全集和业务维度全集做交叉连接，生成「每个维度组对应12个月份」的完整基础骨架
• 先对原表做同维度同月份的金额聚合，再左连到基础骨架上，未匹配到的金额统一填充0

可直接运行的参考代码 #

-- 定义要统计的目标年份，可按需修改
DECLARE @TargetYear INT = 2024;

WITH
-- 步骤1：取目标年份的12个月度周期（和Calendar表Period格式对齐，为每月1号）
MonthList AS (
    SELECT DISTINCT Period
    FROM Calendar
    WHERE YEAR(Period) = @TargetYear
),
-- 步骤2：取所有需要统计的去重维度组合
DimCombo AS (
    SELECT DISTINCT ActiveMark, Account, Company, Currency
    FROM YourSourceTable -- 替换为实际业务表名
),
-- 步骤3：生成全量骨架：每个维度组合对应12个月份
FullSkeleton AS (
    SELECT 
        m.Period,
        d.ActiveMark,
        d.Account,
        d.Company,
        d.Currency
    FROM DimCombo d
    CROSS JOIN MonthList m
),
-- 步骤4：对原表做月度聚合，避免同维度同月份多条记录重复计算
MonthlyAgg AS (
    SELECT
        Period,
        ActiveMark,
        Account,
        Company,
        Currency,
        SUM(Amount) AS TotalAmount
    FROM YourSourceTable -- 替换为实际业务表名
    WHERE YEAR(Period) = @TargetYear
    GROUP BY Period, ActiveMark, Account, Company, Currency
)
-- 最终关联取数，缺失金额补0
SELECT
    s.Period,
    s.ActiveMark,
    s.Account,
    s.Company,
    s.Currency,
    ISNULL(a.TotalAmount, 0) AS Amount
FROM FullSkeleton s
LEFT JOIN MonthlyAgg a
    ON s.Period = a.Period
    AND s.ActiveMark = a.ActiveMark
    AND s.Account = a.Account
    AND s.Company = a.Company
    AND s.Currency = a.Currency
ORDER BY s.ActiveMark, s.Account, s.Company, s.Currency, s.Period;

注意事项 #

• 如果需要统计多年数据，只需要调整MonthList里的年份筛选条件即可，核心逻辑无需改动
• 如果存在维度生效/失效时间要求，可在DimCombo部分增加筛选逻辑，过滤不需要生成骨架的维度组合
• 关联条件必须覆盖四个维度字段+Period字段，否则会出现数据匹配错位的问题

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