10-189 連続したIDごとの最大値と最小値(重複を考慮)

SQLパズル

mytbl
ID
--
 1
 2
 4
 5
 6
 8
 9
10
11
11
12
15
15
15
16
16
20
20
20

連続したIDごとの最大値と最小値と件数を求める。

出力結果
MinID  MaxID  RecCount
-----  -----  --------
    1      2         2
    4      6         3
    8     12         6
   15     16         5
   20     20         3

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

データ作成スクリプト

create table mytbl as
select 1 as ID from dual
union all select  2 from dual
union all select  4 from dual
union all select  5 from dual
union all select  6 from dual
union all select  8 from dual
union all select  9 from dual
union all select 10 from dual
union all select 11 from dual
union all select 11 from dual
union all select 12 from dual
union all select 15 from dual
union all select 15 from dual
union all select 15 from dual
union all select 16 from dual
union all select 16 from dual
union all select 20 from dual
union all select 20 from dual
union all select 20 from dual;

SQL

--■■■旅人算の感覚を使う方法■■■
select min(ID) as MinID,
max(ID) as MaxID,
count(*) as RecCount
from (select ID,
      dense_rank() over(order by ID)
    - ID as GroupID
      from mytbl)
group by GroupID
order by min(ID);

--■■■旅人算の感覚を使わない方法■■■
select min(ID) as MinID,
max(ID) as MaxID,
count(*) as RecCount
from (select ID,sum(WillSum) over(order by ID) as GroupID
      from (select ID,
            case when ID <= 1+Lag(ID) over(order by ID)
                 then 0 else 1 end as WillSum
            from mytbl))
group by GroupID
order by min(ID);

解説

旅人算の感覚を使う方法では、2人の旅人をイメージしてます。
dense_rank() over(order by ID)が、IDが変化した時に1m進む旅人で、
IDが、単調増加ではあるが、速度が不規則な旅人です。

そして、この2人の旅人の位置の差(原点からの変位の差)でグループ化してます。

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

---