在Oracle SQL中，对于连续或重叠的日期范围，将开始和结束日期一起打印在一行上。

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子句将所有日期放在一列中，并检查前后行是否重叠或连续，然后消除这些行，但是这种方法不起作用，因为如果存在重叠，则日期的顺序将不再是开始日期后跟结束日期。

这不适合作为起点。

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

有没有其他解决方案？

- Pato

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

- user5683823

---

最后两行重叠，请将它们排序为第三个。 - Pato

@ PatrikMelichercik - 更好的是！我可以删除编辑 :-) - user5683823

Answer 2

使用窗口函数查看前面记录是否有重叠。然后使用累积求和来分配“分组”并进行聚合。

我喜欢使用累积最大值来处理更通用的重叠情况：

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;

只要前一行有重叠部分，这个方法就有效，而通常情况下都是如此。

- Gordon Linoff

---

嗨，Gordon，我在Live SQL中使用了你的查询，但出现了错误，我认为问题在于第二个前置部分的“1”。 - Pato

@ PatrikMelichercik - 你的 Oracle 版本是什么？ - user5683823

我在LIVE SQL中尝试了它，那里使用的版本我不知道。 - Pato