在Apache Superset中编写PostgreSQL查询补全时间序列缺失数据

核心思路 #

• 生成所有meter_id与每小时时间区间的完整笛卡尔积，确保没有遗漏的时间点
• 左关联原始消费数据表，匹配已有记录
• 对缺失的记录填充默认值，并标记Notes字段
• 适配Redshift严格的GROUP BY规则，避免字段聚合错误

完整查询语句 #

WITH all_meters AS (
    -- 从account表获取所有有效的meter_id（对应device_id）
    SELECT DISTINCT device_id AS meter_id
    FROM account
),
hourly_time_series AS (
    -- 生成需要覆盖的时间范围，这里以2024-09-10为例，可根据实际调整
    SELECT 
        date_trunc('hour', ts) AS start_read_time_local,
        date_trunc('hour', ts) + INTERVAL '1 hour' AS end_read_time_local
    FROM generate_series(
        '2024-09-10 00:00:00'::TIMESTAMP WITHOUT TIME ZONE,
        '2024-09-10 23:00:00'::TIMESTAMP WITHOUT TIME ZONE,
        INTERVAL '1 hour'
    ) AS ts
),
complete_meter_hours AS (
    -- 生成所有meter_id与小时时间的组合
    SELECT 
        am.meter_id,
        hts.start_read_time_local,
        hts.end_read_time_local
    FROM all_meters am
    CROSS JOIN hourly_time_series hts
)
SELECT 
    cmh.meter_id,
    cmh.start_read_time_local,
    cmh.end_read_time_local,
    -- 填充缺失的consumption值，已有记录保留原数值
    COALESCE(wc.consumption, 0) AS consumption,
    -- 标记数据缺失状态
    CASE WHEN wc.meter_id IS NULL THEN '数据缺失' ELSE NULL END AS Notes
FROM complete_meter_hours cmh
LEFT JOIN water_consumption wc
    ON cmh.meter_id = wc.meter_id
    AND cmh.start_read_time_local = wc.start_read_time_local
-- 如果需要按维度聚合，确保所有非聚合字段都在GROUP BY中
GROUP BY 
    cmh.meter_id,
    cmh.start_read_time_local,
    cmh.end_read_time_local,
    wc.consumption,
    wc.meter_id
ORDER BY cmh.meter_id, cmh.start_read_time_local;

关键问题解决说明 #

Redshift GROUP BY错误修复 #

你之前遇到的column 'wc.end_read_time_local' must appear in the GROUP BY clause or be used in an aggregate function错误，本质是Redshift要求SELECT中所有非聚合函数包裹的字段必须出现在GROUP BY中。

• 本查询中，我们将所有需要展示的字段（包括关联表的字段）都加入了GROUP BY；
• 如果原表中每个meter_id + start_read_time_local对应唯一记录，GROUP BY不会改变数据结果，只是满足Redshift的语法要求。

时间序列生成注意事项 #

• Redshift的generate_series支持TIMESTAMP类型，但如果是大范围时间，建议用递归CTE替代（避免性能问题）：

-- 递归生成时间序列的替代写法
WITH RECURSIVE hourly_time_series AS (
    SELECT '2024-09-10 00:00:00'::TIMESTAMP WITHOUT TIME ZONE AS start_read_time_local
    UNION ALL
    SELECT start_read_time_local + INTERVAL '1 hour'
    FROM hourly_time_series
    WHERE start_read_time_local < '2024-09-10 23:00:00'::TIMESTAMP WITHOUT TIME ZONE
)
SELECT 
    start_read_time_local,
    start_read_time_local + INTERVAL '1 hour' AS end_read_time_local
FROM hourly_time_series;

字段填充逻辑 #

• 用COALESCE(wc.consumption, 0)将缺失的consumption填充为0（可根据业务需求改为NULL或其他默认值）；
• 通过CASE WHEN wc.meter_id IS NULL THEN '数据缺失' ELSE NULL END精准标记缺失的记录。

适配Apache Superset的注意事项 #

• 将时间范围参数化：可以用Superset的时间过滤器替代硬编码的时间值，比如用{{ start_dttm }}和{{ end_dttm }}变量，让查询支持交互式时间选择；
• 如果meter_id数量较多，建议添加WHERE条件过滤特定meter_id，提升查询性能。

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