PostgreSQL查找小时序列缺失时间及多Key适配、CTE疑问

1. continuous("timestamp") CTE的定义 #

你提到的continuous("timestamp")是一个公共表表达式（CTE），并非内置函数，作用是生成一段连续的小时级时间序列，作为基准用来对比实际数据，找出缺失的时间点。典型的实现逻辑是用generate_series生成指定时间范围内的所有整点时间戳，示例如下：

WITH continuous("timestamp") AS (
    SELECT generate_series(
        -- 起始时间：目标Key覆盖的最早时间
        (SELECT MIN("timestamp") FROM period_of_hours WHERE key = ANY('{uuid1, uuid2}'::uuid[])),
        -- 结束时间：目标Key覆盖的最晚时间
        (SELECT MAX("timestamp") FROM period_of_hours WHERE key = ANY('{uuid1, uuid2}'::uuid[])),
        '1 hour'::interval -- 步长为1小时
    ) AS "timestamp"
)

2. 适配多UUID列表的查询方案 #

针对多个Key的场景，需要为每个Key生成完整的时间序列，再与实际表数据做差集筛选缺失项。完整SQL示例如下：

WITH continuous("timestamp") AS (
    -- 生成目标Key覆盖范围内的连续小时序列
    SELECT generate_series(
        (SELECT MIN("timestamp") FROM period_of_hours WHERE key = ANY('{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'::uuid[])),
        (SELECT MAX("timestamp") FROM period_of_hours WHERE key = ANY('{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'::uuid[])),
        '1 hour'::interval
    ) AS "timestamp"
),
target_keys AS (
    -- 将输入的UUID列表转为行数据
    SELECT unnest('{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'::uuid[]) AS key
)
-- 筛选每个Key对应的缺失小时
SELECT t.key, c."timestamp"
FROM target_keys t
CROSS JOIN continuous c
LEFT JOIN period_of_hours p 
    ON p.key = t.key AND p."timestamp" = c."timestamp"
WHERE p."timestamp" IS NULL
ORDER BY t.key, c."timestamp";

关键逻辑说明 #

• target_keys CTE：把传入的UUID数组拆分为单独的行，方便后续与时间序列做笛卡尔积，生成每个Key应有的完整时间组合
• CROSS JOIN：为每个Key匹配所有连续小时，得到理论上应该存在的(key, timestamp)对
• LEFT JOIN + WHERE p."timestamp" IS NULL：过滤掉实际表中已存在的记录，剩余的就是每个Key缺失的小时数据

可选优化 #

• 如果需要固定时间范围（而非依赖目标Key的时间边界），直接替换generate_series的起止参数即可，例如：

  generate_series('2024-01-01 00:00:00'::timestamp, '2024-01-31 23:00:00'::timestamp, '1 hour')
  

• 若表数据量较大，建议给period_of_hours表创建(key, timestamp)复合索引，提升关联查询的效率

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