Oracle 日期交叉点

嗨，团队，
您能否看一下下面的场景，并帮助我构建SQL。

如果存在相交的日期范围，则应返回日期间隔较长的行。
即订单1的记录之一的日期间隔从01/01/2017到01/12/2017，
然而，另一个订单1行存在从01/04/2017到01/12/2017的间隔，该间隔属于先前的内部，即01/12/2017 01/01/2017。
在这种情况下，将返回具有inerval 01/01/2017 01/12/2017的order1，并且应排除01/12/2017的间隔01/04/2017。

NAME    EFF_FROM_DT EFF_TO_DT
------  ----------- ----------
order1 01/01/2017 01/12/2017
order1 01/01/2018 01/12/2018
order2 01/01/2017 01/12/2017
order2 01/01/2018 01/12/2018
复制

-------------- 示例表和数据 ------------------

CREATE TABLE TAB1 
(NAME VARCHAR2(10 BYTE), 
EFF_FROM_DT DATE, 
EFF_TO_DT DATE
);

Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-JAN-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-APR-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-JAN-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-JAN-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-APR-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-JAN-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-AUG-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));

COMMIT;
复制

因此，您只想显示订单中有一行日期在该范围内的范围？

如果是这样，你可以做一些类似的事情:

-生成开始/结束之间的所有可能日期
-将这些加入到您的表中，其中日期介于每行的开始/结束之间
-返回该订单只有一个日期的日期
-在此结果上使用Tabibitosan方法将它们分组
-返回每个组的最小和最大日期

这给出了类似的东西:

with min_max as (
  select min ( eff_from_dt ) first_start,
         max ( eff_to_dt ) last_end
  from   tab1
), all_dates as (
  select first_start + level - 1 dt 
  from   min_max
  connect by level <= ( last_end - first_start + 1 ) 
), exclusions as (
  select name, dt, count (*)
  from   tab1 t
  join   all_dates
  on     dt between eff_from_dt and eff_to_dt
  group  by name, dt
  having count (*) = 1
), grps as (
  select name, 
         dt,
         dt - row_number () over ( partition by name order by dt ) grp 
  from   exclusions
)
  select name, min ( dt ), max ( dt )
  from   grps
  group  by name, grp
  order  by name, grp;

NAME     MIN(DT)       MAX(DT)       
order1   01-JAN-2017   31-MAR-2017   
order1   01-JAN-2018   01-DEC-2018   
order2   01-JAN-2017   31-MAR-2017   
order2   01-JAN-2018   31-JUL-2018  
复制

还有其他方法可以解决这个问题。有13种方法可以根据两个日期范围的重叠方式对它们进行分类。您如何对哪些范围重叠进行分类决定了您的解决方案。

Stew Ashton写了许多关于这个话题的博客文章。我建议你阅读它们:

https://stewashton.wordpress.com/2015/03/21/join-tables-on-date-ranges/
https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/
https://stewashton.wordpress.com/tag/date-ranges-2/