谜题59_合并时间段

案例用表如下

CREATE TABLE Timesheets
(task_id CHAR(10) NOT NULL PRIMARY KEY,
 start_date DATE NOT NULL, 
 end_date DATE NOT NULL, 
 CHECK(start_date <= end_date));
 
INSERT INTO Timesheets 
SELECT 1,  to_date('1997-01-01','yyyy-mm-dd'), to_date('1997-01-03','yyyy-mm-dd') from dual union all 
SELECT 2,  to_date('1997-01-02','yyyy-mm-dd'), to_date('1997-01-04','yyyy-mm-dd') from dual union all 
SELECT 3,  to_date('1997-01-04','yyyy-mm-dd'), to_date('1997-01-05','yyyy-mm-dd') from dual union all 
SELECT 4,  to_date('1997-01-06','yyyy-mm-dd'), to_date('1997-01-09','yyyy-mm-dd') from dual union all 
SELECT 5,  to_date('1997-01-09','yyyy-mm-dd'), to_date('1997-01-09','yyyy-mm-dd') from dual union all 
SELECT 6,  to_date('1997-01-09','yyyy-mm-dd'), to_date('1997-01-09','yyyy-mm-dd') from dual union all 
SELECT 7,  to_date('1997-01-12','yyyy-mm-dd'), to_date('1997-01-15','yyyy-mm-dd') from dual union all 
SELECT 8,  to_date('1997-01-13','yyyy-mm-dd'), to_date('1997-01-14','yyyy-mm-dd') from dual union all 
SELECT 9,  to_date('1997-01-14','yyyy-mm-dd'), to_date('1997-01-14','yyyy-mm-dd') from dual union all 
SELECT 10, to_date('1997-01-17','yyyy-mm-dd'), to_date('1997-01-17','yyyy-mm-dd') from dual;

未想到好方法，抛砖引玉而已

select start_date, min(end_date)
  from (select a.start_date, b.end_date
          from timesheets a, timesheets b
         where a.end_date <= b.end_date
           and not exists (select *
                  from timesheets c
                 where a.start_date > c.start_date
                   and a.start_date <= c.end_date
                   and c.task_id != a.task_id)
           and not exists (select *
                  from timesheets d
                 where b.end_date >= d.start_date
                   and b.end_date < d.end_date
                   and d.task_id != b.task_id))
 group by start_date
 order by 1

现在新方法如下，参考自cookbook

with c as
 (select start_date,
         end_date,
         case
           when MAX(end_date) over(order by start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= start_date then
            0
           else
            1
         end as l
    from Timesheets b),
d as
 (select start_date, end_date, sum(l) over(order by start_date) as l from c)
select min (start_date) as start_date, max(end_date) as end_date
  from d
 group by l
 order by 1