LAG 与时间序列缺口填补

示例说明

传感器丢数据是常态：某小时无记录不代表无温度。补齐的第一步是构造完整时间轴——generate_series 按固定间隔铺出全部桶位，真实观测左连接上去，缺失桶的值为 NULL。LAG 把前一个已知值带到当前行，COALESCE 完成前向填补。间隔检测则反向利用：相邻桶间隔超出一格即为缺口。

技术要点

• generate_series 构造无缺口时间轴
• LAG 前向携带最近已知值
• 间隔差值检测缺口宽度

代码解读

joined 里 LAG 按桶序取前值——注意它在 NULL 行上也会携带更早的值，填补因此可以跨多桶连续生效；外层 COALESCE 优先真实值、缺失时回落前值。第二个查询按相邻桶差值过滤超出一小时的行，输出的缺口起止与宽度支持运维定位采集故障时段。

使用建议

前向填补的陈旧度要设上限——超过 N 桶的缺口应标记而非硬填。桶粒度与 generate_series 步长严格一致。分析口径用填补值、告警口径用原始值分开设表。

代码示例

-- 用 generate_series 铺满时间轴，再左连接真实观测
WITH full_axis AS (
  SELECT generate_series(
    date_trunc('hour', '2026-10-21 00:00'::timestamp),
    date_trunc('hour', '2026-10-21 12:00'::timestamp),
    interval '1 hour'
  ) AS bucket
),
joined AS (
  SELECT
    f.bucket,
    s.temperature,
    LAG(s.temperature) OVER (ORDER BY f.bucket) AS prev_temp
  FROM full_axis f
  LEFT JOIN sensor_readings s
    ON date_trunc('hour', s.read_at) = f.bucket
)
SELECT
  bucket,
  temperature,
  COALESCE(
    temperature,
    prev_temp  -- 缺失点沿用上一个已知值
  ) AS filled_temperature
FROM joined
ORDER BY bucket;

-- 只列出缺口清单：与前一个桶的间隔超过一格
WITH detected AS (
  SELECT
    bucket,
    LAG(bucket) OVER (ORDER BY bucket) AS prev_bucket,
    temperature
  FROM joined_fillable
)
SELECT
  prev_bucket AS gap_start,
  bucket AS gap_end,
  bucket - prev_bucket AS gap_width
FROM detected
WHERE prev_bucket IS NOT NULL
  AND bucket - prev_bucket > interval '1 hour';