9-61 連続的なグルーピング

SQLパズル

T
num  data
---  ----
  1  a
  2  a
  3  b
  6  b
  8  a

各data値がnumの何番から何番まで連続しているかを、
その出現順でまとめる。

出力結果
Low  High  data
---  ----  ----
  1     2     a
  3     6     b
  8     8     a

SQLパズル 第2版のパズル63 [連続的なグルーピング] を参考にさせていただきました

データ作成スクリプト

create table T(
num  number(1) primary key,
data char(1)   not null);

insert into T values(1, 'a');
insert into T values(2, 'a');
insert into T values(3, 'b');
insert into T values(6, 'b');
insert into T values(8, 'a');
commit;

SQL

--■■■旅人算の感覚を使う方法■■■
select min(num) as Low,max(num) as High,data
from (select num,data,
        Row_Number() over(order by num)
      - Row_Number() over(partition by data order by num) as distance
        from T)
group by data,distance
order by min(num);

--■■■標準SQLを使う方法1■■■
select min(num) as Low,max(num) as High,data
  from (select num,data,
        (select count(*)+1 from T b
          where b.num > a.num) as Rn1,
        (select count(*)+1 from T b
          where b.data = a.data
            and b.num > a.num) as Rn2
          from T a)
group by data,Rn1-Rn2
order by min(num);

--■■■標準SQLを使う方法2■■■
select min(num) as Low,max(num) as High,data
  from (select num,data,
        (select count(*)
              - count(case when b.data = a.data
                           then 1 end)
           from T b
          where b.num > a.num) as distance
          from T a)
group by data,distance
order by min(num);

--■■■標準SQLを使う方法3■■■
select min(num) as Low,max(num) as High,data
from (select a.num,a.data,
        count(*)
      - count(case when a.data = b.data
                   then 1 end) as distance
        from T a,T b
       where a.num <= b.num
      group by a.num,a.data)
group by data,distance
order by min(num);

解説

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

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

---