PostgreSQL中如何按指定起始时间与n分钟间隔对时间戳数据分箱

Alright, let's refactor your original PostgreSQL query to handle dynamic n-minute bins starting from a specific timestamp—no more relying on that pre-defined bins table. Here's how to do it, with explanations and extra options:

Basic Dynamic Binning (No Empty Bins) #

First, here's a straightforward version that counts observations in each n-minute bin starting from your chosen timestamp:

WITH params AS (
  SELECT 
    '2024-01-01 00:07:00'::TIMESTAMP AS bin_start_time,  -- Set your start time here
    15 AS bin_interval_minutes                           -- Set your bin size in minutes here
)
SELECT
  COUNT(id) AS observation_count,
  -- Calculate bucket number (starts at 1 for readability)
  FLOOR(EXTRACT(EPOCH FROM (time - bin_start_time)) / (bin_interval_minutes * 60)) + 1 AS bucket,
  -- Optional: Show exact time range for each bucket
  bin_start_time + ((FLOOR(EXTRACT(EPOCH FROM (time - bin_start_time)) / (bin_interval_minutes * 60))) * bin_interval_minutes * INTERVAL '1 minute') AS bucket_start,
  bin_start_time + ((FLOOR(EXTRACT(EPOCH FROM (time - bin_start_time)) / (bin_interval_minutes * 60)) + 1) * bin_interval_minutes * INTERVAL '1 minute') AS bucket_end
FROM observations
CROSS JOIN params
WHERE owner_id = 'some id'
  AND time >= bin_start_time  -- Optional: Filter to only data after your start time
GROUP BY bucket, bucket_start, bucket_end
ORDER BY bucket;

How This Works:

• The params CTE keeps your configurable values (start time, bin size) in one easy-to-edit spot, so you don't have to dig through the query logic to make changes.
• EXTRACT(EPOCH FROM (time - bin_start_time)) calculates the number of seconds between each observation and your start timestamp.
• Dividing that by bin_interval_minutes * 60 converts it to the number of full bin intervals that have passed since the start. Adding 1 ensures buckets start at 1 instead of 0 (way more intuitive for reporting!).
• The bucket_start and bucket_end columns are optional but incredibly helpful for verifying exactly which time range each bucket covers.

Including Empty Bins (Count = 0) #

If you need to show bins that have no observations (e.g., for consistent time-series reporting), we can generate all required bins first and then left join with your observations:

WITH params AS (
  SELECT 
    '2024-01-01 00:07:00'::TIMESTAMP AS bin_start_time,
    15 AS bin_interval_minutes,
    '2024-01-01 02:00:00'::TIMESTAMP AS bin_end_time  -- Define your end time here
),
generated_bins AS (
  SELECT
    generate_series(
      1,
      CEIL(EXTRACT(EPOCH FROM (bin_end_time - bin_start_time)) / (bin_interval_minutes * 60))::INTEGER
    ) AS bucket,
    bin_start_time + ((generate_series - 1) * bin_interval_minutes * INTERVAL '1 minute') AS bucket_start,
    bin_start_time + (generate_series * bin_interval_minutes * INTERVAL '1 minute') AS bucket_end
  FROM params
)
SELECT
  gb.bucket,
  gb.bucket_start,
  gb.bucket_end,
  COALESCE(COUNT(o.id), 0) AS observation_count
FROM generated_bins gb
LEFT JOIN observations o
  ON o.owner_id = 'some id'
  AND o.time >= gb.bucket_start
  AND o.time < gb.bucket_end
GROUP BY gb.bucket, gb.bucket_start, gb.bucket_end
ORDER BY gb.bucket;

Key Differences Here:

• The generated_bins CTE uses generate_series to create every bucket between your start and end times upfront, so no gaps in your time range.
• We use a left join with observations to ensure even bins with no matching records show up with a count of 0.
• COALESCE prevents NULL values for empty bins, keeping your output clean and consistent.

Why This Is Better Than Your Original Query #

Your original query relied on a pre-populated bins table, which is inflexible if you want to adjust the bin size or start time on the fly. This dynamic approach lets you tweak parameters in seconds without modifying any table data, making it perfect for ad-hoc analysis or variable reporting needs.

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