如何用最后有效值填充SQL查询结果中的后续NULL值？

你的需求是将机器停机时产生的NULL产量值，替换为该机器上一个小时的有效产量值（向前填充）。直接在透视后的列上处理NULL比较繁琐，更高效的方式是先处理行数据完成填充，再进行透视，以下分不同数据库场景给出实现方案：

通用方案（支持窗口函数+IGNORE NULLS：PostgreSQL、SQL Server等） #

利用LAST_VALUE窗口函数结合IGNORE NULLS参数，直接计算每个小时对应的最后非NULL产量值，再透视成目标格式：

SELECT 
    machine_id,
    MIN(CASE WHEN hour_of_day = 5 THEN filled_production_count END) AS '05:00',
    MIN(CASE WHEN hour_of_day = 6 THEN filled_production_count END) AS '06:00',
    MIN(CASE WHEN hour_of_day = 7 THEN filled_production_count END) AS '07:00',
    MIN(CASE WHEN hour_of_day = 8 THEN filled_production_count END) AS '08:00',
    MIN(CASE WHEN hour_of_day = 9 THEN filled_production_count END) AS '09:00',
    MIN(CASE WHEN hour_of_day = 10 THEN filled_production_count END) AS '10:00',
    MIN(CASE WHEN hour_of_day = 11 THEN filled_production_count END) AS '11:00',
    MIN(CASE WHEN hour_of_day = 12 THEN filled_production_count END) AS '12:00',
    MIN(CASE WHEN hour_of_day = 13 THEN filled_production_count END) AS '13:00',
    MIN(CASE WHEN hour_of_day = 14 THEN filled_production_count END) AS '14:00',
    MIN(CASE WHEN hour_of_day = 15 THEN filled_production_count END) AS '15:00',
    MIN(CASE WHEN hour_of_day = 16 THEN filled_production_count END) AS '16:00',
    MIN(CASE WHEN hour_of_day = 17 THEN filled_production_count END) AS '17:00'
FROM (
    SELECT 
        machine_id,
        HOUR(timestamp) AS hour_of_day,
        production_count,
        -- 按机器分组，按小时排序，取截至当前行的最后非NULL产量
        LAST_VALUE(production_count IGNORE NULLS) OVER (
            PARTITION BY machine_id 
            ORDER BY HOUR(timestamp)
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_production_count
    FROM production
    WHERE HOUR(timestamp) BETWEEN 5 AND 17
) AS filled_data
GROUP BY machine_id;

MySQL兼容方案（无IGNORE NULLS支持） #

MySQL 8.0之前的版本不支持IGNORE NULLS，可以用用户变量来实现向前填充：

SELECT 
    machine_id,
    MIN(CASE WHEN hour_of_day = 5 THEN filled_production_count END) AS '05:00',
    MIN(CASE WHEN hour_of_day = 6 THEN filled_production_count END) AS '06:00',
    MIN(CASE WHEN hour_of_day = 7 THEN filled_production_count END) AS '07:00',
    MIN(CASE WHEN hour_of_day = 8 THEN filled_production_count END) AS '08:00',
    MIN(CASE WHEN hour_of_day = 9 THEN filled_production_count END) AS '09:00',
    MIN(CASE WHEN hour_of_day = 10 THEN filled_production_count END) AS '10:00',
    MIN(CASE WHEN hour_of_day = 11 THEN filled_production_count END) AS '11:00',
    MIN(CASE WHEN hour_of_day = 12 THEN filled_production_count END) AS '12:00',
    MIN(CASE WHEN hour_of_day = 13 THEN filled_production_count END) AS '13:00',
    MIN(CASE WHEN hour_of_day = 14 THEN filled_production_count END) AS '14:00',
    MIN(CASE WHEN hour_of_day = 15 THEN filled_production_count END) AS '15:00',
    MIN(CASE WHEN hour_of_day = 16 THEN filled_production_count END) AS '16:00',
    MIN(CASE WHEN hour_of_day = 17 THEN filled_production_count END) AS '17:00'
FROM (
    SELECT 
        machine_id,
        hour_of_day,
        -- 用变量记录上一个非NULL值，遇到NULL时沿用
        @last_val := IF(production_count IS NOT NULL, production_count, @last_val) AS filled_production_count
    FROM (
        -- 必须先按机器和小时排序，保证变量更新顺序正确
        SELECT 
            machine_id,
            HOUR(timestamp) AS hour_of_day,
            production_count
        FROM production
        WHERE HOUR(timestamp) BETWEEN 5 AND 17
        ORDER BY machine_id, hour_of_day
    ) AS ordered_data
    -- 初始化变量
    CROSS JOIN (SELECT @last_val := NULL) AS init_vars
) AS filled_data
GROUP BY machine_id;

说明 #

1. 原查询中使用MIN是假设每个小时可能有多条记录，若每个小时仅一条记录，替换为MAX或直接取字段值均可，不影响结果。
2. 两种方案均先完成数据的向前填充，再通过CASE语句透视成小时列，确保NULL值被正确替换为最后一个有效值。

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