如何用SQL快速替换大表中Null值为对应ID最近非Null值

我有一张包含id、date、var1字段的数据表，当某行的var1为Null时，需要将其替换为同一ID下日期更早的最近非Null值。如何针对超大规模的表快速完成这个操作？

初始数据表 #

+----+------------+-------+
| id | date       | var1  |
+----+------------+-------+
|  1 |'01-01-2022'|55     |
|  2 |'01-01-2022'|12     |
|  3 |'01-01-2022'|45     |
|  1 |'01-02-2022'|Null   |
|  2 |'01-02-2022'|Null   |
|  3 |'01-02-2022'|20     |
|  1 |'01-03-2022'|15     |
|  2 |'01-03-2022'|Null   |
|  3 |'01-03-2022'|Null   |
|  1 |'01-04-2022'|Null   |
|  2 |'01-04-2022'|77     |
+----+------------+-------+

期望结果表 #

+----+------------+-------+
| id | date       | var1  |
+----+------------+-------+
|  1 |'01-01-2022'|55     |
|  2 |'01-01-2022'|12     |
|  3 |'01-01-2022'|45     |
|  1 |'01-02-2022'|55     |
|  2 |'01-02-2022'|12     |
|  3 |'01-02-2022'|20     |
|  1 |'01-03-2022'|15     |
|  2 |'01-03-2022'|12     |
|  3 |'01-03-2022'|20     |
|  1 |'01-04-2022'|15     |
|  2 |'01-04-2022'|77     |
+----+------------+-------+

解决方案：用窗口函数实现高效填充 #

针对超大规模表，最高效的方式是利用数据库原生窗口函数，避免低效的自连接或全表扫描。以下是适配不同数据库的实现方案：

方法1：支持IGNORE NULLS的数据库（PostgreSQL、SQL Server、BigQuery等） #

直接使用LAST_VALUE窗口函数，忽略Null值后取同一ID下历史最近的非Null值：

SELECT
    id,
    date,
    LAST_VALUE(var1 IGNORE NULLS) OVER (
        PARTITION BY id
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_var1
FROM your_table;

方法2：MySQL 8.0+（不支持IGNORE NULLS） #

通过标记非Null值的连续分组，再取组内的非Null值完成填充：

WITH grouped_data AS (
    SELECT
        id,
        date,
        var1,
        -- 为每个非Null值创建分组标记，Null值继承前一个非Null的分组ID
        SUM(CASE WHEN var1 IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id
            ORDER BY date
            ROWS UNBOUNDED PRECEDING
        ) AS group_id
    FROM your_table
)
SELECT
    id,
    date,
    MAX(var1) OVER (PARTITION BY id, group_id) AS filled_var1
FROM grouped_data;

性能优化要点 #

• 给表创建复合索引：(id, date)，窗口函数可利用该索引快速完成分组排序，避免全表扫描。
• 分布式数据库（如BigQuery、Spark SQL）可按id或date分区，进一步提升处理效率。
• 绝对避免使用自连接关联历史数据，这种方式在超大规模数据下会产生笛卡尔积，性能极差。

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