如何在含Union的T-SQL查询中将Null替换为前值？附示例

嘿，这个场景我之前处理过不少——时间序列数据里的Null填充前值，咱们可以用SQL窗口函数来优雅解决，分两种情况看你的数据库版本：

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

这种情况最简洁，直接用LAST_VALUE()函数搭配IGNORE NULLS参数，按时间顺序往前抓取最近的非Null值填充当前的Null。

首先把你的Union查询包装成一个CTE（公共表表达式），方便后续处理：

WITH RawData AS (
    -- 第一个子查询：S1的Stat数据，其他字段为变量值（可能为Null）
    SELECT 
        aa.t_stamp, 
        aa.Stat AS S1_Stat, 
        @s2_data AS S2_Stat, 
        @s3_data AS S3_Stat, 
        @s4_data AS S4_Stat, 
        @s5_data AS S5_Stat, 
        @s6_data AS S6_Stat 
    FROM Line4283_1_rtStat aa 
    WHERE t_stamp BETWEEN '{Root Container.Date Range.startDate}' AND '{Root Container.Date Range.endDate}'
    
    UNION
    
    -- 第二个子查询：S2的Stat数据，其他字段为变量值
    SELECT 
        ab.t_stamp, 
        @s1_data, 
        ab.Stat AS S2_Stat, 
        @s3_data, 
        @s4_data, 
        @s5_data, 
        @s6_data 
    FROM Line4283_2_rtStat ab 
    WHERE t_stamp BETWEEN '{Root Container.Date Range.startDate}' AND '{Root Container.Date Range.endDate}'
    
    -- 继续补充S3到S6对应的Union子查询，逻辑和上面一致
    UNION
    
    SELECT 
        ac.t_stamp, 
        @s1_data, 
        @s2_data, 
        ac.Stat AS S3_Stat, 
        @s4_data, 
        @s5_data, 
        @s6_data 
    FROM Line4283_3_rtStat ac 
    WHERE t_stamp BETWEEN '{Root Container.Date Range.startDate}' AND '{Root Container.Date Range.endDate}'
    
    -- ... 以此类推完成S4、S5、S6的查询
)
-- 对每个字段进行Null填充
SELECT 
    t_stamp,
    LAST_VALUE(S1_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S1_Stat_Filled,
    LAST_VALUE(S2_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S2_Stat_Filled,
    LAST_VALUE(S3_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S3_Stat_Filled,
    LAST_VALUE(S4_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S4_Stat_Filled,
    LAST_VALUE(S5_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S5_Stat_Filled,
    LAST_VALUE(S6_Stat) OVER (ORDER BY t_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IGNORE NULLS) AS S6_Stat_Filled
FROM RawData
ORDER BY t_stamp;

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

如果你的数据库不支持IGNORE NULLS，咱们可以用分组的思路：给每个非Null值后面的Null分配同一个组号，然后取组内的非Null值填充。

WITH RawData AS (
    -- 同上的RawData定义，包含所有Union子查询
),
GroupedData AS (
    SELECT 
        t_stamp,
        S1_Stat,
        S2_Stat,
        S3_Stat,
        S4_Stat,
        S5_Stat,
        S6_Stat,
        -- 为每个字段创建分组：遇到非Null时组号递增，Null继承前一个组号
        COUNT(S1_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S1_Group,
        COUNT(S2_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S2_Group,
        COUNT(S3_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S3_Group,
        COUNT(S4_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S4_Group,
        COUNT(S5_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S5_Group,
        COUNT(S6_Stat) OVER (ORDER BY t_stamp ROWS UNBOUNDED PRECEDING) AS S6_Group
    FROM RawData
)
-- 按分组取非Null值填充
SELECT 
    t_stamp,
    MAX(S1_Stat) OVER (PARTITION BY S1_Group) AS S1_Stat_Filled,
    MAX(S2_Stat) OVER (PARTITION BY S2_Group) AS S2_Stat_Filled,
    MAX(S3_Stat) OVER (PARTITION BY S3_Group) AS S3_Stat_Filled,
    MAX(S4_Stat) OVER (PARTITION BY S4_Group) AS S4_Stat_Filled,
    MAX(S5_Stat) OVER (PARTITION BY S5_Group) AS S5_Stat_Filled,
    MAX(S6_Stat) OVER (PARTITION BY S6_Group) AS S6_Stat_Filled
FROM GroupedData
ORDER BY t_stamp;

关键注意点 #

• 必须保证数据按t_stamp排序：填充逻辑依赖时间顺序，所以ORDER BY t_stamp是核心，不能省略。
• 如果Union子查询有重复的t_stamp：可以根据需求用UNION ALL保留重复，或者UNION自动去重，但要确保重复时间点的处理符合你的业务逻辑。
• 初始Null的处理：如果序列开头就是Null，这两种方法都会保留Null，因为没有前一个非Null值；如果需要填充默认值，可以在最后用COALESCE()包裹填充后的字段，比如COALESCE(S1_Stat_Filled, 0)。

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