10-310 ソートキーに重複がある旅人算の感覚

SQLパズル

dupSortKey
sortKey  Val1  Val2
-------  ----  ----
      1  AA    XX
      2  AA    XX
      3  BB    YY
      4  BB    YY
      5  AA    XX
      5  BB    XX
      6  AA    YY
      7  AA    YY
      7  BB    YY
      8  AA    YY

sortKeyの昇順で、Val1とVal2が同じだったら、
開始のsortKeyと終了のsortKey
をまとめて表示する。

出力結果
Val1  Val2  sta  end
----  ----  ---  ---
AA    XX      1    2
AA    XX      5    5
AA    YY      6    8
BB    YY      3    4
BB    XX      5    5
BB    YY      7    7

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

データ作成スクリプト

create table dupSortKey(sortKey,Val1,Val2) as
select 1,'AA','XX' from dual union all
select 2,'AA','XX' from dual union all
select 3,'BB','YY' from dual union all
select 4,'BB','YY' from dual union all
select 5,'AA','XX' from dual union all
select 5,'BB','XX' from dual union all
select 6,'AA','YY' from dual union all
select 7,'AA','YY' from dual union all
select 7,'BB','YY' from dual union all
select 8,'AA','YY' from dual;

SQL

--■■■旅人算の感覚を使う方法1■■■
select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
from (select sortKey,Val1,Val2,
       dense_rank() over(order by sortKey)
      -Row_Number() over(partition by Val1,Val2 order by sortKey) as distance
      from dupSortKey)
group by Val1,Val2,distance
order by Val1,min(sortKey);

--■■■旅人算の感覚を使う方法2■■■
select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
from (select sortKey,Val1,Val2,
       sortKey
      -Row_Number() over(partition by Val1,Val2 order by sortKey) as distance
      from dupSortKey)
group by Val1,Val2,distance
order by Val1,min(sortKey);

--■■■旅人算の感覚を使わない方法■■■
select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
from (select sortKey,Val1,Val2,
      sum(willSum) over(partition by Val1 order by sortKey) as GID
      from (select sortKey,Val1,Val2,
            case when Val2 = max(Val2) over(partition by Val1
                                            order by sortKey range between 1 preceding
                                                                       and 1 preceding)
                 then 0 else 1 end as willSum
              from dupSortKey))
group by Val1,Val2,GID
order by Val1,min(sortKey);

--■■■model句を使う方法(10g以降)■■■
select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
from (select sortKey,Val1,Val2,GID
        from dupSortKey
       model
      partition by(Val1)
      dimension by(Row_Number() over(partition by Val1 order by sortKey) as rn)
      measures(sortKey,Val2,1 as GID)
      rules(
      GID[any] order by sortKey = case when cv(rn) = 1 then 1
                                       when sortKey[cv()] = sortKey[cv()-1]+1
                                        and Val2[cv()] = Val2[cv()-1] then GID[cv()-1]
                                       else GID[cv()-1]+1 end))
group by Val1,Val2,GID
order by Val1,min(sortKey);

解説

割とレアな、dense_rank関数を使う旅人算の感覚です。
昇順にソートした場合に、差が2以上のデータがないので、
dense_rank関数を使わなくてもいいですが・・・

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

---