SQL Server按proj_id与年份分区计算累计和异常排查

需求说明 #

基于SQL Server Management Studio环境，需要按projectid和日期年份两个维度分区，计算同项目、同年份维度下按日期排序的生产值累计和，核心规则为同一projectid下的同一年份数据归为同一组计算累计值。

测试输入数据 #

Dates            Production   projectid      
---------------------------------------
2026-09-05       10           4                
2026-10-05       20           4                
2026-11-05       20           4                 
2027-09-05       15           5                 
2028-09-05       15           6        

期望输出结果 #

Dates            Production   projectid      cumulativesum
-----------------------------------------------------------
2026-09-05       10           4                    10             
2026-10-05       20           4                    30
2026-11-05       20           4                    50
2027-09-05       15           5                    15 
2028-09-05       15           6                    15

原有代码问题 #

之前编写的代码未达到预期，存在3个明确问题：

• 分区字段名不匹配：PARTITION BY中写的proj_id和业务表实际存在的project_id字段名不一致，导致分区维度失效
• 多表关联字段存在歧义：查询字段、聚合字段未指定表别名，两表都存在id字段时容易触发字段模糊报错
• 窗口逻辑不严谨：排序字段冗余加入了proj_id（分区内该字段值完全一致，无排序意义），也未显式指定累计计算的行范围，SQL Server默认窗口范围在日期重复场景下会出现计算偏差

正确实现代码 #

SELECT
    Prod.Electro.*, 
    Ap.Proj.id, 
    Ap.Proj.name,
    SUM(Prod.Electro.production) OVER (
        PARTITION BY Prod.Electro.project_id, YEAR(Prod.Electro.Dates) 
        ORDER BY Prod.Electro.Dates
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Cumulative_sum
FROM 
    Prod.Electro
LEFT JOIN 
    Ap.Proj ON Prod.Electro.project_id = Ap.Proj.id

关键逻辑说明 #

• 分区规则：严格以项目ID+日期年份作为窗口分区依据，保证同项目同年份的数据进入同一个累计计算组
• 排序规则：分区内仅按日期升序排列，累计值从同分区最早的日期开始逐行累加
• 显式窗口范围：指定累计范围为「分区第一行到当前行」，彻底避免日期重复时的累计值计算错误

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