在oracle SQL中连续或重叠日期范围的一行中打印开始和结束日期 [英] print start and end date in one row for continous or overlaping date ranges in oracle SQL

问题描述

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

I would like to print in one row start date and end date for continous or overlaping date ranges.

这是数据

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.

first I tried to use unpivot clause to get all dates in one column and to check previous and following rows if they are overlaping or continous and then eliminate this rows but it won't work because if there is overlap the order of dates will be not startdate following by enddate anymore.

这不会作为起点

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

还有其他解决方案吗?

推荐答案

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

There is an elegant (and efficient) solution using the match_recognize clause (which requires Oracle 12.1 or higher).

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)  
);

这篇关于在oracle SQL中连续或重叠日期范围的一行中打印开始和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！