まえがき

マイナビ出版のビッグデータ分析・活用のためのSQLレシピp400あたりに
アソシーエーション分析という言葉がありました。

以下引用です。
『本来のアソシエーション分析は「商品の組み合わせ（A,B,C,・・・）を買った人のn%は、商品の組み合わせ（X,Y,Z,・・・）を買う」など、商品の組み合わせに関するルールも発見できる汎用的なアルゴリズムですが、完全なアソシエーション分析には再帰的な繰り返し処理が必要となるため、、』

そこで、ひとまず再帰的に商品の組み合わせを求めることができないかと思い、sql書いてみました。

【データ投入】

init.sql

DROP TABLE purchase_detail_log;

CREATE TABLE purchase_detail_log(
    stamp       varchar(255)
  , logsession     varchar(255)
  , purchase_id integer
  , product_id  varchar(255)
);

INSERT ALL
INTO purchase_detail_log VALUES('2016-11-03 18:10', '989004ea',  1, 'D001')
INTO purchase_detail_log VALUES('2016-11-03 18:10', '989004ea',  1, 'D002')
INTO purchase_detail_log VALUES('2016-11-03 20:00', '47db0370',  2, 'D001')
INTO purchase_detail_log VALUES('2016-11-04 13:00', '1cf7678e',  3, 'D002')
INTO purchase_detail_log VALUES('2016-11-04 15:00', '5eb2e107',  4, 'A001')
INTO purchase_detail_log VALUES('2016-11-04 15:00', '5eb2e107',  4, 'A002')
INTO purchase_detail_log VALUES('2016-11-04 16:00', 'fe05e1d8',  5, 'A001')
INTO purchase_detail_log VALUES('2016-11-04 16:00', 'fe05e1d8',  5, 'A003')
INTO purchase_detail_log VALUES('2016-11-04 17:00', '87b5725f',  6, 'A001')
INTO purchase_detail_log VALUES('2016-11-04 17:00', '87b5725f',  6, 'A003')
INTO purchase_detail_log VALUES('2016-11-04 17:00', '87b5725f',  6, 'A004')
INTO purchase_detail_log VALUES('2016-11-04 18:00', '5d5b0997',  7, 'A005')
INTO purchase_detail_log VALUES('2016-11-04 18:00', '5d5b0997',  7, 'A006')
INTO purchase_detail_log VALUES('2016-11-04 19:00', '111f2996',  8, 'A002')
INTO purchase_detail_log VALUES('2016-11-04 19:00', '111f2996',  8, 'A003')
INTO purchase_detail_log VALUES('2016-11-04 20:00', '3efe001c',  9, 'A001')
INTO purchase_detail_log VALUES('2016-11-04 20:00', '3efe001c',  9, 'A003')
INTO purchase_detail_log VALUES('2016-11-04 21:00', '9afaf87c', 10, 'D001')
INTO purchase_detail_log VALUES('2016-11-04 21:00', '9afaf87c', 10, 'D003')
INTO purchase_detail_log VALUES('2016-11-04 22:00', 'd45ec190', 11, 'D001')
INTO purchase_detail_log VALUES('2016-11-04 22:00', 'd45ec190', 11, 'D002')
INTO purchase_detail_log VALUES('2016-11-04 23:00', '36dd0df7', 12, 'A002')
INTO purchase_detail_log VALUES('2016-11-04 23:00', '36dd0df7', 12, 'A003')
INTO purchase_detail_log VALUES('2016-11-04 23:00', '36dd0df7', 12, 'A004')
INTO purchase_detail_log VALUES('2016-11-05 15:00', 'cabf98e8', 13, 'A002')
INTO purchase_detail_log VALUES('2016-11-05 15:00', 'cabf98e8', 13, 'A004')
INTO purchase_detail_log VALUES('2016-11-05 16:00', 'f3b47933', 14, 'A005')
select * from dual
;

【商品数取得】

getItem.sql

select distinct
	PRODUCT_ID
from
	PURCHASE_DETAIL_LOG
order by
	PRODUCT_ID
;

【商品数取得結果】

