如何替换外连接产生的列中Null值：取前非空值或默认0

外连接操作导致多列出现NULL，这类NULL对于累计值类列不符合逻辑，需要将NULL替换为上一个非空值，首行无前置非空值时用0填充，以下是主流数据库的批量处理方案（以10列为例，列名假设为Value1至Value10）：

MySQL 8.0+ #

利用LAST_VALUE()窗口函数结合IGNORE NULLS特性，配合COALESCE处理首行NULL：

SELECT 
  ID,
  COALESCE(LAST_VALUE(Value1 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value1,
  COALESCE(LAST_VALUE(Value2 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value2,
  COALESCE(LAST_VALUE(Value3 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value3,
  COALESCE(LAST_VALUE(Value4 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value4,
  COALESCE(LAST_VALUE(Value5 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value5,
  COALESCE(LAST_VALUE(Value6 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value6,
  COALESCE(LAST_VALUE(Value7 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value7,
  COALESCE(LAST_VALUE(Value8 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value8,
  COALESCE(LAST_VALUE(Value9 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value9,
  COALESCE(LAST_VALUE(Value10 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value10
FROM 表A;

若需更新原表：

UPDATE 表A t1
JOIN (
  SELECT 
    ID,
    COALESCE(LAST_VALUE(Value1 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS new_Value1,
    COALESCE(LAST_VALUE(Value2 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS new_Value2,
    -- 依次写出剩余8列的处理逻辑
    COALESCE(LAST_VALUE(Value10 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS new_Value10
  FROM 表A
) t2 ON t1.ID = t2.ID
SET 
  t1.Value1 = t2.new_Value1,
  t1.Value2 = t2.new_Value2,
  -- 依次写出剩余8列的更新逻辑
  t1.Value10 = t2.new_Value10;

PostgreSQL #

语法与MySQL 8.0+类似，同样支持LAST_VALUE+IGNORE NULLS：

SELECT 
  ID,
  COALESCE(LAST_VALUE(Value1 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value1,
  -- 复制上述逻辑处理剩余9列
  COALESCE(LAST_VALUE(Value10 IGNORE NULLS) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS Value10
FROM 表A;

SQL Server #

2022+版本（支持IGNORE NULLS） #

SELECT 
  ID,
  COALESCE(LAST_VALUE(Value1) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS), 0) AS Value1,
  -- 处理剩余9列
  COALESCE(LAST_VALUE(Value10) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS), 0) AS Value10
FROM 表A;

旧版本（无IGNORE NULLS支持） #

通过分组标记非空值区间，用MAX()窗口函数填充：

SELECT 
  ID,
  COALESCE(MAX(Value1) OVER (PARTITION BY grp1 ORDER BY ID), 0) AS Value1,
  COALESCE(MAX(Value2) OVER (PARTITION BY grp2 ORDER BY ID), 0) AS Value2,
  -- 处理剩余8列
  COALESCE(MAX(Value10) OVER (PARTITION BY grp10 ORDER BY ID), 0) AS Value10
FROM (
  SELECT 
    ID,
    Value1, Value2, ..., Value10,
    SUM(CASE WHEN Value1 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS grp1,
    SUM(CASE WHEN Value2 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS grp2,
    -- 为剩余8列生成对应的分组标记
    SUM(CASE WHEN Value10 IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS grp10
  FROM 表A
) t;

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