10-118 二通りの連番付与

SQLパズル

hogeテーブル
NO  区分  連番A  連番B
--  ----  -----  -----
 1     1  null   null
 2     2  null   null
 3     2  null   null
 4     3  null   null
 5     1  null   null
 6     2  null   null
 7     2  null   null
 8     2  null   null
 9     3  null   null

hogeテーブルを更新して、
連番Aと、連番Bに、連番を割り振る。

No：入力データの先頭から最後まで振られている連番。
区分：1〜3でワンセットです。必ずこの順番で並んでいますが、2はn件連続している場合があります。
連番A：No昇順でソートし、区分1〜3のワンセット単位で連番を振ります。
連番B：No昇順でソートし、連番A内で連番を振ります。

更新結果
NO  区分  連番A  連番B
--  ----  -----  -----
 1     1      1      1
 2     2      1      2
 3     2      1      3
 4     3      1      4
 5     1      2      1
 6     2      2      2
 7     2      2      3
 8     2      2      4
 9     3      2      5

データ作成スクリプト

create table hoge(
NO    number(1),
区分  number(1),
連番A number(1),
連番B number(1));

insert into hoge values(1,1,null,null);
insert into hoge values(2,2,null,null);
insert into hoge values(3,2,null,null);
insert into hoge values(4,3,null,null);
insert into hoge values(5,1,null,null);
insert into hoge values(6,2,null,null);
insert into hoge values(7,2,null,null);
insert into hoge values(8,2,null,null);
insert into hoge values(9,3,null,null);
commit;

SQL

--■■■mergeを使わない方法■■■
update hoge a set (連番A,連番B) =
(select b.rn1,b.rn2
   from (select c.no,c.rn1,row_number() over (partition by c.rn1 order by c.no) as rn2
           from (select d.no,sum(decode(d.区分,1,1,0)) over(order by d.no) as rn1
                   from hoge d) c) b
where b.no = a.no);

--■■■mergeを使う方法(10g以降)■■■
merge into hoge a
using (select c.no,c.rn1,
       row_number() over (partition by c.rn1 order by c.no) as rn2
         from (select d.no,
               sum(decode(d.区分,1,1,0)) over(order by d.no) as rn1
                 from hoge d) c) b
on (a.no=b.no)
when matched then
update set a.連番A=b.rn1,
           a.連番B=b.rn2;

解説

sum関数とdecode関数を組み合わせてます。

---