前回
[sql oracle 組み合わせ 再帰with]
https://qiita.com/ukijumotahaneniarukenia/items/f16de6051fd2ffa09300#%E3%81%BE%E3%81%88%E3%81%8C%E3%81%8D
まえがき
前回の続きです。
データいい感じにたくさん作れないかと思い、plsqlで作成してみました。
作成手順を順に記したいと思います。
商品を9個のうち何個購入したのかをいろんなバリエーションでデータをつくります。
【やりたいこと】
9つの商品が存在するので、
9つの中から1つ選んで買ったデータ
9つの中から2つ選んで買ったデータ
9つの中から3つ選んで買ったデータ
9つの中から4つ選んで買ったデータ
9つの中から5つ選んで買ったデータ
9つの中から6つ選んで買ったデータ
9つの中から7つ選んで買ったデータ
9つの中から8つ選んで買ったデータ
9つの中から9つ選んで買ったデータ
のすべてを用意したい。
元データ
前回使用したデータを使用します。
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」に保持しておきます。
②商品数は9個(リンク参照)なので、
9個の「src」を自身よりも大きい文字コードが存在すれば、
left outer joinで結合していきます。
作成した集合はサブクエリ「src_tmp」で保持しておきます。
comb列はnullを含む数でグループピングしています。
この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp」実行結果(一部抜粋)】
③comb列ごとに含まれるデータパターンを一意に識別するための連番を付与します。
作成した集合はサブクエリ「src_tmp_rn」で保持しておきます。
この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_rn」実行結果(一部抜粋)】
④商品の組み合わせが横持ちとなっていますが、
unpivotでこれを縦持ちにします。(unpivot使ってみたかっただけ。。)
作成した集合はサブクエリ「src_tmp_unpivot」で保持しておきます。
この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_unpivot」実行結果(一部抜粋)】
⑤各データパターンごとに使用される商品数と
データパターンをまたいだ総数を
後で検算で使用できるようにしておきます。
作成した集合はサブクエリ「src_tmp_summury」で保持しておきます。
この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_summry」実行結果(一部抜粋)】
⑥plsqlでループをまわす回数を確認するために、
各comb列に含まれるデータパターン数を求めておきます。
作成した集合はサブクエリ「src_cnt_by_comb_max」で保持しておきます。
この時点での取得結果は以下のような感じです。
【サブクエリ「src_cnt_by_comb_max」実行結果(一部抜粋)】
comb「5」の70個が最大のようです。
⑦サマリを算出している最後のメインクエリにはこれからplsqlに移植するにあたり、
サブクエリ「src_tmp_last_summry」として保持しておきます。
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に移植します。
①ループ回数は調査どおり70回にします。
②サブクエリ「src_tmp_uni」からデータ投入用の最終的な集合を作成します。
作成した集合はサブクエリ「src_fin」で保持しておきます。
タイムスタンプとセッションの値は適当です。
ログセッション上3桁のakgはアジカンがふっと浮かんだので、そうしています。
購入IDはデータパターンを示すrnと同じ意味を持つ
ループカウンタ変数値をそのままいれます。
case文はデータパターンが識別できた分だけ、データを投入します。
識別できない場合はnullを投入することになります。
nullは入れたくないので、メインクエリで「product_id IS NOT NULL」で
nullを省きます。
--SET SERVEROUTPUT ON--実行結果を画面に出力させます
BEGIN
FOR I IN 1..70 LOOP--comb「5」の70個が最大のため、70回ループまわします
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実行
exec.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
;
合ってそうです。
発見
insert-selectにサブクエリwith句かけるのか。。
unpivot便利。。