PostgreSQL中generate_series关联时如何处理传感器数据间隙？

Alright, let's solve this problem properly—you need exactly 13 hourly records for the last 13 hours, with any data gaps filled by the most recent non-null pulse count, and it has to perform well on a table with millions of rows. Here's a robust, efficient solution:

First, let's break down what we need to do:

• Generate a precise 13-hour hourly time series (no missing buckets)
• Efficiently aggregate your sensor data to get the maximum pulse count per hour (since your counter only increments, the max value each hour is the cumulative count at that hour's end)
• Fill gaps with the last known valid value without killing query performance

The Query #

We'll use CTEs to split the work into manageable, efficient steps, plus PostgreSQL's LAST_VALUE with IGNORE NULLS (available in PG11+) to fill gaps quickly:

WITH hourly_max_pulses AS (
  -- Get the highest pulse count for each hour in the last 13 hours
  SELECT
    date_trunc('hour', inserted_at) AS hour_start,
    MAX((payload->>'pulse_count')::integer) AS max_pulse
  FROM ttnmessages
  -- Filter to only the data we need (avoids scanning the entire million-row table)
  WHERE inserted_at >= NOW() - INTERVAL '13 hours'
  GROUP BY date_trunc('hour', inserted_at)
),
hourly_time_series AS (
  -- Generate exactly 13 hourly buckets (from 12 hours ago to current hour)
  SELECT generate_series(
    date_trunc('hour', NOW() - INTERVAL '12 hours'),
    date_trunc('hour', NOW()),
    INTERVAL '1 hour'
  ) AS hour_start
)
SELECT
  ts.hour_start,
  -- Fill gaps with the most recent non-null pulse count
  LAST_VALUE(hm.max_pulse) OVER (
    ORDER BY ts.hour_start
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    IGNORE NULLS
  ) AS filled_pulse_count
FROM hourly_time_series ts
LEFT JOIN hourly_max_pulses hm ON ts.hour_start = hm.hour_start
ORDER BY ts.hour_start;

Performance Optimization (Critical for Large Tables) #

To make this query fly on your million-row table, you need a composite index that lets PostgreSQL quickly filter and aggregate the data:

CREATE INDEX idx_ttnmessages_inserted_at_pulse ON ttnmessages USING btree (
  inserted_at,
  (payload->>'pulse_count')::integer
);

This index allows PostgreSQL to:

• Quickly find all rows from the last 13 hours without scanning the entire table
• Compute the MAX(pulse_count) per hour using the index's ordered structure, avoiding expensive sorting operations

Edge Case Handling #

• If the first hour in your series has no data, filled_pulse_count will be NULL. If you want to default this to an initial value (like 0), wrap the LAST_VALUE call in COALESCE:

  COALESCE(
    LAST_VALUE(hm.max_pulse) OVER (
      ORDER BY ts.hour_start
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      IGNORE NULLS
    ),
    0 -- Replace with your sensor's initial pulse count if different
  ) AS filled_pulse_count
  

This approach guarantees exactly 13 rows, correctly fills gaps with the last known value, and runs efficiently even on large datasets—no slow recursive queries or full table scans required.

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