【やりたいこと】
９つの商品が存在するので、
９つの中から１つ選ぶ組み合わせ
９つの中から２つ選ぶ組み合わせ
９つの中から３つ選ぶ組み合わせ
９つの中から４つ選ぶ組み合わせ
９つの中から５つ選ぶ組み合わせ
９つの中から６つ選ぶ組み合わせ
９つの中から７つ選ぶ組み合わせ
９つの中から８つ選ぶ組み合わせ
９つの中から９つ選ぶ組み合わせ
のすべての事象を一覧化したい

メイン処理

main.sql

select
	sub.*
from
	(
	with rec (
			flg
			,prod1
			,prod2
			,prod3
			,prod4
			,prod5
			,prod6
			,prod7
			,prod8
			,prod9
			)as(
			select distinct
				1
				,product_id
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
				,nvl(null,' ')
			from
				purchase_detail_log					
			union all
			select
				rec.flg + 1
				,rec.prod1
				,case when rec.flg + 1 = 2 then src.product_id else nvl(rec.prod2,' ') end
				,case when rec.flg + 1 = 3 then src.product_id else nvl(rec.prod3,' ') end
				,case when rec.flg + 1 = 4 then src.product_id else nvl(rec.prod4,' ') end
				,case when rec.flg + 1 = 5 then src.product_id else nvl(rec.prod5,' ') end
				,case when rec.flg + 1 = 6 then src.product_id else nvl(rec.prod6,' ') end
				,case when rec.flg + 1 = 7 then src.product_id else nvl(rec.prod7,' ') end
				,case when rec.flg + 1 = 8 then src.product_id else nvl(rec.prod8,' ') end
				,case when rec.flg + 1 = 9 then src.product_id else nvl(rec.prod9,' ') end
			from
				rec
					inner join
								(
								select distinct
									product_id
								from
									purchase_detail_log					
								) src
						on
							(--TorU
							case
								when--１回目のループ（９つの中から２つ選ぶの組み合わせ）
									rec.flg = 1 then rec.prod1
								when--２回目のループ（９つの中から３つ選ぶの組み合わせ）
									rec.flg = 2
								and rec.prod1 < rec.prod2 then rec.prod2
								when--３回目のループ（９つの中から４つ選ぶの組み合わせ）
									rec.flg = 3
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3 then rec.prod3
								when--４回目のループ（９つの中から５つ選ぶの組み合わせ）
									rec.flg = 4
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3
								and rec.prod3 < rec.prod4 then rec.prod4
								when--５回目のループ（９つの中から６つ選ぶの組み合わせ）
									rec.flg = 5
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3
								and rec.prod3 < rec.prod4
								and rec.prod4 < rec.prod5 then rec.prod5
								when--６回目のループ（９つの中から７つ選ぶの組み合わせ）
									rec.flg = 6
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3
								and rec.prod3 < rec.prod4
								and rec.prod4 < rec.prod5
								and rec.prod5 < rec.prod6 then rec.prod6
								when--７回目のループ（９つの中から８つ選ぶの組み合わせ）
									rec.flg = 7
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3
								and rec.prod3 < rec.prod4
								and rec.prod4 < rec.prod5
								and rec.prod5 < rec.prod6
								and rec.prod6 < rec.prod7 then rec.prod7
								when--８回目のループ（９つの中から９つ選ぶ組み合わせ）
									rec.flg = 8
								and rec.prod1 < rec.prod2
								and rec.prod2 < rec.prod3
								and rec.prod3 < rec.prod4
								and rec.prod4 < rec.prod5
								and rec.prod5 < rec.prod6
								and rec.prod6 < rec.prod7
								and rec.prod7 < rec.prod8 then rec.prod8
								else null--１０回目のループはunknownで評価対象外とする
							end < src.product_id
							)
			)
	select
		flg
		,row_number() over (
							partition BY flg
							order by
								prod1
								,prod2
								,prod3
								,prod4
								,prod5
								,prod6
								,prod7
								,prod8
								,prod9
							) as rn
		,prod1
		,prod2
		,prod3
		,prod4
		,prod5
		,prod6
		,prod7
		,prod8
		,prod9
	from
		rec
	) sub
order by
	sub.flg
	,sub.rn
;

【組み合わせ取得結果（一部抜粋）】

・
・
・

【計算式】
9C1 = 9
9C2 = 36
9C3 = 84
9C4 = 126
9C5 = 126
9C6 = 84
9C7 = 36
9C8 = 9
9C9 = 1

