アソシエーション分析
配点 : 40点難易度 : 4
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

あるECサイトの購買履歴データ(PURCHASE_HISTORY)から、アソシエーション分析に必要な3つの指標を求めて、商品の組合せ毎に一覧で表示しなさい。商品の組合せは、商品Aと商品Bの2種類の組合せのみとし、また、購買履歴データには、各会員コードの購入履歴は１セッションIDのみ存在するものとする。

アソシエーション分析とは、顧客の購買履歴などから行動パターンを分析する手法のことで、「データマイニング」の1分野である「相関ルール抽出」の代表的な手法のひとつである。有名なものに「おむつとビール」の事例がある。

アソシエーション分析では、以下の3つの指標を使って分析する。
「支持度(Support)」：すべての購買データのうち、商品Aと商品Bが購買された割合を表す
「信頼度(Confidence)」：商品Aを買った人のうち、商品Bも同時に購買した人の割合を表す
「リフト値(Lift)」：すべての購買データのうち、商品Aがあることで、商品Bの購買率がどのくらい引き上げられているかを表す
　リフト値が高いほど、商品Aがあることで商品Bも購買されたという相関関係が強いと評価できる。

指標の計算方法(※指標の計算結果は、小数点第6位を四捨五入すること)

  • 支持度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 全顧客数 × 100
  • 信頼度 ＝ 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数 × 100
  • リフト値 ＝ (商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数) ÷ (商品Bを購買した顧客数÷全顧客数)

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

  • 商品Aの商品コード → ITEM_A
  • 商品Bの商品コード → ITEM_B
  • 算出した支持度 → SUPPORT
  • 算出した信頼度 → CONFIDENCE
  • 算出したリフト値 → LIFT

表示順

  1. 算出したリフト値の降順
  2. 算出した支持度の降順
  3. 算出した信頼度値の降順
  4. 商品Aの商品コードの降順

表示フォーマット

※あくまでフォーマットを示すもので、正解例ではありません。行数も正解とは異なります。

ITEM_A ITEM_B SUPPORT CONFIDENCE LIFT
P002 P001 42.85714 100 1.4
P001 P002 42.85714 60 1.4
P003 P004 14.28571 50 1.16667
P004 P003 14.28571 33.33333 1.16667
P003 P001 14.28571 50 0.7

ER図 (半角:物理名、全角:論理名)


テーブル定義

テーブル名: PURCHASE_HISTORY

主キー 列名 データ型 必須 デフォルト値
✔︎ SESSION_ID VARCHAR ✔︎ NULL
✔︎ MEMBER_CODE VARCHAR ✔︎ NULL
✔︎ ITEM_CODE VARCHAR ✔︎ NULL
EX_TIMESTAMP VARCHAR ✔︎ NULL

サンプルデータ

テーブル名: PURCHASE_HISTORY

SESSION_ID MEMBER_CODE ITEM_CODE EX_TIMESTAMP
S00001 10000 P001 2024-01-01 10:10:10
S00001 10000 P002 2024-01-01 10:10:10
S00002 20000 P001 2024-01-01 10:10:20
S00002 20000 P003 2024-01-01 10:10:20
S00003 30000 P001 2024-01-01 10:11:10
S00003 30000 P004 2024-01-01 10:11:20
S00004 40000 P001 2024-01-01 10:13:10
S00004 40000 P002 2024-01-01 10:13:10
S00005 50000 P001 2024-01-01 11:10:20
S00005 50000 P002 2024-01-01 11:10:10
S00006 60000 P003 2024-01-02 10:10:10
S00006 60000 P004 2024-01-02 10:10:10
S00007 70000 P004 2024-01-01 11:10:20

Sorry, Japanese Only.