如何用SQL/Presto查询生成房间人员时间重叠区间表？

原始表（table_1）结构及数据 #

目标输出表 #

可以通过Presto/SQL实现这个需求，以下是具体方案：

Presto实现SQL #

WITH time_points AS (
    -- 提取所有唯一时间节点并排序
    SELECT DISTINCT time_point
    FROM (
        SELECT start_time AS time_point FROM table_1
        UNION ALL
        SELECT end_time AS time_point FROM table_1
    ) t
    ORDER BY time_point
),
time_intervals AS (
    -- 生成连续的相邻时间段
    SELECT
        tp1.time_point AS start_time,
        tp2.time_point AS end_time
    FROM time_points tp1
    JOIN time_points tp2 ON tp2.time_point > tp1.time_point
    WHERE NOT EXISTS (
        SELECT 1 FROM time_points tp3
        WHERE tp3.time_point > tp1.time_point AND tp3.time_point < tp2.time_point
    )
)
-- 匹配时间段内的人员并聚合
SELECT
    ARRAY_AGG(DISTINCT t.person ORDER BY t.person) AS people,
    ti.start_time,
    ti.end_time
FROM time_intervals ti
JOIN table_1 t
    ON t.start_time <= ti.start_time AND t.end_time >= ti.end_time
GROUP BY ti.start_time, ti.end_time
ORDER BY ti.start_time;

逻辑说明 #

1. time_points CTE：收集所有人员的开始、结束时间，去重排序后得到所有关键时间节点，这些节点是划分时间段的依据。
2. time_intervals CTE：将相邻的时间节点配对，生成无重叠的连续时间段区间，避免无效的中间区间。
3. 主查询：将每个时间段与原始表关联，筛选出该时间段内在场的人员（人员的进入时间早于等于区间开始，离开时间晚于等于区间结束），最后用ARRAY_AGG聚合人员列表并按时间排序输出。

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