Shoeisha Technology Media

CodeZine(コードジン)

記事種別から探す

SQLでバスケット分析と時系列分析を用いたデータ解析に挑戦する

ビジネスデータ解析のためのSQL入門 第2回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2017/07/19 14:00

 本連載ではビジネスデータ解析でデータベースから情報を取得して活用する必要がある方を対象に、デシル分析や時系列分析などビジネスデータ解析でよく使われる実践的なSQL例を示し、解説します。前回はクロス集計とデシル分析について取り上げました。今回扱うテーマは、バスケット分析と時系列分析です。

目次

対象読者

  • SQLの基本は理解しているが、より実践的な分析に取り組みたい方

サンプルの動作確認環境

  • PostgreSQL
  • CentOS 6.7

データの準備

 前回に引き続き、データ解析の現場で必要となるSQLを解説していきます。

 本稿で用いるサンプルデータとして、以下のテーブルを準備しました。

 サンプルデータ作成のためのSQLを、以下に示しておきます。

リスト1 購買履歴 テーブルの作成(create_table_sales_history.sql)
create table sales_history(
item_name char(12),  (商品名)
item_category char(12 ),    (商品カテゴリ)
date date,  (購買日)
place char(8),  (購買地域・場所)
user_id char(8),    (ユーザーコード)
price int  (料金)
);
リスト2 購買履歴 データの挿入(insert_sales_history.sql)
insert into sales_history values
('鮭','food','2017/3/29','AB Store','UID0001','250'),
('豆乳','food','2017/3/29','AB Store','UID0001','250'),
('豆乳','food','2017/3/30','AB Store','UID0001','200'),
('鮭','food','2017/3/30','AB Store','UID0001','350'),
('卵','food','2017/3/30','AB Store','UID0001','200'),
('鮭','food','2017/3/30','AB Store','UID0001','350'),
('牛肉','food','2017/4/1','AB Store','UID0001','450'),
('牛肉','food','2017/4/2','AB Store','UID0001','450'),
('深皿','zakka','2017/4/3','AB Store','UID0001','980'),
('卵','food','2017/4/4','AB Store','UID0001','250'),
('豆乳','food','2017/4/4','AB Store','UID0001','250'),
('卵','food','2017/4/5','AB Store','UID0001','250'),
('卵','food','2017/4/6','AB Store','UID0001','250'),
('鮭','food','2017/4/7','AB Store','UID0001','250'),
('鮭','food','2017/4/8','AB Store','UID0001','250'),
('卵','food','2017/4/9','AB Store','UID0001','250'),
('豆乳','food','2017/4/10','AB Store','UID0001','250'),
('卵','food','2017/4/10','AB Store','UID0001','250'),
('鮭','food','2017/4/10','AB Store','UID0005','250'),
('卵','food','2017/4/12','AB Store','UID0001','250'),
('卵','food','2017/4/12','AB Store','UID0001','250'),
('鮭','food','2017/4/13','AB Store','UID0001','250'),
('豆乳','food','2017/4/13','AB Store','UID0001','250'),
('深皿','zakka','2017/4/14','AB Store','UID0001','980'),
('深皿','zakka','2017/4/14','AB Store','UID0001','780')
;

バスケット分析

 バスケット分析とは、ある商品を購入された際の買い物カゴ(バスケット)、つまり同一レシートで一緒に買われている商品が何かを分析する手法です。 例えば「iPhoneとスマホケース」や「カモとネギ」のような同時に購入されやすい商品を発見することにより、同時購入頻度の高い商品との相乗効果を狙った販促や棚割りへ活用することができます。

アソシエーション分析

 バスケット分析は「アソシエーション分析」と呼ばれる分析手法のひとつになります。アソシエーション分析は同時に購入される関係性が強い商品の組み合わせやその割合、統計的に見て強い関係を持つ商品間の関係(ルール)を抽出する分析手法で、一般に以下3つの指標を算出します。

  • 信頼度:商品Xを買った顧客が商品Yも買う確率
  • 支持度:商品Xと商品Yが同時に買われる確率
  • リフト値:「信頼度」÷商品Yが買われる確率

 今回の記事で取り上げたバスケット分析は単純に同時購入された(同じレシート内で登場する)商品ペアの頻度(信頼度)を求めるものとします。上記3つの指標のうち「信頼度」のみを使っているため、簡易版アソシエーション分析と考えることができます。

