优化PostgreSQL环境下dbt时间序列数据补全管道性能

你的核心需求是给6个数值列补全缺失值：优先取同group_id下最近的非空前置值，没有的话用全表平均值。原代码逻辑没问题，但因为用了关联子查询，每一行缺失值都要单独查询一次，120万条数据会产生数百万次重复查询，直接导致性能爆炸。

优化思路 #

用PostgreSQL的窗口函数替代关联子查询，只需要扫描一次表就能完成所有列的前置非空值填充，再结合预计算的全表平均值做兜底，整体性能会提升几个数量级。

优化后代码 #

with avg_stats as (
    select 
        avg(stat_1) as avg_stat_1,
        avg(stat_2) as avg_stat_2,
        avg(stat_3) as avg_stat_3,
        avg(stat_4) as avg_stat_4,
        avg(stat_5) as avg_stat_5,
        avg(stat_6) as avg_stat_6
    from my_db.my_table
),
filled_previous as (
    select 
        group_id,
        timestamp,
        -- 用LAST_VALUE取同组内当前行之前最近的非空值
        last_value(stat_1 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_1,
        last_value(stat_2 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_2,
        last_value(stat_3 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_3,
        last_value(stat_4 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_4,
        last_value(stat_5 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_5,
        last_value(stat_6 ignore nulls) over (
            partition by group_id 
            order by timestamp 
            rows between unbounded preceding and current row
        ) as filled_stat_6
    from my_db.my_table
)
select 
    fp.group_id,
    fp.timestamp,
    -- 先取原字段值，没有的话用填充的前置值，再没有用全表平均值
    coalesce(fp.filled_stat_1, as_avg.avg_stat_1) as stat_1,
    coalesce(fp.filled_stat_2, as_avg.avg_stat_2) as stat_2,
    coalesce(fp.filled_stat_3, as_avg.avg_stat_3) as stat_3,
    coalesce(fp.filled_stat_4, as_avg.avg_stat_4) as stat_4,
    coalesce(fp.filled_stat_5, as_avg.avg_stat_5) as stat_5,
    coalesce(fp.filled_stat_6, as_avg.avg_stat_6) as stat_6
from filled_previous fp
cross join avg_stats as_avg; -- 全表平均值只有一行，cross join不影响结果

额外性能优化建议 #

给原表建复合索引，窗口函数的partition by group_id order by timestamp会用到这个索引，能大幅减少排序开销：

create index idx_group_timestamp on my_db.my_table (group_id, timestamp);

为什么原代码慢？ #

原代码里的(select previous.stat_1 ... limit 1)是关联子查询，每一行数据都会单独执行一次这个查询，6个字段就是6次/行，120万条数据就是720万次查询，数据库根本扛不住。而窗口函数是一次性扫描表，按分组排序后批量计算所有行的前置非空值，只需要几次表扫描就能完成。

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