10-279 旅人算の代わりにmodel句

SQLパズル

DateTable
ID   FromData    ToDate
---  ----------  ----------
333  2008-02-01  2008-02-03
333  2008-02-05  2008-03-10
333  2008-03-12  2008-03-15
333  2008-03-16  2008-03-30
444  2008-01-01  2008-02-03
444  2008-02-04  2008-03-10
888  2008-02-01  2008-02-29
999  2008-01-01  2008-01-31
999  2008-02-01  2008-02-29
999  2008-04-01  2008-04-30
999  2008-05-01  2008-05-31
999  2008-06-01  2008-06-30

IDごとに、FromDataの昇順でソートしたレコードで
FromData,ToDate,(次の行の)FromData,(次の行の)ToDate,(次の次の行の)FromData,(次の次の行の)ToDate
で連続したグループでのMinDataとMaxDateを求める。

出力結果
ID   MinData     MaxDate
---  ----------  ----------
333  2008-02-01  2008-02-03
333  2008-02-05  2008-03-10
333  2008-03-12  2008-03-30
444  2008-01-01  2008-03-10
888  2008-02-01  2008-02-29
999  2008-01-01  2008-02-29
999  2008-04-01  2008-06-30

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table DateTable(ID,FromData,ToDate) as
select '333',date '2008-02-01',date '2008-02-03' from dual union all
select '333',date '2008-02-05',date '2008-03-10' from dual union all
select '333',date '2008-03-12',date '2008-03-15' from dual union all
select '333',date '2008-03-16',date '2008-03-30' from dual union all
select '444',date '2008-01-01',date '2008-02-03' from dual union all
select '444',date '2008-02-04',date '2008-03-10' from dual union all
select '888',date '2008-02-01',date '2008-02-29' from dual union all
select '999',date '2008-01-01',date '2008-01-31' from dual union all
select '999',date '2008-02-01',date '2008-02-29' from dual union all
select '999',date '2008-04-01',date '2008-04-30' from dual union all
select '999',date '2008-05-01',date '2008-05-31' from dual union all
select '999',date '2008-06-01',date '2008-06-30' from dual;

SQL

--■■■model句を使う方法1(10g以降)■■■
select ID,min(FromData) as MinData,max(ToDate) as MaxDate
from (select ID,FromData,ToDate,GID
        from DateTable
       model
      partition by(ID)
      dimension by(Row_Number() over(partition by ID order by FromData) as soeji)
      measures(FromData,ToDate,0 as willsum,0 as GID)
      rules(
      willSum[any] = decode(FromData[cv()],ToDate[cv()-1]+1,0,1),
      GID[any] = sum(willSum)[cv() >= soeji]))
group by ID,GID
order by ID,GID;

--■■■model句を使う方法2(10g以降)■■■
select ID,min(FromData) as MinData,max(ToDate) as MaxDate
from (select ID,FromData,ToDate,GID
        from DateTable
       model
      partition by(ID)
      dimension by(FromData)
      measures(ToDate,0 as GID,
               case when FromData = 1+Lag(ToDate) over(partition by ID order by FromData)
                    then 0 else 1 end as willSum)
      rules(GID[any] = sum(willSum)[cv() >= FromData]))
group by ID,GID
order by ID,GID;

--■■■model句を使わない方法■■■
select ID,min(FromData) as MinData,max(ToDate) as MaxDate
from (select ID,FromData,ToDate,
      sum(willSum) over(partition by ID order by FromData) as GID
      from (select ID,FromData,ToDate,
            case when FromData = 1+Lag(ToDate)
                                   over(partition by ID order by FromData)
                 then 0 else 1 end as willSum
            from DateTable))
group by ID,GID
order by ID,GID;

解説

旅人算の感覚が使えない場合は、model句を使うのもいいでしょう。
インラインビューを1つ減らせます。

9-52 最大のリージョンを求める(境界なし)

---