sql oracle 順列 ファンクション版

# まえがき
再帰関数でカンマ区切り文字列リストから順列列挙するファンクションを作成した。

# 参考文献

とってもわかりやすい。

https://www.ns.kogakuin.ac.jp/~cu40887/prog2/chapter5.html


http://gushwell.ifdef.jp/etude/Permutation.html

# 環境

```sql
$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 月 11月 25 23:43:03 2019
Version 19.3.0.0.0

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

最終正常ログイン時間: 月 11月 25 2019 20:52:26 +09:00


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

# スクリプト

引数にカンマ区切り文字列とそこから取得する要素数を指定して、順列をコレクションで返却してくれるファンクションの作成を目標とする。

汎用化ファンクションに落とし込むまでの過程も記載することにする。

## 汎用化に必要な基本ファンクション

あると汎用化ファンクションの作成が捗るので、作成。

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

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

create or replace function del_ele(tgt_ele varchar2,rsv_args liz)
return liz
as
rt liz;
begin
    select
        case
            when trim(tgt_ele) is not null and exists(select 1 from table(rsv_args) where column_value = tgt_ele) then (select cast(collect(column_value)as liz) from table(rsv_args) where column_value <> tgt_ele)
            else rsv_args
        end
    into rt
    from dual;
    return rt;
end;
/
```


## 汎用化前

結果リストと残リストを各グループごとに次の処理に持ち回っているところがポイント。そのために型宣言。

```sql
create or replace type tgtliz is object(grp number,rt liz,remain liz);
/

create or replace type grpliz is table of tgtliz;
/
```

カンマ区切りリスト**a,b,c,d**で汎用化前の動きをイメージする。

```sql
with sub as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            rownum as grp
            ,liz(column_value) as rt
            ,del_ele(column_value,split('a,b,c,d')) as remain
        from
            table(split('a,b,c,d'))
        )
)
select grp, rt, remain from sub,table(rtliz);
```

rt列には与えられた引数**(a,b,c,d)**をtable関数で行展開したcolumn_valueを追加し、remain列には取得したcolumn_value以外を保持する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/4bbe6e26-409e-d5ab-9074-f337399b02c7.png)


前回結果サブクエリをtable関数で指定した列で行展開。

```sql
with sub as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            rownum as grp
            ,liz(column_value) as rt
            ,del_ele(column_value,split('a,b,c,d')) as remain
        from
            table(split('a,b,c,d'))
        )
)
,subb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            , del_ele(column_value,remain) as remain 
        from
            sub
            ,table(rtliz)
            ,table(remain)
        )
)
select grp, rt, remain from subb,table(rtliz);
```

rt列には与えられた引数**(前回のremain列)**をtable関数で行展開したcolumn_valueを追加し、remain列には取得したcolumn_value以外を保持する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/d454af30-c0a1-a502-1a86-78c499cfec54.png)


前回結果サブクエリをtable関数で指定した列で行展開。

```sql
with sub as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            rownum as grp
            ,liz(column_value) as rt
            ,del_ele(column_value,split('a,b,c,d')) as remain
        from
            table(split('a,b,c,d'))
        )
)
,subb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            , del_ele(column_value,remain) as remain 
        from
            sub
            ,table(rtliz)
            ,table(remain)
        )
)
,subbb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            ,del_ele(column_value,remain) as remain
        from
            subb
            ,table(rtliz)
            ,table(remain)
        )
)
select grp, rt, remain from subbb,table(rtliz);
```

rt列には与えられた引数**(前回のremain列)**をtable関数で行展開したcolumn_valueを追加し、remain列には取得したcolumn_value以外を保持する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/a7eeef52-d23a-077c-8984-6e0e5414e4c3.png)

前回結果サブクエリをtable関数で指定した列で行展開。

