10-266 model句でコントロールブレイク

SQLパズル

FlagTable
day1       Flag
---------  ----
2008/8/25  Y
2008/8/26  Y
2008/8/27  N
2008/8/28  Y
2008/8/29  Y
2008/8/30  Y
2008/8/31  N
2008/9/1   Y
2008/9/2   Y
2008/9/3   N
2008/9/4   N

day1の昇順で、FlagがYの行に連番を付与するが、
ただし、FlagがNの行があったら、連番は1から振りなおしになります。

出力結果
day1       Flag  SeqCnt
---------  ----  ------
2008/8/25  Y     1
2008/8/26  Y     2
2008/8/27  N     0
2008/8/28  Y     1
2008/8/29  Y     2
2008/8/30  Y     3
2008/8/31  N     0
2008/9/1   Y     1
2008/9/2   Y     2
2008/9/3   N     0
2008/9/4   N     0

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

データ作成スクリプト

create table FlagTable(day1,Flag) as
select to_date('8/25/2008','mm/dd/yyyy'),'Y' from dual union
select to_date('8/26/2008','mm/dd/yyyy'),'Y' from dual union
select to_date('8/27/2008','mm/dd/yyyy'),'N' from dual union
select to_date('8/28/2008','mm/dd/yyyy'),'Y' from dual union
select to_date('8/29/2008','mm/dd/yyyy'),'Y' from dual union
select to_date('8/30/2008','mm/dd/yyyy'),'Y' from dual union
select to_date('8/31/2008','mm/dd/yyyy'),'N' from dual union
select to_date('9/1/2008' ,'mm/dd/yyyy'),'Y' from dual union
select to_date('9/2/2008' ,'mm/dd/yyyy'),'Y' from dual union
select to_date('9/3/2008' ,'mm/dd/yyyy'),'N' from dual union
select to_date('9/4/2008' ,'mm/dd/yyyy'),'N' from dual;

SQL

--■■■model句でコントロールブレイクさせる方法(10g以降)■■■
select day1,Flag,SeqCnt
  from FlagTable
 model
 dimension by (day1)
 measures(Flag,0 as SeqCnt)
 rules(SeqCnt[any] order by day1
     = case when Flag[cv()] = 'Y'
            then presentv(SeqCnt[cv()-1],SeqCnt[cv()-1]+1,1)
            else 0 end);

--■■■分析関数を使う方法■■■
select day1,Flag,
case Flag
when 'Y' then Row_Number() over(partition by GID,Flag order by day1)
else 0 end as SeqCnt
from (select day1,Flag,
      count(decode(Flag,'N',1)) over(order by day1) as GID
      from FlagTable);

解説

分析関数を使う方法では、
旅人算の感覚を使おうと思いましたが、
Row_Number関数を2回使うよりも、Count関数1回のほうがシンプルと判断し、
累計を求めることにしました。

---