如何使用Oracle SQL更新同表中的YTD累计值（YTD_Total）

需求说明 #

需要在Oracle表中更新YTD_Total字段，实现按ID和年份分组，对Total进行年度累计求和。

原始表数据 #

ID      Date        Total     YTD_Total
123   01/01/2022     50          
123   02/01/2022     25          
123   08/01/2023     60          
123   09/01/2023     50          
123   10/01/2023     20

期望更新结果 #

ID      Date        Total     YTD_Total
123   01/01/2022     50          50
123   02/01/2022     25          75
123   08/01/2023     60          60
123   09/01/2023     50          110
123   10/01/2023     20          130

解决方案 #

使用Oracle的MERGE语句结合窗口函数SUM() OVER()实现，窗口函数会按ID和年份分组、按日期排序计算累计值：

MERGE INTO your_table t
USING (
    SELECT 
        ID,
        Date,
        SUM(Total) OVER (PARTITION BY ID, EXTRACT(YEAR FROM Date) ORDER BY Date) AS calc_ytd
    FROM your_table
) s
ON (t.ID = s.ID AND t.Date = s.Date)
WHEN MATCHED THEN
    UPDATE SET t.YTD_Total = s.calc_ytd;

补充说明 #

• PARTITION BY ID, EXTRACT(YEAR FROM Date)：按ID和日期的年份分组，确保累计计算仅在同一ID的同一年度内进行
• ORDER BY Date：分组内按日期排序，保证累计值按时间顺序累加
• 若Date字段为字符串类型，需先转换为DATE类型再提取年份，修改子查询中的年份提取逻辑：

  EXTRACT(YEAR FROM TO_DATE(Date, 'MM/DD/YYYY'))
  

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