-- 全トランザクション数を計算
WITH total_transactions AS (
    SELECT COUNT(DISTINCT 注文ID) AS total
    FROM 注文履歴
),
-- 各商品の出現頻度を計算
product_counts AS (
    SELECT 製品, COUNT(DISTINCT 注文ID) AS product_count
    FROM 注文履歴
    GROUP BY 製品
),
-- 商品ペアの生成と共起の頻度を計算
product_pairs AS (
    SELECT 
        a.製品 AS product_a,
        b.製品 AS product_b,
        COUNT(DISTINCT a.注文ID) AS pair_count
    FROM 
        注文履歴 a
    INNER JOIN 
        注文履歴 b
    ON 
        a.注文ID = b.注文ID
        AND a.製品 <> b.製品
    GROUP BY 
        a.製品, b.製品
)

-- サポート、信頼度、リフト値を計算
SELECT
    pp.product_a,
    pp.product_b,
    pp.pair_count,
    (pp.pair_count / tt.total) AS support,
    (pp.pair_count / pc_a.product_count) AS confidence,
    (pp.pair_count / pc_a.product_count) / (pc_b.product_count / tt.total) AS lift
FROM 
    product_pairs pp
INNER JOIN 
    product_counts pc_a ON pp.product_a = pc_a.製品
INNER JOIN 
    product_counts pc_b ON pp.product_b = pc_b.製品
CROSS JOIN
    total_transactions tt
ORDER BY 
    lift DESC;

処理をサブルーチン化して再利用する

通常、SQLでは関数やストアドプロシージャを用いることでロジックの再利用を実現できます。しかし一般的なプログラミング言語に比べると柔軟性に欠ける場合があります。

DS Scriptでは複数の処理をまとめて実行するためのサブルーチンを定義することができ、複雑なロジックであっても容易に再利用できます。DS Scriptでバスケット分析のSQLクエリをサブルーチン化すると以下のようになります。SUBコマンドを利用して「BasketAnalysis」という名称のサブルーチンを定義しており、CALLコマンドで呼び出しています。

条件を変更しながら繰り返し実行する

以下のような要件をSQLで実現しようとすると、どのような実装になるのでしょうか。

分析対象月ごとにバスケット分析のSQLクエリを記述して、それらをUNION ALLすることで実現できそうです。
しかし、分析対象月数分のSQLクエリを個別に記述すると、コードが煩雑化し、開発効率が低下するだけでなく、保守性の低下やバグの発生リスクの増大といった品質面での問題も生じます。
このような冗長な記述は、変更が必要な際に全てのSQLクエリに対して同様の修正を行う必要があるため、人的ミスが発生しやすく、全体の信頼性が損なわれる可能性があります。

DS Scriptは任意のパラメータを渡してサブルーチンを呼び出すことができます。
以下のスクリプトでは、テーブル型の変数に処理したい月の情報を格納しています。この変数をFOREACHで繰り返し参照し、月の情報を渡しながらサブルーチンBasketAnalysisを呼び出しています。

スクリプトを実行する

注文履歴データの準備

注文履歴データをDr.Sumの任意のデータベースにインポートしてください。テーブル名は「注文履歴」としてください。

SUB ("BasketAnalysis", $_VARIABLE_ISOLATE) {
    // 接続先のデータベース(DB名は適宜変更してください)
    CONNECT("サンプルDB") {
    }

    // エクスポート先のデータベース(DB名は適宜変更してください)
    EXPORT("サンプルDB",
          "BASKET_ANALYSIS_RESULT",
          $_EXPORT_MODE_APPEND,
          $_ERROR_MODE_INTERRUPTED,
          $_TABLE_CREATE_MODE_NORMAL) {
        -- 全トランザクション数を計算
        WITH total_transactions AS (
        SELECT COUNT(DISTINCT 注文ID) AS total
        FROM 注文履歴
        WHERE 注文月 = '${_1}'
        ),

        -- 各製品の出現頻度を計算
        product_counts AS (
            SELECT 製品, COUNT(DISTINCT 注文ID) AS product_count
            FROM 注文履歴
            WHERE 注文月 = '${_1}'
            GROUP BY 製品
        ),

        -- 商品ペアの生成と共起の頻度を計算
        product_pairs AS (
            SELECT 
                a.注文月 AS month,
                a.製品 AS product_a,
                b.製品 AS product_b,
                COUNT(DISTINCT a.注文ID) AS pair_count
            FROM 
                注文履歴 a
            INNER JOIN 
                注文履歴 b
            ON 
                a.注文ID = b.注文ID
                AND a.製品 <> b.製品
            WHERE
                a.注文月 = '${_1}'
                AND b.注文月 = '${_1}'
            GROUP BY 
                a.注文月, a.製品, b.製品
        )

        -- サポート、信頼度、リフト値を計算
        SELECT 
            pp.month AS 注文月,
            pp.product_a AS 製品A,
            pp.product_b AS 製品B,
            pp.pair_count,
            (pp.pair_count / tt.total) AS サポート,
            (pp.pair_count / pc_a.product_count) AS 信頼度,
            (pp.pair_count / pc_a.product_count) / (pc_b.product_count / tt.total) AS リフト値
        FROM 
            product_pairs pp
        INNER JOIN 
            product_counts pc_a ON pp.product_a = pc_a.製品
        INNER JOIN 
            product_counts pc_b ON pp.product_b = pc_b.製品
        CROSS JOIN
            total_transactions tt
        ORDER BY 
            リフト値 DESC;
    }

    RETURN () {
        // 値を返して処理を抜ける
        0;
    }

}

SET() {
    -- 1月、2月、3月をバスケット分析の対象月にする
    @VAR_MONTHS(月) =[
                        ["01"],
                        ["02"],
                        ["03"]
    ]
}

FOREACH (@VAR_MONTHS, FOREACH_ROW_COUNT) {
    CALL () {
        // SUBコマンドを呼び出し、戻り値を受け取る
        BasketAnalysis($VAR_MONTHS_月);
    }

}

dwscr_execute "<Dr.Sumサーバー名>" 6001 "ユーザーID" "パスワード" "SCRIPT_ROOT/【完成版】BasketAnalysisScript.dhn" ""