この記事は約61分14秒で読むことができます。

connect by の練習

参考文献

訪問経路の列挙  
 
第3回 AVL木で木構造を学ぼう  
 
データ構造の選択次第で天国と地獄の差 (3/3)  
 
グラフ理論  
 
知れば天国、知らねば地獄――「探索」虎の巻  
 

作成したデータはこちら。grpは処理単位をイメージしています。rnは処理単位の中での処理順位をイメージしています。seqはpkのイメージです。処理単位の中での。

コード表示

WITH sub AS (
	SELECT
		ROW_NUMBER() OVER(
			PARTITION BY mod(level, 2), mod(level, 3)
			ORDER BY
				mod(level, 4)
		) AS seq
		, mod(level, 2) AS grp1
		, mod(level, 3) AS grp2
		, mod(level, 4) AS rn
		, level * 10 AS qty
	FROM
		dual
	CONNECT BY
		level <= 20
) SELECT
	grp1
	, grp2
	, rn
	, seq
	, qty
  FROM
	sub
ORDER BY
	1
	, 2
	, 3
	, 4;

         GRP1          GRP2            RN           SEQ           QTY
------------- ------------- ------------- ------------- -------------
            0             0             0             1           120
            0             0             2             2           180
            0             0             2             3            60
            0             1             0             1           160
            0             1             0             2            40
            0             1             2             3           100
            0             2             0             1           200
            0             2             0             2            80
            0             2             2             3            20
            0             2             2             4           140
            1             0             1             1            90
            1             0             3             2           150
            1             0             3             3            30
            1             1             1             1            10
            1             1             1             2           130
            1             1             3             3           190
            1             1             3             4            70
            1             2             1             1           170
            1             2             1             2            50
            1             2             3             3           110

20 rows selected.

Elapsed: 00:00:00.00

作成したデータはこちら。grpは処理単位をイメージしています。rnは処理単位の中での処理順位をイメージしています。seqはpkのイメージです。処理単位の中での。処理単位の中で、seqが1のデータから並列に処理するイメージ。prior grp1 = grp1、prior grp2 = grp2で処理単位はすみ分ける。リニア処理(処理単位でシーケンシャルに処理するイメージ、大体は処理順をrow_numberとかで明記してやるといい感じになる。)をするために、PRIOR seq = seq – 1を指定。sys_connect_by_pathで結果を蓄積されていく様子をデバッグする。

コード表示

WITH sub AS (
	SELECT
		ROW_NUMBER() OVER(
			PARTITION BY mod(level, 2), mod(level, 3)
			ORDER BY
				mod(level, 4)
		) AS seq
		, mod(level, 2) AS grp1
		, mod(level, 3) AS grp2
		, mod(level, 4) AS rn
		, level * 10 AS qty
	FROM
		dual
	CONNECT BY
		level <= 20
) SELECT
	grp1
	, grp2
	, rn
	, seq
	, qty
	, sys_connect_by_path(TO_CHAR(qty), ',') AS path
  FROM
	sub
START WITH
	seq = 1
CONNECT BY PRIOR grp1 = grp1
           AND PRIOR grp2 = grp2
           AND PRIOR seq = seq - 1;


col path for a100

         GRP1          GRP2            RN           SEQ           QTY PATH
------------- ------------- ------------- ------------- ------------- ----------------------------------------------------------------------------------------------------
            0             0             0             1           120 ,120
            0             0             2             2           180 ,120,180
            0             0             2             3            60 ,120,180,60
            0             1             0             1           160 ,160
            0             1             0             2            40 ,160,40
            0             1             2             3           100 ,160,40,100
            0             2             0             1           200 ,200
            0             2             0             2            80 ,200,80
            0             2             2             3            20 ,200,80,20
            0             2             2             4           140 ,200,80,20,140
            1             0             1             1            90 ,90
            1             0             3             2           150 ,90,150
            1             0             3             3            30 ,90,150,30
            1             1             1             1            10 ,10
            1             1             1             2           130 ,10,130
            1             1             3             3           190 ,10,130,190
            1             1             3             4            70 ,10,130,190,70
            1             2             1             1           170 ,170
            1             2             1             2            50 ,170,50
            1             2             3             3           110 ,170,50,110

20 rows selected.

Elapsed: 00:00:00.02

訪問経路の列挙

START WITH depart IN (‘A’,’B’,’C’)とかで処理起点を明記。書かなければ、すべてのレコードが処理起点となり、connect by にしていした条件に従い、再帰する。

コード表示