```sql
with sub as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            rownum as grp
            ,liz(column_value) as rt
            ,del_ele(column_value,split('a,b,c,d')) as remain
        from
            table(split('a,b,c,d'))
        )
)
,subb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            , del_ele(column_value,remain) as remain 
        from
            sub
            ,table(rtliz)
            ,table(remain)
        )
)
,subbb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            ,del_ele(column_value,remain) as remain
        from
            subb
            ,table(rtliz)
            ,table(remain)
        )
)
,subbbb as(
    select 
        cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
    from (
        select
            grp
            ,rt multiset union all liz(column_value) as rt
            ,del_ele(column_value,remain) as remain
        from
            subbb
            ,table(rtliz)
            ,table(remain)
        )
)
select grp, rt, remain from subbbb,table(rtliz);
```

rt列には与えられた引数**(前回のremain列)**をtable関数で行展開したcolumn_valueを追加し、remain列には取得したcolumn_value以外を保持する。

remain列は空になったので、処理終了。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/8a3ae12e-1df8-9255-1d57-599bfe5de6ec.png)


## 汎用化後

以上を踏まえたのが以下のファンクション。

```sql
create or replace function perm (grz grpliz,n number)
return grpliz
as
rt grpliz :=grz;
begin
    select
        case
            when n = 0 then grpliz(tgtliz(null,null,null))
            else rt multiset union all perm(rtliz,n-1)
        end
    into rt
    from(
        select 
            cast(collect(tgtliz(grp,rt,remain)) as grpliz) as rtliz 
        from (
            select
                grp+1 as grp
                ,rt multiset union all liz(column_value) as rt
                ,del_ele(column_value,remain) as remain
            from
                table(grz),table(remain)
            )
        );
    return rt;
end;
/
```

このファンクションはまだ少し、エグみがある。

以下のように実行すると、

**:n=2**

```sql
select * from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),:n+1)) where grp=:n;
```

rt列にnullが混在してしまう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/62054a97-cd69-7a50-7eed-f43dc7548a41.png)

そこで、作成しておいたstrliz関数で、文字列に変換すると、nullを除外してくれる。これはlistagg関数の処理によるもの。


**:n=2**

```sql
select grp,strliz(rt) as rt,strliz(remain) as remain from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),:n+1)) where grp=:n;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/4210e6e6-a865-ce2c-52e4-1dc65485a1c5.png)

バインド値をいろいろ変えてみる。

**:n=null**

nvlでハンドリングしたほうが良さげ。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/a77e5cc0-6d1e-78b9-c359-ea78ed9f12b9.png)

**:n=-1**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/51c52735-29d5-cb70-34c5-485e9fa02849.png)


**:n=0**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/fa566e41-a1c7-87cb-2756-2b29ba71c380.png)

**:n=1**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/b2efead9-976c-393e-c9bb-e4289bc36978.png)


**:n=2**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/c8d54906-219b-1eb8-12fb-bdbde5d23d24.png)


**:n=3**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/5e0aba78-36c5-72a2-b6eb-d8fdeea917ba.png)


**:n=4**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/41313fcb-0fda-9cf0-3f28-813e433f6c85.png)


**:n=5**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/48635577-b1b5-66e6-9de1-efbff059ceac.png)

全部取得版

```sql
select
    grp
    ,row_number()over(partition by grp order by rownum) as seq
    ,count(*)over(partition by grp) as grp_cnt
    ,sum(case when grp is not null then 1 else 0 end)over(order by grp)-1 as cum_grp_cnt
    ,strliz(rt) as rt
    ,strliz(remain) as remain
