时间戳

首页 > 解决方案 > 合并重叠日期的记录

sql - 合并重叠日期的记录

问题描述

我有如下数据，想合并重叠日期的记录。重叠记录的开始和结束日期的 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;

标签： sqloracle

解决方案

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

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 以了解数据中发生的情况。

推荐阅读