在OracleSQL中，对于连续或重叠的日期范围，在一行中打印开始和结束日期

Question

我想在一行中打印连续或重叠日期范围的开始日期和结束日期。

这是的数据

create table orders (
po varchar2(6),
startdate date,
enddate date
);
insert into orders values ('order1',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order1',to_date('01-02-2020','dd-MM-yyyy'),to_date('31-03-2020','dd-MM-yyyy'));
insert into orders values ('order1',to_date('01-04-2020','dd-MM-yyyy'),to_date('30-06-2020','dd-MM-yyyy'));
insert into orders values ('order2',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order2',to_date('01-03-2020','dd-MM-yyyy'),to_date('31-03-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('01-01-2020','dd-MM-yyyy'),to_date('31-01-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('02-02-2020','dd-MM-yyyy'),to_date('31-05-2020','dd-MM-yyyy'));
insert into orders values ('order3',to_date('01-05-2020','dd-MM-yyyy'),to_date('31-07-2020','dd-MM-yyyy'));

预期输出为

order1  01-01-2020   30-06-2020
order2  01-01-2020   31-01-2020
order2  01-03-2020   31-03-2020
order3  01-01-2020   31-01-2020
order3  02-02-2020   31-07-2020

首先，我尝试使用unpivot子句来获取一列中的所有日期，并检查前一行和后一行是否重叠或连续，然后消除这些行，但这行不通，因为如果重叠，日期的顺序将不再是startdate后面跟着enddate。

这不能作为的起点

select * from(
select * from (
select po,startdate,enddate from orders)
unpivot(column_val for column_name in (startdate,enddate)) )order by po,column_val

还有其他解决方案吗？

Answer 1

有一个使用match_recognize子句的优雅(高效(解决方案(需要Oracle 12.1或更高版本(。

select po, startdate, enddate
from   orders
match_recognize (
  partition by po
  order     by startdate
  measures  first(startdate) as startdate, max(enddate) as enddate
  pattern   ( c* n )
  define    c as max(enddate) + 1 >= next(startdate)  
);

Answer 2

使用窗口函数查看是否与以前的记录有任何重叠。然后将一个累积和分配给一个"0"；分组"；和骨料。

我喜欢使用累积最大值来进行更通用的重叠：

select po, min(startdate), max(enddate)
from (select o.*,
             sum(case when prev_enddate >= startdate then 0 else 1 end) over (partition by po order by startdate) as grouping
      from (select o.*,
                   max(enddate) over (partition by po order by startdate range between unbounded preceding and '1' second preceding) as prev_enddate
            from orders o
           ) o
     ) o
group by po, grouping;

在许多情况下，您可以使用lag()而不是max():

select po, min(startdate), max(enddate)
from (select o.*,
             sum(case when prev_enddate >= startdate then 0 else 1 end) over (partition by po order by startdate) as grouping
      from (select o.*,
                   lag(enddate) over (partition by po order by startdate) as prev_enddate
            from orders o
           ) o
     ) o
group by po, grouping;

只要前一行有重叠，这就可以工作，这是典型的情况。