from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),:n+1))
where grp is not null
;
```



```bash
USER01@ORCLPDB01> column GRP for 99
USER01@ORCLPDB01> column GRP_CNT for 99
USER01@ORCLPDB01> column CUM_GRP_CNT for 99
USER01@ORCLPDB01> column RT for a30
USER01@ORCLPDB01> column REMAIN for a30
USER01@ORCLPDB01> select grp ,row_number()over(partition by grp order by rownum) as seq ,count(*)over(partition by grp) as grp_cnt ,sum(case when grp is not null then 1 else 0 end)over(order by grp)-1 as cum_grp_cnt ,strliz(rt) as rt,strliz(remain) as remain from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),&n+1)) where grp is not null ; 
nに値を入力してください: 5
旧   1: select grp ,row_number()over(partition by grp order by rownum) as seq ,count(*)over(partition by grp) as grp_cnt ,sum(case when grp is not null then 1 else 0 end)over(order by grp)-1 as cum_grp_cnt ,strliz(rt) as rt,strliz(remain) as remain from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),&n+1)) where grp is not null
新   1: select grp ,row_number()over(partition by grp order by rownum) as seq ,count(*)over(partition by grp) as grp_cnt ,sum(case when grp is not null then 1 else 0 end)over(order by grp)-1 as cum_grp_cnt ,strliz(rt) as rt,strliz(remain) as remain from table(perm(grpliz(tgtliz(0,liz(''),split('a,b,c,d'))),5+1)) where grp is not null

GRP           SEQ GRP_CNT CUM_GRP_CNT RT                             REMAIN
--- ------------- ------- ----------- ------------------------------ ------------------------------
  0             1       1           0                                a,b,c,d
  1             1       4           4 a                              b,c,d
  1             2       4           4 b                              a,c,d
  1             3       4           4 c                              a,b,d
  1             4       4           4 d                              a,b,c
  2             1      12          16 a,b                            c,d
  2             2      12          16 a,c                            b,d
  2             3      12          16 a,d                            b,c
  2             4      12          16 b,a                            c,d
  2             5      12          16 b,c                            a,d
  2             6      12          16 b,d                            a,c
  2             7      12          16 c,a                            b,d
  2             8      12          16 c,b                            a,d
  2             9      12          16 c,d                            a,b
  2            10      12          16 d,a                            b,c
  2            11      12          16 d,b                            a,c
  2            12      12          16 d,c                            a,b
  3             1      24          40 a,b,c                          d
  3             2      24          40 a,b,d                          c
  3             3      24          40 a,c,b                          d
  3             4      24          40 a,c,d                          b
  3             5      24          40 a,d,b                          c
  3             6      24          40 a,d,c                          b
  3             7      24          40 b,a,c                          d
  3             8      24          40 b,a,d                          c
  3             9      24          40 b,c,a                          d
  3            10      24          40 b,c,d                          a
  3            11      24          40 b,d,a                          c
  3            12      24          40 b,d,c                          a
  3            13      24          40 c,a,b                          d
  3            14      24          40 c,a,d                          b
  3            15      24          40 c,b,a                          d
  3            16      24          40 c,b,d                          a
  3            17      24          40 c,d,a                          b
  3            18      24          40 c,d,b                          a
  3            19      24          40 d,a,b                          c
  3            20      24          40 d,a,c                          b
  3            21      24          40 d,b,a                          c
  3            22      24          40 d,b,c                          a
  3            23      24          40 d,c,a                          b
  3            24      24          40 d,c,b                          a
  4             1      24          64 a,b,c,d
  4             2      24          64 a,b,d,c
  4             3      24          64 a,c,b,d
  4             4      24          64 a,c,d,b
  4             5      24          64 a,d,b,c
  4             6      24          64 a,d,c,b
  4             7      24          64 b,a,c,d
  4             8      24          64 b,a,d,c
  4             9      24          64 b,c,a,d
  4            10      24          64 b,c,d,a
  4            11      24          64 b,d,a,c
  4            12      24          64 b,d,c,a
  4            13      24          64 c,a,b,d
  4            14      24          64 c,a,d,b
  4            15      24          64 c,b,a,d
  4            16      24          64 c,b,d,a
  4            17      24          64 c,d,a,b
  4            18      24          64 c,d,b,a
  4            19      24          64 d,a,b,c
  4            20      24          64 d,a,c,b
  4            21      24          64 d,b,a,c
  4            22      24          64 d,b,c,a
  4            23      24          64 d,c,a,b
  4            24      24          64 d,c,b,a

