使用T-SQL按ID分组处理重叠时间戳的技术问题

需求：按ID和task_id分组，移除嵌套重叠的时间戳。时间戳存在以下特征：

• 可能出现嵌套重叠、相同开始/结束时间
• 若后一条记录的开始时间早于前一条的结束时间，则其结束时间必然早于或等于前一条的结束时间，且时间差不超过12小时

示例数据 #

ID  task_id starttime                       endtime
11  1       2023-01-10 06:31:00.000         2023-01-10 08:53:00.000
11  1       2023-01-10 08:00:00.000         2023-01-10 08:53:00.000
11  2       2023-01-10 13:14:00.000         2023-01-10 15:15:00.000
11  2       2023-01-10 15:46:00.000         2023-01-10 17:59:00.000
11  2       2023-01-10 18:49:00.000         2023-01-10 18:50:00.000
12  3       2023-01-09 10:10:00.000         2023-01-09 11:10:00.000
12  3       2023-01-09 10:10:00.000         2023-01-09 10:50:00.000
13  4       2023-01-08 20:00:00.000         2023-01-09 03:44:00.000
13  4       2023-01-08 21:00:00.000         2023-01-09 02:00:00.000
14  5       2023-01-01 19:23:00.000         2023-01-01 20:47:00.000
14  5       2023-01-02 03:35:00.000         2023-01-02 06:57:00.000

期望结果 #

ID  task_id starttime                       endtime
11  1       2023-01-10 06:31:00.000         2023-01-10 08:53:00.000
11  2       2023-01-10 13:14:00.000         2023-01-10 15:15:00.000
11  2       2023-01-10 15:46:00.000         2023-01-10 17:59:00.000
11  2       2023-01-10 18:49:00.000         2023-01-10 18:50:00.000
12  3       2023-01-09 10:10:00.000         2023-01-09 11:10:00.000
13  4       2023-01-08 20:00:00.000         2023-01-09 03:44:00.000
14  5       2023-01-01 19:23:00.000         2023-01-01 20:47:00.000
14  5       2023-01-02 03:35:00.000         2023-01-02 06:57:00.000

之前尝试的问题 #

用LEAD函数标记重叠的写法无法处理边缘场景：

CASE WHEN LEAD(starttime) OVER (PARTITION BY task_id ORDER BY starttime) <> endtime THEN 1 ELSE 0 END AS overlap_tag

• 无法识别ID11、task_id2中18:49-18:50这类非重叠记录
• 未考虑跨日期的时间范围

解决方案 #

使用NOT EXISTS子查询筛选出未被其他记录完全包含的时间范围，同时保留不重叠的独立记录：

SELECT t1.*
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.ID = t1.ID
      AND t2.task_id = t1.task_id
      -- 其他记录的时间范围完全包含当前记录
      AND t2.starttime <= t1.starttime
      AND t2.endtime >= t1.endtime
      -- 排除自身（即当前记录和其他记录不是完全相同的时间范围）
      AND (t2.starttime < t1.starttime OR t2.endtime > t1.endtime)
);

逻辑说明 #

1. 按ID和task_id分组匹配记录
2. 检查当前记录是否被同组内的其他记录完全包含（开始时间更早/相同，结束时间更晚/相同）
3. 排除被包含的记录，保留覆盖范围最大的记录以及不重叠的独立记录

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