我很少需要update语句，但这可以工作并返回预期的结果。你只需要在你的windows函数中同时按ID和YEAR分区。

update my_table a
 set ytd_total = (select running_total
  from (
   select
    id, 
    order_date, 
    sum(total) over (partition by id, extract(year from order_date) order by order_date) as running_total
   from my_table
  )b
where a.id = b.id
 and a.order_date = b.order_date
);

个字符
| ID|订单日期|总|YTD_TOTAL|
| --|--|--|--|
| 123 |2022年1月1日| 50 | 50 |
| 123 |2022年2月1日| 25 | 75 |
| 123 |23年8月1日| 60 | 60 |
| 123 |2023年9月1日| 50 | 110 |
| 123 |2023年10月1日| 20 | 130 |
fiddle

要计算列的运行总计，您可以使用分析函数，也就是窗口函数。基本语法是：

sum ( col ) over ( order by sort_col rows unbounded preceding )

SELECT id, date, total,
       sum (total) OVER (order by date) YTD_Total
  FROM my_table

字符串
这将根据示例数据回答您的问题。对于更复杂的场景，请阅读我向您指出的文档。

我不知道，如果我猜你的计算规则正确，但以下工程（我用数字而不是日期，但它也将与日期一起工作）
创建和填充表格：

DROP TABLE TABLE_XYZ
/
CREATE TABLE TABLE_XYZ (
  CID NUMBER(10) PRIMARY KEY,
  CIDGROUP NUMBER (10),
  CDATE NUMBER(2),
  CTOTAL NUMBER (3),
  CYTD_TOTAL NUMBER (10)
) 
/
INSERT INTO TABLE_XYZ SELECT 10, 123, 10, 50, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 20, 123, 20, 25, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 30, 123, 30, 60, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 40, 123, 40, 50, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 50, 123, 50, 20, NULL FROM DUAL;

INSERT INTO TABLE_XYZ SELECT 60, 124, 10, 1, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 70, 124, 20, 2, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 80, 124, 30, 3, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 90, 124, 40, 4, NULL FROM DUAL;
INSERT INTO TABLE_XYZ SELECT 100, 124, 50, 5, NULL FROM DUAL;

字符串
更新-声明

UPDATE TABLE_XYZ OUTSIDE SET CYTD_TOTAL =
    ( 
        SELECT NEW_VALUE FROM (
            SELECT
                CID,
                SUM(CTOTAL) OVER (PARTITION BY CIDGROUP ORDER BY CDATE) NEW_VALUE
            FROM  
                TABLE_XYZ 
        ) INSIDE
        WHERE 
            INSIDE.CID = OUTSIDE.CID
    )  

CID CIDGROUP CDATE CTOTAL CYDT_TOTAL
 10      123    10    50          50
 20      123    20    25          75
 30      123    30    60         135
 40      123    40    50         185
 50      123    50    20         205
 60      124    10     1           1
 70      124    20     2           3
 80      124    30     3           6
 90      124    40     4          10
100      124    50     5          15

型