65行が選択されました。

経過: 00:00:00.02

```


ここでnullのハンドリングに加え、インターフェースがシンプルになるように**permファンクション**をラップした**permsファンクション**を作成する。

```sql
create or replace function perms(tgt varchar2,n number)
return liz
as
rt liz;
begin
    select cast(collect(strliz(rt)) as liz) into rt from table(perm(grpliz(tgtliz(0,liz(''),split(tgt))),nvl(n,0)+1)) where grp=nvl(n,0);
    return rt;
end;
/
```

**:n=null**

```sql
select * from table(perms('a,b,c,d',null));
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/f6a5443f-3471-c1e4-5ba5-836cae92ae3f.png)

**:n=2**

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/befaf324-4af5-c563-4e54-0d8b588a9f20.png)


最後に絵文字で

```sql
drop table emoji2 purge;
create table emoji2 (
    emo varchar2(100 byte)
);
insert into emoji2 ( emo ) values ( '🐭' );
insert into emoji2 ( emo ) values ( '🐮' );
insert into emoji2 ( emo ) values ( '🐱' );
insert into emoji2 ( emo ) values ( '🐵' );
commit;
```

```sql
select * from table(perms((select listagg(emo,',')within group(order by rownum) from emoji2),2));
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/a1a1f958-b4da-42d5-0559-05d1662fa895.png)


あとさいごに


**:n=10**したらメモリ容量超えました。^^
**:n**を大きくにするにつれ、危険。

```sql
select
    grp
    ,row_number()over(partition by grp order by rownum) as seq
    ,count(*)over(partition by grp) as grp_cnt
    ,sum(case when grp is not null then 1 else 0 end)over(order by grp)-1 as cum_grp_cnt
    ,strliz(rt) as rt,strliz(remain) as remain
from table(perm(grpliz(tgtliz(0,liz(''),split('0,1,2,3,4,5,6,7,8,9'))),&n+1))
where grp is not null
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/163ee2e1-69ce-08ca-5fd4-732275cb75e3.png)


Rだと結果帰ってきた。

```R
> install.packages("gtools") 
> library(gtools)
```

```bash
$time Rscript -e 'library(gtools);permutations(n=10,r=10,v=c(0,1,2,3,4,5,6,7,8,9),repeats.allowed = F)' |& head 2>/dev/null
           [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
      [1,]    0    1    2    3    4    5    6    7    8     9
      [2,]    0    1    2    3    4    5    6    7    9     8
      [3,]    0    1    2    3    4    5    6    8    7     9
      [4,]    0    1    2    3    4    5    6    8    9     7
      [5,]    0    1    2    3    4    5    6    9    7     8
      [6,]    0    1    2    3    4    5    6    9    8     7
      [7,]    0    1    2    3    4    5    7    6    8     9
      [8,]    0    1    2    3    4    5    7    6    9     8
      [9,]    0    1    2    3    4    5    7    8    6     9

real	0m25.434s
user	0m24.569s
sys	0m0.871s
$time Rscript -e 'library(gtools);permutations(n=10,r=10,v=c(0,1,2,3,4,5,6,7,8,9),repeats.allowed = F)' |& tail 2>/dev/null
   [9991,]    0    1    3    9    8    4    5    2    6     7
   [9992,]    0    1    3    9    8    4    5    2    7     6
   [9993,]    0    1    3    9    8    4    5    6    2     7
   [9994,]    0    1    3    9    8    4    5    6    7     2
   [9995,]    0    1    3    9    8    4    5    7    2     6
   [9996,]    0    1    3    9    8    4    5    7    6     2
   [9997,]    0    1    3    9    8    4    6    2    5     7
   [9998,]    0    1    3    9    8    4    6    2    7     5
   [9999,]    0    1    3    9    8    4    6    5    2     7
 [ reached getOption("max.print") -- omitted 3618801 rows ]

real	0m25.314s
user	0m24.438s
sys	0m1.004s
```

