PostgreSQL：基于前序非空值填充空行的技术需求

初始数据 #

year |  status
--------------
2018 | null
2019 | 1
2020 | null
2021 | null
2022 | 0
2023 | null
2024 | 1
2025 | null

需求 #

从第一个非空status值之后的空行开始，用前一条记录的有效值填充空行，第一个非空值之前的null保持不变。

预期结果 #

year |  status
--------------
2018 | null
2019 | 1
2020 | 1
2021 | 1
2022 | 0
2023 | 0
2024 | 1
2025 | 1

解决方案 #

方法一：支持IGNORE NULLS的数据库（PostgreSQL 11+、SQL Server 2022+、Oracle等） #

利用窗口函数LAST_VALUE结合IGNORE NULLS特性，同时用累加计数判断是否已进入需要填充的阶段：

SELECT 
    year,
    CASE 
        -- 第一个非空值出现前，保留原null
        WHEN SUM(CASE WHEN status IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 0
        THEN status
        -- 后续空值用最近的非空值填充
        ELSE LAST_VALUE(status IGNORE NULLS) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    END AS status
FROM your_table
ORDER BY year;

方法二：MySQL（不支持IGNORE NULLS） #

使用用户变量追踪最近的有效值和是否已遇到第一个非空值：

SELECT 
    year,
    CASE 
        -- 未遇到第一个非空值时，null保持不变
        WHEN @has_non_null = 0 AND status IS NULL THEN NULL
        -- 遇到非空值则更新变量，空值则沿用变量值
        ELSE @last_status := COALESCE(status, @last_status)
    END AS status
FROM your_table,
     -- 初始化变量
     (SELECT @last_status := NULL, @has_non_null := 0) AS init
ORDER BY year;

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