1 + (9 + 36 + 84 + 126) * 2
= 1 + 510
= 511

合ってそう！！

【振り返り】

商品数の数だけsqlメンテしないといけないところ。。

もっといい書き方あれば、共有していただきたいです。。

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

#20180906追記
@tlokweng さんが
スマートな組み合わせの求め方をご教示してくださったので、
追記したいと思います。

@tlokweng さん、ありがとうございます。

init.sql


drop table test_tbl;

create table test_tbl (id,item) as (
select 1 ,'A0001' from dual union all
select 2 ,'A0002' from dual union all
select 3 ,'A0003' from dual union all
select 4 ,'A0004' from dual union all
select 5 ,'A0005' from dual union all
select 6 ,'A0006' from dual union all
select 7 ,'A0007' from dual union all
select 8 ,'A0008' from dual union all
select 9 ,'A0009' from dual
)
;

【init.sqlの実行結果】

rec_with.sql


WITH recur (
	lv
	, target
	, list
) AS (
	SELECT
		1
		, id
		, item
	FROM
		test_tbl
	UNION ALL
	SELECT
		lv + 1--どのパターンの組み合わせかlv擬似列で表現してくださいました
		, id
		, list
		  || ','
		  || item
	FROM
		recur
		,test_tbl
	WHERE
		target < id
) SELECT
	rownum--行数をrownum擬似列で表現してくださいました
	, lv
	, list
  FROM
	recur
;

【rec_withの取得結果（一部抜粋）】

・
・
・

カンマ区切りを列持ちにばらしたいと思います。ばらしてみたくなった。。

conv_horizon.sql


select
	lv
	,cmb
	,nvl(substr(cmb, 1, CASE WHEN instr(cmb,',',1,1) <> 0 THEN instr(cmb,',',1,1) - 1 ELSE NULL END),'  ') AS s1
	,nvl(substr(cmb, instr(cmb,',',1,1) + 1, CASE WHEN instr(cmb,',',1,2) <> 0 THEN instr(cmb,',',1,2) - instr(cmb,',',1,1) - 1 ELSE NULL END),'  ') AS s2
	,nvl(substr(cmb, instr(cmb,',',1,2) + 1, CASE WHEN instr(cmb,',',1,3) <> 0 THEN instr(cmb,',',1,3) - instr(cmb,',',1,2) - 1 ELSE NULL END),'  ') AS s3
	,nvl(substr(cmb, instr(cmb,',',1,3) + 1, CASE WHEN instr(cmb,',',1,4) <> 0 THEN instr(cmb,',',1,4) - instr(cmb,',',1,3) - 1 ELSE NULL END),'  ') AS s4
	,nvl(substr(cmb, instr(cmb,',',1,4) + 1, CASE WHEN instr(cmb,',',1,5) <> 0 THEN instr(cmb,',',1,5) - instr(cmb,',',1,4) - 1 ELSE NULL END),'  ') AS s5
	,nvl(substr(cmb, instr(cmb,',',1,5) + 1, CASE WHEN instr(cmb,',',1,6) <> 0 THEN instr(cmb,',',1,6) - instr(cmb,',',1,5) - 1 ELSE NULL END),'  ') AS s6
	,nvl(substr(cmb, instr(cmb,',',1,6) + 1, CASE WHEN instr(cmb,',',1,7) <> 0 THEN instr(cmb,',',1,7) - instr(cmb,',',1,6) - 1 ELSE NULL END),'  ') AS s7
	,nvl(substr(cmb, instr(cmb,',',1,7) + 1, CASE WHEN instr(cmb,',',1,8) <> 0 THEN instr(cmb,',',1,8) - instr(cmb,',',1,7) - 1 ELSE NULL END),'  ') AS s8
	,nvl(substr(cmb, instr(cmb,',',1,8) + 1, CASE WHEN instr(cmb,',',1,9) <> 0 THEN instr(cmb,',',1,9) - instr(cmb,',',1,8) - 1 ELSE NULL END),'  ') AS s9