# あとがき

結構便利なファンクションができたと思う。sqlもっと楽しめそう！^^

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

# 20191202追記

すばらしいのができた。

lateral最高。

```sql
drop table test_tbl purge;
create table test_tbl as
select level as seq,chr(level+96) as item from dual connect by level<=4; 

USER01@ORCLPDB01> select * from test_tbl;

          SEQ ITEM
------------- ----
            1 a
            2 b
            3 c
            4 d

4行が選択されました。

経過: 00:00:00.02
```

```sql
create or replace type liz is table of varchar2(4000);
/
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;
/
```


```sql
with rec(grp,seq,item,rt,rest,cnt) as(
    select
        1 as grp
        ,s1.seq
        ,s1.item
        ,s1.item
        ,(select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2)
        ,count(*)over()
    from test_tbl s1
    union all
    select
        s0.grp+1
        ,s0.seq
        ,s1.item
        ,s0.rt||','||substr(s2.rest,s1.seq,1)
        ,replace(s2.rest,substr(s2.rest,s1.seq,1),'')
        ,count(*)over(partition by s0.grp+1,substr(s0.rt,1,1))
    from rec s0
    ,test_tbl s1
    ,lateral(
        select listagg(s2.item) within group (order by s2.seq) as rest
        from test_tbl s2
        where not exists(select 1 from table(split(s0.rt))s3 where s2.item = s3.column_value ) )s2
    where
        s0.rest=s2.rest
    and s1.seq <=length(s0.rest)
)select * from rec
;

```

ラテラル最高。大好き。

```bash
USER01@ORCLPDB01> column grp for 99
USER01@ORCLPDB01> column seq for 99
USER01@ORCLPDB01> column item for a10
USER01@ORCLPDB01> column rt for a10
USER01@ORCLPDB01> column rest for a10
USER01@ORCLPDB01> column cnt for 99

GRP SEQ ITEM       RT         REST       CNT
--- --- ---------- ---------- ---------- ---
  1   1 a          a          bcd          4
  1   2 b          b          acd          4
  1   3 c          c          abd          4
  1   4 d          d          abc          4
  2   1 b          a,c        bd           3
  2   1 a          a,b        cd           3
  2   1 c          a,d        bc           3
  2   2 b          b,c        ad           3
  2   2 a          b,a        cd           3
  2   2 c          b,d        ac           3
  2   3 a          c,a        bd           3
  2   3 b          c,b        ad           3
  2   3 c          c,d        ab           3
  2   4 a          d,a        bc           3
  2   4 b          d,b        ac           3
  2   4 c          d,c        ab           3
  3   1 a          a,d,b      c            6
  3   1 b          a,b,d      c            6
  3   1 a          a,b,c      d            6
  3   1 b          a,c,d      b            6
  3   1 a          a,c,b      d            6
  3   1 b          a,d,c      b            6
  3   2 a          b,d,a      c            6
  3   2 b          b,a,d      c            6
  3   2 a          b,a,c      d            6
  3   2 b          b,c,d      a            6
  3   2 a          b,c,a      d            6
  3   2 b          b,d,c      a            6
  3   3 a          c,a,b      d            6
  3   3 b          c,a,d      b            6
  3   3 a          c,b,a      d            6
  3   3 b          c,b,d      a            6
  3   3 a          c,d,a      b            6
  3   3 b          c,d,b      a            6
  3   4 a          d,a,b      c            6
  3   4 b          d,a,c      b            6
  3   4 a          d,b,a      c            6
  3   4 b          d,b,c      a            6
  3   4 a          d,c,a      b            6
  3   4 b          d,c,b      a            6
  4   1 a          a,c,b,d                 6
  4   1 a          a,c,d,b                 6
  4   1 a          a,b,c,d                 6
  4   1 a          a,b,d,c                 6
  4   1 a          a,d,b,c                 6
  4   1 a          a,d,c,b                 6
  4   2 a          b,c,a,d                 6
  4   2 a          b,c,d,a                 6
  4   2 a          b,a,c,d                 6
  4   2 a          b,a,d,c                 6
  4   2 a          b,d,a,c                 6
  4   2 a          b,d,c,a                 6
  4   3 a          c,a,b,d                 6
  4   3 a          c,a,d,b                 6
  4   3 a          c,b,a,d                 6
  4   3 a          c,b,d,a                 6
  4   3 a          c,d,a,b                 6
  4   3 a          c,d,b,a                 6
  4   4 a          d,a,b,c                 6
  4   4 a          d,a,c,b                 6
  4   4 a          d,b,a,c                 6
  4   4 a          d,b,c,a                 6
  4   4 a          d,c,a,b                 6
  4   4 a          d,c,b,a                 6

64行が選択されました。

経過: 00:00:00.04

```

