9-4 空き期間を取得

SQLパズル

TimeGapテーブル
Name  StartDate   EndDate
----  ----------  ----------
John  2005-11-01  2005-11-05
John  2005-11-06  2005-11-09
John  2005-11-12  2005-11-15
John  2005-11-17  2005-11-17
Mike  2005-11-19  2005-11-25
Mike  2005-11-26  2005-11-28
Tom   2005-11-10  2005-11-17
Tom   2005-11-19  2005-11-25

TimeGapテーブルのGap(空き期間)を取得する
同一Nameでの、StartDateとEndDateの間の、日の重複は存在しないものとする

出力結果
Name  GapStart    GapEnd
----  ----------  ----------
John  2005-11-10  2005-11-11
John  2005-11-16  2005-11-16
Tom   2005-11-18  2005-11-18

プログラマのためのSQL第2版の4章[SQLの時間データ型]を参考にさせていただきました

データ作成スクリプト

create table TimeGap(
Name      char(4),
StartDate Date,
EndDate   Date,
primary key (Name,StartDate));

insert into TimeGap values('John',to_date('20051101','yyyymmdd'),to_date('20051105','yyyymmdd'));
insert into TimeGap values('John',to_date('20051106','yyyymmdd'),to_date('20051109','yyyymmdd'));
insert into TimeGap values('John',to_date('20051112','yyyymmdd'),to_date('20051115','yyyymmdd'));
insert into TimeGap values('John',to_date('20051117','yyyymmdd'),to_date('20051117','yyyymmdd'));
insert into TimeGap values('Mike',to_date('20051119','yyyymmdd'),to_date('20051125','yyyymmdd'));
insert into TimeGap values('Mike',to_date('20051126','yyyymmdd'),to_date('20051130','yyyymmdd'));
insert into TimeGap values('Tom' ,to_date('20051110','yyyymmdd'),to_date('20051117','yyyymmdd'));
insert into TimeGap values('Tom' ,to_date('20051119','yyyymmdd'),to_date('20051125','yyyymmdd'));
commit;

SQL

--■■■Lead関数を使う方法■■■
select Name,GapStart,GapEnd from(
    select Name,
           EndDate+1 as GapStart,
           Lead(StartDate) over(partition by Name order by StartDate) -1 as GapEnd
    from TimeGap)
where GapStart <= GapEnd
order by Name,GapStart;

--■■■Lag関数を使う方法■■■
select Name,GapStart,GapEnd from(
    select Name,
           Lag(EndDate) over(partition by Name order by StartDate) +1 as GapStart,
           StartDate-1 as GapEnd
    from TimeGap)
where GapStart <= GapEnd
order by Name,GapStart;

--■■■相関サブクエリを使う方法1■■■
select Name,
EndDate+1 as GapStart,
(select min(b.StartDate) -1 from TimeGap b
  where b.Name=a.Name
    and b.StartDate > a.StartDate) as GapEnd
from TimeGap a
where (select min(b.StartDate) -1 from TimeGap b
        where b.Name=a.Name
          and b.StartDate > a.StartDate) >= EndDate+1
order by Name,GapStart;

--■■■相関サブクエリを使う方法2■■■
select Name,
(select max(b.EndDate) + 1 from TimeGap b
  where b.Name=a.Name
    and b.StartDate < a.StartDate) as GapStart,
StartDate-1 as GapEnd
from TimeGap a
where (select max(b.EndDate) + 1 from TimeGap b
        where b.Name=a.Name
          and b.StartDate < a.StartDate) <= StartDate-1
order by Name,GapStart;

解説

GapEndがGapStart以上なら、Gap(空き期間)があると判断してます。

---