10-13 リニアサーチによる探索

SQLパズル

休館日テーブル    訪問予定日テーブル
         day             day
------------      ----------
  2003/03/12      2003/03/11
  2003/03/15      2003/03/15
  2003/03/16      2003/03/17
  2003/03/18      2003/03/20
  2003/03/19      2003/03/25
  2003/04/25      2003/04/25

上の2テーブルから
訪問予定日と訪問日(訪問予定日以降で休館日でない最小の日付)
を出力する。

出力結果
 訪問予定日        訪問日
----------    ----------
2003/03/11    2003/03/11
2003/03/15    2003/03/17
2003/03/17    2003/03/17
2003/03/20    2003/03/20
2003/03/25    2003/03/25
2003/04/25    2003/04/26

データ作成スクリプト

create table 訪問予定日(day date);
create table 休館日(day date);

insert into 訪問予定日 values(to_date('20030311','yyyymmdd'));
insert into 訪問予定日 values(to_date('20030315','yyyymmdd'));
insert into 訪問予定日 values(to_date('20030317','yyyymmdd'));
insert into 訪問予定日 values(to_date('20030320','yyyymmdd'));
insert into 訪問予定日 values(to_date('20030325','yyyymmdd'));
insert into 訪問予定日 values(to_date('20030425','yyyymmdd'));

insert into 休館日 values(to_date('20030312','yyyymmdd'));
insert into 休館日 values(to_date('20030315','yyyymmdd'));
insert into 休館日 values(to_date('20030316','yyyymmdd'));
insert into 休館日 values(to_date('20030318','yyyymmdd'));
insert into 休館日 values(to_date('20030319','yyyymmdd'));
insert into 休館日 values(to_date('20030425','yyyymmdd'));
commit;

SQL

--■■■case式を使用■■■
select to_char(day,'yyyy/mm/dd') as 訪問予定日,
to_char(case when exists(select 1 from 休館日 b
                          where a.day=b.day)
             then (select min(b.day) + 1 from 休館日 b
                    where b.day >= a.day
                      and not exists(select 1 from 休館日 c
                                      where c.day = b.day+1))
             else a.day end,'yyyy/mm/dd') as 訪問日
from 訪問予定日 a
order by 訪問予定日,訪問日;

--■■■decode関数を使用■■■
select to_char(day,'yyyy/mm/dd') as 訪問予定日,
to_char(decode((select count(*) from 休館日 b where a.day=b.day)
               ,1,(select min(b.day) + 1 from 休館日 b
                    where b.day >= a.day
                      and not exists(select 1 from 休館日 c
                                      where c.day = b.day+1))
               ,a.day),'yyyy/mm/dd') as 訪問日
from 訪問予定日 a
order by 訪問予定日,訪問日;

--■■■分析関数を使用■■■
select to_char(訪問予定日,'yyyy/mm/dd') as 訪問予定日,
to_char(
case willSeek when 1 then min(kouho)
     else 訪問予定日 end,'yyyy/mm/dd') as 訪問日
from (select a.day as 訪問予定日,
      case a.day when min(b.day) over(partition by a.day)
                 then 1 else 0 end as willSeek,
      nullIf(b.day+1,Lead(b.day)
                     over(partition by a.day
                     order by b.day)) as kouho
        from 訪問予定日 a,休館日 b
       where a.day <= b.day)
group by 訪問予定日,willSeek
order by 訪問予定日;

解説

第一候補の日に訪問可能かを調べて、
訪問可能の場合は、その日となります。

第一候補の日に訪問不可の場合は、休館日が最大のデータを番兵とした、
第一候補以降で訪問可能な日の探索を行います。

---