10-238 縦方向の中央で折り返して出力

SQLパズル

testT
Val
---
 10
 20
 30
 40
 50
 60
 70

Valの昇順で出力するが、
縦方向の中央で折り返して出力する。

出力結果
LEFT  RIGHT
----  -----
  10     70
  20     60
  30     50
  40   null



行数が偶数の場合

testT
Val
---
 10
 20
 30
 40
 50
 60

出力結果
LEFT  RIGHT
----  -----
  10     60
  20     50
  30     40

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

データ作成スクリプト

create table testT(Val) as
select RowNum*10 from all_catalog
 where RowNum <=7;

SQL

--■■■Lead関数を使う方法■■■
select Left,Right
from (select Rn,Cnt,Val as Left,
      case when Rn <= Cnt/2
           then Lead(Val,abs(Cnt-2*Rn+1)) over(order by Val) end as Right
      from (select Val,
            Row_Number() over(order by Val) as Rn,
            count(*) over() as Cnt
            from testT))
where Rn <= ceil(Cnt/2);

--■■■model句を使う方法(10g以降)■■■
select Left,Right
from (select Left,Right,Rn,cnt
        from testT
       model
       dimension by (Row_number() over(order by Val) as Rn)
       measures(Val,Val as Left,Val as Right,count(*) over() as cnt)
       rules(Right[Rn] = case when CV(Rn)*2 <= cnt[1]
                              then Val[cnt[1]-CV()+1] end))
 where Rn <= ceil(Cnt/2)
order by Left;

解説

Cnt-2*Rn+1
は、Y=aX+bの方程式を解いて、一般化しました。

■■■■■■■■■■■■■■■■■■■■■■■■■
LEFT  RIGHT
----  -----
   1      6
   2      5
   3      4

Y=aX+b は、
(1,5),(2,3),(3,1)を通るので
を通る。

5=a+b
3=2a+b
1=3a+b

連立方程式を解いて
(a,b) = (-2,7)

■■■■■■■■■■■■■■■■■■■■■■■■■
LEFT  RIGHT
----  -----
   1      7
   2      6
   3      5
   4   null

Y=aX+b は、
(1,6),(2,4),(3,2)
を通る。

6=a+b
4=2a+b
2=3a+b

連立方程式を解いて
(a,b) = (-2,8)

■■■■■■■■■■■■■■■■■■■■■■■■■

傾きaは、2で確定
切片bは、件数-1
だと推測できますね。

---