如何实现按年度重置的SQL累计求和？现有查询存在异常

原始数据表 #

Year    Month   Value   YearMonth
2011      1      70      201101
2011      1      100     201101
2011      2      200     201102
2011      2      50      201102
2011      3      80      201103
2011      3      250     201103
2012      1      100     201201
2012      2      200     201202
2012      3      250     201203

预期结果 #

Year    Month       Sum
-----------------------
2011      1         170
2011      2         420  <--- 170 + 250
2011      3         750  <--- 170 + 250 + 330
2012      1         100
2012      2         300  <--- 100 + 200
2012      3         550  <--- 100 + 200 + 250

现有查询问题分析 #

第一个自连接查询的问题 #

查询代码：

Select c1.YearMonth, Sum(c2.Value) CumulativeSumValue
From @Tbl c1,  @Tbl c2
Where c1.YearMonth >= c2.YearMonth
Group By c1.YearMonth
Order By c1.YearMonth Asc

问题点：

• 未按年度限制连接范围，导致2012年的累计会包含2011年的数据，不符合"按年度重置"的要求。
• 原始表中同一YearMonth存在多条记录，自连接会产生笛卡尔积，使得每个月的累计值被重复计算（比如201101有2条记录，自连接后匹配4条记录，总和直接翻倍）。

第二个窗口函数查询的问题 #

查询代码：

select year, (Sum (aa.[Value]) Over (partition by aa.Year Order By aa.Month)) as 'Cumulative Sum'
from @Tbl aa

问题点：

• 窗口函数会为原始表的每一条记录计算累计值，但同一Year+Month有多条记录时，相同的累计值会重复出现，导致结果包含冗余记录。

正确查询方法 #

方法1：先汇总月度数据，再计算累计 #

先对每个年度-月份的Value求和得到月度小计，再基于小计按年度分区计算累计，是最稳妥的方案：

WITH MonthlyTotals AS (
    SELECT 
        Year,
        Month,
        SUM(Value) AS MonthlySum
    FROM @Tbl
    GROUP BY Year, Month
)
SELECT 
    Year,
    Month,
    SUM(MonthlySum) OVER (PARTITION BY Year ORDER BY Month) AS Sum
FROM MonthlyTotals
ORDER BY Year, Month;

方法2：用DISTINCT去重窗口函数结果 #

如果不想用CTE，可以直接在查询中添加DISTINCT，过滤掉同一月份的重复累计值：

SELECT DISTINCT
    Year,
    Month,
    SUM(Value) OVER (PARTITION BY Year ORDER BY Month) AS Sum
FROM @Tbl
ORDER BY Year, Month;

注意：部分SQL数据库可能对窗口函数结合DISTINCT的语法支持有限，需根据实际使用的数据库验证。

方法3：修正自连接查询 #

若坚持使用自连接，需先汇总月度数据，再按年度匹配、月份范围连接：

SELECT 
    c1.Year,
    c1.Month,
    SUM(c2.MonthlySum) AS Sum
FROM (
    SELECT Year, Month, SUM(Value) AS MonthlySum 
    FROM @Tbl 
    GROUP BY Year, Month
) c1
JOIN (
    SELECT Year, Month, SUM(Value) AS MonthlySum 
    FROM @Tbl 
    GROUP BY Year, Month
) c2 
    ON c1.Year = c2.Year 
    AND c1.Month >= c2.Month
GROUP BY c1.Year, c1.Month
ORDER BY c1.Year, c1.Month;

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