Oracle 分割日期

嗨，汤姆，

当一个日期范围可以包含其他日期范围时，我需要拆分日期，如下所示。

需要帮助编写sql查询。

这些是不同的例子。


输入:

IK IK1开始日期结束日期
417532654 887825619 5/1/2017 4/30/2018
417532654 901076156 5/1/2017 12/31/2017

输出:

IK IK1开始日期结束日期
417532654 887825619 5/1/2017 12/31/2017
417532654 901076156 1/1/2018 4/30/2018
417532654 901076156 5/1/2017 12/31/2017

输入:
IK IK1开始日期结束日期
417517804 919990044 10/1/2020 10/31/2020
417517804 915844176 10/1/2020 9/30/2021
417517804 918267561 11/1/2020 12/31/2020

输出:

IK IK1开始日期结束日期
417517804 919990044 10/1/2020 10/31/2020
417517804 915844176 10/1/2020 10/31/2020
417517804 918267561 11/1/2020 12/31/2020
417517804 915844176 11/1/2020 12/31/2020
417517804 915844176 1/1/2021 9/30/2021

输入:

IK IK1开始日期结束日期
417517805 919990044 8/1/2020 8/1/2020
417517805 915844176 8/1/2020 8/31/2021
417517805 918267561 8/1/2020 8/1/2020
417517805 915844177 8/1/2020 8/31/2021

输出:

IK IK1开始日期结束日期
417517805 919990044 8/1/2020 8/1/2020
417517805 915844176 8/1/2020 8/1/2021
417517805 915844176 8/2/2020 8/31/2021
417517805 918267561 8/1/2020 8/1/2020
417517805 915844177 8/1/2020 8/1/2021
417517805 915844177 8/2/2020 8/31/2021

这是脚本:

create table test_dates
( ik number,
ik1 number,
start_date date,
end_date date);

REM INSERTING into TEST_DATES
SET DEFINE OFF;
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517804,919990044,to_date('10/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('10/31/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517804,915844176,to_date('10/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('09/30/2021 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517804,918267561,to_date('11/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('12/31/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517805,915844177,to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('08/31/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517805,919990044,to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517805,915844176,to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('08/31/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417517805,918267561,to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('08/01/2020 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417532654,887825619,to_date('05/01/2017 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('04/30/2018 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));
Insert into TEST_DATES (IK,IK1,START_DATE,END_DATE) values (417532654,901076156,to_date('05/01/2017 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'),to_date('12/31/2017 12.00.00 AM','MM/DD/YYYY HH.MI.SS AM'));

如果你想找到属于另一个范围的所有范围，这里有一种方法来解决这个问题:

-将表自身联接，获取所有开始日期和结束日期都在自联接中的开始日期和日期之间的行

dups.start_date between base.start_date and base.end_date
and    dups.end_date between base.start_date and base.end_date

-开始日期是前一个结束日期1或第一行的当前开始日期-您可以使用lag() 执行此操作:

lag ( 
 dups.end_date + 1, 1, base.start_date 
) over (
 partition by base.ik, base.ik1
 order by dups.end_date
)

-结束日期是到目前为止的最大结束日期-您可以使用max() 完成此操作...

max ( dups.end_date ) over (
 partition by base.ik, base.ik1
 order by dups.end_date
)

总的来说，这是:

alter session set nls_date_format = '  DD-MON-YYYY  ';

with rws as (
  select base.ik, base.ik1, 
         lag ( 
           dups.end_date + 1, 1, base.start_date 
         ) over (
           partition by base.ik, base.ik1
           order by dups.end_date
         ) start_date,
         max ( dups.end_date ) over (
           partition by base.ik, base.ik1
           order by dups.end_date
         ) end_date
  from   test_dates base
  join   test_dates dups
  on     dups.start_date between base.start_date and base.end_date
  and    dups.end_date between base.start_date and base.end_date
)
select * from rws
where  start_date <= end_date
order  by start_date, end_date;

IK           IK1         START_DATE         END_DATE          
417532654    887825619   01-MAY-2017        31-DEC-2017      
417532654    901076156   01-MAY-2017        31-DEC-2017      
417532654    887825619   01-JAN-2018        30-APR-2018  

417517805    919990044   01-AUG-2020        01-AUG-2020      
417517805    918267561   01-AUG-2020        01-AUG-2020      
417517805    915844177   01-AUG-2020        01-AUG-2020      
417517805    915844176   01-AUG-2020        01-AUG-2020      
417517805    915844176   02-AUG-2020        31-AUG-2020      
417517805    915844177   02-AUG-2020        31-AUG-2020

417517804    915844176   01-OCT-2020        31-OCT-2020      
417517804    919990044   01-OCT-2020        31-OCT-2020      
417517804    918267561   01-NOV-2020        31-DEC-2020      
417517804    915844176   01-NOV-2020        31-DEC-2020      
417517804    915844176   01-JAN-2021        30-SEP-2021

这假设所有子周期都包含在另一个子周期中，没有重叠。

例如: 没有这样的时期:

2021年1月1日-2021年5月1日
2021年2月1日-2021年7月1日

你需要分成1月1日-2月1日-5月1日-7月1日它也假设子范围没有间隙。如果有的话，你不能 (总是) 取上一个结束日期。

如果这些假设是错误的，则需要更新解决方案。我建议阅读Stew Ashton关于合并/拆分重叠日期范围的博客文章

https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/