DROP TABLE test____$_____ PURGE;
CREATE TABLE test____$_____ AS
WITH src AS (
    SELECT
        LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <= 26
	ORDER BY
		dbms_random.VALUE
)
SELECT
	CHR(64 + decode(MOD(s1.rn, 26), 0, 26, MOD(s1.rn, 26) ) ) AS depart
	,MIN(CHR(64 + decode(MOD(s2.rn, 26), 0, 26, MOD(s2.rn, 26) ) )) KEEP (DENSE_RANK FIRST ORDER BY dbms_random.VALUE) AS arrival
FROM
	src s1
	, src s2
GROUP BY
	CHR(64 + decode(MOD(s1.rn, 26), 0, 26, MOD(s1.rn, 26) ) )
;

AINE@pdb1> col depart for a20
AINE@pdb1> col arrival for a20
AINE@pdb1> select * from test____$_____;

DEPART               ARRIVAL
-------------------- --------------------
A                    A
B                    Q
C                    S
D                    K
E                    F
F                    R
G                    T
H                    U
I                    W
J                    U
K                    G
L                    F
M                    Y
N                    D
O                    P
P                    D
Q                    C
R                    Y
S                    G
T                    B
U                    C
V                    S
W                    Z
X                    K
Y                    N
Z                    N

26 rows selected.

Elapsed: 00:00:00.02

コード表示

WITH sub AS(
SELECT
	DENSE_RANK() OVER (ORDER BY CONNECT_BY_ROOT depart) AS grp
	,LEVEL AS rn
	, depart
	, arrival
	, PRIOR arrival AS pre_arrival
	, CONNECT_BY_ISLEAF AS isleaf
	, CONNECT_BY_ROOT arrival AS root_arrival_val
	, CONNECT_BY_ROOT depart AS root_depart_val
	, sys_connect_by_path(depart, ',') AS PATH
	, CONNECT_BY_ISCYCLE AS iscycle
FROM
	test____$_____
START WITH
	depart IN ('A','B','C')
CONNECT BY NOCYCLE
	depart = PRIOR arrival
ORDER BY
	LEVEL
	, PATH
)
SELECT
	s1.*
	,CASE
		WHEN NOT EXISTS(
					SELECT
						1
					FROM
						sub s2
					WHERE
						s1.root_depart_val = s2.root_depart_val
					AND s1.rn > s2.rn
					)THEN 1
		ELSE 0
	END AS root_flg
FROM
	sub s1
ORDER BY
	s1.grp
	,s1.rn
;

AINE@pdb1> col path for a50

          GRP            RN DEPA ARRI PRE_        ISLEAF ROOT ROOT PATH                                                     ISCYCLE      ROOT_FLG
------------- ------------- ---- ---- ---- ------------- ---- ---- -------------------------------------------------- ------------- -------------
            1             1 A    A                     1 A    A    ,A                                                             1             1
            2             1 B    Q                     0 Q    B    ,B                                                             0             1
            2             2 Q    C    Q                0 Q    B    ,B,Q                                                           0             0
            2             3 C    S    C                0 Q    B    ,B,Q,C                                                         0             0
            2             4 S    G    S                0 Q    B    ,B,Q,C,S                                                       0             0
            2             5 G    T    G                0 Q    B    ,B,Q,C,S,G                                                     0             0
            2             6 T    B    T                1 Q    B    ,B,Q,C,S,G,T                                                   1             0
            3             1 C    S                     0 S    C    ,C                                                             0             1
            3             2 S    G    S                0 S    C    ,C,S                                                           0             0
            3             3 G    T    G                0 S    C    ,C,S,G                                                         0             0
            3             4 T    B    T                0 S    C    ,C,S,G,T                                                       0             0
            3             5 B    Q    B                0 S    C    ,C,S,G,T,B                                                     0             0
            3             6 Q    C    Q                1 S    C    ,C,S,G,T,B,Q                                                   1             0

13 rows selected.

Elapsed: 00:00:00.01

たとえば、Gまでいく経路はとなったら、where prior arrival = ‘G’と指定すればいけると思う。出発地点を気にせずに。また、Kは経由せずにみたいなときどう書けばいいのだろう。。あるいはK、Yを経由して、今回の例だと、Gまでいく経路は24通り。

方針としてはカンマリストを行展開して検索がいいぽい。いつも思いますけど、書かかれているsqlのレベルが圧倒的で、素敵過ぎます。何度でも読み返したくなる記事ばかりです。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法  
 

コード表示

