我在一条记录中有以下数据，我想更新同一表中的YTD合计，不确定如何编写此SQL
实际表
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
我很少需要update条语句，但这是有效的，并返回您预期的结果.你只需要在你的Windows函数中同时按ID和年份进行分区.
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
);
select *
from my_table
| ID | ORDER_DATE | TOTAL | YTD_TOTAL |
|---|---|---|---|
| 123 | 01-JAN-22 | 50 | 50 |
| 123 | 01-FEB-22 | 25 | 75 |
| 123 | 01-AUG-23 | 60 | 60 |
| 123 | 01-SEP-23 | 50 | 110 |
| 123 | 01-OCT-23 | 20 | 130 |