基于另一列行差值更新SQL表new_vax字段的技术求助

问题场景 #

现有包含缺失值的疫苗接种数据表，结构如下（多地区，日期更新间隔不均，各地区total_vax非空起始日期不同）：

+-------------+------------+---------+----------+
| Location    | date       | new_vax | total_vax|
+-------------+------------+---------+----------+
| Afghanistan | 01-01-2020 |  NULL   |  NULL    |
| Afghanistan | 01-02-2020 |  NULL   |  5000    |
| Afghanistan | 01-05-2020 |  NULL   |  7000    |
| Afghanistan | 01-09-2020 |  1000   |  8000    |
| Afghanistan | 01-10-2020 |  1000   |  9000    |
+-------------+------------+---------+----------+

需要按地区和日期排序，将new_vax更新为：

• 若当前行total_vax为空，保持new_vax不变
• 若当前行是该地区total_vax首次非空，new_vax等于当前total_vax
• 其他情况，new_vax等于当前total_vax与上一个非空total_vax的差值

期望结果：

+-------------+------------+---------+----------+
| Location    | date       | new_vax | total_vax|
+-------------+------------+---------+----------+
| Afghanistan | 01-01-2020 |  NULL   |  NULL    |
| Afghanistan | 01-02-2020 |  5000   |  5000    |
| Afghanistan | 01-05-2020 |  2000   |  7000    |
| Afghanistan | 01-09-2020 |  1000   |  8000    |
| Afghanistan | 01-10-2020 |  1000   |  9000    |
+-------------+------------+---------+----------+

原方案失败原因 #

你提供的SQL中，LAG(total_vax)会直接取上一行的total_vax值，若上一行total_vax为NULL（比如示例中第二行的上一行），则total_vax - prev_total_vax结果为NULL，再加上COALESCE中的new_vax本身也是NULL，最终fixed_new_vax仍为NULL，导致更新无效。

解决方案 #

方案1：支持IGNORE NULLS的数据库（SQL Server 2022+、PostgreSQL等） #

利用LAG函数的IGNORE NULLS参数，直接获取当前行之前最近的非空total_vax值：

WITH cte AS (
    SELECT 
        location, 
        date, 
        new_vax,
        total_vax,
        -- 获取当前行之前最近的非空total_vax
        LAG(total_vax) OVER (PARTITION BY location ORDER BY date IGNORE NULLS) AS prev_non_null_total
    FROM #temp_death_vax
)
UPDATE #temp_death_vax
SET new_vax = CASE
    WHEN total_vax IS NULL THEN new_vax  -- total_vax为空时不修改
    WHEN prev_non_null_total IS NULL THEN total_vax  -- 首次非空，直接取total_vax
    ELSE total_vax - prev_non_null_total  -- 计算差值
END
FROM #temp_death_vax
JOIN cte ON #temp_death_vax.location = cte.location 
         AND #temp_death_vax.date = cte.date
WHERE #temp_death_vax.new_vax IS NULL 
  AND #temp_death_vax.total_vax IS NOT NULL;  -- 只更新需要修正的行

方案2：不支持IGNORE NULLS的旧版数据库（如SQL Server 2019及更早） #

使用OUTER APPLY子查询，找到当前行之前最近的非空total_vax：

UPDATE t
SET new_vax = CASE
    WHEN prev.total_vax IS NULL THEN t.total_vax
    ELSE t.total_vax - prev.total_vax
END
FROM #temp_death_vax t
OUTER APPLY (
    SELECT TOP 1 total_vax
    FROM #temp_death_vax
    WHERE location = t.location 
      AND date < t.date 
      AND total_vax IS NOT NULL
    ORDER BY date DESC  -- 取最近的日期
) prev
WHERE t.new_vax IS NULL 
  AND t.total_vax IS NOT NULL;

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