from
	(
		select
			lv
			,list || ',' as cmb
		from
			(
				WITH recur (
					lv
					, target
					, list
				) AS (
					SELECT
						1
						, id
						, item
					FROM
						test_tbl
					UNION ALL
					SELECT
						lv + 1
						, id
						, list
						  || ','
						  || item
					FROM
						recur
						,test_tbl
					WHERE
						target < id
				) SELECT
					lv
					, list
				  FROM
					recur
				) cmb_rec
			) cmb_tmp
;

【conv_horizon.sqlの取得結果】

・
・
・

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

#20180914追記

@youmil_rainさんからは
再帰なしでの組み合わせの出し方とビット演算をもちいたやり方をご教示いただきました。
ビット演算を用いた方は私自身の理解がまだ浅いので、今回は再帰なしの方を追記させていただきます。

@youmil_rainさん、ありがとうございます。

@tlokweng さんからは
collectionとtable()のスマートな使い方に加え、カンマ区切りをばらすやり方も
ご教示してくださいました。

@tlokweng さん、ありがとうございます。

まず、@youmil_rainさんの再帰なしでの組み合わせの出し方です。

init.sql


drop table test_tbl;

create table test_tbl (id,item) as (
select 1 ,'A0001' from dual union all
select 2 ,'A0002' from dual union all
select 3 ,'A0003' from dual union all
select 4 ,'A0004' from dual union all
select 5 ,'A0005' from dual union all
select 6 ,'A0006' from dual union all
select 7 ,'A0007' from dual union all
select 8 ,'A0008' from dual union all
select 9 ,'A0009' from dual
)
;

without_rec.sql


WITH vals AS (
	SELECT
		item AS id
	FROM
		test_tbl
	UNION ALL
	SELECT
		NULL AS id
	FROM
		dual
) SELECT
	pr1.id AS prod1
	, coalesce(pr2.id, ' ') AS prod2
	, coalesce(pr3.id, ' ') AS prod3
	, coalesce(pr4.id, ' ') AS prod4
	, coalesce(pr5.id, ' ') AS prod5
	, coalesce(pr6.id, ' ') AS prod6
	, coalesce(pr7.id, ' ') AS prod7
	, coalesce(pr8.id, ' ') AS prod8
	, coalesce(pr9.id, ' ') AS prod9
  FROM
	(
		SELECT
			*
		FROM
			vals
		WHERE
			id IS NOT NULL
	) pr1
	INNER JOIN vals pr2
		ON pr1.id < pr2.id OR pr2.id IS NULL
	INNER JOIN vals pr3
		ON pr2.id < pr3.id OR pr3.id IS NULL
	INNER JOIN vals pr4
		ON pr3.id < pr4.id OR pr4.id IS NULL
	INNER JOIN vals pr5
		ON pr4.id < pr5.id OR pr5.id IS NULL
	INNER JOIN vals pr6
		ON pr5.id < pr6.id OR pr6.id IS NULL
	INNER JOIN vals pr7
		ON pr6.id < pr7.id OR pr7.id IS NULL
	INNER JOIN vals pr8
		ON pr7.id < pr8.id OR pr8.id IS NULL
	INNER JOIN vals pr9
		ON pr8.id < pr9.id OR pr9.id IS NULL
;

【without_rec.sqlの取得結果（一部抜粋）】

without_rec.sqlの過程を少しづつ追ってみたいとおもいます。

without_rec_little_by_litte_step1.sql


SELECT
	item AS id
FROM
	test_tbl
UNION ALL
SELECT
	NULL AS id
FROM
	dual
;

【without_rec_little_by_little_step1.sqlの取得結果】

without_rec_little_by_litte_step2.sql


WITH vals AS (
	SELECT
		item AS id
	FROM
		test_tbl
	UNION ALL
	SELECT
		NULL AS id
	FROM
		dual
) SELECT
	pr1.id AS prod1
	, coalesce(pr2.id, ' ') AS prod2
--	, coalesce(pr3.id, ' ') AS prod3
--	, coalesce(pr4.id, ' ') AS prod4
--	, coalesce(pr5.id, ' ') AS prod5
--	, coalesce(pr6.id, ' ') AS prod6
--	, coalesce(pr7.id, ' ') AS prod7
--	, coalesce(pr8.id, ' ') AS prod8
--	, coalesce(pr9.id, ' ') AS prod9
  FROM
	(
		SELECT
			*
		FROM
			vals
		WHERE
			id IS NOT NULL
	) pr1
	INNER JOIN vals pr2
		ON pr1.id < pr2.id OR pr2.id IS NULL
