如何按移动非固定时间间隔分组统计并发事件数？能否仅用SQL实现？

Absolutely, you can achieve this with pure SQL! The key is to break down the timeline using all the start and end points from your events, then calculate how many events are active during each of these segmented intervals. Let's walk through two approaches, including a more efficient one for larger datasets.

Approach 1: Interval Segmentation with Subquery Count #

This method first extracts all critical time points (starts and ends), pairs them into consecutive intervals, then counts active events for each interval.

Assuming your table is named events, here's the query:

WITH time_points AS (
    -- Collect all unique start and end times (handle timezone consistency)
    SELECT CAST(start AS TIMESTAMP WITH TIME ZONE) AS point FROM events
    UNION
    SELECT end AS point FROM events
),
time_intervals AS (
    -- Pair each time point with the previous one to form intervals
    SELECT
        LAG(point) OVER (ORDER BY point) AS interval_start,
        point AS interval_end
    FROM time_points
)
-- Count events active throughout each interval
SELECT
    interval_start AS start,
    interval_end AS end,
    (SELECT COUNT(*) 
     FROM events e
     WHERE CAST(e.start AS TIMESTAMP WITH TIME ZONE) <= ti.interval_start
       AND e.end >= ti.interval_end) AS count
FROM time_intervals ti
WHERE interval_start IS NOT NULL -- Skip the first interval with no start
ORDER BY interval_start;

How it works:

1. time_points: Gathers all unique start/end times, converting start times to timezone-aware timestamps to match your end times (which have +02 offset).
2. time_intervals: Uses the LAG() window function to create continuous intervals between each sorted time point.
3. Final Count: For each interval, we count events that started before/at the interval start and end after/at the interval end—meaning they were active the entire time.

Approach 2: Efficient Active Count with Cumulative Sum #

This method is better for large datasets because it avoids repeated subqueries. It tracks changes in active events as we move through the timeline:

WITH time_points AS (
    -- Mark start events as +1, end events as -1
    SELECT CAST(start AS TIMESTAMP WITH TIME ZONE) AS point, 1 AS delta FROM events
    UNION ALL
    SELECT end AS point, -1 AS delta FROM events
),
sorted_points AS (
    -- Calculate cumulative active events as we sort through time points
    SELECT
        point,
        SUM(delta) OVER (ORDER BY point) AS active_count,
        LAG(point) OVER (ORDER BY point) AS prev_point
    FROM time_points
)
-- Filter out empty intervals and periods with no active events
SELECT
    prev_point AS start,
    point AS end,
    active_count AS count
FROM sorted_points
WHERE 
    prev_point IS NOT NULL
    AND prev_point != point -- Skip zero-length intervals
    AND active_count > 0 -- Skip periods with no active events
GROUP BY prev_point, point, active_count
ORDER BY prev_point;

How it works:

1. time_points: Tags each start event as increasing the active count by 1, and each end event as decreasing it by 1.
2. sorted_points: Sorts all time points and uses a cumulative sum to track how many events are active at each point. The LAG() function pairs each point with the previous one.
3. Final Filter: We only keep intervals where there are active events and the interval has actual duration, grouping duplicates to avoid redundant rows.

Key Notes: #

• Timezone Consistency: Always ensure your timestamps are timezone-aware (or converted to a common timezone, like UTC) to avoid incorrect comparisons. The examples above convert start times to match the timezone-aware end times.
• Performance: The second approach is far more efficient for large datasets since it processes events in a single pass, whereas the first approach runs a subquery for every interval.

Testing this with your sample data will produce exactly the output you're looking for!

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