中文
devexamples

LAG LEAD Gap Fill Time Series

Detect missing intervals in time series with LAG over generated series, then backfill gaps by carrying the last known value forward.

SqlData Integrity

Code

snippet
-- 用 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';

Related Examples