WITH sub AS(
SELECT
	DENSE_RANK() OVER (ORDER BY CONNECT_BY_ROOT depart) AS grp
	,LEVEL AS rn
	, depart
	, arrival
	, PRIOR arrival AS pre_arrival
	, CONNECT_BY_ISLEAF AS isleaf
	, CONNECT_BY_ROOT arrival AS root_arrival_val
	, CONNECT_BY_ROOT depart AS root_depart_val
	, sys_connect_by_path(depart, ',') AS PATH
	, CONNECT_BY_ISCYCLE AS iscycle
FROM
	test____$_____
WHERE
	prior arrival = 'G'
CONNECT BY NOCYCLE
	depart = PRIOR arrival
ORDER BY
	LEVEL
	, PATH
)
SELECT
	s1.*
	,CASE
		WHEN NOT EXISTS(
					SELECT
						1
					FROM
						sub s2
					WHERE
						s1.root_depart_val = s2.root_depart_val
					AND s1.rn > s2.rn
					)THEN 1
		ELSE 0
	END AS root_flg
FROM
	sub s1
ORDER BY
	s1.grp
	,s1.rn
;

AINE@pdb1> col path for a50

          GRP            RN DEPA ARRI PRE_        ISLEAF ROOT ROOT PATH                                                     ISCYCLE      ROOT_FLG
------------- ------------- ---- ---- ---- ------------- ---- ---- -------------------------------------------------- ------------- -------------
            1             5 G    T    G                0 Q    B    ,B,Q,C,S,G                                                     0             1
            2             3 G    T    G                0 S    C    ,C,S,G                                                         0             1
            3             3 G    T    G                0 K    D    ,D,K,G                                                         0             1
            4             8 G    T    G                0 F    E    ,E,F,R,Y,N,D,K,G                                               0             1
            5             7 G    T    G                0 R    F    ,F,R,Y,N,D,K,G                                                 0             1
            6             5 G    T    G                0 U    H    ,H,U,C,S,G                                                     0             1
            7             7 G    T    G                0 W    I    ,I,W,Z,N,D,K,G                                                 0             1
            8             5 G    T    G                0 U    J    ,J,U,C,S,G                                                     0             1
            9             2 G    T    G                0 G    K    ,K,G                                                           0             1
           10             8 G    T    G                0 F    L    ,L,F,R,Y,N,D,K,G                                               0             1
           11             6 G    T    G                0 Y    M    ,M,Y,N,D,K,G                                                   0             1
           12             4 G    T    G                0 D    N    ,N,D,K,G                                                       0             1
           13             5 G    T    G                0 P    O    ,O,P,D,K,G                                                     0             1
           14             4 G    T    G                0 D    P    ,P,D,K,G                                                       0             1
           15             4 G    T    G                0 C    Q    ,Q,C,S,G                                                       0             1
           16             6 G    T    G                0 Y    R    ,R,Y,N,D,K,G                                                   0             1
           17             2 G    T    G                0 G    S    ,S,G                                                           0             1
           18             6 G    T    G                1 B    T    ,T,B,Q,C,S,G                                                   1             1
           19             4 G    T    G                0 C    U    ,U,C,S,G                                                       0             1
           20             3 G    T    G                0 S    V    ,V,S,G                                                         0             1
           21             6 G    T    G                0 Z    W    ,W,Z,N,D,K,G                                                   0             1
           22             3 G    T    G                0 K    X    ,X,K,G                                                         0             1
           23             5 G    T    G                0 N    Y    ,Y,N,D,K,G                                                     0             1
           24             5 G    T    G                0 N    Z    ,Z,N,D,K,G                                                     0             1

24 rows selected.

Elapsed: 00:00:00.01

リスト行展開ファンクション

せっかくなので、作られ方をトレースしたいと思います。

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法  
 

まずはリスト作成

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select * from sub;

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select * from sub;

LIZ
----------------------------------------------------------------------------------------------------
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1>

カンマ含む長さとカンマ含まない長さを求める。求めてカンマ要素の個数を求める

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	length(liz) as oncnm
	,length(replace(liz,',','')) as offcnm
	,length(liz) - length(replace(liz,',','')) as diff
	,length(liz) - length(replace(liz,',','')) + 1 as elecnt
from
	sub
;

AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select  length(liz) as oncnm  ,length(replace(liz,',','')) as offcnm  ,length(liz) - length(replace(liz,',','')) as diff  ,length(liz) - length(replace(liz,',','')) + 1 as elecnt from  sub  ;

        ONCNM        OFFCNM          DIFF        ELECNT
------------- ------------- ------------- -------------
           51            26            25            26

1 row selected.

Elapsed: 00:00:00.00

要素分、行複写 with connect by

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	s1.*
	,level
from
	sub s1
connect by
	level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1
;

AINE@pdb1> col liz for a100
AINE@pdb1> with sub as( select listagg(depart,',')within group (order by rownum) as liz from test____$_____ ) select s1.* ,level from sub s1 connect by level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1 ;

LIZ                                                                                                          LEVEL
---------------------------------------------------------------------------------------------------- -------------
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              1
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              2
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              3
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              4
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              5
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              6
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              7
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              8
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                              9
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             10
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             11
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             12
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             13
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             14
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             15
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             16
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             17
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             18
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             19
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             20
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             21
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             22
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             23
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             24
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             25
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z                                                             26

