5-62 連続数の最大値を求める

SQLパズル

ValTable
Val  SortKey
---  -------
AAA       10
AAA       15
AAA       20
BBB       25
AAA       30
AAA       35
BBB       40
BBB       45
AAA       50
BBB       55
BBB       60
BBB       65
BBB       70
AAA       75
AAA       80

SortKeyの昇順で、
Valごとの最大の連続数を出力する。

出力結果
Val  MaxSeq
---  ------
AAA       3
BBB       4

データ作成スクリプト

create table ValTable(Val,SortKey) as
select 'AAA',10 from dual union
select 'AAA',15 from dual union
select 'AAA',20 from dual union
select 'BBB',25 from dual union
select 'AAA',30 from dual union
select 'AAA',35 from dual union
select 'BBB',40 from dual union
select 'BBB',45 from dual union
select 'AAA',50 from dual union
select 'BBB',55 from dual union
select 'BBB',60 from dual union
select 'BBB',65 from dual union
select 'BBB',70 from dual union
select 'AAA',75 from dual union
select 'AAA',80 from dual;

SQL

select distinct Val,max(count(*)) over(partition by Val) as MaxSeq
  from (select SortKey,Val,
          Row_Number() over(order by SortKey)
        - Row_Number() over(partition by Val order by SortKey) as distance
          from ValTable)
group by Val,distance
order by Val;

解説

旅人算の感覚を使ってます。

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

---