TECH BLOG

MENU

オンプレクラウド

Dr.Sum

Dr.Sum

データ分析の世界では、SQLは非常に強力なツールです。SQLを利用することで、複雑なデータの操作や集計、分析を効率的に行うことができます。

しかしSQLだけで高度な分析を行う場合、「繰り返し処理やロジックの再利用、分析結果の一時退避といったプログラミング的な操作を実装しづらい」といった課題に直面することがあります。
このような課題を解決しようとするとき、SQLの限界を感じることもあるでしょう。

そこで登場するのがDS Scriptです。DS ScriptをSQLと併用することで、SQLの持つ制約を補完し、より効率的かつ効果的なデータ分析が可能になります。

本記事ではバスケット分析を題材に、SQLとDS Scriptを組み合わせることで、どのように課題を解決していくのかを解説します。なお、記事内では「DS Script」を機能名とし、DS Scriptの実装を「スクリプト」と表現します。

バスケット分析をSQLクエリで実装してみる

バスケット分析はデータマイニングの手法の一つで、複数の商品が一緒に購入されるパターンを見つけるために使用されます。

次のデータは顧客の注文履歴データです。注文IDによって、一緒に購入された商品ペアを識別することができます。

このデータをバスケット分析するSQLクエリは以下のようになります。

-- 全トランザクション数を計算
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;

Dr.SumのSQLはWith句に対応しているため、シンプルなSQLクエリを記述することができました。

 

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

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

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

 

Dr.Sumマニュアル:SUBコマンド

 

Dr.Sumマニュアル:CALLコマンド

 

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

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

  • 各月ごとにバスケット分析をして、結果を比較したい

 

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

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

Dr.Sumマニュアル:FOREACHコマンド

 

スクリプトを実行する

注文履歴データの準備

注文履歴データを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_月);
    }

}

スクリプトファイルをスクリプトルート(SCRIPT_ROOT)に配置してください。

スクリプト内の3行目と7行目にある「サンプルDB」の記述は、注文履歴データをインポートしたデータベース名に変更してください。

スクリプトファイル(【完成版】BasketAnalysisScript.dhn)のダウンロード

 

スクリプトルートの場所は、[Enterprise Manager]-[サーバー]-[設定]から確認できます。

 

スクリプトの実行

コマンドプロンプトを起動し、スクリプトを実行します。
スクリプトを実行するコマンド(dwscr_execute)のパラメータは実行する環境に合わせて適宜変更してください。

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

スクリプトを実行すると、Dr.Sumの当該データベースにBASKET_ANALYSIS_RESULTテーブルが生成され、バスケット分析の結果が格納されていることを確認できます。

 

さいごに

本記事ではSQLによるバスケット分析を題材として、ロジックの再利用(サブルーチン化)や分析結果のエクスポートといった処理をDS Scriptで実装しました。その他にも、DS Scriptはデータのクレンジングやデータ加工といった分野でも活用いただける機能です。皆さんもぜひ試してみてください。

 

 

この記事にリアクションしてみませんか?

What do you think of this post?
  • 分かりやすい (0)
  • 問題が解決した (0)
ありがとうございます!!!

安部 省吾

Dr.Sum
プロフィールへ >

ウイングアーク1stでBIツールを中心にプリセールスをしています。色々なサービスを連携したい。統計とウサギが大好き。ライティングは目下修行中。

Related article

Related article関連記事

Pick up

Pick upおすすめ記事

invoiceAgent【「業務を変える動画たち」シリーズ】記事まとめ

公開:2025.01.16
更新:2025.01.16

分かりやすい

0

解決した

0
  • 業務を変えるシリーズ

invoiceAgent

【脱Excel】MotionBoardの入力機能で業務アプリをつくる

公開:2024.08.05
更新:2024.08.05

分かりやすい

2

解決した

0
  • データ入力
  • デザイン
  • コンテナ

MotionBoard

【まとめ】SVF Cloud帳票をinvoiceAgentに連携する方法

公開:2024.04.18
更新:2024.08.07

分かりやすい

2

解決した

2
  • 電帳法
  • 文書定義
  • 処理定義
  • 帳票作成・出力
  • システム連携

SVF

Ranking

Rankingランキング

1

Excelのデータを加工したい。~変換(横持ち⇔縦持ち)について~

公開:2021.01.19
更新:2024.05.08

分かりやすい

7

解決した

1
  • データ取り込み
  • データ加工

Dr.Sum

2

【あの頃に戻りたい】バックアップから任意のテーブルだけを戻す方法

公開:2023.11.07
更新:2023.11.20

分かりやすい

1

解決した

0
  • 設定

Dr.Sum

3

【DELETE-INSERT】特定期間のデータのみ洗い替えたい!【手動編】

公開:2024.01.10
更新:2024.05.08

分かりやすい

0

解決した

0
  • データ取り込み
  • SQL

Dr.Sum

Info

Information

ウイングアーク1stからのお知らせ