SQLサンプル/サンプル解説

 サンプルとして冒頭で準備した顧客テーブルを用います。

 ある2つの商品の組み合わせが購入される確率を、それぞれ算出したい場合で考えます。

 欲しい結果としては以下になります。

   item_name    |   item_name2   |    confidence
----------------+----------------+-------------------
 卵             | 豆乳           | 0.428571428571429
 卵             | 鮭             | 0.285714285714286
 豆乳           | 卵             |               0.6
 豆乳           | 鮭             |               0.8
 鮭             | 卵             | 0.333333333333333
 鮭             | 豆乳           | 0.666666666666667

 このような結果の場合、例えば上記のテーブル1行目に着目すると「卵を買った顧客が豆乳も買う確率」である信頼度(confidence)は0.428〜なので、約43%であると分かります。

 SQLは以下の通りです。内側のサブクエリの内容から順を追って確認していきます。

リスト3 バスケット分析のSQL(basket.sql)
select
    combi_count.item_name,
    combi_count.item_name2,
    cast(combi_count.order_count as real)/item_count.order_count as confidence /*(3)-(a)*/
from
    (select /*(2)*/
        item_name,
        count(distinct date) as order_count
    from sales_history
    group by item_name
    )item_count
    inner join
        (select /*(1)*/
            shl.item_name,
            shr.item_name as item_name2,
            count(distinct shl.date) as order_count
        from sales_history as shl
            inner join sales_history shr /*(1)-(a)*/
            on shl.date = shr.date
                and shl.item_name <> shr.item_name
        group by shl.item_name,shr.item_name /*(1)-(b)*/
        )combi_count
    on combi_count.item_name = item_count.item_name
;

(1)商品の組み合わせと購入回数

  • (a)sales_historyテーブル同士をinner joinします。この時条件として「購買日が同じ」「商品名が違う」を指定することで同じレシートで買われた商品組み合わせを作成します。
  • (b)商品組み合わせごとにグループ化(group by)します。

 このクエリによって得られる途中結果は、以下の通りになります。

   item_name    |   item_name2   | order_count
----------------+----------------+-------------
 卵             | 豆乳           |           3
 卵             | 鮭             |           2
 豆乳           | 卵             |           3
 豆乳           | 鮭             |           4
 鮭             | 卵             |           2
 鮭             | 豆乳           |           4

(2)商品ごとの購入回数

 商品ごとに購入回数をカウントしたテーブルを作成します。

 このクエリによって得られる途中結果は、以下の通りになります。

   item_name    | order_count
----------------+-------------
 卵             |           7
 深皿           |           2
 牛肉           |           2
 豆乳           |           5
 鮭             |           6

(3)商品が同時に購入される頻度を算出する

 (1)および(2)で求めたテーブル同士を内部結合(inner join)すると、以下のテーブルが得られます。

   item_name    | order_count |   item_name    |   item_name2   | order_count
----------------+-------------+----------------+----------------+-------------
 卵             |           7 | 卵             | 豆乳           |           3
 卵             |           7 | 卵             | 鮭             |           2
 豆乳           |           5 | 豆乳           | 卵             |           3
 豆乳           |           5 | 豆乳           | 鮭             |           4
 鮭             |           6 | 鮭             | 卵             |           2
 鮭             |           6 | 鮭             | 豆乳           |           4

 例えば1行目からは、卵の購入回数は7、卵と豆乳の組み合わせの購入回数は3であることが分かります。そのため最終的に得たい結果である「卵を買った顧客が豆乳も買う確率」である信頼度(confidence)は3/7=0.428と算出することができます。

  • (a)組み合わせが発生する頻度をcountで集計し、CASTで整数から実数に型を変更します。これを全体の購入数で割ることで信頼度が算出されます。

 以上で商品の組み合わせごとの、購入確率の表を得ることができました。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

  • WINGSプロジェクト 西 潤史郎(ニシ ジュンシロウ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2017年5月時点での登録メンバは52名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂きたい...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XMLD...

バックナンバー

連載:ビジネスデータ解析のためのSQL入門
All contents copyright © 2005-2017 Shoeisha Co., Ltd. All rights reserved. ver.1.5