sql oracle 組み合わせ 再帰with 【検証データ作成plsql】

#前回

[sql oracle 組み合わせ 再帰with]
https://qiita.com/ukijumotahaneniarukenia/items/f16de6051fd2ffa09300#%E3%81%BE%E3%81%88%E3%81%8C%E3%81%8D

#まえがき

前回の続きです。
データいい感じにたくさん作れないかと思い、plsqlで作成してみました。
作成手順を順に記したいと思います。

商品を９個のうち何個購入したのかをいろんなバリエーションでデータをつくります。

【やりたいこと】
９つの商品が存在するので、
９つの中から１つ選んで買ったデータ
９つの中から２つ選んで買ったデータ
９つの中から３つ選んで買ったデータ
９つの中から４つ選んで買ったデータ
９つの中から５つ選んで買ったデータ
９つの中から６つ選んで買ったデータ
９つの中から７つ選んで買ったデータ
９つの中から８つ選んで買ったデータ
９つの中から９つ選んで買ったデータ
のすべてを用意したい。

#元データ

前回使用したデータを使用します。

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

```

#元データ増幅

投入した元データよりデータをたくさん作っていきます。

①商品数をサブクエリ「src」に保持しておきます。

②商品数は９個（リンク参照）なので、
９個の「src」を自身よりも大きい文字コードが存在すれば、
left outer joinで結合していきます。
作成した集合はサブクエリ「src_tmp」で保持しておきます。
comb列はnullを含む数でグループピングしています。

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp」実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/2fdeba6e-2764-e900-991c-b469ed31fae2.png)

③comb列ごとに含まれるデータパターンを一意に識別するための連番を付与します。
作成した集合はサブクエリ「src_tmp_rn」で保持しておきます。

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_rn」実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/088f2044-4c66-9ec3-251f-a952b08b1252.png)

④商品の組み合わせが横持ちとなっていますが、
unpivotでこれを縦持ちにします。（unpivot使ってみたかっただけ。。）
作成した集合はサブクエリ「src_tmp_unpivot」で保持しておきます。

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_unpivot」実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/64cbb550-dc85-3b17-e3be-143b690fda9e.png)

⑤各データパターンごとに使用される商品数と
データパターンをまたいだ総数を
後で検算で使用できるようにしておきます。
作成した集合はサブクエリ「src_tmp_summury」で保持しておきます。

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_summry」実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/f94af7cf-ef2c-dd8e-a8ad-ce21c0577f3d.png)

⑥plsqlでループをまわす回数を確認するために、
各comb列に含まれるデータパターン数を求めておきます。
作成した集合はサブクエリ「src_cnt_by_comb_max」で保持しておきます。

この時点での取得結果は以下のような感じです。
【サブクエリ「src_cnt_by_comb_max」実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/5d025d7e-1461-ec32-936e-5b157b66855c.png)

comb「５」の７０個が最大のようです。

⑦サマリを算出している最後のメインクエリにはこれからplsqlに移植するにあたり、
サブクエリ「src_tmp_last_summry」として保持しておきます。

```sql:incre.sql

