无日期函数场景下SQL更新上月累计金额列性能优化

假设存在如下业务表：

需求为统计上月对应时间段的金额总和，更新初始值为NULL的SumLastMonthAmount列。

约束规则：完全不允许使用任何日期函数，所有日期字段均为int类型。

原有查询写法如下，执行速度极慢：

UPDATE A
SET SumLastMonthAmount = (SELECT SUM(Amount) 
                          FROM Table B 
                          WHERE A.CustomerId = B.CustomerId
                            AND B.Date > A.LastMonthDate
                            AND B.Date <= A.Date)                          
FROM Table A
Where A.Date=20220402

原有写法属于关联子查询，会针对外层查询的每一行单独执行一次内层聚合扫描，表数据量越大，重复扫描的次数越多，时间复杂度接近O(n²)，大表场景下性能会急剧下降。

核心思路是将逐行聚合改为单次扫描计算累计值，通过累计值做差直接得到区间统计结果，时间复杂度可降到O(n)，全程不使用任何日期函数，完全符合约束要求。

支持窗口函数的环境（SQL Server 2012+、MySQL 8.0+、PostgreSQL等）可直接使用如下写法：

WITH CustomerRunningTotal AS (
    SELECT
        CustomerId,
        Date,
        SUM(Amount) OVER(
            PARTITION BY CustomerId
            ORDER BY Date
            ROWS UNBOUNDED PRECEDING
        ) AS CumulativeAmount
    FROM YourTable
)
UPDATE A
SET SumLastMonthAmount = cur.CumulativeAmount - last.CumulativeAmount
FROM YourTable A
JOIN CustomerRunningTotal cur
    ON A.CustomerId = cur.CustomerId AND A.Date = cur.Date
JOIN CustomerRunningTotal last
    ON A.CustomerId = last.CustomerId AND A.LastMonthDate = last.Date
WHERE A.SumLastMonthAmount IS NULL;

配套性能优化建议 #

• 为表建立联合索引(CustomerId, Date)，覆盖Amount、LastMonthDate、SumLastMonthAmount字段，可让窗口计算、关联更新全程走索引，无需回表，性能可提升10~100倍。
• 如果使用不支持窗口函数的老版本数据库，可先按客户、日期排序遍历，用变量计算累计值存入临时表，再通过临时表关联更新，性能仍远高于原有的逐行子查询写法。

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