sql oracle 組み合わせ ファンクション版

# まえがき

この記事は、1年ほど前、tlokwengさん、youmil_rainさんのアドバイスをいただきながら書いた[以前の記事](https://qiita.com/ukijumotahaneniarukenia/items/f16de6051fd2ffa09300)をファンクション化したものです。

# 環境

```bash
$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 日 11月 24 18:00:40 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 土 11月 23 2019 17:01:57 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
```

# データ準備

```sql
drop table emoji purge;
drop table tmp purge;
create table tmp(emo nvarchar2(1000));

insert into tmp values ('🐭');
insert into tmp values ('🐮');
insert into tmp values ('🐱');
insert into tmp values ('🐵');
insert into tmp values ('🐿');
insert into tmp values ('👀');
insert into tmp values ('👆');
insert into tmp values ('👇');
insert into tmp values ('👈');
insert into tmp values ('👉');
insert into tmp values ('👊');
commit;
create table emoji as select listagg(emo,',')within group (order by rownum) as emo from tmp;

select * from emoji;

USER01@ORCLPDB01> select * from emoji;

EMO
----------------------------------------------------------------------------------------------------------------------------------
🐭,🐮,🐱,🐵,🐿,👀,👆,👇,👈,👉,👊

1行が選択されました。

経過: 00:00:00.00

```


# スクリプト

## 組み合わせファンクションに必要なファンクション

```sql
create or replace type liz is table of varchar2(4000);
/

create or replace type mapliz as object (grp number,value liz);
/

create or replace type grpliz is table of mapliz;
/

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

create or replace function strliz(rsv_args liz)
return varchar2
as
rt varchar2(4000);
begin
    select listagg(column_value,',')within group(order by rownum) into rt from table(rsv_args);
    return rt;
end;
/
```

## ファンクション化前


```sql
with sub as(
    select column_value as ele,rownum as rn from table(split(:tgt))
),rec(grp,ele,liz,cnt,cum_cnt) as(
    select 1,ele,ele,count(*)over() as cnt,count(*)over() as cum_cnt from sub
    union all
    select s0.grp+1,s1.ele,s0.liz||','||s1.ele,count(*)over() as cnt,s0.cum_cnt+count(*)over() as cum_cnt from rec s0,sub s1
    where s0.ele < s1.ele
)select * from rec s1 ;--where grp=:r;
```

**:tgt**が**a,b,c,d**
**:grp**が未指定


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/f5739865-63dd-88fc-d99d-cec6497ec400.png)


**:tgt**が**a,b,c,d**
**:grp**が**2**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/0a7b15ca-7c0b-9a3a-11b6-a343c7f34bf0.png)





## 組み合わせファンクション

第一引数にカンマ区切り文字列とそこから取得する要素数を第2引数で指定。

```sql
create or replace function comb(tgt varchar2,r number)
return grpliz
as
rt grpliz;
begin
    with sub as(
        select column_value as ele,rownum as rn from table(split(tgt))
    ),rec(grp,ele,liz) as(
        select 1,ele,ele from sub
        union all
        select s0.grp+1,s1.ele,s0.liz||','||s1.ele from rec s0,sub s1
        where s0.ele < s1.ele
    )select cast(collect(mapliz(rownum,split(s1.liz))) as grpliz) into rt from rec s1 where grp=r;
    return rt;
end;
/
```

普通に取ると、コレクション型で返ってきてちょっと不便。

```sql
select comb('a,b,c,d',2) as cmb from dual;
```

```bash
USER01@ORCLPDB01> column cmb for a180
USER01@ORCLPDB01> select comb('a,b,c,d',2) as cmb from dual;

CMB(GRP, VALUE)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRPLIZ(MAPLIZ(1, LIZ('a', 'b')), MAPLIZ(2, LIZ('a', 'c')), MAPLIZ(3, LIZ('a', 'd')), MAPLIZ(4, LIZ('b', 'c')), MAPLIZ(5, LIZ('b', 'd')), MAPLIZ(6, LIZ('c', 'd')))

1行が選択されました。

経過: 00:00:00.00
```

table関数を使って行展開。

```sql
select * from table(comb('a,b,c,d',2));
```


```bash
USER01@ORCLPDB01> column grp for 99
USER01@ORCLPDB01> column value for a180
USER01@ORCLPDB01> select * from table(comb('a,b,c,d',2));

GRP VALUE
--- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 LIZ('a', 'b')
  2 LIZ('a', 'c')
  3 LIZ('a', 'd')
  4 LIZ('b', 'c')
  5 LIZ('b', 'd')
  6 LIZ('c', 'd')

6行が選択されました。

経過: 00:00:00.01
```

コレクションをカンマ区切り文字列に戻す。

```sql
select grp,strliz(value) as cmb from table(comb('a,b,c,d',2));
```


```bash
USER01@ORCLPDB01> column grp for 99
USER01@ORCLPDB01> column cmb for a180
USER01@ORCLPDB01> select grp,strliz(value) as cmb from table(comb('a,b,c,d',2));

GRP CMB
--- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 a,b
  2 a,c
  3 a,d
  4 b,c
  5 b,d
  6 c,d

6行が選択されました。

経過: 00:00:00.01
```

ダイナミックにしてみる。

```sql
drop table emoji2 purge;
create table emoji2 as
select column_value as emo from table(select split(emo) from emoji) where rownum <=4;

select * from emoji2;
```

```bash
USER01@ORCLPDB01> column emo for a18
USER01@ORCLPDB01> select * from emoji2;

EMO
------------------
🐭
🐮
🐱
🐵

4行が選択されました。

経過: 00:00:00.00
```

```sql
select grp,strliz(value) from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),2));
```

```bash
USER01@ORCLPDB01> select grp,strliz(value) as cmb from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),2));

GRP CMB
--- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 🐭,🐮
  2 🐭,🐱
  3 🐭,🐵
  4 🐮,🐱
  5 🐮,🐵
  6 🐱,🐵

6行が選択されました。

経過: 00:00:00.01
```

取得数に-1,0,要素数より大きな値,nullを設定してみる。

```bash
USER01@ORCLPDB01> select grp,strliz(value) from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),-1));

レコードが選択されませんでした。

経過: 00:00:00.00
USER01@ORCLPDB01> select grp,strliz(value) from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),0));

レコードが選択されませんでした。

経過: 00:00:00.00
USER01@ORCLPDB01> select grp,strliz(value) from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),5));

レコードが選択されませんでした。

経過: 00:00:00.01
USER01@ORCLPDB01> select grp,strliz(value) from table(comb((select listagg(emo,',')within group (order by rownum) from emoji2),null));

レコードが選択されませんでした。

経過: 00:00:00.02

```

# 補足

再帰中でコレクションを使用すると、今回の環境では動かなかった。なので、文字列操作にした。

```sql
with sub as(
    select chr(64+level) as ele,level as rn from dual connect by level <=9
),rec(grp,ele,liz,cnt,cum_cnt) as(
    select 1,ele,liz(ele),count(*)over() as cnt,count(*)over() as cum_cnt from sub
    union all
    select s0.grp+1,s1.ele,s0.liz multiset union all liz(s1.ele),count(*)over() as cnt,s0.cnt+count(*)over() as cum_cnt from rec s0,sub s1
    where s0.ele < s1.ele
)select s1.* from rec s1;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/7180d5b4-68b3-30a1-8c5d-4aef4a559090.png)


# あとがき

便利なファンクションが生まれたと思う。

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