选择具有重叠时间戳值的所有 ID

Question

给定此表，我试图选择所有具有重叠的start_time和end_time按ID分组的ID。在这种情况下，表有多个id，这些id可能有也可能没有多个条目。(在这种情况下，id的1和2没有多行，而0有。)是否有一种方法来检索有一个重叠的开始和结束时间在Sql中的所有id ?

示例:给定这个表，设计一些查询来得到后面的表。

tbody> <<tr>12

id	起始时间	结束时间
0	2022-06-10 12:44:55	2022-06-10 12:46:55
	2022-06-10 12:47:55	2022-06-10 12:48:55
	2022-06-10 12:49:00	2022-06-10 12:50:00
0	2022-06-10 12:45:55	2022-06-10 12:48:55

小提琴:

With SRC AS (
SELECT 0 id,    '2022-06-10 12:44:55' start_time,   '2022-06-10 12:46:55' end_time FROM DUAL UNION ALL
SELECT 1,   '2022-06-10 12:47:55',  '2022-06-10 12:48:55'  FROM DUAL UNION ALL
SELECT 2,   '2022-06-10 12:49:00',  '2022-06-10 12:50:00'  FROM DUAL UNION ALL
SELECT 0,   '2022-06-10 12:45:55',  '2022-06-10 12:48:55'  FROM DUAL )
SELECT distinct A.id
FROM SRC A
CROSS JOIN SRC B
on  (B.start_time between A.start_time and A.end_time 
OR B.end_time between  A.start_time and A.end_time)
AND (A.start_time <> B.start_Time
OR  B.end_time <> B.end_time)
AND  A.id = B.id

给我们:

+-----+
| Aid |
+-----+
|   0 |
+-----+

有多种方法可以做到这一点，但基本的思想是这样的:

WITH mytable AS
(
SELECT row_number() OVER () rid, id, starttime, endtime
FROM myrealtable
)
SELECT distinct id
FROM mytable mt1
WHERE exists
(SELECT *
FROM mytable mt2
WHERE mt2.id = mt1.id 
AND mt2.starttime >= mt1.starttime and mt2.starttime < mt1.endtime)
OR exists 
(SELECT *
FROM mytable mt2
WHERE mt2.id = mt1.id 
AND mt2.endtime > mt1.starttime and mt2.endtime <= mt1.endtime)

或:

WITH mytable AS
(
SELECT row_number() OVER () rid, id, starttime, endtime
FROM myrealtable
)
SELECT distinct mt1.id
FROM mytable mt1 INNER JOIN mytable mt2
ON mt2.id = mt1.id and mt2.rid <> mt1.rid
WHERE (mt2.starttime >= mt1.starttime and mt2.starttime < mt1.endtime)
OR (mt2.endtime > mt1.starttime and mt2.endtime <= mt1.endtime)

CTE只是从row_number获得一个唯一的id (rid)，以确保我们不匹配行本身。您可能有另一种方法来做到这一点(例如，也许您知道所有列都不可能相同)。

如果你想处理时间的包容性，你可以使用BETWEEN使条件更简单一点，但我认为10:00-11:00和11:00-11:30不应该被认为是重叠的。

我列出了两种不同的方法，因为我认为第一种方法(与EXISTS)是非常可读的，很容易理解它在做什么。第二种方法(使用JOIN)可能更好(更短，最终可能优化得更好)，如果您理解第一个示例，应该是有意义的。

这是一个DB小提琴显示它。