26 rows selected.

Elapsed: 00:00:00.00


instrとsubstrでバラス。instrって見つからないと0を返却するから、nullifで0だったら、nullにしてそのあと、nvlで置き換えしてるのがミソだとおもう。非再帰項と再帰項は条件分岐点となりうるのが多いと思われるので、decodeなりcaseでいい感じにする。最後の要素切り取るためにnvlで置き換える値は残り全部切り取るぐらいの気持ちで、大きい値を指定する。4000とか。

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)
select
	s1.*
	,substr(liz,1,instr(liz,',',1,1) - 1) as non_rec
	,substr(liz,instr(liz,',',1,1) + 1,instr(liz,',',1,2)- instr(liz,',',1,1)-1) as on_rec_1
	,substr(liz,instr(liz,',',1,2) + 1,instr(liz,',',1,3) - instr(liz,',',1,2)-1) as on_rec_2
	,substr(liz,instr(liz,',',1,3) + 1,instr(liz,',',1,4) - instr(liz,',',1,3)-1) as on_rec_3
	,'--------' as tbc
	,substr(liz,instr(liz,',',1,24) + 1,instr(liz,',',1,25) - instr(liz,',',1,24)-1) as on_rec_24
	,substr(liz,instr(liz,',',1,25) + 1,instr(liz,',',1,26) - instr(liz,',',1,25)-1) as on_rec_25
	,substr(liz,instr(liz,',',1,25) + 1,nvl(nullif(instr(liz,',',1,26),0),4000) - instr(liz,',',1,25)-1) as on_rec_25
	,instr(liz,',',1,26)
	,level
from
	sub s1
connect by
	level <= length(s1.liz) - length(replace(s1.liz,',','')) + 1
;


規則性のあるリテラルをlevelで置き換える。

コード表示

WITH sub AS(
SELECT LISTAGG(depart,',')WITHIN GROUP (ORDER BY ROWNUM) AS liz FROM test____$_____
)
SELECT
	decode(LEVEL,1,substr(liz,1,instr(liz,',',1,LEVEL) - 1),substr(liz,instr(liz,',',1,LEVEL -1 ) + 1,nvl(nullif(instr(liz,',',1,LEVEL),0),4000)- instr(liz,',',1,LEVEL-1)-1)) AS ele
FROM
	sub s1
CONNECT BY
	LEVEL <= LENGTH(s1.liz) - LENGTH(REPLACE(s1.liz,',','')) + 1
;

カンマを与えたら、コレクション返却するようにする。collect関数、cast,コレクションタイプ宣言する。

COLLECT  
 

コンテキスト・スイッチについてplsqlからsql、sqlからplsql。参考。

BULK COLLECTとFORALLによるバルク処理  
 
コード表示

CREATE OR replace TYPE liz AS TABLE OF VARCHAR2(4000);
/

WITH sub AS(
SELECT LISTAGG(depart,',')WITHIN GROUP (ORDER BY ROWNUM) AS liz FROM test____$_____
)
SELECT
	CAST(COLLECT(
		decode(LEVEL,1,substr(liz,1,instr(liz,',',1,LEVEL) - 1),substr(liz,instr(liz,',',1,LEVEL -1 ) + 1,nvl(nullif(instr(liz,',',1,LEVEL),0),4000)- instr(liz,',',1,LEVEL-1)-1))
	)AS liz) as ele
FROM
	sub s1
CONNECT BY
	LEVEL <= LENGTH(s1.liz) - LENGTH(REPLACE(s1.liz,',','')) + 1
;

ファンクション化

コード表示

CREATE OR replace TYPE liz AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION segregate(p_liz VARCHAR2)
RETURN liz
AS v_liz liz;
BEGIN
WITH sub AS(
SELECT
	LEVEL AS rn
	,p_liz AS liz
FROM
	dual
CONNECT BY
	LEVEL <= LENGTH(p_liz) - LENGTH(REPLACE(p_liz,',','')) + 1
)
SELECT
	CAST(COLLECT(
		decode(rn,1,substr(liz,1,instr(liz,',',1,rn) - 1),substr(liz,instr(liz,',',1,rn -1 ) + 1,nvl(nullif(instr(liz,',',1,rn),0),4000)- instr(liz,',',1,rn-1)-1))
	)AS liz) AS ele
INTO v_liz
FROM
	sub;
RETURN v_liz;
END;
/

ファンクションのよびだし

コード表示

with sub as(
select listagg(depart,',')within group (order by rownum) as liz from test____$_____
)select * from (select * from sub),table(segregate(liz));

Leave a Reply

Your email address will not be published. Required fields are marked *