10-316 旅人算の感覚で連続数を求める

SQLパズル

cntSeqT
ID  SortKey  Val
--  -------  ----
 1      150  SAND
 1      200  COAL
 1      250  SAND
 1      300  COAL
 2       75  SAND
 2      100  COAL
 2      150  COAL
 2      200  COAL
 2      250  COAL
 2      300  SAND
 2      400  COAL
 2      450  COAL

IDごとで、SortKeyの昇順で同じValが2回以上連続したら、その連続数を出力する。

出力結果
ID  Val   cnt
--  ----  ---
 2  COAL    4
 2  COAL    2

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

データ作成スクリプト

create table cntSeqT(ID,SortKey,Val) as
select 1,150,'SAND' from dual union all
select 1,200,'COAL' from dual union all
select 1,250,'SAND' from dual union all
select 1,300,'COAL' from dual union all
select 2, 75,'SAND' from dual union all
select 2,100,'COAL' from dual union all
select 2,150,'COAL' from dual union all
select 2,200,'COAL' from dual union all
select 2,250,'COAL' from dual union all
select 2,300,'SAND' from dual union all
select 2,400,'COAL' from dual union all
select 2,450,'COAL' from dual;

SQL

--■■■旅人算の感覚を使う方法■■■
select ID,Val,count(*) as cnt
from (select ID,Val,
       Row_Number() over(partition by ID     order by SortKey)
      -Row_Number() over(partition by ID,Val order by SortKey) dis
        from cntSeqT)
group by ID,Val,dis
having count(*) > 1;

--■■■旅人算の感覚を使わない方法■■■
select ID,Val,count(*) as cnt
from (select ID,Val,
      sum(willSum) over(partition by ID order by SortKey) as GID
      from (select ID,Val,SortKey,
            case when Val = Lag(Val) over(partition by ID order by SortKey)
                 then 0 else 1 end as willSum
            from cntSeqT))
group by ID,Val,GID
having count(*) > 1;

解説

必ず進む旅人でもパーティションを切っている珍しいケースです。
9-52 最大のリージョンを求める(境界なし)
Tabibitosan method tutorial by Aketi Jyuuzou

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
下記の結果を取得したいのであれば、Row_Number関数で順位をつけるといいでしょう。

ID  cnt
--  ----
 1  null
 2     4
 2     2

select ID,nullif(cnt,1) as cnt
from (select ID,count(*) as cnt,
      Row_Number() over(partition by ID order by count(*) desc) as rn
      from (select ID,Val,
             Row_Number() over(partition by ID     order by SortKey)
            -Row_Number() over(partition by ID,Val order by SortKey) dis
              from cntSeqT)
      group by ID,Val,dis)
where cnt > 1 or rn = 1;

---