10-187 パーティションごとの連続したグループを求める

SQLパズル

tmp_table
item    period  slot
------  ------  ----
abc123       1     A
abc123       2     A
abc123       3     A
abc123       4     B
abc123       5     B
abc123       6     C
abc123       7     A
abc123       8     A
abc123       9     C
def456       1     D
def456       2     D
def456       3     E
def456       4     E
def456       5     D
def456       6     D
def456       7     E
def456       8     E
def456       9     D

itemごとのperiodの昇順で、
同じslotが連続するパーティションごとに
最小のperiodと、rankを求める。

出力結果
item    period  slot  minPeriod  rank
------  ------  ----  ---------  ----
abc123       1     A          1     1
abc123       2     A          1     2
abc123       3     A          1     3
abc123       4     B          4     1
abc123       5     B          4     2
abc123       6     C          6     1
abc123       7     A          7     1
abc123       8     A          7     2
abc123       9     C          9     1
def456       1     D          1     1
def456       2     D          1     2
def456       3     E          3     1
def456       4     E          3     2
def456       5     D          5     1
def456       6     D          5     2
def456       7     E          7     1
def456       8     E          7     2
def456       9     D          9     1

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

データ作成スクリプト

create table tmp_table(
item   varchar2(6),
period number(1),
slot   varchar2(1));

insert into tmp_table values('abc123',1,'A');
insert into tmp_table values('abc123',2,'A');
insert into tmp_table values('abc123',3,'A');
insert into tmp_table values('abc123',4,'B');
insert into tmp_table values('abc123',5,'B');
insert into tmp_table values('abc123',6,'C');
insert into tmp_table values('abc123',7,'A');
insert into tmp_table values('abc123',8,'A');
insert into tmp_table values('abc123',9,'C');
insert into tmp_table values('def456',1,'D');
insert into tmp_table values('def456',2,'D');
insert into tmp_table values('def456',3,'E');
insert into tmp_table values('def456',4,'E');
insert into tmp_table values('def456',5,'D');
insert into tmp_table values('def456',6,'D');
insert into tmp_table values('def456',7,'E');
insert into tmp_table values('def456',8,'E');
insert into tmp_table values('def456',9,'D');
commit;

SQL

select item,period,slot,
min(period) over(partition by item,slot,SubRank) as minPeriod,
Row_Number() over(partition by item,slot,SubRank order by period) as Rank
from (select item,period,slot,
      period-Row_Number() over(partition by slot order by period) as SubRank
      from tmp_table)
order by item,period;

解説

旅人算の感覚を使ってます。
9-52 最大のリージョンを求める(境界なし)

---