如何从PostgreSQL的时间记录表中提取无重叠的唯一时间段？

表结构 #

CREATE TABLE time_records (
  id uuid NOT NULL,
  employee_id uuid NOT NULL,
  starttime timestampt NOT NULL,
  endtime timestampt NOT NULL
);

示例数据 #

预期结果 #

当前错误的SQL及结果 #

错误SQL #

select timea.employee_id,
       min(timea.starttime) starttime,
       max(timea.endtime)   endtime,
       array_agg(timea.id) ids
from time_records timea
         inner join time_records timea2 on timea.employee_id = timea2.employee_id and
                                           tsrange(timea2.starttime, timea2.endtime, '[]') &&
                                           tsrange(timea.starttime, timea.endtime, '[]')
    and timea.id != timea2.id
group by timea.employee_id;

错误结果 #

正确的SQL实现 #

要解决多组重叠区间的合并问题，需要通过窗口函数识别连续/重叠的区间组，再进行聚合：

WITH ranked_records AS (
    SELECT 
        id,
        employee_id,
        starttime,
        endtime,
        -- 标记当前记录是否为新的区间组起始点
        CASE 
            WHEN starttime > LAG(endtime) OVER (PARTITION BY employee_id ORDER BY starttime)
            THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM time_records
),
grouped_records AS (
    SELECT 
        *,
        -- 累加起始点标记，得到每个记录所属的组ID
        SUM(is_new_group) OVER (PARTITION BY employee_id ORDER BY starttime) AS group_id
    FROM ranked_records
)
SELECT 
    employee_id,
    MIN(starttime) AS starttime,
    MAX(endtime) AS endtime,
    ARRAY_AGG(id ORDER BY id) AS 关联记录ID列表
FROM grouped_records
GROUP BY employee_id, group_id
ORDER BY employee_id, starttime;

逻辑说明 #

1. ranked_records CTE：按员工ID分组、开始时间排序，用LAG函数获取上一条记录的结束时间，判断当前记录是否与上一条重叠，标记新组起始点。
2. grouped_records CTE：累加起始点标记，为每个重叠区间组生成唯一的group_id。
3. 最终聚合：按员工ID和组ID分组，取每组的最小开始时间、最大结束时间，并聚合所有关联的记录ID。

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