WITH src AS (
		SELECT DISTINCT
			product_id
		FROM
			purchase_detail_log					
)
,src_tmp AS (
	SELECT
		s1.product_id AS prod1
		,s2.product_id AS prod2
		,s3.product_id AS prod3
		,s4.product_id AS prod4
		,s5.product_id AS prod5
		,s6.product_id AS prod6
		,s7.product_id AS prod7
		,s8.product_id AS prod8
		,s9.product_id AS prod9
		,CASE
			WHEN COALESCE(s2.product_id,s3.product_id,s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 1
			WHEN COALESCE(s3.product_id,s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 2
			WHEN COALESCE(s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 3
			WHEN COALESCE(s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 4
			WHEN COALESCE(s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 5
			WHEN COALESCE(s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 6
			WHEN COALESCE(s8.product_id,s9.product_id) IS NULL THEN 7
			WHEN nvl(s9.product_id,NULL) IS NULL THEN 8
			ELSE 9
		END AS comb
	FROM
		src s1
			LEFT OUTER JOIN src s2
				ON
					s1.product_id < s2.product_id
			LEFT OUTER JOIN src s3
				ON
					s2.product_id < s3.product_id
			LEFT OUTER JOIN src s4
				ON
					s3.product_id < s4.product_id
			LEFT OUTER JOIN src s5
				ON
					s4.product_id < s5.product_id
			LEFT OUTER JOIN src s6
				ON
					s5.product_id < s6.product_id
			LEFT OUTER JOIN src s7
				ON
					s6.product_id < s7.product_id
			LEFT OUTER JOIN src s8
				ON
					s7.product_id < s8.product_id
			LEFT OUTER JOIN src s9
				ON
					s8.product_id < s9.product_id
)
,src_tmp_rn AS (
	SELECT
		sub.*
		,row_number() OVER (PARTITION BY sub.comb
							ORDER BY
								prod1
								,prod2
								,prod3
								,prod4
								,prod5
								,prod6
								,prod7
								,prod8
								,prod9
							) AS rn
	FROM
		src_tmp sub
)
,src_tmp_unpivot AS (
	SELECT
		comb
		,rn
		,prod
		,vals
	FROM
		src_tmp_rn
	UNPIVOT (
				vals FOR prod IN (prod1,prod2,prod3,prod4,prod5,prod6,prod7,prod8,prod9)
			)
	ORDER BY
		comb DESC
		,rn ASC
		,prod ASC
		,vals ASC
)
,src_tmp_uni AS (
	SELECT
		comb
		,rn
		,row_number() OVER (PARTITION BY comb, rn ORDER BY prod ASC) AS uni
		,prod
		,vals
	FROM
		src_tmp_unpivot
	ORDER BY
		comb DESC
		,rn ASC
)
,src_tmp_summry AS (
	SELECT
		comb
		,rn
		,MAX(uni) AS cnt_by_patn
		,MAX(rn) OVER (PARTITION BY comb) AS cnt_by_comb
		,SUM(MAX(uni)) OVER () AS summury 
	FROM
		src_tmp_uni
	GROUP BY
		comb
		,rn
)
,src_cnt_by_comb_max AS (
SELECT
	comb
	,MAX(cnt_by_comb) AS cnt_by_comb_max
FROM
	src_tmp_summry
GROUP BY
	comb
ORDER BY
	MAX(cnt_by_comb) DESC
)
SELECT--【TODO】「src_tmp_last_summry」としてサブクエリに保持しておく
	s1.cnt_by_comb_max
	,s2.comb
	,s2.rn
	,s2.cnt_by_patn
	,s2.cnt_by_comb
	,s2.summury
FROM
	src_cnt_by_comb_max s1
		INNER JOIN src_tmp_summry s2
			ON
				s1.comb = s2.comb
ORDER BY
	s2.comb DESC
	,s2.rn ASC
;

```

#plsqlへ移植

これまで作成したincre.sqlをplsqlに移植します。

①ループ回数は調査どおり７０回にします。

②サブクエリ「src_tmp_uni」からデータ投入用の最終的な集合を作成します。
作成した集合はサブクエリ「src_fin」で保持しておきます。

タイムスタンプとセッションの値は適当です。

ログセッション上３桁のakgはアジカンがふっと浮かんだので、そうしています。

購入IDはデータパターンを示すrnと同じ意味を持つ
ループカウンタ変数値をそのままいれます。

case文はデータパターンが識別できた分だけ、データを投入します。
識別できない場合はnullを投入することになります。

nullは入れたくないので、メインクエリで「product_id IS NOT NULL」で
nullを省きます。



```sql:exec.sql

--SET SERVEROUTPUT ON--実行結果を画面に出力させます

BEGIN
    FOR I IN 1..70 LOOP--comb「５」の７０個が最大のため、７０回ループまわします		
        INSERT INTO purchase_detail_log tar
		WITH src AS (
				SELECT DISTINCT
					product_id
				FROM
					purchase_detail_log					
		)
		,src_tmp AS (
			SELECT
				s1.product_id AS prod1
				,s2.product_id AS prod2
				,s3.product_id AS prod3
				,s4.product_id AS prod4
				,s5.product_id AS prod5
				,s6.product_id AS prod6
				,s7.product_id AS prod7
				,s8.product_id AS prod8
				,s9.product_id AS prod9
				,CASE
					WHEN COALESCE(s2.product_id,s3.product_id,s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 1
					WHEN COALESCE(s3.product_id,s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 2
					WHEN COALESCE(s4.product_id,s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 3
					WHEN COALESCE(s5.product_id,s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 4
					WHEN COALESCE(s6.product_id,s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 5
					WHEN COALESCE(s7.product_id,s8.product_id,s9.product_id) IS NULL THEN 6
					WHEN COALESCE(s8.product_id,s9.product_id) IS NULL THEN 7
					WHEN nvl(s9.product_id,NULL) IS NULL THEN 8
					ELSE 9
				END AS comb
			FROM
				src s1
					LEFT OUTER JOIN src s2
						ON
							s1.product_id < s2.product_id
					LEFT OUTER JOIN src s3
						ON
							s2.product_id < s3.product_id
					LEFT OUTER JOIN src s4
						ON
							s3.product_id < s4.product_id
					LEFT OUTER JOIN src s5
						ON
							s4.product_id < s5.product_id
					LEFT OUTER JOIN src s6
						ON
							s5.product_id < s6.product_id
					LEFT OUTER JOIN src s7
						ON
							s6.product_id < s7.product_id
					LEFT OUTER JOIN src s8
						ON
							s7.product_id < s8.product_id
					LEFT OUTER JOIN src s9
						ON
							s8.product_id < s9.product_id
		)
		,src_tmp_rn AS (
			SELECT
				sub.*
				,row_number() OVER (PARTITION BY sub.comb
									ORDER BY
										prod1
										,prod2
										,prod3
										,prod4
										,prod5
										,prod6
										,prod7
										,prod8
										,prod9
									) AS rn
			FROM
				src_tmp sub
		)
		,src_tmp_unpivot AS (
			SELECT
				comb
				,rn
				,prod
				,vals
			FROM
				src_tmp_rn
			UNPIVOT (
						vals FOR prod IN (prod1,prod2,prod3,prod4,prod5,prod6,prod7,prod8,prod9)
					)
			ORDER BY
				comb DESC
				,rn ASC
				,prod ASC
				,vals ASC
		)
		,src_tmp_uni AS (
			SELECT
				comb
				,rn
				,row_number() OVER (PARTITION BY comb, rn ORDER BY prod ASC) AS uni
				,prod
				,vals
			FROM
				src_tmp_unpivot
			ORDER BY
				comb DESC
				,rn ASC
		)
		,src_tmp_summry AS (
			SELECT
				comb
				,rn
				,MAX(uni) AS cnt_by_patn
				,MAX(rn) OVER (PARTITION BY comb) AS cnt_by_comb
				,SUM(MAX(uni)) OVER () AS summury 
			FROM
				src_tmp_uni
			GROUP BY
				comb
				,rn
		)
		,src_cnt_by_comb_max AS (
		SELECT
			comb
			,MAX(cnt_by_comb) AS cnt_by_comb_max
		FROM
			src_tmp_summry
		GROUP BY
			comb
		ORDER BY
			MAX(cnt_by_comb) DESC
		)
		,src_tmp_last_summry AS (
		SELECT--【TODO】「src_tmp_last_summry」としてサブクエリに保持しておく
			s1.cnt_by_comb_max
			,s2.comb
			,s2.rn
			,s2.cnt_by_patn
			,s2.cnt_by_comb
			,s2.summury
		FROM
			src_cnt_by_comb_max s1
				INNER JOIN src_tmp_summry s2
					ON
						s1.comb = s2.comb
		ORDER BY
			s2.comb DESC
			,s2.rn ASC
		)
		,src_fin AS(
			SELECT
				to_char(CAST(current_timestamp AS TIMESTAMP) + I , 'YYYY-MM-DD HH:MM')AS stamp
				,'a' || 'k' || 'g' || lpad(to_char(I),2,0) || lpad(to_char(I),3,0) AS logsession
				,comb || lpad(I,2,0) AS purchase_id
				,CASE
					WHEN sub.comb = 9 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 8 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 7 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 6 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 5 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 4 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 3 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 2 AND sub.rn = I THEN sub.vals
					WHEN sub.comb = 1 AND sub.rn = I THEN sub.vals
					ELSE NULL
				END AS product_id
			FROM
				src_tmp_uni sub
		)
		SELECT
			*
		FROM
			src_fin
		WHERE
			product_id IS NOT NULL
		;
    END LOOP;

END
;
--commit;
--rollback;


```

#plsql実行

データ投入前はこのようになっています。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/d8dedfef-cb82-403c-8d78-006e7bc67380.png)

exec.sqlを実行します。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/ae5f81d1-52ff-423c-d126-32a3dec55aa4.png)


実行結果を確認します。

```sql:conf.sql

with conf_sum as (
	select
		substr(purchase_id,1,1) as comb
		,to_number(substr(purchase_id,2,2)) as rn
		,count(*) over () as summury
	from
		purchase_detail_log
	where
		logsession LIKE 'akg%'
)
,conf_detail as (
	select
		substr(purchase_id,1,1) as comb
		,to_number(substr(purchase_id,2,2)) as rn
		,count(to_number(substr(purchase_id,2,2))) over (partition by substr(purchase_id,1,1),to_number(substr(purchase_id,2,2))) as cnt_by_patn
	from
		purchase_detail_log
	where
		logsession LIKE 'akg%'
	order by
		substr(purchase_id,1,1) desc--comb
		,to_number(substr(purchase_id,2,2)) asc--rn
)
select
	s1.comb
	,s1.rn
	,min(s2.cnt_by_patn) as cnt_by_patn
	,min(s1.summury) as summury
from
	conf_sum s1
		inner join conf_detail s2
			on
				s1.comb = s2.comb
			and s1.rn = s2.rn
group by
	s1.comb
	,s1.rn
order by
	s1.comb desc
	,s1.rn asc
;

```

【実行結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/24fc48f6-a526-219d-bcad-1117a401626c.png)

合ってそうです。

#発見
insert-selectにサブクエリwith句かけるのか。。
unpivot便利。。