在PostgreSQL中用默认值填充结果集中的时间间隙

要实现你的需求，核心思路是先生成所有需要的3分钟间隔分组，再和原统计结果做左连接，最后用COALESCE将空值替换为0。以下是完整的PostgreSQL查询方案：

WITH time_intervals AS (
    -- 生成1小时内所有3分钟间隔的minute_part（0到19，共20个）
    SELECT 
        '2021-01-01 19:00:00'::TIMESTAMP AS created,
        generate_series(0, 19) AS minute_part,
        'bar' AS worker
),
perf_stats_avg AS (
    -- 保留你原有的统计逻辑
    SELECT 
        date_trunc('hour', created) AS created, 
        (extract(minute FROM created)::int / 3) AS minute_part, 
        worker, 
        AVG(performance) AS avg_perf
    FROM perf_stats 
    WHERE group_id = 'foo' 
      AND worker = 'bar' 
      AND created > '2021-01-01 19:00:00' 
      AND created < '2021-01-01 20:00:00' 
    GROUP BY 1, 2, worker 
)
SELECT 
    ti.created,
    ti.minute_part,
    ti.worker,
    COALESCE(psa.avg_perf, 0) AS avg
FROM time_intervals ti
LEFT JOIN perf_stats_avg psa 
    ON ti.created = psa.created 
    AND ti.minute_part = psa.minute_part 
    AND ti.worker = psa.worker
ORDER BY ti.created, ti.minute_part, ti.worker;

关键部分解释： #

1. time_intervals CTE：生成全量的目标分组——固定目标小时的created值，生成0到19的minute_part（对应每3分钟一个时间块），并指定worker为'bar'。这一步确保了即使没有数据，每个间隔都会出现在最终结果里。
2. perf_stats_avg CTE：复用你原来的统计逻辑，计算有数据的间隔的平均performance，并重命名平均值为avg_perf方便后续关联。
3. 左连接与COALESCE：将全量间隔和统计结果左连接，对于没有匹配到数据的行，用COALESCE(psa.avg_perf, 0)把空的平均值替换为0，完美实现你想要的填充效果。

动态时间范围优化（可选） #

如果需要适配任意时间范围，而不是固定的19:00-20:00，可以修改time_intervals来动态生成目标小时：

WITH target_hour AS (
    SELECT '2021-01-01 19:00:00'::TIMESTAMP AS hour_start
),
time_intervals AS (
    SELECT 
        th.hour_start AS created,
        generate_series(0, 19) AS minute_part,
        'bar' AS worker
    FROM target_hour th
),
-- 后续perf_stats_avg和查询部分保持不变
...

这样只需要修改target_hour中的时间，就能轻松切换到其他小时的统计。

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