如何编写PostgreSQL查询按指定时间间隔补全数据缺失行？

针对你需要按1秒间隔补全时间序列缺失行的需求，可以通过生成连续时间序列+左连接原表的方式实现，以下是具体查询示例及说明：

基础查询示例（适配你的数据集） #

假设你的表名为entity_data，执行以下SQL即可得到预期结果：

WITH time_series AS (
    -- 生成原数据时间范围内的连续1秒时间序列
    SELECT generate_series(
        (SELECT MIN(date) FROM entity_data),
        (SELECT MAX(date) FROM entity_data),
        INTERVAL '1 second'
    ) AS series_date
),
distinct_entities AS (
    -- 提取所有唯一的实体名称（支持多实体场景）
    SELECT DISTINCT name FROM entity_data
)
SELECT 
    ts.series_date AS date,
    de.name,
    ed.value
FROM time_series ts
-- 为每个实体生成全量时间框架
CROSS JOIN distinct_entities de
-- 左连接原表，匹配时间和实体，缺失行自动填充null
LEFT JOIN entity_data ed 
    ON ed.date = ts.series_date 
    AND ed.name = de.name
-- 按实体和时间排序
ORDER BY de.name, ts.series_date;

查询逻辑说明 #

1. time_series CTE：利用PostgreSQL内置的generate_series函数，基于原表的最小/最大时间生成连续的1秒间隔时间序列，覆盖所有需要补全的时间点。
2. distinct_entities CTE：提取表中所有唯一的实体名称，确保即使存在多个实体，每个实体都能生成完整的时间序列。
3. CROSS JOIN：将每个实体与所有时间点组合，构建出每个实体的全量时间框架。
4. LEFT JOIN：关联原表数据，存在匹配的行则取出value，无匹配的行value自动为null。
5. ORDER BY：按实体名称和时间排序，得到有序的最终结果。

扩展场景建议 #

• 固定时间范围：如果不需要基于原表的min/max时间，可直接指定起止时间：

  generate_series('2022-10-09T10:00:00+00:00'::timestamptz, '2022-10-09T10:00:05+00:00'::timestamptz, INTERVAL '1 second')
  

• 自定义时间间隔：如需调整步长（如5分钟），修改INTERVAL参数即可：INTERVAL '5 minutes'。
• 插值填充非null值：如果希望缺失行的value填充前一个有效值，可结合窗口函数LAG：

  SELECT 
      ts.series_date AS date,
      de.name,
      -- 用前一个有效值填充null
      COALESCE(ed.value, LAG(ed.value) OVER (PARTITION BY de.name ORDER BY ts.series_date)) AS value
  FROM time_series ts
  CROSS JOIN distinct_entities de
  LEFT JOIN entity_data ed 
      ON ed.date = ts.series_date 
      AND ed.name = de.name
  ORDER BY de.name, ts.series_date;
  

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