--	INNER JOIN vals pr3
--		ON pr2.id < pr3.id OR pr3.id IS NULL
--	INNER JOIN vals pr4
--		ON pr3.id < pr4.id OR pr4.id IS NULL
--	INNER JOIN vals pr5
--		ON pr4.id < pr5.id OR pr5.id IS NULL
--	INNER JOIN vals pr6
--		ON pr5.id < pr6.id OR pr6.id IS NULL
--	INNER JOIN vals pr7
--		ON pr6.id < pr7.id OR pr7.id IS NULL
--	INNER JOIN vals pr8
--		ON pr7.id < pr8.id OR pr8.id IS NULL
--	INNER JOIN vals pr9
--		ON pr8.id < pr9.id OR pr9.id IS NULL
;

【without_rec_little_by_little_step2.sqlの取得結果】

step3以降も同じ感じで見ていくと、最初のsubqueryのwithでnullを含めたことがわかりました。
要素数をひとつふたつと増やしていく中で、
増やす直前までの組み合わせをor is nullで表現してくださいました。

自身よりも大きいアイテム文字コードを紐付けるのに加え、
要素数を増やす直前までの組み合わせを表現する行だけは
nullで紐付けて残すみたいな感じです。

こういう発想私にはなかったです、、貴重な視点からのコメントありがとうございます。

続きまして@tlokweng さんの
collectionとtable()のを使ったやり方とそのあとカンマ区切りをばらすやり方です。

11gと12cでの違いもあわせて記載したいと思います。

11gでは再帰withの中で、collectionは使えて、12cでは使えませんでした。。

【11g】

【12c】

まずは11gからです。

init.sql


drop table test_tbl;

create table test_tbl (id,item) as (
select 1 ,'A0001' from dual union all
select 2 ,'A0002' from dual union all
select 3 ,'A0003' from dual union all
select 4 ,'A0004' from dual union all
select 5 ,'A0005' from dual union all
select 6 ,'A0006' from dual union all
select 7 ,'A0007' from dual union all
select 8 ,'A0008' from dual union all
select 9 ,'A0009' from dual
)
;

non_pivot.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

WITH rec (
	lv
	,TARGET
	,list
) AS (
		SELECT
			1
			,ID
			,item_tbl(item)--collectionの形でカラム値を保持
		FROM
			test_tbl
		UNION ALL
		SELECT
			lv + 1
			,ID
			,list MULTISET UNION item_tbl(item)--結合条件満たした分のみ、collectionに追加していく
		FROM
			rec
				INNER JOIN test_tbl
					ON
						TARGET < ID
		)
SELECT
	*
FROM
	(
	SELECT
		grp
		,row_number() OVER(
							PARTITION BY grp
							ORDER BY
								COLUMN_VALUE
							) AS rn
		,COLUMN_VALUE AS item--column_valueはtable()を使用したときに現れる擬似列で、各listの要素が入っている
	FROM
		(
			SELECT
				ROWNUM grp
				, lv
				, list
			FROM
				rec
		) R 
		CROSS JOIN TABLE ( R.list )--table()でsubquery「R」をcollectionの要素数分縦持ちに行複写したあと、テーブルに変換
	)
;

【non_pivot.sqlの取得結果（一部抜粋）】

pivot.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

WITH rec (
	lv
	,TARGET
	,list
) AS (
		SELECT
			1
			,ID
			,item_tbl(item)--collectionの形でカラム値を保持
		FROM
			test_tbl
		UNION ALL
		SELECT
			lv + 1
			,ID
			,list MULTISET UNION item_tbl(item)--結合条件満たした分のみ、collectionに追加していく
		FROM
			rec
				INNER JOIN test_tbl
					ON
						TARGET < ID
		)
SELECT
	*
