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

まえがき

この記事は、1年ほど前、tlokwengさん、youmil_rainさんのアドバイスをいただきながら書いた以前の記事をファンクション化したものです。

環境

$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
に接続されました。

データ準備

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

スクリプト

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

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

ファンクション化前

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が未指定

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

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

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

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

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

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

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関数を使って行展開。

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

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

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

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

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

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

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;

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

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

4行が選択されました。

経過: 00:00:00.00

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

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を設定してみる。

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

補足

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

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;

あとがき

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

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