9-3 開始日と終了日をまとめる

SQLパズル

TimeSheetsテーブル
StartDate   EndDate
----------  ----------
2005-01-01  2005-01-03
2005-01-02  2005-01-04
2005-01-04  2005-01-05
2005-01-06  2005-01-09
2005-01-09  2005-01-09
2005-01-12  2005-01-15
2005-01-13  2005-01-14
2005-01-14  2005-01-14
2005-01-17  2005-01-17

TimeSheetsテーブルの重複した期間をまとめて、
期間の開始と終了を表示する。
TimeSheetsのプライマリキーは、StartDateとする。

出力結果
StartDate   EndDate
----------  ----------
2005-01-01  2005-01-05
2005-01-06  2005-01-09
2005-01-12  2005-01-15
2005-01-17  2005-01-17

SQLパズル 第2版のパズル59 [期間を結合する] を参考にさせていただきました

データ作成スクリプト

create table TimeSheets(
StartDate Date,
EndDate   Date,
primary key (StartDate));

insert into TimeSheets values(to_date('20050101','yyyymmdd'),to_date('20050103','yyyymmdd'));
insert into TimeSheets values(to_date('20050102','yyyymmdd'),to_date('20050104','yyyymmdd'));
insert into TimeSheets values(to_date('20050104','yyyymmdd'),to_date('20050105','yyyymmdd'));
insert into TimeSheets values(to_date('20050106','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050109','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050112','yyyymmdd'),to_date('20050115','yyyymmdd'));
insert into TimeSheets values(to_date('20050113','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050114','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050117','yyyymmdd'),to_date('20050117','yyyymmdd'));
commit;

SQL

--■■■インラインビューを使う方法■■■
select StartDate,min(EndDate) as EndDate
from
(select a.StartDate,b.EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate <= b.EndDate
    and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate <= c.EndDate)
                       or (c.StartDate <= b.EndDate   and b.EndDate   <  c.EndDate)))
group by StartDate
order by StartDate;

--■■■インラインビューを使わない方法■■■
select a.StartDate,min(b.EndDate) as EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate <= b.EndDate
    and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate <= c.EndDate)
                       or (c.StartDate <= b.EndDate   and b.EndDate   <  c.EndDate))
group by a.StartDate
order by a.StartDate;

--■■■階層問い合わせを使う方法1(10g以降)■■■
select min(connect_by_root StartDate) as StartDate,EndDate
  from TimeSheets
 where connect_by_IsLeaf = 1
connect by nocycle prior EndDate between StartDate and EndDate
group by EndDate
order by StartDate;

--■■■階層問い合わせを使う方法2(10g以降)■■■
select connect_by_root StartDate as StartDate,EndDate
  from TimeSheets a
 where connect_by_IsLeaf = 1
start with not exists(select 1 from TimeSheets b
                       where a.RowID != b.RowID
                         and a.StartDate between b.StartDate and b.EndDate)
connect by nocycle prior EndDate between StartDate and EndDate
order by StartDate;

--■■■分析関数のrange指定を使う方法■■■
select min(StartDate) as StartDate,max(EndDate) as EndDate
from (select StartDate,EndDate,
      sum(willSum) over(order by StartDate) as GID
      from (select StartDate,EndDate,
            case when StartDate
              <= max(EndDate) over(order by StartDate
                                   range between unbounded preceding
                                             and 1 preceding)
                 then 0 else 1 end as willSum
              from TimeSheets))
group by GID
order by StartDate;

解説

自己結合でStartDateとEndDateの組み合わせ候補を求め、
not existsと、min関数を組み合わせてます。

開始日と終了日をまとめる

分析関数を使う方法と、階層問い合わせを使う方法も
必要に応じて使い分けるいいでしょう。

5-65 連続した期間の抽出(OverLapを考慮せず)
10-329 連続した範囲をまとめ、最も優先される期間を出力

---