如何在UPDATE语句中按DateTime计算a_increment的累积和

需求：基于DateTime时间戳列，计算包含当前行在内的所有历史a_increment的累积和，存入a_cumulative列。

之前尝试方法的问题分析 #

• 迭代更新：依赖ID递推累加，不仅要求ID连续且与时间顺序完全一致，还需要多次执行才能得到正确结果，逐行更新的IO开销极大，数据量大时效率极低。
• 暴力子查询更新：每行都执行一次全表求和，时间复杂度为O(n²)，数据量稍大就会耗时极长（如用户所述耗时超5天）。
• 窗口函数直接用于UPDATE：虽然窗口函数能正确计算累积和，但多数数据库不允许在UPDATE的SET子句中直接使用窗口函数，会触发window functions are not allowed in UPDATE错误。

高效解决方案 #

核心思路：先通过窗口函数一次性计算所有行的累积和，再关联原表进行批量更新，仅需两次表扫描，时间复杂度为O(n log n)（主要是排序开销），效率远超之前的方法。

PostgreSQL 写法

WITH cumulative_data AS (
    SELECT 
        ID,
        SUM(a_increment) OVER (ORDER BY "DateTime") AS new_a_cumulative
    FROM data_cumulative_sums
)
UPDATE data_cumulative_sums t
SET a_cumulative = c.new_a_cumulative
FROM cumulative_data c
WHERE t.ID = c.ID;

MySQL 8.0+ 写法

WITH cumulative_data AS (
    SELECT 
        ID,
        SUM(a_increment) OVER (ORDER BY `DateTime`) AS new_a_cumulative
    FROM data_cumulative_sums
)
UPDATE data_cumulative_sums t
JOIN cumulative_data c ON t.ID = c.ID
SET t.a_cumulative = c.new_a_cumulative;

兼容无CTE支持的数据库写法

UPDATE data_cumulative_sums t
JOIN (
    SELECT 
        ID,
        SUM(a_increment) OVER (ORDER BY "DateTime") AS new_a_cumulative
    FROM data_cumulative_sums
) c ON t.ID = c.ID
SET t.a_cumulative = c.new_a_cumulative;

验证结果 #

执行后可得到预期的累积和结果：

ID "DateTime" a_increment a_cumulative
1 "2024-05-17 09:00:04+02" 1 1
2 "2024-05-17 09:00:14+02" 2 3
3 "2024-05-17 09:00:24+02" 0 3
4 "2024-05-17 09:00:34+02" 5 8

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