ORACLE SQL overlap时间段重叠计算方法

在ORACLE里如何查找重叠时间段的记录，先看建表语句：

drop table overlap_test;
CREATE TABLE overlap_test (
  id         NUMBER primary key,
  staff_name varchar2(100),
  start_date DATE,
  end_date   DATE
);


-- id是主键


-- 查询相同staff有日期重叠的，从下面的看出，要查除了除了id=1,2,8的
INSERT INTO overlap_test VALUES (1, 'a',TO_DATE('01-JAN-2024','DD-MON-YYYY'), TO_DATE('05-JAN-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (2, 'a',TO_DATE('05-MAR-2024','DD-MON-YYYY'), TO_DATE('08-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (3, 'b',TO_DATE('04-MAR-2024','DD-MON-YYYY'), TO_DATE('07-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (4, 'b',TO_DATE('06-MAR-2024','DD-MON-YYYY'), TO_DATE('09-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (5, 'b',TO_DATE('06-MAR-2024','DD-MON-YYYY'), TO_DATE('07-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (6, 'c',TO_DATE('04-MAR-2024','DD-MON-YYYY'), TO_DATE('09-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (7, 'c',TO_DATE('05-MAR-2024','DD-MON-YYYY'), TO_DATE('10-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (8, 'c',TO_DATE('11-MAR-2024','DD-MON-YYYY'), TO_DATE('12-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (9, 'd',TO_DATE('01-MAR-2024','DD-MON-YYYY'), TO_DATE('12-MAR-2024','DD-MON-YYYY'));
INSERT INTO overlap_test VALUES (10,'d',TO_DATE('02-MAR-2024','DD-MON-YYYY'), TO_DATE('03-MAR-2024','DD-MON-YYYY'));
COMMIT;


-- 建个索引，分析不同写法的执行计划
create index idx_overlap_test on overlap_test(staff_name,start_date,end_date);

overlap重叠查找算法：
一般用子查询实现，如果用join会导致结果集重复数量增多，因为不是1对1关系，而是1行对应多行。

使用子查询自关联，比如主表a,子查询的a取个别名b,对应的有start_date,end_date以及唯一标识id以及自关联的比如这里是staff_id

overlap思想：
分为交集和包含关系两种：
1）交集
start           end
|               |       -- 记录 a
   |                |   -- 第二条记录 b
可以用a.end_date between b.start_date and b.end_date
查询第二条可以用a.start_date between b.start_date and a.end_date
整体可以用or关系表示
a.end_date between b.start_date and b.end_date
or
a.start_date between b.start_date and a.end_date

2） 包含关系
start          end
|              |  -- 记录 a
   |      |       -- 第二条 b


用1）交集算法则a查询不到的，要查询a，也即按照第二条的start_date为基准即可
a.start_date < b.start_date and a.end_date > b.start_date

将1）和2）合并
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始 


a.start_date <= b.end_date and a.end_date>= b.start_date




1： 子查询里有OR，不能展开，走FILTER，可能效率不好
1) a的开始时间在b的时间范围内 或
2) a的结束时间在b的时间范围内 或
3) a的开始时间

2：没有OR，按照条件关联比如staff_name一样的，可以走HASH JOIN，可能更好
如果将 交集和并集合并，简单的算法是: a的开始小于等于b的结束，a的结束大于等于b的开始 
a.start_date <= b.end_date and a.end_date>= b.start_date

1实现： 这种子查询有or的复杂条件，不能展开，只能走FILTER，可能效率低：

SELECT a.*
FROM  overlap_test a 
WHERE  EXISTS (SELECT 1
               FROM   overlap_test b
               WHERE ( a.start_date between b.start_date and b.end_date
                  or a.end_date between b.start_date and b.end_date
                  or (a.start_date < b.start_date and a.end_date > b.start_date)
               )
               AND    a.staff_name = b.staff_name
               AND    a.id         <>  b.id
               );




        ID STAFF_NAME           START_DATE        END_DATE
---------- -------------------- ----------------- -----------------
         3 b                    20240304 00:00:00 20240307 00:00:00
         4 b                    20240306 00:00:00 20240309 00:00:00
         5 b                    20240306 00:00:00 20240307 00:00:00
         6 c                    20240304 00:00:00 20240309 00:00:00
         7 c                    20240305 00:00:00 20240310 00:00:00
         9 d                    20240301 00:00:00 20240312 00:00:00
        10 d                    20240302 00:00:00 20240303 00:00:00


7 rows selected.          


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 3561317091


---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     1 |    21 |    13   (0)| 00:00:01 |
|*  1 |  FILTER                              |                  |       |       |            |          |
|   2 |   TABLE ACCESS FULL                  | OVERLAP_TEST     |    10 |   210 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED| OVERLAP_TEST     |     1 |    21 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | IDX_OVERLAP_TEST |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT 0 FROM "OVERLAP_TEST" "B" WHERE "B"."STAFF_NAME"=:B1 AND
              "B"."ID"<>:B2 AND ("B"."START_DATE"<=:B3 AND "B"."END_DATE">=:B4 OR "B"."START_DATE"<=:B5 AND
              "B"."END_DATE">=:B6 OR "B"."START_DATE">:B7 AND "B"."START_DATE"<:B8)))
   3 - filter("B"."ID"<>:B1)
   4 - access("B"."STAFF_NAME"=:B1)
       filter("B"."START_DATE"<=:B1 AND "B"."END_DATE">=:B2 OR "B"."START_DATE"<=:B3 AND
              "B"."END_DATE">=:B4 OR "B"."START_DATE">:B5 AND "B"."START_DATE"<:B6)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        988  bytes sent via SQL*Net to client
        811  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed


              
--缺少包含关系，少个id=9             
SELECT a.*
FROM  overlap_test a 
WHERE  EXISTS (SELECT 1
               FROM   overlap_test b
               WHERE  (a.start_date between b.start_date and b.end_date
               or     a.end_date between b.start_date and b.end_date)
               AND    b.staff_name = a.staff_name
               AND    b.id         <>  a.id);
               
        ID STAFF_NAME           START_DATE        END_DATE
---------- -------------------- ----------------- -----------------
         3 b                    20240304 00:00:00 20240307 00:00:00
         4 b                    20240306 00:00:00 20240309 00:00:00
         5 b                    20240306 00:00:00 20240307 00:00:00
         6 c                    20240304 00:00:00 20240309 00:00:00
         7 c                    20240305 00:00:00 20240310 00:00:00
        10 d                    20240302 00:00:00 20240303 00:00:00


6 rows selected.

第二种算法，没有or,可以走hash join ,可能更好：

SELECT a.*
FROM  overlap_test a 
WHERE  EXISTS (SELECT 1
               FROM   overlap_test b
               WHERE ( a.start_date <= b.end_date
                       and a.end_date >= b.start_date
               )
               AND    b.staff_name = a.staff_name
               AND    b.id         <>  a.id);






        ID STAFF_NAME           START_DATE        END_DATE
---------- -------------------- ----------------- -----------------
         4 b                    20240306 00:00:00 20240309 00:00:00
         5 b                    20240306 00:00:00 20240307 00:00:00
         3 b                    20240304 00:00:00 20240307 00:00:00
         7 c                    20240305 00:00:00 20240310 00:00:00
         6 c                    20240304 00:00:00 20240309 00:00:00
        10 d                    20240302 00:00:00 20240303 00:00:00
         9 d                    20240301 00:00:00 20240312 00:00:00


7 rows selected.
Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 1291046832


--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |    10 |   420 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI         |                  |    10 |   420 |     4   (0)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_001 |    10 |   210 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| IDX_OVERLAP_TEST |    10 |   210 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| SYS_C009213      |    10 |   210 |     1   (0)| 00:00:01 |
|   6 |   VIEW                  | index$_join$_002 |    10 |   210 |     2   (0)| 00:00:01 |
|*  7 |    HASH JOIN            |                  |       |       |            |          |
|   8 |     INDEX FAST FULL SCAN| IDX_OVERLAP_TEST |    10 |   210 |     1   (0)| 00:00:01 |
|   9 |     INDEX FAST FULL SCAN| SYS_C009213      |    10 |   210 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("B"."STAFF_NAME"="A"."STAFF_NAME")
       filter("A"."START_DATE"<="B"."END_DATE" AND "A"."END_DATE">="B"."START_DATE"
              AND "B"."ID"<>"A"."ID")
   3 - access(ROWID=ROWID)
   7 - access(ROWID=ROWID)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        988  bytes sent via SQL*Net to client
        682  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed




--不能使用join,有重复数据
SELECT a.*
FROM  overlap_test a ,overlap_test b
WHERE  (a.start_date between b.start_date and b.end_date
       or     a.end_date between b.start_date and b.end_date)
       AND    b.staff_name = a.staff_name
       AND    b.id         <>  a.id;


 
        ID STAFF_NAME           START_DATE        END_DATE
---------- -------------------- ----------------- -----------------
         4 b                    20240306 00:00:00 20240309 00:00:00
         5 b                    20240306 00:00:00 20240307 00:00:00
         3 b                    20240304 00:00:00 20240307 00:00:00
         5 b                    20240306 00:00:00 20240307 00:00:00
         3 b                    20240304 00:00:00 20240307 00:00:00
         4 b                    20240306 00:00:00 20240309 00:00:00
         7 c                    20240305 00:00:00 20240310 00:00:00
         6 c                    20240304 00:00:00 20240309 00:00:00
        10 d                    20240302 00:00:00 20240303 00:00:00


9 rows selected.


Elapsed: 00:00:00.00