10-125 開始と終了が等しいレコードをグループ化

SQLパズル

WorkTable
開始              終了              値
----------------  ----------------  --
2006/09/03 00:00  2006/09/03 00:05  33
2006/09/03 11:00  2006/09/03 11:35  66
2006/09/03 13:01  2006/09/06 14:57  73
2006/09/06 14:57  2006/09/08 14:47  47
2006/09/08 14:50  2006/09/12 12:27  90
2006/09/12 12:27  2006/09/14 17:58  50
2006/09/14 17:58  2006/09/15 19:21  20

開始の昇順で考えた時に
直前のレコードの終了と、開始が同じレコード同士をグループとみなして
値の合計を以下の形式で出力する。

出力結果
開始              終了               値
----------------  ----------------  ---
2006/09/03 00:00  2006/09/03 00:05   33
2006/09/03 11:00  2006/09/03 11:35   66
2006/09/03 13:01  2006/09/08 14:47  120  ← 73+47
2006/09/08 14:50  2006/09/15 19:21  160  ← 90+50+20

データ作成スクリプト

create table WorkTable as
select to_date('2006/09/03 00:00','yyyy/mm/dd hh24:mi') as 開始,
       to_date('2006/09/03 00:05','yyyy/mm/dd hh24:mi') as 終了,33 as 値 from dual
union select to_date('2006/09/03 11:00','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/03 11:35','yyyy/mm/dd hh24:mi'),66 from dual
union select to_date('2006/09/03 13:01','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/06 14:57','yyyy/mm/dd hh24:mi'),73 from dual
union select to_date('2006/09/06 14:57','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/08 14:47','yyyy/mm/dd hh24:mi'),47 from dual
union select to_date('2006/09/08 14:50','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/12 12:27','yyyy/mm/dd hh24:mi'),90 from dual
union select to_date('2006/09/12 12:27','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/14 17:58','yyyy/mm/dd hh24:mi'),50 from dual
union select to_date('2006/09/14 17:58','yyyy/mm/dd hh24:mi'),
             to_date('2006/09/15 19:21','yyyy/mm/dd hh24:mi'),20 from dual;

SQL

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

--■■■階層問い合わせを使う方法(10g以降)■■■
select 開始,max(終了) as 終了,sum(値) as 値
  from (select 開始,
        connect_by_root 終了 as 終了,
        connect_by_root 値 as 値
          from WorkTable
         where connect_by_isleaf = 1
        connect by prior 開始 = 終了)
group by 開始
order by 開始;

--■■■分析関数を使う方法(10g以降)■■■
select 開始,終了,sum(値) as 値
from (select 値,
      Last_Value(nullif(開始,Lag終了) ignore nulls) over(order by 開始) as 開始,
      First_Value(nullif(終了,Lead開始) ignore nulls)
             over(order by 開始 Rows between Current Row and Unbounded Following) as 終了
        from (select 開始,終了,値,
               Lag(終了) over(order by 開始) as Lag終了,
              Lead(開始) over(order by 開始) as Lead開始
                from WorkTable))
group by 開始,終了
order by 開始;

--■■■分析関数を使う方法1■■■
select 開始,終了,sum(値) as 値
from (select 値,
      max(nullif(開始,Lag終了)) over(order by 開始) as 開始,
      min(nullif(終了,Lead開始))
      over(order by 開始 Rows between Current Row and Unbounded Following) as 終了
        from (select 開始,終了,値,
               Lag(終了) over(order by 開始) as Lag終了,
              Lead(開始) over(order by 開始) as Lead開始
                from WorkTable))
group by 開始,終了
order by 開始;

--■■■分析関数を使う方法2■■■
select min(開始) as 開始,max(終了) as 終了,sum(値) as 値
  from (select 開始,終了,値,
        sum(willSum) over(order by 開始) as GID
        from (select 開始,終了,値,
              case when 開始 = Lag(終了) over(order by 開始)
                   then 0 else 1 end as willSum
              from WorkTable))
group by GID
order by 開始;

解説

開始と終了が等しいレコードをグループ化する方法として
階層問い合わせを使う方法と、分析関数を使う方法があります。

---