まえがき
先頭からある件数ごとにグルーピングするのどうやるんだろとおもって調べた。
参考文献
環境
$sqlplus user01/ORACLE_PWD@ORCLPDB01
SQL*Plus: Release 19.0.0.0.0 - Production on 金 11月 22 14:40:36 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
最終正常ログイン時間: 金 11月 22 2019 11:15:06 +09:00
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
スクリプト
levelが割られる数。3が割る数。
with sub as(
select
level as rn
,remainder(level,3) as grp1
,mod(level,3) as grp2
,level-decode(mod(level,3),0,3,mod(level,3)) as grp3
from dual
connect by level <=10
)
,subb as(
select
rn
,grp1
,grp2
,grp3
,sum(grp1) over(order by rn) as run_sum1
,sum(grp2) over(order by rn) as run_sum2
,sum(grp3) over(order by rn) as run_sum3
from sub
)select
rn
,sum(run_sum1) over(order by rn) as grp1
,grp2
,decode(mod(rn,3),0,3,mod(rn,3))
,grp3
from subb
;
subクエリまでの実行結果
with sub as(
select
level as rn
,remainder(level,3) as grp1
,mod(level,3) as grp2
,level-decode(mod(level,3),0,3,mod(level,3)) as grp3
from dual
connect by level <=10
)select * from sub;
RN GRP1 GRP2 GRP3
------------- ------------- ------------- -------------
1 1 1 0
2 -1 2 0
3 0 0 0
4 1 1 3
5 -1 2 3
6 0 0 3
7 1 1 6
8 -1 2 6
9 0 0 6
10 1 1 9
subbまでの実行結果
with sub as(
select
level as rn
,remainder(level,3) as grp1
,mod(level,3) as grp2
,level-decode(mod(level,3),0,3,mod(level,3)) as grp3
from dual
connect by level <=10
),subb as(
select
rn
,grp1
,grp2
,grp3
,sum(grp1) over(order by rn) as run_sum1
,sum(grp2) over(order by rn) as run_sum2
,sum(grp3) over(order by rn) as run_sum3
from sub
)
select * from subb;
RN GRP1 GRP2 GRP3 RUN_SUM1 RUN_SUM2 RUN_SUM3
------------- ------------- ------------- ------------- ------------- ------------- -------------
1 1 1 0 1 1 0
2 -1 2 0 0 3 0
3 0 0 0 0 3 0
4 1 1 3 1 4 3
5 -1 2 3 0 6 6
6 0 0 3 0 6 9
7 1 1 6 1 7 15
8 -1 2 6 0 9 21
9 0 0 6 0 9 27
10 1 1 9 1 10 36
最終結果
grp1ではremainder関数すると1から連番振られる。grp2に関しては割る数の商ごとにグルーピングしている。
grp3はmod関数の計算結果を1,2,3...で振り直した値から各連番値までの距離でグルーピングしている。
RN GRP1 GRP2 DECODE(MOD(RN,3),0,3,MOD(RN,3)) GRP3
------------- ------------- ------------- ------------------------------- -------------
1 1 1 1 0
2 1 2 2 0
3 1 0 3 0
4 2 1 1 3
5 2 2 2 3
6 2 0 3 3
7 3 1 1 6
8 3 2 2 6
9 3 0 3 6
10 4 1 1 9
こういうのでもいける
select
level
,mod(level,3)
,level/3
,level/3-1
,trunc(level/3)
,trunc(level/3-1)
,case when mod(level,3)=0 then trunc(level/3-1,0) else trunc(level/3,0) end as grp
from
dual
connect by level <=10;
LEVEL MOD(LEVEL,3) LEVEL/3 LEVEL/3-1 TRUNC(LEVEL/3) TRUNC(LEVEL/3-1) GRP
------------- ------------- ------------- ------------- -------------- ---------------- -------------
1 1 .333333333333 -.66666666667 0 0 0
2 2 .666666666667 -.33333333333 0 0 0
3 0 1 0 1 0 0
4 1 1.33333333333 .333333333333 1 0 1
5 2 1.66666666667 .666666666667 1 0 1
6 0 2 1 2 1 1
7 1 2.33333333333 1.33333333333 2 1 2
8 2 2.66666666667 1.66666666667 2 1 2
9 0 3 2 3 2 2
10 1 3.33333333333 2.33333333333 3 2 3
グルーピング早見表
こういうのを作成してじーっと眺めてcase式とかでこねくりまわして、規則性を見出していくのが確実だとおもう。
ので、つくってみた。
select
level as numerator
,3 as denominator
,level/3 as quotient
,remainder(level,3) as remainder
,mod(level,3) as modulo
,level - 3*floor(level/3)
,level - 3*trunc(level/3)
,level - 3*round(level/3)
,level - 3*ceil(level/3)
,floor(level/3)
,trunc(level/3)
,round(level/3)
,ceil(level/3)
from dual
connect by level <=10
;
REMAINDERは**level - 3round(level/3)と同じ。
MODULOはLEVEL-3TRUNC(LEVEL/3)**と同じ。
NUMERATOR DENOMINATOR QUOTIENT REMAINDER MODULO LEVEL-3*FLOOR(LEVEL/3) LEVEL-3*TRUNC(LEVEL/3) LEVEL-3*ROUND(LEVEL/3) LEVEL-3*CEIL(LEVEL/3) FLOOR(LEVEL/3) TRUNC(LEVEL/3) ROUND(LEVEL/3) CEIL(LEVEL/3)
------------- ------------- ------------- ------------- ------------- ---------------------- ---------------------- ---------------------- --------------------- -------------- -------------- -------------- -------------
1 3 .333333333333 1 1 1 1 1 -2 0 0 0 1
2 3 .666666666667 -1 2 2 2 -1 -1 0 0 1 1
3 3 1 0 0 0 0 0 0 1 1 1 1
4 3 1.33333333333 1 1 1 1 1 -2 1 1 1 2
5 3 1.66666666667 -1 2 2 2 -1 -1 1 1 2 2
6 3 2 0 0 0 0 0 0 2 2 2 2
7 3 2.33333333333 1 1 1 1 1 -2 2 2 2 3
8 3 2.66666666667 -1 2 2 2 -1 -1 2 2 3 3
9 3 3 0 0 0 0 0 0 3 3 3 3
10 3 3.33333333333 1 1 1 1 1 -2 3 3 3 4
汎用ファンクション化
早見表をファンクション化してみることにした。
まず小さく始める。
create type mymap as object (key varchar2(4000), value number);
/
create type mymapliz is table of mymap;
/
select mymap(chr(64+level),level) as mymap from dual connect by level<=10;
USER01@ORCLPDB01> select chr(64+level) as str,level,mymap(chr(64+level),level) as mymap from dual connect by level<=10;
STR LEVEL MYMAP(KEY, VALUE)
---- ------------- ----------------------------------------------------------------------------------------------------
A 1 MYMAP('A', 1)
B 2 MYMAP('B', 2)
C 3 MYMAP('C', 3)
D 4 MYMAP('D', 4)
E 5 MYMAP('E', 5)
F 6 MYMAP('F', 6)
G 7 MYMAP('G', 7)
H 8 MYMAP('H', 8)
I 9 MYMAP('I', 9)
J 10 MYMAP('J', 10)
んで、さまる。
with sub as(select mymap(chr(64+level),level) as mymap from dual connect by level<=10)select cast(collect(mymap) as mymapliz) as mymapliz from sub;
USER01@ORCLPDB01> column MYMAPLIZ for a180
USER01@ORCLPDB01> with sub as(select mymap(chr(64+level),level) as mymap from dual connect by level<=10)select cast(collect(mymap) as mymapliz) as mymapliz from sub;
MYMAPLIZ(KEY, VALUE)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYMAPLIZ(MYMAP('A', 1), MYMAP('B', 2), MYMAP('C', 3), MYMAP('D', 4), MYMAP('E', 5), MYMAP('F', 6), MYMAP('G', 7), MYMAP('H', 8), MYMAP('I', 9), MYMAP('J', 10))
列のインターフェースは2列**object (key varchar2(4000), value number)**なので、unpivotで列インターフェースを整える。
整形前
with sub as(
select
level as numerator
,3 as denominator
,level/3 as quotient
,remainder(level,3) as remainder
,mod(level,3) as modulo
,level - 3*floor(level/3) as remain_floor
,level - 3*trunc(level/3) as remain_trunc
,level - 3*round(level/3) as remain_round
,level - 3*ceil(level/3) as remain_ceil
,floor(level/3) as floor
,trunc(level/3) as trunc
,round(level/3) as round
,ceil(level/3) as ceil
from dual
connect by level <=10
)select * from sub;
NUMERATOR DENOMINATOR QUOTIENT REMAINDER MODULO REMAIN_FLOOR REMAIN_TRUNC REMAIN_ROUND REMAIN_CEIL FLOOR TRUNC ROUND CEIL
------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- -------------
1 3 .333333333333 1 1 1 1 1 -2 0 0 0 1
2 3 .666666666667 -1 2 2 2 -1 -1 0 0 1 1
3 3 1 0 0 0 0 0 0 1 1 1 1
4 3 1.33333333333 1 1 1 1 1 -2 1 1 1 2
5 3 1.66666666667 -1 2 2 2 -1 -1 1 1 2 2
6 3 2 0 0 0 0 0 0 2 2 2 2
7 3 2.33333333333 1 1 1 1 1 -2 2 2 2 3
8 3 2.66666666667 -1 2 2 2 -1 -1 2 2 3 3
9 3 3 0 0 0 0 0 0 3 3 3 3
10 3 3.33333333333 1 1 1 1 1 -2 3 3 3 4
整形後
with sub as(
select
level as numerator
,3 as denominator
,level/3 as quotient
,remainder(level,3) as remainder
,mod(level,3) as modulo
,level - 3*floor(level/3) as remain_floor
,level - 3*trunc(level/3) as remain_trunc
,level - 3*round(level/3) as remain_round
,level - 3*ceil(level/3) as remain_ceil
,floor(level/3) as floor
,trunc(level/3) as trunc
,round(level/3) as round
,ceil(level/3) as ceil
from dual
connect by level <=10
)select * from sub unpivot(vals for cols in (denominator, quotient, remainder, modulo, remain_floor, remain_trunc, remain_round, remain_ceil, floor, trunc, round, ceil))
;
NUMERATORごとにととのった。COLSがkeyでVALSがvalue。
NUMERATOR COLS VALS
------------- ------------ -------------
1 DENOMINATOR 3
1 QUOTIENT .333333333333
1 REMAINDER 1
1 MODULO 1
1 REMAIN_FLOOR 1
1 REMAIN_TRUNC 1
1 REMAIN_ROUND 1
1 REMAIN_CEIL -2
1 FLOOR 0
1 TRUNC 0
1 ROUND 0
1 CEIL 1
2 DENOMINATOR 3
2 QUOTIENT .666666666667
2 REMAINDER -1
2 MODULO 2
2 REMAIN_FLOOR 2
2 REMAIN_TRUNC 2
2 REMAIN_ROUND -1
2 REMAIN_CEIL -1
2 FLOOR 0
2 TRUNC 0
2 ROUND 1
2 CEIL 1
3 DENOMINATOR 3
3 QUOTIENT 1
3 REMAINDER 0
3 MODULO 0
3 REMAIN_FLOOR 0
3 REMAIN_TRUNC 0
3 REMAIN_ROUND 0
3 REMAIN_CEIL 0
3 FLOOR 1
3 TRUNC 1
3 ROUND 1
3 CEIL 1
4 DENOMINATOR 3
4 QUOTIENT 1.33333333333
4 REMAINDER 1
4 MODULO 1
4 REMAIN_FLOOR 1
4 REMAIN_TRUNC 1
4 REMAIN_ROUND 1
4 REMAIN_CEIL -2
4 FLOOR 1
4 TRUNC 1
4 ROUND 1
4 CEIL 2
5 DENOMINATOR 3
5 QUOTIENT 1.66666666667
5 REMAINDER -1
5 MODULO 2
5 REMAIN_FLOOR 2
5 REMAIN_TRUNC 2
5 REMAIN_ROUND -1
5 REMAIN_CEIL -1
5 FLOOR 1
5 TRUNC 1
5 ROUND 2
5 CEIL 2
6 DENOMINATOR 3
6 QUOTIENT 2
6 REMAINDER 0
6 MODULO 0
6 REMAIN_FLOOR 0
6 REMAIN_TRUNC 0
6 REMAIN_ROUND 0
6 REMAIN_CEIL 0
6 FLOOR 2
6 TRUNC 2
6 ROUND 2
6 CEIL 2
7 DENOMINATOR 3
7 QUOTIENT 2.33333333333
7 REMAINDER 1
7 MODULO 1
7 REMAIN_FLOOR 1
7 REMAIN_TRUNC 1
7 REMAIN_ROUND 1
7 REMAIN_CEIL -2
7 FLOOR 2
7 TRUNC 2
7 ROUND 2
7 CEIL 3
8 DENOMINATOR 3
8 QUOTIENT 2.66666666667
8 REMAINDER -1
8 MODULO 2
8 REMAIN_FLOOR 2
8 REMAIN_TRUNC 2
8 REMAIN_ROUND -1
8 REMAIN_CEIL -1
8 FLOOR 2
8 TRUNC 2
8 ROUND 3
8 CEIL 3
9 DENOMINATOR 3
9 QUOTIENT 3
9 REMAINDER 0
9 MODULO 0
9 REMAIN_FLOOR 0
9 REMAIN_TRUNC 0
9 REMAIN_ROUND 0
9 REMAIN_CEIL 0
9 FLOOR 3
9 TRUNC 3
9 ROUND 3
9 CEIL 3
10 DENOMINATOR 3
10 QUOTIENT 3.33333333333
10 REMAINDER 1
10 MODULO 1
10 REMAIN_FLOOR 1
10 REMAIN_TRUNC 1
10 REMAIN_ROUND 1
10 REMAIN_CEIL -2
10 FLOOR 3
10 TRUNC 3
10 ROUND 3
10 CEIL 4
120行が選択されました。
NUMERATORごとにさまる。
with sub as(
select
level as numerator
,3 as denominator
,level/3 as quotient
,remainder(level,3) as remainder
,mod(level,3) as modulo
,level - 3*floor(level/3) as remain_floor
,level - 3*trunc(level/3) as remain_trunc
,level - 3*round(level/3) as remain_round
,level - 3*ceil(level/3) as remain_ceil
,floor(level/3) as floor
,trunc(level/3) as trunc
,round(level/3) as round
,ceil(level/3) as ceil
from dual
connect by level <=10
)select
numerator
,cast(collect(mymap(cols,vals)) as mymapliz)
from sub unpivot(vals for cols in (denominator, quotient, remainder, modulo, remain_floor, remain_trunc, remain_round, remain_ceil, floor, trunc, round, ceil))
group by numerator
;
さまった。
NUMERATOR
---------
CAST(COLLECT(MYMAP(COLS,VALS))ASMYMAPLIZ)(KEY, VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 0), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', .333333333333))
2
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', .666666666667))
3
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 1))
4
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 1.33333333333))
5
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 1.66666666667))
6
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 2))
7
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 2.33333333333))
8
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 2.66666666667))
9
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 3))
10
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 4), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 3.33333333333))
10行が選択されました。
んで、単一行にサマれるように型宣言。
create or replace type mymaplizz as object (id number,value mymapliz);
/
create or replace type mymaplizzz is table of mymaplizz;
/
with sub as(
select
level as numerator
,3 as denominator
,level/3 as quotient
,remainder(level,3) as remainder
,mod(level,3) as modulo
,level - 3*floor(level/3) as remain_floor
,level - 3*trunc(level/3) as remain_trunc
,level - 3*round(level/3) as remain_round
,level - 3*ceil(level/3) as remain_ceil
,floor(level/3) as floor
,trunc(level/3) as trunc
,round(level/3) as round
,ceil(level/3) as ceil
from dual
connect by level <=10
),subb as(
select
NUMERATOR
,cast(collect(mymap(COLS,VALS)) as mymapliz) as mymapliz_by_NUMERATOR
from sub unpivot(vals for cols in (DENOMINATOR, QUOTIENT, REMAINDER, MODULO, REMAIN_FLOOR, REMAIN_TRUNC, REMAIN_ROUND, REMAIN_CEIL, FLOOR, TRUNC, ROUND, CEIL))
group by NUMERATOR
)select cast(collect(mymaplizz(NUMERATOR,mymapliz_by_NUMERATOR)) as mymaplizzz) from subb
;
単一行になった。
CAST(COLLECT(MYMAPLIZZ(NUMERATOR,MYMAPLIZ_BY_NUMERATOR))ASMYMAPLIZZZ)(ID, VALUE(KEY, VALUE))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYMAPLIZZZ(MYMAPLIZZ(1, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 0), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', .333333333333))), MYMAPLIZZ(2, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', .666666666667))), MYMAPLIZZ(3, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 1))), MYMAPLIZZ(4, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 1), M
YMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 1.33333333333))), MYMAPLIZZ(5, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 1.66666666667))), MYMAPLIZZ(6, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 2))), MYMAPLIZZ(7, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('
REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 2.33333333333))), MYMAPLIZZ(8, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 2.66666666667))), MYMAPLIZZ(9, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 3))), MYMAPLIZZ(10, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 4), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('
QUOTIENT', 3.33333333333))))
1行が選択されました。
行複写するためには引数で与えられたlizの要素数を知る必要があるので、コレクションの要素数を返却してくれる関数を別途作成。
create or replace type liz is table of number;
/
create or replace function cnt(rsv_args liz)
return number
as
rt number;
begin
select count(*) into rt from table(rsv_args);
return rt;
end;
/
テスト。
select listagg(level,',')within group(order by level) as liz from dual connect by level <=10;
select liz(1,2,3,4,5,6,7,8,9,10) as nums from dual;
select cnt(liz(1,2,3,4,5,6,7,8,9,10)) as cnt from dual;
USER01@ORCLPDB01> column liz for a180
USER01@ORCLPDB01> select listagg(level,',')within group(order by level) as liz from dual connect by level <=10;
LIZ
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10
1行が選択されました。
経過: 00:00:00.00
USER01@ORCLPDB01> column nums for a180
USER01@ORCLPDB01> select liz(1,2,3,4,5,6,7,8,9,10) as nums from dual;
NUMS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
1行が選択されました。
経過: 00:00:00.00
USER01@ORCLPDB01> select cnt(liz(1,2,3,4,5,6,7,8,9,10)) as cnt from dual;
CNT
-------------
10
1行が選択されました。
経過: 00:00:00.00
やっとできた。汎用ファンクション。
create or replace function div(denominator number,nums liz)
return mymaplizzz
as
rt mymaplizzz;
begin
with sub as(
select
level as numerator
,denominator as denominator
,level/denominator as quotient
,remainder(level,denominator) as remainder
,mod(level,denominator) as modulo
,level - denominator*floor(level/denominator) as remain_floor
,level - denominator*trunc(level/denominator) as remain_trunc
,level - denominator*round(level/denominator) as remain_round
,level - denominator*ceil(level/denominator) as remain_ceil
,floor(level/denominator) as floor
,trunc(level/denominator) as trunc
,round(level/denominator) as round
,ceil(level/denominator) as ceil
from dual
connect by level <=cnt(nums)
),subb as(
select
numerator
,cast(collect(mymap(cols,vals)) as mymapliz) as mymapliz_by_numerator
from sub
unpivot(vals for cols in (denominator, quotient, remainder, modulo, remain_floor, remain_trunc, remain_round, remain_ceil, floor, trunc, round, ceil))
group by numerator
)select cast(collect(mymaplizz(numerator,mymapliz_by_numerator)) as mymaplizzz) into rt from subb ;
return rt;
end;
/
このまま、実行した結果はわかりづらいので、table関数でラップ剥がし。
USER01@ORCLPDB01> select div(3,liz(1,2,3,4,5,6,7,8,9,10)) as nums from dual;
NUMS(ID, VALUE(KEY, VALUE))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYMAPLIZZZ(MYMAPLIZZ(1, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 0), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', .333333333333))), MYMAPLIZZ(2, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', .666666666667))), MYMAPLIZZ(3, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 1))), MYMAPLIZZ(4, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 1), M
YMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 1.33333333333))), MYMAPLIZZ(5, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 1.66666666667))), MYMAPLIZZ(6, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 2))), MYMAPLIZZ(7, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('
REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 2.33333333333))), MYMAPLIZZ(8, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 2.66666666667))), MYMAPLIZZ(9, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 3))), MYMAPLIZZ(10, MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 4), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('
QUOTIENT', 3.33333333333))))
1行が選択されました。
ラップ剥がし一つ目。
with sub as(
select id,value from table(div(3,liz(1,2,3,4,5,6,7,8,9,10)))
)select * from sub;
見づらい。
ID
-------------
VALUE(KEY, VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 0), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', .333333333333))
2
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', .666666666667))
3
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 1))
4
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 1.33333333333))
5
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 1.66666666667))
6
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 2))
7
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 2.33333333333))
8
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 2.66666666667))
9
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 3))
10
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 4), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 3.33333333333))
10行が選択されました。
ラップ剥がし２つ目。
with sub as(
select id,value from table(div(3,liz(1,2,3,4,5,6,7,8,9,10)))
),subb as(
select s1.id,s2.key,s2.value
from sub s1,table(value) s2
)select * from subb;
見やすいけど、縦に長い。
USER01@ORCLPDB01> column id for 99
USER01@ORCLPDB01> column key for a20
USER01@ORCLPDB01> column value for a160
ID KEY VALUE
--- -------------------- -------------
1 DENOMINATOR 3
1 CEIL 1
1 ROUND 0
1 TRUNC 0
1 FLOOR 0
1 REMAIN_CEIL -2
1 REMAIN_ROUND 1
1 REMAIN_TRUNC 1
1 REMAIN_FLOOR 1
1 MODULO 1
1 REMAINDER 1
1 QUOTIENT .333333333333
2 DENOMINATOR 3
2 CEIL 1
2 ROUND 1
2 TRUNC 0
2 FLOOR 0
2 REMAIN_CEIL -1
2 REMAIN_ROUND -1
2 REMAIN_TRUNC 2
2 REMAIN_FLOOR 2
2 MODULO 2
2 REMAINDER -1
2 QUOTIENT .666666666667
3 DENOMINATOR 3
3 CEIL 1
3 ROUND 1
3 TRUNC 1
3 FLOOR 1
3 REMAIN_CEIL 0
3 REMAIN_ROUND 0
3 REMAIN_TRUNC 0
3 REMAIN_FLOOR 0
3 MODULO 0
3 REMAINDER 0
3 QUOTIENT 1
4 DENOMINATOR 3
4 CEIL 2
4 ROUND 1
4 TRUNC 1
4 FLOOR 1
4 REMAIN_CEIL -2
4 REMAIN_ROUND 1
4 REMAIN_TRUNC 1
4 REMAIN_FLOOR 1
4 MODULO 1
4 REMAINDER 1
4 QUOTIENT 1.33333333333
5 DENOMINATOR 3
5 CEIL 2
5 ROUND 2
5 TRUNC 1
5 FLOOR 1
5 REMAIN_CEIL -1
5 REMAIN_ROUND -1
5 REMAIN_TRUNC 2
5 REMAIN_FLOOR 2
5 MODULO 2
5 REMAINDER -1
5 QUOTIENT 1.66666666667
6 DENOMINATOR 3
6 CEIL 2
6 ROUND 2
6 TRUNC 2
6 FLOOR 2
6 REMAIN_CEIL 0
6 REMAIN_ROUND 0
6 REMAIN_TRUNC 0
6 REMAIN_FLOOR 0
6 MODULO 0
6 REMAINDER 0
6 QUOTIENT 2
7 DENOMINATOR 3
7 CEIL 3
7 ROUND 2
7 TRUNC 2
7 FLOOR 2
7 REMAIN_CEIL -2
7 REMAIN_ROUND 1
7 REMAIN_TRUNC 1
7 REMAIN_FLOOR 1
7 MODULO 1
7 REMAINDER 1
7 QUOTIENT 2.33333333333
8 DENOMINATOR 3
8 CEIL 3
8 ROUND 3
8 TRUNC 2
8 FLOOR 2
8 REMAIN_CEIL -1
8 REMAIN_ROUND -1
8 REMAIN_TRUNC 2
8 REMAIN_FLOOR 2
8 MODULO 2
8 REMAINDER -1
8 QUOTIENT 2.66666666667
9 DENOMINATOR 3
9 CEIL 3
9 ROUND 3
9 TRUNC 3
9 FLOOR 3
9 REMAIN_CEIL 0
9 REMAIN_ROUND 0
9 REMAIN_TRUNC 0
9 REMAIN_FLOOR 0
9 MODULO 0
9 REMAINDER 0
9 QUOTIENT 3
10 DENOMINATOR 3
10 CEIL 4
10 ROUND 3
10 TRUNC 3
10 FLOOR 3
10 REMAIN_CEIL -2
10 REMAIN_ROUND 1
10 REMAIN_TRUNC 1
10 REMAIN_FLOOR 1
10 MODULO 1
10 REMAINDER 1
10 QUOTIENT 3.33333333333
120行が選択されました。
pivotで列展開。
with sub as(
select id,value from table(div(3,liz(1,2,3,4,5,6,7,8,9,10)))
),subb as(
select s1.id,s2.key,s2.value
from sub s1,table(value) s2
)select * from subb pivot(min(value) for key in ('DENOMINATOR','QUOTIENT','REMAINDER','MODULO','REMAIN_FLOOR','REMAIN_TRUNC','REMAIN_ROUND','REMAIN_CEIL','FLOOR','TRUNC','ROUND','CEIL'))
order by id
;
ID 'DENOMINATOR' 'QUOTIENT' 'REMAINDER' 'MODULO' 'REMAIN_FLOOR' 'REMAIN_TRUNC' 'REMAIN_ROUND' 'REMAIN_CEIL' 'FLOOR' 'TRUNC' 'ROUND' 'CEIL'
--- ------------- ------------- ------------- ------------- -------------- -------------- -------------- ------------- ------------- ------------- ------------- -------------
1 3 .333333333333 1 1 1 1 1 -2 0 0 0 1
2 3 .666666666667 -1 2 2 2 -1 -1 0 0 1 1
3 3 1 0 0 0 0 0 0 1 1 1 1
4 3 1.33333333333 1 1 1 1 1 -2 1 1 1 2
5 3 1.66666666667 -1 2 2 2 -1 -1 1 1 2 2
6 3 2 0 0 0 0 0 0 2 2 2 2
7 3 2.33333333333 1 1 1 1 1 -2 2 2 2 3
8 3 2.66666666667 -1 2 2 2 -1 -1 2 2 3 3
9 3 3 0 0 0 0 0 0 3 3 3 3
10 3 3.33333333333 1 1 1 1 1 -2 3 3 3 4
10行が選択されました。
まとめ
使用した型とファンクションをまとめておく。
ここで気付いたんだけど、ダイナミックにするにはsplit関数的なやつが必要だ。
なので、追加しておく。あと、lizはvarchar2(4000)に変えておくべきだ。。。
create or replace type liz is table of varchar2(4000);
/
create or replace function cnt(rsv_args liz)
return number
as
rt number;
begin
select count(*) into rt from table(rsv_args);
return rt;
end;
/
create type mymap as object (key varchar2(4000), value number);
/
create type mymapliz is table of mymap;
/
create or replace type mymaplizz as object (id number,value mymapliz);
/
create or replace type mymaplizzz is table of mymaplizz;
/
create or replace function div(denominator number,nums liz)
return mymaplizzz
as
rt mymaplizzz;
begin
with sub as(
select
level as numerator
,denominator as denominator
,level/denominator as quotient
,remainder(level,denominator) as remainder
,mod(level,denominator) as modulo
,level - denominator*floor(level/denominator) as remain_floor
,level - denominator*trunc(level/denominator) as remain_trunc
,level - denominator*round(level/denominator) as remain_round
,level - denominator*ceil(level/denominator) as remain_ceil
,floor(level/denominator) as floor
,trunc(level/denominator) as trunc
,round(level/denominator) as round
,ceil(level/denominator) as ceil
from dual
connect by level <=cnt(nums)
),subb as(
select
numerator
,cast(collect(mymap(cols,vals)) as mymapliz) as mymapliz_by_numerator
from sub
unpivot(vals for cols in (denominator, quotient, remainder, modulo, remain_floor, remain_trunc, remain_round, remain_ceil, floor, trunc, round, ceil))
group by numerator
)select cast(collect(mymaplizz(numerator,mymapliz_by_numerator)) as mymaplizzz) into rt from subb ;
return rt;
end;
/
create or replace function split(rsv_args varchar2)
return liz
as
rt liz;
begin
select
cast(collect(substr(rsv_args,decode(level-1,0,0,instr(rsv_args,',',1,level-1))+1,decode(instr(rsv_args,',',1,level),0,4000,instr(rsv_args,',',1,level))-decode(level-1,0,0,instr(rsv_args,',',1,level-1))-1)) as liz)
into rt
from
dual
connect by
level <=length(rsv_args) - length(replace(rsv_args,',',''))+1;
return rt;
end;
/
split
select listagg(level,',')within group (order by level) from dual connect by level <=10;
select split(listagg(level,',')within group (order by level)) from dual connect by level <=10;
select id,value from table(div(3,(select split(listagg(level,',')within group (order by level)) from dual connect by level <=10)));
USER01@ORCLPDB01> column liz for a20
USER01@ORCLPDB01> select listagg(level,',')within group (order by level) as liz from dual connect by level <=10;
LIZ
--------------------
1,2,3,4,5,6,7,8,9,10
1行が選択されました。
USER01@ORCLPDB01> column liz for a100
USER01@ORCLPDB01> select split(listagg(level,',')within group (order by level)) as liz from dual connect by level <=10;
LIZ
----------------------------------------------------------------------------------------------------
LIZ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
1行が選択されました。
USER01@ORCLPDB01> select id,value from table(div(3,(select split(listagg(level,',')within group (order by level)) from dual connect by level <=10)));
ID
---
VALUE(KEY, VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 0), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', .333333333333))
2
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 0), MYMAP('FLOOR', 0), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', .666666666667))
3
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 1), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 1))
4
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 1), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 1.33333333333))
5
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 1), MYMAP('FLOOR', 1), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 1.66666666667))
6
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 2), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 2))
7
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 2), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 2.33333333333))
8
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 2), MYMAP('FLOOR', 2), MYMAP('REMAIN_CEIL', -1), MYMAP('REMAIN_ROUND', -1), MYMAP('REMAIN_TRUNC', 2), MYMAP('REMAIN_FLOOR', 2), MYMAP('MODULO', 2), MYMAP('REMAINDER', -1), MYMAP('QUOTIENT', 2.66666666667))
9
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 3), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', 0), MYMAP('REMAIN_ROUND', 0), MYMAP('REMAIN_TRUNC', 0), MYMAP('REMAIN_FLOOR', 0), MYMAP('MODULO', 0), MYMAP('REMAINDER', 0), MYMAP('QUOTIENT', 3))
10
MYMAPLIZ(MYMAP('DENOMINATOR', 3), MYMAP('CEIL', 4), MYMAP('ROUND', 3), MYMAP('TRUNC', 3), MYMAP('FLOOR', 3), MYMAP('REMAIN_CEIL', -2), MYMAP('REMAIN_ROUND', 1), MYMAP('REMAIN_TRUNC', 1), MYMAP('REMAIN_FLOOR', 1), MYMAP('MODULO', 1), MYMAP('REMAINDER', 1), MYMAP('QUOTIENT', 3.33333333333))
10行が選択されました。
使用例
:divisor=3
:divided_end=10
with sub as(
select id,value from table(div(:divisor,(select split(listagg(level,',')within group (order by level)) from dual connect by level <=:divided)))
),subb as(
select s1.id,s2.key,s2.value
from sub s1,table(value) s2
)select * from subb pivot(min(value) for key in ('DENOMINATOR','QUOTIENT','REMAINDER','MODULO','REMAIN_FLOOR','REMAIN_TRUNC','REMAIN_ROUND','REMAIN_CEIL','FLOOR','TRUNC','ROUND','CEIL'))
order by id
;
:divisor=3
:divided_end=16
:divisor=5
:divided_end=16
あとがき
汎用ファンクション作成するの大変。でも楽しい。便利なファンクションができた。
以上、ありがとうございました。
20191122追記
divファンクションの引数は**create or replace function div(denominator number,start_rn number,end_rn number)**に変更。
create or replace function div(denominator number,start_rn number,end_rn number)
return mymaplizzz
as
rt mymaplizzz;
begin
with sub as(
select
level as numerator
,denominator as denominator
,level/denominator as quotient
,remainder(level,denominator) as remainder
,mod(level,denominator) as modulo
,level - denominator*floor(level/denominator) as remain_floor
,level - denominator*trunc(level/denominator) as remain_trunc
,level - denominator*round(level/denominator) as remain_round
,level - denominator*ceil(level/denominator) as remain_ceil
,floor(level/denominator) as floor
,trunc(level/denominator) as trunc
,round(level/denominator) as round
,ceil(level/denominator) as ceil
from dual
where
level >= start_rn
connect by level <=end_rn
),subb as(
select
numerator
,cast(collect(mymap(cols,vals)) as mymapliz) as mymapliz_by_numerator
from sub
unpivot(vals for cols in (denominator, quotient, remainder, modulo, remain_floor, remain_trunc, remain_round, remain_ceil, floor, trunc, round, ceil))
group by numerator
)select cast(collect(mymaplizz(numerator,mymapliz_by_numerator)) as mymaplizzz) into rt from subb ;
return rt;
end;
/
select id,value from table(div(3,3,11));
with sub as(
select id,value from table(div(3,3,11))
),subb as(
select s1.id,s2.key,s2.value
from sub s1,table(value) s2
)select * from subb pivot(min(value) for key in ('DENOMINATOR','QUOTIENT','REMAINDER','MODULO','REMAIN_FLOOR','REMAIN_TRUNC','REMAIN_ROUND','REMAIN_CEIL','FLOOR','TRUNC','ROUND','CEIL'))
order by id
;
こういうの作るとき、依存関係鬱陶しいので、毎度クリーンな状態にするために、以下のスクリプトで気持ちを新たに再出発。
create or replace procedure all_drop_obj as
cursor csr is with sub as (
select
object_type
, object_name
from
user_objects s1
where
exists (
select
1
from
user_objects s2
where
s1.object_type = s2.object_type
)
and not exists (
select
1
from
user_objects s2
where
s2.object_type = 'PROCEDURE'
and s2.object_name = 'ALL_DROP_OBJ'
and s1.object_type = s2.object_type
and s1.object_name = s2.object_name
)
) select
case
when s1.object_type = 'JOB' then 'BEGIN DBMS_SCHEDULER.DROP_JOB('|| Q'Q'Q' || s1.object_name || Q'Q'Q' ||'); END;'
when s1.object_type = 'CREDENTIAL' then 'BEGIN DBMS_SCHEDULER.DROP_CREDENTIAL('|| Q'Q'Q' || s1.object_name || Q'Q'Q' ||'); END;'
else
'DROP '
|| s1.object_type
|| ' '
|| s1.object_name
||
case
when s1.object_type = 'TABLE' then ' CASCADE CONSTRAINTS PURGE'
when s1.object_type in (
'SYNONYM'
, 'TYPE'
) then ' FORCE'
else ''
end
end as build_sql
from
sub s1;
build_sql clob;
begin
build_sql := to_clob(' ');
open csr;
loop
begin
fetch csr into build_sql;
exit when csr%notfound;
dbms_output.put_line(build_sql);
execute immediate build_sql;
exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
end;
end loop;
close csr;
end;
/
exec all_drop_obj;
/