如何在Oracle DB中查找具有递归重叠的日期范围的记录 [英] How to find records with recursively overlapping date ranges in Oracle DB

问题描述

我有一个这样的表:

| ID   | DSTART     | DEND
+------+------------+-----------
| fat1 | 01/01/2017 | 31/01/2017
| fat2 | 01/02/2017 | 28/02/2017
| fat3 | 01/03/2017 | 31/03/2017
| fat4 | 01/04/2017 | 30/04/2017
| fat5 | 01/02/2017 | 31/03/2017
| fat6 | 01/01/2017 | 28/02/2017
| fat7 | 01/03/2017 | 30/04/2017
| fat8 | 01/06/2017 | 30/06/2017
| fat9 | 28/04/2017 | 02/05/2017

给定一条记录，我想找到所有重叠的记录以及所有与重叠的记录重叠的记录.

given a record I want to find all the overlapping records and all the records overlapping the overlapping records.

例如搜索 fat7 的重叠记录应返回fat5(与fat7重叠)fat4(与fat7重叠)fat3(与fat7重叠)fat2(*重叠fat5)fat6(*重叠fat5)fat1(**与fat6重叠)

e.g. searching for overlapping records of fat7 should return fat5 (overlaps fat7) fat4 (overlaps fat7) fat3 (overlaps fat7) fat2 (*overlaps fat5) fat6 (*overlaps fat5) fat1 (**overlaps fat6)

创建数据集:

create table zz_fatt
( id      varchar2(100) primary key,
  dstart  date,
  dend    date);
insert into zz_fatt (id, dstart, dend) values ('fat7', to_date('03/01/2017', 'mm/dd/yyyy'), to_date('04/30/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat1', to_date('01/01/2017', 'mm/dd/yyyy'), to_date('01/31/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat2', to_date('02/01/2017', 'mm/dd/yyyy'), to_date('02/28/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat3', to_date('03/01/2017', 'mm/dd/yyyy'), to_date('03/31/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat4', to_date('04/01/2017', 'mm/dd/yyyy'), to_date('04/30/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat5', to_date('02/01/2017', 'mm/dd/yyyy'), to_date('03/31/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat6', to_date('01/01/2017', 'mm/dd/yyyy'), to_date('02/28/2017', 'mm/dd/yyyy'));
insert into zz_fatt (id, dstart, dend) values ('fat8', to_date('06/01/2017', 'mm/dd/yyyy'), to_date('06/15/2017', 'mm/dd/yyyy'));

推荐答案

您可以为记录分配组标识符.这个想法是找到不重叠的记录，并将它们用作组的开始.

You can assign a group identifier to the records. The idea is to find records that do not overlap, and use them as the beginning of a group.

以下将组分配给每个记录:

The following assigns the groups to each record:

  select t.*, sum(group_start) over (order by dstart) as grp
  from (select t.*,
               (case when not exists (select 1
                                      from t t2
                                      where t2.dstart < t.dstart and t2.dend >= t.dstart
                                     )
                     then 1 else 0
                end) group_start
        from t
       ) t

如果您只希望组用于某个记录，则有几种方法，例如:

If you only want the groups for a certain record then there are several ways, such as:

with overlaps as (
      <query above>
     )
select o.*
from overlaps o
where o.grp = (select o2.grp from overlaps o2 where o2.id = ???);

这篇关于如何在Oracle DB中查找具有递归重叠的日期范围的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！