如何用SQL查询所有存在时间戳区间重叠的ID

需求说明 #

现有业务表包含id、start time、end time三个字段，同一ID可能对应一条或多条时间区间记录，需要筛选出组内存在任意两条时间区间重叠的ID。
示例源表数据：

预期返回结果为存在区间重叠的ID：0。

最优实现（支持窗口函数的数据库，如MySQL8+、PostgreSQL、Hive、SparkSQL等） #

实现逻辑 #

利用窗口函数避免低效自连接：

• 按ID分组，组内按开始时间升序排序，通过LAG函数拿到每条记录上一条区间的结束时间
• 只要当前记录的开始时间早于上一条区间的结束时间，就说明两个区间存在重叠
• 对符合条件的ID去重即可，单条记录的ID因为没有上一条区间，会被自动排除

代码示例 #

假设业务表名为biz_time_record，SQL如下：

WITH sorted_interval AS (
    SELECT
        id,
        `start time`,
        `end time`,
        LAG(`end time`) OVER (PARTITION BY id ORDER BY `start time`) AS prev_end
    FROM biz_time_record -- 替换为实际表名
)
SELECT DISTINCT id
FROM sorted_interval
WHERE `start time` < prev_end;

注意：如果你的业务规则中「前一个区间的结束时间等于后一个区间的开始时间」也算重叠，只需要把WHERE条件的<改成<=即可。

兼容旧版本数据库的实现（不支持窗口函数的场景，如MySQL5.x） #

如果数据库版本不支持窗口函数，可以通过自连接匹配同ID下的重叠区间，缺点是同ID下记录数较多时性能较差：

SELECT DISTINCT t1.id
FROM biz_time_record t1
INNER JOIN biz_time_record t2
ON t1.id = t2.id
-- 排除同一条记录和自身匹配
AND NOT (t1.`start time` = t2.`start time` AND t1.`end time` = t2.`end time`)
-- 时间区间重叠判断规则
AND t1.`start time` < t2.`end time`
AND t1.`end time` > t2.`start time`;

结果验证 #

用示例数据运行上述窗口函数写法的SQL：

• id=1、id=2均只有1条记录，prev_end为空，不会被筛选
• id=0的两条记录按开始时间排序后，第二条记录的开始时间2022-06-10 12:45:55小于上一条的结束时间2022-06-10 12:46:55，满足重叠条件，最终返回id=0，完全符合预期。

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