10-307 where句でフィルタしてから旅人算

SQLパズル

prodT
prod  day1       Val
----  ---------  ---
ABC   6/12/2009    1
ABC   6/13/2009    1
ABC   6/14/2009    1
ABC   6/15/2009    0
ABC   6/16/2009    0
ABC   6/17/2009    1
ABC   6/18/2009    1
ABC   6/19/2009    1
ABC   6/20/2009    1
ABC   6/21/2009    1
ABC   6/22/2009    1
DEF   6/18/2009    1
DEF   6/19/2009    1
DEF   6/20/2009    0
DEF   6/21/2009    0
DEF   6/22/2009    0

prodごとで、Valが0より大きくて、day1が連続している区間で、
最小のday1をStaDay1として求める。レコード数もcntとして求める。

出力結果
prod  StaDay1    cnt
----  ---------  ---
ABC   6/12/2009    3
ABC   6/17/2009    6
DEF   6/18/2009    2

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

データ作成スクリプト

create table prodT(prod,day1,Val) as
select 'ABC',to_date('6/12/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/13/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/14/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/15/2009','fmmm/dd/yyyy'),0 from dual union all
select 'ABC',to_date('6/16/2009','fmmm/dd/yyyy'),0 from dual union all
select 'ABC',to_date('6/17/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/18/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/19/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/20/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/21/2009','fmmm/dd/yyyy'),1 from dual union all
select 'ABC',to_date('6/22/2009','fmmm/dd/yyyy'),1 from dual union all
select 'DEF',to_date('6/18/2009','fmmm/dd/yyyy'),1 from dual union all
select 'DEF',to_date('6/19/2009','fmmm/dd/yyyy'),1 from dual union all
select 'DEF',to_date('6/20/2009','fmmm/dd/yyyy'),0 from dual union all
select 'DEF',to_date('6/21/2009','fmmm/dd/yyyy'),0 from dual union all
select 'DEF',to_date('6/22/2009','fmmm/dd/yyyy'),0 from dual;

SQL

--■■■旅人算の感覚を使う方法1■■■
select prod,min(day1),count(*)
from (select prod,day1,Val,
       Row_Number() over(partition by prod     order by day1)
      -Row_Number() over(partition by prod,Val order by day1)
      as distance
        from prodT)
 where Val > 0
group by prod,distance
order by min(day1);

--■■■旅人算の感覚を使う方法2■■■
select prod,min(day1),count(*)
from (select prod,day1,Val,
      day1-Row_Number() over(partition by prod order by day1)
      as distance
        from prodT
       where Val > 0)
group by prod,distance
order by min(day1);

解説

旅人算の感覚を使う方法2のように
where句でフィルタしてから旅人算の感覚を使える場合は、
先に、where句でフィルタして行数を減らしておいたほうがいいでしょう。

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

---