如何高效更新员工银行数据序列表中的Delta空缺值？

问题描述 #

现有存储员工银行数据的表，源数据如下：

Employee      |Bank          |Date           |Delta
---------------------------------------------------
Smith         |Vacation      |2023-01-01     |15.0
Smith         |Vacation      |2023-01-02     |Null
Smith         |Vacation      |2023-01-03     |Null
Smith         |Vacation      |2023-01-04     |7.5

需要将其中Delta为Null的行（2023-01-02、2023-01-03），填充为该行日期之前最近的非空Delta值，处理后目标数据如下：

Employee      |Bank          |Date           |Delta
---------------------------------------------------
Smith         |Vacation      |2023-01-01     |15.0
Smith         |Vacation      |2023-01-02     |15.0
Smith         |Vacation      |2023-01-03     |15.0
Smith         |Vacation      |2023-01-04     |7.5

源表拥有Employee、Bank、Date降序的唯一索引，数据量最高可达20亿行，当前使用以下SQL实现更新，但需更高效的方案：

WITH cte_date
     AS (SELECT dd.date_key, 
                db.balance_key, 
                feb.employee_key
         FROM shared.dim_date dd
              CROSS JOIN
         (
             SELECT DISTINCT 
                    employee_key
             FROM wfms.fact_employee_balance
         ) feb
              CROSS JOIN wfms.dim_balance db
         WHERE dd.date BETWEEN DATEFROMPARTS(DATEPART(YY, GETDATE()) - 2, 12, 31) AND GETDATE())
     SELECT dd.*, 
            t.delta
     INTO wfms.test2
     FROM cte_date dd
          LEFT JOIN wfms.test1 t ON dd.balance_key = t.balance_key
                                  AND dd.employee_key = t.employee_key
                                  AND t.date_key =  (SELECT TOP 1 tt1.date_key
                                                    FROM    wfms.test1 tt1
                                                    WHERE   tt1.balance_key = t.balance_key
                                                    AND     tt1.employee_key = t.employee_key
                                                    AND     tt1.date_key < dd.date_key);

高效解决方案 #

针对20亿级的超大规模数据集，原方案的关联子查询会逐行执行，性能极低。应改用窗口函数，结合已有的(Employee, Bank, Date)索引实现批量计算，大幅提升效率：

方案1：使用LAST_VALUE（支持IGNORE NULLS的SQL环境） #

这是逻辑最简洁、性能最优的方案，适合PostgreSQL、SQL Server 2022+等支持IGNORE NULLS的环境：

UPDATE t
SET Delta = new_delta
FROM (
    SELECT 
        Employee,
        Bank,
        Date,
        Delta,
        LAST_VALUE(Delta IGNORE NULLS) OVER (
            PARTITION BY Employee, Bank 
            ORDER BY Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS new_delta
    FROM wfms.fact_employee_balance
) t
WHERE t.Delta IS NULL;

方案2：兼容旧版SQL的分组填充 #

若你的SQL环境不支持IGNORE NULLS（如SQL Server 2019及更早版本），可通过分组标记的方式实现：

UPDATE t
SET Delta = new_delta
FROM (
    SELECT 
        Employee,
        Bank,
        Date,
        Delta,
        MAX(Delta) OVER (PARTITION BY Employee, Bank, grp) AS new_delta
    FROM (
        SELECT 
            *,
            SUM(CASE WHEN Delta IS NOT NULL THEN 1 ELSE 0 END) OVER (
                PARTITION BY Employee, Bank 
                ORDER BY Date 
                ROWS UNBOUNDED PRECEDING
            ) AS grp
        FROM wfms.fact_employee_balance
    ) AS sub
) t
WHERE t.Delta IS NULL;

方案3：批量生成新表（适合超大表处理） #

若直接更新原表IO压力过大，可直接生成填充后的新表，利用索引快速完成分区计算：

SELECT 
    Employee,
    Bank,
    Date,
    LAST_VALUE(Delta IGNORE NULLS) OVER (
        PARTITION BY Employee, Bank 
        ORDER BY Date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Delta
INTO wfms.fact_employee_balance_filled
FROM wfms.fact_employee_balance
ORDER BY Employee, Bank, Date;

生成完成后可替换原表或建立只读视图对外提供数据。

性能说明 #

• 窗口函数会直接利用(Employee, Bank, Date)的唯一索引，按分区批量计算，避免逐行关联，处理20亿级数据的效率远高于原方案。
• 优先选择支持IGNORE NULLS的LAST_VALUE方案，其执行计划更简洁，资源占用更低。
• 若使用SQL Server，需确保数据库兼容性级别≥110，以支持窗口函数的行范围特性。

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