LoginSignup
2

More than 5 years have passed since last update.

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

Posted at

前回

[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つ選んで買ったデータ
のすべてを用意したい。

元データ

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

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」に保持しておきます。

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

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp」実行結果(一部抜粋)】
image.png

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

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_rn」実行結果(一部抜粋)】
image.png

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

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_unpivot」実行結果(一部抜粋)】
image.png

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

この時点での取得結果は以下のような感じです。
【サブクエリ「src_tmp_summry」実行結果(一部抜粋)】
image.png

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

この時点での取得結果は以下のような感じです。
【サブクエリ「src_cnt_by_comb_max」実行結果(一部抜粋)】
image.png

comb「5」の70個が最大のようです。

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

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に移植します。

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

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

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

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

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

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

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

exec.sql

--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実行

データ投入前はこのようになっています。
image.png

exec.sqlを実行します。

image.png

実行結果を確認します。

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

合ってそうです。

発見

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2