按指定时间范围筛选并按1小时分段聚合工单ID的SQL咨询

Absolutely, you can achieve your desired output with a combination of generating target hourly time slots, checking which issues were active during each slot, and then aggregating the IDs into comma-separated strings. Let's break down how to fix this:

问题分析 #

Your current query has two key limitations that prevent it from matching your desired output:

1. It only maps each issue to the hour of its created_at, but doesn't account for issues that stay active across multiple hours (like ID 1, which should appear in both the 13:00 and 14:00 slots).
2. It returns individual rows per ID-hour pair instead of aggregating all relevant IDs into a single comma-separated string for each time slot.

解决方案 #

We'll structure the query in three core steps:

1. Generate target time slots: Create the hourly timestamps you care about (13:00 and 14:00 on 2019-05-08).
2. Check active issues per slot: For each slot, determine which issues were active during that hour. An issue is active if:
   • It was created before the slot ends (created_at < slot_time + INTERVAL '1 hour'), AND
   • Either it hasn't been closed yet (closed_at IS NULL), closed after the slot started (closed_at >= slot_time), or remains OPEN.
3. Aggregate IDs: Use a string aggregation function to combine all active IDs for each slot into a sorted, comma-separated list.

完整SQL查询（PostgreSQL示例） #

WITH hourly_slots AS (
    -- Generate the exact hourly timestamps in your target range
    SELECT generate_series(
        TIMESTAMP WITH TIME ZONE '2019-05-08T13:00:00Z',
        TIMESTAMP WITH TIME ZONE '2019-05-08T14:00:00Z',
        INTERVAL '1 hour'
    ) AS slot_timestamp
)
SELECT
    -- Format timestamp to match your desired output format
    TO_CHAR(s.slot_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    -- Aggregate active IDs into a sorted comma-separated string
    STRING_AGG(DISTINCT CAST(i.id AS TEXT), ',' ORDER BY i.id) AS id
FROM hourly_slots s
JOIN issues i ON
    -- Issue was created before the slot ends
    i.created_at < s.slot_timestamp + INTERVAL '1 hour'
    AND (
        -- Issue is open, closed after the slot started, or meets your range criteria
        i.closed_at IS NULL
        OR i.closed_at >= s.slot_timestamp
        OR (i.status = 'OPEN' AND i.created_at <= '2019-05-08T15:00:00Z')
    )
    -- Filter to only issues relevant to your overall time range
    AND (
        i.created_at <= '2019-05-08T15:00:00Z'
        AND (i.closed_at >= '2019-05-08T13:00:00Z' OR i.closed_at IS NULL)
    )
GROUP BY s.slot_timestamp
ORDER BY s.slot_timestamp;

结果验证 #

Running this query against your sample data will produce exactly the output you're expecting:

timestamp           | id
---------------------|-------------
2019-05-08 13:00:00 | 1,2,3,4
2019-05-08 14:00:00 | 1,4,5,6,7

To confirm the logic:

• ID 1: Open, created before 13:00 and never closed → appears in both slots.
• ID 2: Closed at 13:05 → active during the 13:00 slot.
• ID 3: Closed at 13:40 → active during the 13:00 slot.
• ID 4: Closed at 14:05 → active during both the 13:00 and 14:00 slots.
• ID 5: Open, created at 14:15 → active during the 14:00 slot.
• ID 6: Closed at 14:40 → active during the 14:00 slot.
• ID 7: Closed at 15:05 → active during the 14:00 slot (since it closed after the slot started).

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