FROM
	(
	SELECT
		grp
		,row_number() OVER(
							PARTITION BY grp
							ORDER BY
								COLUMN_VALUE
							) AS rn
		,COLUMN_VALUE AS item--column_valueはtable()を使用したときに現れる擬似列で、各listの要素が入っている
	FROM
		(
			SELECT
				ROWNUM grp
				, lv
				, list
			FROM
				rec
		) R 
		CROSS JOIN TABLE ( R.list )--table()でcollectionをcollectionの要素数分縦持ちに行複写したあと、テーブルに変換
	)
	PIVOT (--grpごとにアイテムの要素数を横持ちに変換
			MAX(item) AS item--grpごとの要素数が一意に集約されるようにするために集約関数でサマル
							  --max(case when rn = 1 then item else null end) as item_1
							  --,max(case when rn = 2 then item else null end) as item_2 
							  --,max(case when rn = 3 then item else null end) as item_3
							  --,max(case when rn = 4 then item else null end) as item_4
							  --,max(case when rn = 5 then item else null end) as item_5
							  --,max(case when rn = 6 then item else null end) as item_6
							  --,max(case when rn = 7 then item else null end) as item_7
							  --,max(case when rn = 8 then item else null end) as item_8
							  --,max(case when rn = 9 then item else null end) as item_9
							  --*
							  --*
							  --*
							  --group by grpみたいなイメージ。ただ、明示的に集約単位を指定しているわけではないので、暗黙の集約です
			FOR rn IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
			)
;

【pivot.sqlの取得結果（一部抜粋）】

12cですと以下のようになりました。使用するsqlは同じです。

【non_pivot.sqlの取得結果（一部抜粋）】

【pivot.sqlの取得結果（一部抜粋）】

collectionとtable()について少し段階を追っていきたいと思います。
キャプチャは上が11gで下が12cの順で並べています。

まずはcollectionからです。

collection_non_rec.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

SELECT
	1
	,ID
	,item_tbl(item) as collection--collectionの形でカラム値を保持
FROM
	test_tbl
;

【collection_non_rec_11g.sqlの取得結果】

ukijumはスキーマ名です。。

【collection_non_rec_12c.sqlの取得結果】

aineはスキーマ名です。。

collection_rec.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

WITH rec (
    lv
    ,TARGET
    ,list
) AS (
        SELECT
            1
            ,ID
            ,item_tbl(item)--collectionの形でカラム値を保持
        FROM
            test_tbl
        UNION ALL
        SELECT
            lv + 1
            ,ID
            ,list MULTISET UNION item_tbl(item)--結合条件満たした分のみ、collectionに追加していく
        FROM
            rec
                INNER JOIN test_tbl
                    ON
                        TARGET < ID
        )
select 
	*
from
	rec
;

【collection_rec_11g.sqlの取得結果（一部抜粋）】

結合条件で紐づいた分だけ、カンマ区切りでうしろに溜まっていきます。

【collection_rec_12c.sqlの取得結果】

続いてtable()です。

table()_with_subq_ng.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

WITH colc AS (
SELECT
	1
	,ID
	,item_tbl(item) AS lis--collectionの形でカラム値を保持
FROM
	test_tbl
)
SELECT
	*
FROM
	TABLE(colc.lis)
;

【table()_with_subq_ng_11g.sqlの取得結果】

直接は見れませんでした。。

【table()_with_subq_ng_12c.sqlの取得結果】

こちらも直接は見れませんでした。。

いけたやつを。

table()_with_subq_ok.sql


drop type item_tbl;

create type item_tbl is table of varchar2(100);--テーブルのカラム値をcollectionに格納するための変数宣言

WITH colc AS (
SELECT
	1
	,ID
	,item_tbl(item) AS lis--collectionの形でカラム値を保持
FROM
	test_tbl
)
SELECT
	*
FROM
	(
	SELECT * FROM colc
	) tmp--table()の直前でsubqueryにしてあげる必要があるぽいです。
	,TABLE(tmp.lis)--cross join TABLE(tmp.lis)と同じ
;

【table()_with_subq_ok_11g.sqlの取得結果】

column_valueはtable()をかました結果でできる列です。

collectionをテーブルに整形するには直前で流し込む感じで使います。

【table()_with_subq_ok_12c.sqlの取得結果】

12cでも同じように使えます。

collectionとtable()とカンマ区切りはlistaggとイメージは
なんとなく似ているなーと思いました。

あと、個人的にcollectionとtable()のコンボが好きになりました！

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