sql oracle 数値関数によるグルーピング

# まえがき

先頭からある件数ごとにグルーピングするのどうやるんだろとおもって調べた。

# 参考文献

[REMAINDER](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions133.htm)

[MOD](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions088.htm#i77996)




# 環境

```bash
$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が割る数。

```sql
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クエリまでの実行結果

```sql
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;
```

```bash
           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までの実行結果

```sql
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;
```


```bash
           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...で振り直した値から各連番値までの距離でグルーピングしている。

```bash
           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

```

こういうのでもいける

```sql
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;
```

```bash
        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式とかでこねくりまわして、規則性を見出していくのが確実だとおもう。

ので、つくってみた。

```sql
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 - 3*round(level/3)**と同じ。
**MODULO**は**LEVEL-3*TRUNC(LEVEL/3)**と同じ。


```bash
    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

```

# 汎用ファンクション化

早見表をファンクション化してみることにした。


まず小さく始める。

```sql
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;
```

```bash
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)

```

んで、さまる。

```sql
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;
```

```bash
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で列インターフェースを整える。

整形前

```sql
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;
```


```bash
    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
```

整形後

```sql
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。

```bash
    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ごとにさまる。

```sql
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
;
```

さまった。

```bash
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行が選択されました。

```

んで、単一行にサマれるように型宣言。

```sql
create or replace type mymaplizz as object (id number,value mymapliz);
/
create or replace type mymaplizzz is table of mymaplizz;
/
```

```sql
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
;
```

単一行になった。

```sql
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の要素数を知る必要があるので、コレクションの要素数を返却してくれる関数を別途作成。

```sql
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;
/
```

テスト。

```sql
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

```

やっとできた。汎用ファンクション。

```sql
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関数でラップ剥がし。

```sql
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行が選択されました。

```

ラップ剥がし一つ目。

```sql
with sub as(
select id,value from table(div(3,liz(1,2,3,4,5,6,7,8,9,10)))
)select * from sub;
```

見づらい。

```sql
           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行が選択されました。

```

ラップ剥がし２つ目。

```sql
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;
```

見やすいけど、縦に長い。

```bash
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で列展開。

```sql
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
;
```


```bash
 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)に変えておくべきだ。。。



```sql
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

```sql
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)));

```

```bash
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**

```sql
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
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/3385b146-7806-7add-39fb-cd3385d0576f.png)



**:divisor=3**
**:divided_end=16**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/405c4ffd-3c9b-bb24-3b8f-c06f1b7a5fab.png)

**:divisor=5**
**:divided_end=16**


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/4be2447a-edca-950b-f158-d4a1bdab590a.png)


# あとがき

汎用ファンクション作成するの大変。でも楽しい。便利なファンクションができた。

以上、ありがとうございました。


# 20191122追記

divファンクションの引数は**create or replace function div(denominator number,start_rn number,end_rn number)**に変更。

```sql
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;
/

```

```sql
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
;
```


こういうの作るとき、依存関係鬱陶しいので、毎度クリーンな状態にするために、以下のスクリプトで気持ちを新たに再出発。

```sql
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;
/
```