番号振り直した。

```sql
with rec(grp,seq,item,rt,rest) as(
    select
        1 as grp
        ,s1.seq
        ,s1.item
        ,s1.item
        ,(select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2)
    from test_tbl s1
    union all
    select
        s0.grp+1
        ,s0.seq
        ,s1.item
        ,s0.rt||','||substr(s2.rest,s1.seq,1)
        ,replace(s2.rest,substr(s2.rest,s1.seq,1),'')
    from rec s0
    ,test_tbl s1
    ,lateral(
        select listagg(s2.item) within group (order by s2.seq) as rest
        from test_tbl s2
        where not exists(select 1 from table(split(s0.rt))s3 where s2.item = s3.column_value ) )s2
    where
        s0.rest=s2.rest
    and s1.seq <=length(s0.rest)
)select
    grp
    , seq as subgrp
    ,row_number()over(partition by grp,seq order by item) as seq
    , item
    , rt
    , rest
    , count(*) over(partition by grp)  as cnt
from rec
;
```

```bash
GRP        SUBGRP SEQ ITEM       RT         REST       CNT
--- ------------- --- ---------- ---------- ---------- ---
  1             1   1 a          a          bcd          4
  1             2   1 b          b          acd          4
  1             3   1 c          c          abd          4
  1             4   1 d          d          abc          4
  2             1   1 a          a,b        cd          12
  2             1   2 b          a,c        bd          12
  2             1   3 c          a,d        bc          12
  2             2   1 a          b,a        cd          12
  2             2   2 b          b,c        ad          12
  2             2   3 c          b,d        ac          12
  2             3   1 a          c,a        bd          12
  2             3   2 b          c,b        ad          12
  2             3   3 c          c,d        ab          12
  2             4   1 a          d,a        bc          12
  2             4   2 b          d,b        ac          12
  2             4   3 c          d,c        ab          12
  3             1   1 a          a,b,c      d           24
  3             1   2 a          a,d,b      c           24
  3             1   3 a          a,c,b      d           24
  3             1   4 b          a,c,d      b           24
  3             1   5 b          a,b,d      c           24
  3             1   6 b          a,d,c      b           24
  3             2   1 a          b,a,c      d           24
  3             2   2 a          b,c,a      d           24
  3             2   3 a          b,d,a      c           24
  3             2   4 b          b,c,d      a           24
  3             2   5 b          b,d,c      a           24
  3             2   6 b          b,a,d      c           24
  3             3   1 a          c,d,a      b           24
  3             3   2 a          c,a,b      d           24
  3             3   3 a          c,b,a      d           24
  3             3   4 b          c,a,d      b           24
  3             3   5 b          c,b,d      a           24
  3             3   6 b          c,d,b      a           24
  3             4   1 a          d,c,a      b           24
  3             4   2 a          d,a,b      c           24
  3             4   3 a          d,b,a      c           24
  3             4   4 b          d,b,c      a           24
  3             4   5 b          d,a,c      b           24
  3             4   6 b          d,c,b      a           24
  4             1   1 a          a,b,c,d                24
  4             1   2 a          a,b,d,c                24
  4             1   3 a          a,c,b,d                24
  4             1   4 a          a,c,d,b                24
  4             1   5 a          a,d,b,c                24
  4             1   6 a          a,d,c,b                24
  4             2   1 a          b,a,d,c                24
  4             2   2 a          b,a,c,d                24
  4             2   3 a          b,c,a,d                24
  4             2   4 a          b,d,c,a                24
  4             2   5 a          b,d,a,c                24
  4             2   6 a          b,c,d,a                24
  4             3   1 a          c,a,b,d                24
  4             3   2 a          c,a,d,b                24
  4             3   3 a          c,b,a,d                24
  4             3   4 a          c,b,d,a                24
  4             3   5 a          c,d,a,b                24
  4             3   6 a          c,d,b,a                24
  4             4   1 a          d,a,b,c                24
  4             4   2 a          d,a,c,b                24
  4             4   3 a          d,c,a,b                24
  4             4   4 a          d,b,c,a                24
  4             4   5 a          d,b,a,c                24
  4             4   6 a          d,c,b,a                24

64行が選択されました。

経過: 00:00:00.03

```

