TSQL计算滚动累计总额：跨时段累加需求及实现问询

看起来你在计算跨时间段滚动累计时踩了窗口函数分区的坑——你的PARTITION BY里包含了MonthNumber，这会让每个月份的数据单独成组，自然没法实现跨月份的累计。我们来一步步解决这个问题：

需求拆解 #

你提到的示例逻辑是：

• 1月Expense+Indemnity总额≈31.7k
• 2月两类总额≈4.1k，累计后≈35.9k

所以我们需要分两种场景处理：要么按费用类型单独计算累计，要么先汇总月度总额再计算整体累计。

解决方案1：按CostType单独计算滚动累计 #

如果你需要每个费用类型（Expense/Indemnity）各自的滚动累计值，直接调整窗口函数的分区和排序规则即可：

CREATE TABLE #temptable ( 
    Catastrophe VARCHAR (60), 
    Type VARCHAR (256), 
    CostType VARCHAR (256), 
    FirstLossDate DATE, 
    MonthNumber INT, 
    Amount DECIMAL (38, 6) 
);

INSERT INTO #temptable ( Catastrophe, Type, CostType, FirstLossDate, MonthNumber, Amount) 
VALUES 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 1, 13460.320000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 1, 18314.610000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 2, -1589.340000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 2, 5750.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 3, -2981.250000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 3, -10000.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 4, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 4, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 5, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 5, 0.000000);

-- 分CostType的滚动累计
SELECT 
    Catastrophe, 
    Type, 
    CostType, 
    FirstLossDate, 
    MonthNumber, 
    Amount,
    -- 关键：PARTITION BY不包含MonthNumber，按费用类型分组，按月份排序
    SUM(Amount) OVER (
        PARTITION BY Catastrophe, Type, CostType 
        ORDER BY MonthNumber 
        ROWS UNBOUNDED PRECEDING
    ) AS CostType_RunningTotal
FROM #temptable 
ORDER BY Catastrophe, Type, CostType, MonthNumber;

DROP TABLE #temptable;

结果中CostType_RunningTotal会展示每个费用类型从第1个月到当前月的累计值，比如Expense类型：

• Month1: 13460.32
• Month2: 13460.32 - 1589.34 = 11870.98
• Month3: 11870.98 - 2981.25 = 8889.73

解决方案2：按月度汇总所有CostType后计算总额累计 #

如果你需要的是每个月份所有费用类型的总额，再计算整体累计（也就是你示例里的31.7k→35.9k逻辑），可以先汇总月度总额，再做累计：

CREATE TABLE #temptable ( 
    Catastrophe VARCHAR (60), 
    Type VARCHAR (256), 
    CostType VARCHAR (256), 
    FirstLossDate DATE, 
    MonthNumber INT, 
    Amount DECIMAL (38, 6) 
);

INSERT INTO #temptable ( Catastrophe, Type, CostType, FirstLossDate, MonthNumber, Amount) 
VALUES 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 1, 13460.320000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 1, 18314.610000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 2, -1589.340000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 2, 5750.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 3, -2981.250000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 3, -10000.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 4, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 4, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 5, 0.000000), 
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 5, 0.000000);

-- 先汇总月度总额，再计算整体累计
WITH MonthlyTotals AS (
    SELECT 
        Catastrophe, 
        Type, 
        FirstLossDate, 
        MonthNumber,
        SUM(Amount) AS MonthlyTotal
    FROM #temptable
    GROUP BY Catastrophe, Type, FirstLossDate, MonthNumber
)
SELECT 
    Catastrophe, 
    Type, 
    FirstLossDate, 
    MonthNumber,
    MonthlyTotal,
    SUM(MonthlyTotal) OVER (
        PARTITION BY Catastrophe, Type 
        ORDER BY MonthNumber 
        ROWS UNBOUNDED PRECEDING
    ) AS Overall_RunningTotal
FROM MonthlyTotals
ORDER BY Catastrophe, Type, MonthNumber;

DROP TABLE #temptable;

这个查询的结果完全匹配你的示例逻辑：

• Month1: MonthlyTotal=31774.93，Overall_RunningTotal=31774.93
• Month2: MonthlyTotal=4160.66，Overall_RunningTotal=31774.93+4160.66=35935.59
• Month3: MonthlyTotal=-12981.25，Overall_RunningTotal=35935.59-12981.25=22954.34

为什么你的原代码没生效？ #

你的原查询中，PARTITION BY Catastrophe, MonthNumber, Type把每个月份的数据单独分成了一个分区，导致SUM(Amount)只会计算当前月份内的金额，无法跨月份累加。去掉PARTITION里的MonthNumber，才能让窗口函数覆盖从第一个月份到当前月份的所有数据。

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