対象読者
- SQLの基本は理解しているが、より実践的な分析に取り組みたい方
サンプルの動作確認環境
- PostgreSQL
- CentOS 6.7
データの準備
前回に引き続き、データ解析の現場で必要となるSQLを解説していきます。
本稿で用いるサンプルデータとして、以下のテーブルを準備しました。
サンプルデータ作成のためのSQLを、以下に示しておきます。
create table sales_history( item_name char(12), （商品名） item_category char(12 ), （商品カテゴリ） date date, （購買日） place char(8), （購買地域・場所） user_id char(8), （ユーザーコード） price int （料金） );
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は以下の通りです。内側のサブクエリの内容から順を追って確認していきます。
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で整数から実数に型を変更します。これを全体の購入数で割ることで信頼度が算出されます。
以上で商品の組み合わせごとの、購入確率の表を得ることができました。