デバッグ用追記。

collectitonうまくできないよ。。

```sql
with rec(grp,seq,item,rt,rest,rest_cnt,pre_rest,pre_rest_cnt) as(
    select
        1 as grp
        ,s1.seq
        ,s1.item
        ,s1.item
        ,(select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2)
        ,length((select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2))
        ,(select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2)
        ,length((select listagg(s2.item) within group (order by s2.seq) from lateral(select s2.* from test_tbl s2 where s2.seq <> s1.seq)s2))
    from test_tbl s1
    union all
    select
        s0.grp+1
        ,s0.seq
        ,s1.item
        ,s0.rt||','||substr(s2.rest,s1.seq,1)
        ,replace(s2.rest,substr(s2.rest,s1.seq,1),'')
        ,length(replace(s2.rest,substr(s2.rest,s1.seq,1),''))
        ,s2.rest
        ,s2.rest_cnt
        from rec s0
    ,test_tbl s1
    ,lateral(
        select listagg(s2.item) within group (order by s2.seq) as rest,length(listagg(s2.item) within group (order by s2.seq)) as rest_cnt
        from test_tbl s2
        where not exists(select 1 from table(split(s0.rt))s3 where s2.item = s3.column_value ) )s2
    where
        s0.rest=s2.rest
    and s1.seq <=length(s0.rest)
)select
    grp
    , seq as subgrp
    , row_number()over(partition by grp,seq order by item) as seq
    , item
    , rt
    , rest
    , rest_cnt
    , pre_rest
    , pre_rest_cnt
    , count(*) over(partition by grp)  as cnt
from rec
;
```

