sql - 合并重叠日期的记录

Question

我有如下数据，想要合并重叠日期的记录。重叠记录的开始日期和结束日期的 MIN 和 MAX 应为合并记录的开始日期和结束日期。

合并前:

Item Code               Start_date       End_date
==============          ===========      ===========
111                     15-May-2004      20-Jun-2004
111                     22-May-2004      07-Jun-2004
111                     20-Jun-2004      13-Aug-2004
111                     27-May-2004      30-Aug-2004
111                     02-Sep-2004      23-Dec-2004
222                     21-May-2004      19-Aug-2004 

所需输出:

Item Code               Start_date       End_date
==============          ===========      ===========
111                     15-May-2004      30-Aug-2004
111                     02-Sep-2004      23-Dec-2004
222                     21-May-2004      19-Aug-2004 

您可以使用创建示例数据

create table item(item_code  number, start_date date, end_date date);

insert into item values (111,to_date('15-May-2004','DD-Mon-YYYY'),to_date('20-Jun-2004','DD-Mon-YYYY'));
insert into item values (111,to_date('22-May-2004','DD-Mon-YYYY'),to_date('07-Jun-2004','DD-Mon-YYYY'));
insert into item values (111,to_date('20-Jun-2004','DD-Mon-YYYY'),to_date('13-Aug-2004','DD-Mon-YYYY'));
insert into item values (111,to_date('27-May-2004','DD-Mon-YYYY'),to_date('30-Aug-2004','DD-Mon-YYYY'));
insert into item values (111,to_date('02-Sep-2004','DD-Mon-YYYY'),to_date('23-Dec-2004','DD-Mon-YYYY'));
insert into item values (222,to_date('21-May-2004','DD-Mon-YYYY'),to_date('19-Aug-2004','DD-Mon-YYYY'));

commit;

Answer 1

解决此类问题的代码相当棘手。这是一种效果很好的方法:

with item (item_code, start_date, end_date) as (
      select 111,to_date('15-05-2004','DD-MM-YYYY'),to_date('20-06-2004','DD-MM-YYYY') from dual union all
      select 111,to_date('22-05-2004','DD-MM-YYYY'),to_date('07-06-2004','DD-MM-YYYY') from dual union all
      select 111,to_date('20-06-2004','DD-MM-YYYY'),to_date('13-08-2004','DD-MM-YYYY') from dual union all
      select 111,to_date('27-05-2004','DD-MM-YYYY'),to_date('30-08-2004','DD-MM-YYYY') from dual union all
      select 111,to_date('02-09-2004','DD-MM-YYYY'),to_date('23-12-2004','DD-MM-YYYY') from dual union all
      select 222,to_date('21-05-2004','DD-MM-YYYY'),to_date('19-08-2004','DD-MM-YYYY') from dual
     ),
     id as (
      select item_code, start_date as dte, count(*) as inc
      from item
      group by item_code, start_date
      union all
      select item_code, end_date, - count(*) as inc
      from item
      group by item_code, end_date
     ),
     id2 as (
      select id.*, sum(inc) over (partition by item_code order by dte) as running_inc
      from id
     ),
     id3 as (
      select id2.*, sum(case when running_inc = 0 then 1 else 0 end) over (partition by item_code order by dte desc) as grp
      from id2
     )
select item_code, min(dte) as start_date, max(dte) as end_date
from id3
group by item_code, grp;

还有一个rextester来验证它。

这是在做什么？好问题。这些问题的想法是定义相邻组。此方法通过计算截至给定日期的“开始”和“结束”次数来实现此目的。当值为0时，一个组结束。

具体步骤如下:

(1) 将所有日期分解为单独的行，并指示该日期是开始日期还是结束日期。该指标是定义范围的关键——+1 表示“进入”，“-1”表示退出。

(2) 计算指标的运行总和。总数中的 0 是重叠范围的末尾。

(3)对0进行反向累加来识别组。

(4)汇总得到最终结果。

您可以查看每个 CTE 以了解数据中发生的情况。