10-188 連続してなかったらインクリメント

SQLパズル

VerTable
Name   SortKey
-----  -------
Name1       10
Name1       20
Name1       30
Name1       40
Name2       50
Name2       60
Name1       70
Name1       80
Name3       90
Name2      100
Name1      110
Name1      120
Name4      130

SortKeyの昇順で、Nameを元にversionを求める。
Nameが最低でも1回違うNameになって、また同じNameになったらバージョンがインクリメントされます。

出力結果
Name   SortKey  version
-----  -------  -------
Name1       10        1
Name1       20        1
Name1       30        1
Name1       40        1
Name2       50        1
Name2       60        1
Name1       70        2
Name1       80        2
Name3       90        1
Name2      100        2
Name1      110        3
Name1      120        3
Name4      130        1

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

データ作成スクリプト

create table VerTable as
select 'Name1' as Name,10 as SortKey from dual
union select 'Name1', 20 from dual
union select 'Name1', 30 from dual
union select 'Name1', 40 from dual
union select 'Name2', 50 from dual
union select 'Name2', 60 from dual
union select 'Name1', 70 from dual
union select 'Name1', 80 from dual
union select 'Name3', 90 from dual
union select 'Name2',100 from dual
union select 'Name1',110 from dual
union select 'Name1',120 from dual
union select 'Name4',130 from dual;

SQL

--■■■旅人算の感覚を使う方法■■■
select Name,SortKey,
dense_rank() over(partition by Name order by PartID) as version
from (select Name,SortKey,
      Row_Number() over(order by SortKey)
    - Row_Number() over(partition by Name order by SortKey) as PartID
      from VerTable)
order by SortKey;

--■■■旅人算の感覚を使わない方法(10g以降)■■■
select Name,SortKey,
sum(willSum) over(partition by Name order by SortKey) as version
from (select Name,SortKey,
       case when lnnvl(Name = Lag(Name) over(order by SortKey))
            then 1 else 0 end as willSum
       from VerTable)
order by SortKey;

--■■■旅人算の感覚を使わない方法■■■
select Name,SortKey,
sum(willSum) over(partition by Name order by SortKey) as version
from (select Name,SortKey,
       case when Name = Lag(Name) over(order by SortKey)
            then 0 else 1 end as willSum
       from VerTable)
order by SortKey;

解説

この場合は、旅人算の感覚を使わないほうが分かりやすいかもしれませんね。

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

---