MS SQL Server按客户分组累计月度金额（缺失月份处理问题）

嘿，这个问题我之前也碰到过，核心痛点就是缺失月份的客户记录会导致累计计算不完整，直接用普通窗口函数会漏掉那些没有交易的月份行。下面给你一步步的解决方案：

思路拆解 #

要得到期望的结果，我们需要先构建一个所有客户 + 所有存在月份的完整组合，确保每个客户的每个月份都有一条记录，再关联原表的数据计算累计金额。

完整SQL代码 #

假设你的表名为CustomerTransactions，替换成你实际的表名即可：

WITH AllMonths AS (
    -- 获取所有存在的唯一月份
    SELECT DISTINCT Month FROM CustomerTransactions
),
AllCustomers AS (
    -- 获取所有存在的唯一客户
    SELECT DISTINCT Customer FROM CustomerTransactions
),
CustomerMonthFullList AS (
    -- 生成每个客户对应每个月份的完整组合（笛卡尔积）
    SELECT c.Customer, m.Month
    FROM AllCustomers c
    CROSS JOIN AllMonths m
)
-- 关联原表，计算累计金额
SELECT 
    cm.Customer,
    cm.Month,
    -- 按客户分区，按月份排序，累计求和（缺失月份的Amount按0处理）
    SUM(ISNULL(ct.Amount, 0)) OVER (
        PARTITION BY cm.Customer 
        ORDER BY cm.Month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Amount
FROM CustomerMonthFullList cm
LEFT JOIN CustomerTransactions ct 
    ON cm.Customer = ct.Customer 
    AND cm.Month = ct.Month
-- 按月份和客户排序，和期望结果一致
ORDER BY cm.Month, cm.Customer;

代码解释 #

1. AllMonths & AllCustomers CTE：分别提取原表中所有的唯一月份和客户，确保不会遗漏任何存在的月份或客户。
2. CustomerMonthFullList CTE：通过CROSS JOIN生成所有客户与月份的组合，补全了像Ali的2月、Kate的3月这类缺失的记录。
3. LEFT JOIN原表：把完整组合和原交易表关联，没有交易记录的行用ISNULL(ct.Amount, 0)将金额置为0。
4. 窗口函数计算累计：SUM() OVER (PARTITION BY ... ORDER BY ...)按客户分组，按月份顺序累计求和，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是默认行为，写出来更清晰，确保从第一个月累加到当前月。

验证结果 #

执行后会得到你期望的输出：

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