10-321 再帰with句で行を分割

SQLパズル

OrderT
ID   Val
---  ---
111   17
222   16
333   22
444    9
555   11
666   15
777    2
888   13

IDごとに、行を分割する。
ただし、分割時のValは最大で10とする。

出力結果
ID   NewVal
---  ------
111      10
111       7
222      10
222       6
333      10
333      10
333       2
444       9
555      10
555       1
666      10
666       5
777       2
888      10
888       3

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

データ作成スクリプト

create table OrderT(ID,Val) as
select 111,17 from dual union
select 222,16 from dual union
select 333,22 from dual union
select 444, 9 from dual union
select 555,11 from dual union
select 666,15 from dual union
select 777, 2 from dual union
select 888,13 from dual;

SQL

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,Val) as(
select ID,Val
  from OrderT
union all
select ID,Val-10
  from rec
 where Val-10 > 0)
select ID,Least(Val,10) as Val from rec
order by ID,Val desc;

--■■■model句を使う方法(10g以降)■■■
select ID,newVal
  from OrderT
 model
partition by(ID)
dimension by(0 as soeji)
measures(Val,0 as newVal)
rules ITERATE (999) UNTIL ((ITERATION_NUMBER+1)*10 > Val[0])
(newVal[ITERATION_NUMBER] = Least(10,Val[0]-10*ITERATION_NUMBER))
order by ID,newVal desc;

--■■■連番表と内部結合させる方法■■■
select ID,Least(10,a.Val-10*(b.cnter-1)) as NewVal
  from OrderT a
  Join (select RowNum as cnter from dict) b
    on ceil(a.Val/10) >= b.cnter
order by a.ID,NewVal desc;

解説

再帰with句を使う方法が分かりやすいと思いますねぇ

---