```bash
USER01@ORCLPDB01> column pre_rest_cnt for 99
USER01@ORCLPDB01> column pre_rest for a10
USER01@ORCLPDB01> column rest_cnt for 99

GRP        SUBGRP SEQ ITEM       RT         REST       REST_CNT PRE_REST   PRE_REST_CNT CNT
--- ------------- --- ---------- ---------- ---------- -------- ---------- ------------ ---
  1             1   1 a          a          bcd               3 bcd                   3   4
  1             2   1 b          b          acd               3 acd                   3   4
  1             3   1 c          c          abd               3 abd                   3   4
  1             4   1 d          d          abc               3 abc                   3   4
  2             1   1 a          a,b        cd                2 bcd                   3  12
  2             1   2 b          a,c        bd                2 bcd                   3  12
  2             1   3 c          a,d        bc                2 bcd                   3  12
  2             2   1 a          b,a        cd                2 acd                   3  12
  2             2   2 b          b,c        ad                2 acd                   3  12
  2             2   3 c          b,d        ac                2 acd                   3  12
  2             3   1 a          c,a        bd                2 abd                   3  12
  2             3   2 b          c,b        ad                2 abd                   3  12
  2             3   3 c          c,d        ab                2 abd                   3  12
  2             4   1 a          d,a        bc                2 abc                   3  12
  2             4   2 b          d,b        ac                2 abc                   3  12
  2             4   3 c          d,c        ab                2 abc                   3  12
  3             1   1 a          a,b,c      d                 1 cd                    2  24
  3             1   2 a          a,d,b      c                 1 bc                    2  24
  3             1   3 a          a,c,b      d                 1 bd                    2  24
  3             1   4 b          a,c,d      b                 1 bd                    2  24
  3             1   5 b          a,b,d      c                 1 cd                    2  24
  3             1   6 b          a,d,c      b                 1 bc                    2  24
  3             2   1 a          b,a,c      d                 1 cd                    2  24
  3             2   2 a          b,c,a      d                 1 ad                    2  24
  3             2   3 a          b,d,a      c                 1 ac                    2  24
  3             2   4 b          b,c,d      a                 1 ad                    2  24
  3             2   5 b          b,d,c      a                 1 ac                    2  24
  3             2   6 b          b,a,d      c                 1 cd                    2  24
  3             3   1 a          c,d,a      b                 1 ab                    2  24
  3             3   2 a          c,a,b      d                 1 bd                    2  24
  3             3   3 a          c,b,a      d                 1 ad                    2  24
  3             3   4 b          c,a,d      b                 1 bd                    2  24
  3             3   5 b          c,b,d      a                 1 ad                    2  24
  3             3   6 b          c,d,b      a                 1 ab                    2  24
  3             4   1 a          d,c,a      b                 1 ab                    2  24
  3             4   2 a          d,a,b      c                 1 bc                    2  24
  3             4   3 a          d,b,a      c                 1 ac                    2  24
  3             4   4 b          d,b,c      a                 1 ac                    2  24
  3             4   5 b          d,a,c      b                 1 bc                    2  24
  3             4   6 b          d,c,b      a                 1 ab                    2  24
  4             1   1 a          a,b,c,d                        d                     1  24
  4             1   2 a          a,b,d,c                        c                     1  24
  4             1   3 a          a,c,b,d                        d                     1  24
  4             1   4 a          a,c,d,b                        b                     1  24
  4             1   5 a          a,d,b,c                        c                     1  24
  4             1   6 a          a,d,c,b                        b                     1  24
  4             2   1 a          b,a,d,c                        c                     1  24
  4             2   2 a          b,a,c,d                        d                     1  24
  4             2   3 a          b,c,a,d                        d                     1  24
  4             2   4 a          b,d,c,a                        a                     1  24
  4             2   5 a          b,d,a,c                        c                     1  24
  4             2   6 a          b,c,d,a                        a                     1  24
  4             3   1 a          c,a,b,d                        d                     1  24
  4             3   2 a          c,a,d,b                        b                     1  24
  4             3   3 a          c,b,a,d                        d                     1  24
  4             3   4 a          c,b,d,a                        a                     1  24
  4             3   5 a          c,d,a,b                        b                     1  24
  4             3   6 a          c,d,b,a                        a                     1  24
  4             4   1 a          d,a,b,c                        c                     1  24
  4             4   2 a          d,a,c,b                        b                     1  24
  4             4   3 a          d,c,a,b                        b                     1  24
  4             4   4 a          d,b,c,a                        a                     1  24
  4             4   5 a          d,b,a,c                        c                     1  24
  4             4   6 a          d,c,b,a                        a                     1  24

64行が選択されました。

経過: 00:00:00.01
```