10-295 Overlapの解消

SQLパズル

tab1
d_begin  d_end
-------  -----
      1      2
      3      7  ←  3, 3 に変更
      4      7  ←  4, 6 に変更
      7      8  ←  7, 8 に変更
      7      9  ←  9, 9 に変更
      7     10  ← 10,10 に変更
      7    999  ← 11,19 に変更
     20     30

(1) 次の有効開始日が、自分の有効終了日と同じか古ければ
    自分の有効終了日を、次の有効開始日-1にします。
(2) 有効開始日が同じものがあれば、
    有効終了日の若い方をそのまま有効にし
    有効終了日の大きい方の有効開始日を、若い方の有効終了日+1 とします。
(3) 有効開始日と有効終了日が同じものは別途処理するものとします。

出力結果
d_begin  d_end
-------  -----
      1      2
      3      3
      4      6
      7      8
      9      9
     10     10
     11     19
     20     30

データ作成スクリプト

create table tab1 (
d_begin number(3),
d_end number(3));

insert into tab1 values(1,2);
insert into tab1 values(3,7);
insert into tab1 values(4,7);
insert into tab1 values(7,8);
insert into tab1 values(7,9);
insert into tab1 values(7,10);
insert into tab1 values(7,999);
insert into tab1 values(20,30);
commit;

SQL

select d_begin,d_end
  from (select Row_Number() over(order by d_begin,d_end) as Rn,
        d_begin,d_end
          from tab1)
 model
 dimension by (Rn)
 measures(d_begin,d_end)
 rules(d_end[Any] order by Rn = case when d_begin[CV()+1] <= d_end[CV()]
                                      and d_begin[CV()] != d_begin[CV()+1]
                                     then d_begin[CV()+1]-1
                                     else d_end[CV()] end,
       d_begin[Any] order by Rn = case when d_begin[CV()] <= d_end[CV()-1]
                                       then d_end[CV()-1]+1
                                       else d_begin[CV()] end);

解説

こういった再帰的な処理がある時には、model句が便利です。

---