优化CUM_MONTH_PREV_YEAR字段更新SQL查询的技术咨询

现有表T的结构及数据如下：

ID|DESC1_ID | DESC2_ID | TS     | CUM_MONTH_PREV_YEAR| CUM_MONTH_THIS_YEAR|ID2
--------------------------------------------------------------------------|-
1 |1        |1         |31.01.22|                    | 220                |1
----------------------------------------------------------------------------
2 |1        |2         |31.01.22|                    | 500                |1
---------------------------------------------------------------------------
3 |1        |3         |31.01.22|                    | 22                 |1
----------------------------------------------------------------------------
4 |2        |1         |31.01.22|                    | 50                 |1
---------------------------------------------------------------------------
5 |1        |1         |01.02.23|                    | 230                |2
----------------------------------------------------------------------------
6 |1        |2         |01.02.23|                    | 300                |2
---------------------------------------------------------------------------
7 |1        |3         |01.02.23|                    | 32                 |2
----------------------------------------------------------------------------
8 |2        |1         |01.02.23|                    | 30                 |2 

需求 #

更新所有ID2=2的记录的CUM_MONTH_PREV_YEAR字段值为对应上年数据（TS日期可能并非恰好一年前）。

已实现SQL #

UPDATE T t1  
    SET CUM_MONTH_PREV_YEAR = (SELECT NVL(CUM_MONTH_THIS_YEAR , 0)
                                FROM T t2
                              WHERE t2.TS = (SELECT MAX(TS) 
                                             FROM T
                                             WHERE TS BETWEEN  ADD_MONTHS( t1.TS - 7, -12) AND ADD_MONTHS( t1.TS, -12)
                                             AND DESC1_ID = t1.DESC1_ID
                                             AND DESC2_ID = t1.DESC2_ID )
                            AND t2.DESC1_ID = t1.DESC1_ID
                            AND t2.DESC2_ID = t1.DESC2_ID )  
WHERE ID2 = 2 ;

补充说明 #

实际场景中ID2取值更多，数据按工作日每日存储，现咨询是否存在更优实现方案。

方案1：MERGE+窗口函数（适配Oracle等支持MERGE的数据库） #

通过窗口函数提前为每组(DESC1_ID, DESC2_ID)筛选出符合日期范围的最新记录，避免嵌套子查询的重复计算，适合大数据量场景：

MERGE INTO T t1
USING (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (PARTITION BY DESC1_ID, DESC2_ID ORDER BY TS DESC) rn
    FROM T t
    WHERE TS BETWEEN ADD_MONTHS((SELECT MIN(TS) FROM T WHERE ID2=2)-7, -12) 
                  AND ADD_MONTHS((SELECT MAX(TS) FROM T WHERE ID2=2), -12)
) t2
ON (
    t1.DESC1_ID = t2.DESC1_ID 
    AND t1.DESC2_ID = t2.DESC2_ID
    AND t1.ID2 = 2
    AND ADD_MONTHS(t2.TS, 12) BETWEEN t1.TS -7 AND t1.TS
    AND t2.rn = 1
)
WHEN MATCHED THEN
    UPDATE SET t1.CUM_MONTH_PREV_YEAR = NVL(t2.CUM_MONTH_THIS_YEAR, 0);

方案2：LATERAL JOIN（适配PostgreSQL、Oracle 12c+等） #

通过LATERAL JOIN直接关联每组对应的最新上年记录，逻辑更直观，执行效率更优：

UPDATE T t1
SET CUM_MONTH_PREV_YEAR = NVL(t2.CUM_MONTH_THIS_YEAR, 0)
FROM LATERAL (
    SELECT CUM_MONTH_THIS_YEAR
    FROM T
    WHERE DESC1_ID = t1.DESC1_ID
      AND DESC2_ID = t1.DESC2_ID
      AND TS BETWEEN ADD_MONTHS(t1.TS -7, -12) AND ADD_MONTHS(t1.TS, -12)
    ORDER BY TS DESC
    LIMIT 1
) t2
WHERE t1.ID2 = 2;

方案3：添加复合索引优化原SQL #

如果不想大幅修改现有语句，可通过添加复合索引提升子查询速度：

CREATE INDEX idx_ts_desc ON T(DESC1_ID, DESC2_ID, TS DESC) INCLUDE (CUM_MONTH_THIS_YEAR);

该索引能让数据库快速定位每组符合日期范围的最新记录，减少全表扫描开销。

优化思路说明 #

1. 减少重复计算：原SQL的嵌套子查询会为每条待更新记录单独执行一次，数据量大时性能损耗严重，优化方案通过批量预筛选降低重复计算量。
2. 逻辑扁平化：用窗口函数或LATERAL JOIN替代嵌套子查询，让执行计划更高效，逻辑更易维护。
3. 索引精准适配：针对查询的过滤、排序字段创建复合索引，直接命中目标数据，避免无效扫描。

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