10-341 ActiveからInActiveまでの期間を求める

SQLパズル

ActivityTable
Status  dayC
------  ----------
     A  2010-01-01
     A  2010-01-12
     I  2010-01-25
     I  2010-02-01
     A  2010-04-06
     A  2010-04-12
     A  2010-04-23
     I  2010-05-30
     A  2010-06-01
     I  2010-09-23

dayCの昇順で、
最初にStatusがAになった日と、
最初にStatusがIになった日までの期間を求める。

出力結果
First_A   First_I
--------  --------
10-01-01  10-01-25
10-04-06  10-05-30
10-06-01  10-09-23

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

データ作成スクリプト

create table ActivityTable(Status,dayC) as
select 'A',date '2010-01-01' from dual union all
select 'A',date '2010-01-12' from dual union all
select 'I',date '2010-01-25' from dual union all
select 'I',date '2010-02-01' from dual union all
select 'A',date '2010-04-06' from dual union all
select 'A',date '2010-04-12' from dual union all
select 'A',date '2010-04-23' from dual union all
select 'I',date '2010-05-30' from dual union all
select 'A',date '2010-06-01' from dual union all
select 'I',date '2010-09-23' from dual;

SQL

--■■■Lead関数のignore nullsを使う方法(11gR2以降)■■■
select FIRST_A,FIRST_I
from (select status,
      Lag(status) over(order by dayC) as LagStatus,
      dayC as FIRST_A,
      Lead(decode(status,'I',dayC))
      ignore nulls over(order by dayC) as FIRST_I
      from ActivityTable)
where status = 'A'
  and (LagStatus = 'I' or LagStatus is null);

--■■■First_Value関数のignore nullsを使う方法(10gR1以降)■■■
select FIRST_A,FIRST_I
from (select status,
      Lag(status) over(order by dayC) as LagStatus,
      dayC as FIRST_A,
      First_Value(decode(status,'I',dayC) ignore nulls)
      over(order by dayC rows between 1 following
                                  and unbounded following) as FIRST_I
      from ActivityTable)
where status = 'A'
  and (LagStatus = 'I' or LagStatus is null);

解説

Lead関数のignore nullsが便利なことに注目したい