1. はじめに
バスケット分析(併売分析)は,アウトプットイメージやその分析の有用性についてはマーケティング業界の誰もが周知しているのに反して,実際にそれを実践できている所はそれ程多くはありません。
また、実践できているにしても上の Tableau のようなソフトウェアを入れなければならなかったり、実装のためのコストやリソースがかかっている所も少なくありません。さらに、そういったツールに頼っているところは、少し条件を変えただけの分析やより踏み込んだ分析に対応する柔軟性を兼ね備えるのは難しいところです。
本シリーズで紹介するトレジャーデータで実戦可能なバスケット分析コンテンツは,誰もがロジック・仕組みの理解から実践まで行えるように、できるだけわかりやすく具体的に紹介していきます。
その中で、ロジックや実装の難しさがあると捉えられていますが、実はバスケット分析が持つ本質的な難しさは次の2つ:①「計算量の問題」②「結果の解釈の問題」であることに迫っていきます。
※ ここで扱う「バスケット分析」は、単純に同時購入された(同じレシート内で登場する)アイテムペアの共起頻度、共起度(コサイン係数,シンプソン係数)を求めるものとします。
※ バスケット分析は、より広域な手法(機械学習)も含んで「アソシエーション分析」と呼ばれることもあります。この分析手法は,蓄積されたユーザー毎の購入データを分析し、併売の関係性が強いアイテムの組み合わせやその割合、統計的に見て強い関係を持つアイテム間の関係(ルール)を抽出するデータマイニング手法で、「アイテム1 を購入した人はアイテム2 も購入する確率が高い」という法則性(アソシエーションルール)を見つけ出す分析手法です。アソシエーション分析は本シリーズの応用編として登場します。
2. SQLで頭の体操 〜組み合わせの計算〜
ゼロからバスケット分析を理解するためには、まずは「頭の体操」が必要です。ここでのポイントは様々な条件下でのモノとモノの組み合わせを、SQLを使って柔軟に求めて行く所から始めましょう。
Numbers:2つ数値の組み合わせ
今,1〜6 までの数値が入った "numbers" テーブルがあるとします。
number 1 2 3 4 5 6
1〜6までの数字を使って2つの数字の組み合わせをSQLで作って見ましょう。
課題1:2つの数字の組み合わせをSQLで全て求める
SELECT n1.number, n2.number FROM ( SELECT number FROM numbers ) n1, ( SELECT number FROM numbers ) n2 ORDER BY n1.number, n2.number
同じテーブル同士をCROSS JOINして全組み合わせを算出します。
number number 1 1 1 2 1 3 1 4 1 5 1 6 2 1 2 2 2 3 2 4 2 5
36個の数値の組み合わせ結果が得られました。
課題2:2つの数字の組み合わせで,同じ数字の組み合わせは避ける
SELECT n1.number, n2.number FROM ( SELECT number FROM numbers ) n1, ( SELECT number FROM numbers ) n2 WHERE n1.number != n2.number ORDER BY n1.number, n2.number
Where 句で同じ数値を除外しています。
number number 1 2 1 3 1 4 1 5 1 6 2 1 2 3 2 4
30個の数値の組み合わせ結果が得られました。
課題3:2つの数字の組み合わせで順序を気にしない
例えば「1, 2」と「2, 1」は同じものと扱います。また課題2に引き続き、同じ数字同士の場合も除外します。
SELECT n1.number, n2.number FROM ( SELECT number FROM numbers ) n1, ( SELECT number FROM numbers ) n2 WHERE n1.number < n2.number ORDER BY n1.number, n2.number
number number 1 2 1 3 1 4 1 5 1 6 2 3 2 4 2 5 2 6 3 4 3 5 3 6 4 5 4 6 5 6
先ほどの半分の15個の組み合わせが得られました。
このようにある一つのカラムに対して、存在する値の組み合わせを求めることはSQLで簡単に記述できます。
Matching:男女のマッチング
次に以下のような "men_and_women" テーブル(男:2人,女:3人)に対して、以下を考えます。
課題4:可能な男女のマッチングを考える(mistake)
id,gender m1,man m2,man w1,woman w2,woman w3,woman
とりあえず何も考えずに課題3と同種のクエリを実行してみます。
SELECT n1.id, n2.id, n1.id || ' ♥ ' || n2.id FROM ( SELECT id, gender FROM men_and_women ) n1, ( SELECT id, gender FROM men_and_women ) n2 WHERE n1.id < n2.id ORDER BY n1.id, n2.id
id id pair m1 m2 m1 ♥ m2 m1 w1 m1 ♥ w1 m1 w2 m1 ♥ w2 m1 w3 m1 ♥ w3 m2 w1 m2 ♥ w1 m2 w2 m2 ♥ w2 m2 w3 m2 ♥ w3 w1 w2 w1 ♥ w2 w1 w3 w1 ♥ w3 w2 w3 w2 ♥ w3
おやおやこれはいけません。一部で男同士、女同士のペアが作られてしまっています。
課題4':可能な男女のマッチングを考える(correct)
SELECT n1.id, n2.id, n1.id || ' ♥ ' || n2.id FROM ( SELECT id, gender FROM men_and_women ) n1 JOIN ( SELECT id, gender FROM men_and_women ) n2 ON n1.gender != n2.gender HAVING n1.id < n2.id ORDER BY n1.id, n2.id
そこで上の様に明示的に JOIN キーを指定して、異なる gender の値をもつもののみを結合するようにします。
id id pair m1 w1 m1 ♥ w1 m1 w2 m1 ♥ w2 m1 w3 m1 ♥ w3 m2 w1 m2 ♥ w1 m2 w2 m2 ♥ w2 m2 w3 m2 ♥ w3
うまく男女のマッチングだけが抽出できました。
Trump:同じ絵柄のカードの組み合わせ
トランプは様々な組み合わせを考えるための最適な材料です。トランプには異なる4種の絵柄があり、それぞれの絵柄で1〜13の値を持ったカードがあります。さらに(ここでは目立ちませんが)ジョーカーという存在もあります。
symbol,number ♦,1 ... ♦,13 ♤,1 ... ♤,13 ♣,1 ... ♣,13 ♡,1 ... ♡,13 joker,0
この53枚のトランプに対して、いくつか組み合わせを考えてみましょう。
課題5:異なる絵柄のカードの組み合わせを考える
課題4と近い、異なる絵柄の数字の組み合わせパターンから始めましょう。以降,jokerは省くことにします。
SELECT n1.symbol,n2.symbol, n1.number, n2.number FROM ( SELECT number, symbol FROM trump WHERE symbol!='joker' ) n1 JOIN ( SELECT number, symbol FROM trump WHERE symbol!='joker' ) n2 ON n1.symbol != n2.symbol ORDER BY n1.symbol, n2.symbol, n1.number, n2.number
symbol symbol number number ♡ ♣ 1 1 ♡ ♣ 1 2 ... ♡ ♣ 2 1 ...
2,028通りの組み合わせとなり、だいぶ多くなってきました。
課題6:同じ絵柄のカードであるが,同じ数字は省いた組み合わせを考える
同じ絵柄同士を抽出条件にして、課題3と同じ事をやります。
SELECT n1.symbol,n2.symbol, n1.number, n2.number FROM ( SELECT number, symbol FROM trump WHERE symbol!='joker' ) n1 JOIN ( SELECT number, symbol FROM trump WHERE symbol!='joker' ) n2 ON n1.symbol = n2.symbol HAVING n1.number < n2.number ORDER BY n1.symbol, n2.symbol, n1.number, n2.number
symbol symbol number number ♡ ♡ 1 2 ♡ ♡ 1 3 ... ♡ ♡ 1 13 ♡ ♡ 2 3 ...
こちらは312通りとなりました。
課題7:いくつかのカードが抜かれた部分トランプの存在する課題6での組み合わせを考える
symbol,number ♦,1 ♦,3 ♦,6 ♦,8 ♦,10 ♦,11 ♦,13 ♤,1 ♤,3 ♤,7 ♤,8 ♤,9 ♤,12 ♤,13 ♣,3 ♣,4 ♣,8 ♣,9 ♣,10 ♣,11 ♣,13 ♡,1 ♡,4 ♡,5 ♡,8 ♡,9 ♡,11 ♡,12
上のテーブルのように、いくつかのカードが抜けてしまったトランプに対して課題6と同様に以下のクエリを実行します。
SELECT n1.symbol,n2.symbol, n1.number, n2.number FROM ( SELECT number, symbol FROM trump_part WHERE symbol!='joker' ) n1 JOIN ( SELECT number, symbol FROM trump_part WHERE symbol!='joker' ) n2 ON n1.symbol = n2.symbol HAVING n1.number < n2.number ORDER BY n1.symbol, n2.symbol, n1.number, n2.number
symbol symbol number number ♡ ♡ 1 4 ♡ ♡ 1 5 ♡ ♡ 1 8 ♡ ♡ 1 9 ♡ ♡ 1 11 ♡ ♡ 1 12 ♡ ♡ 4 5
課題8:課題7で,絵柄を区別せず,数字の組み合わせの登場回数を考える
課題7のクエリを絵柄で区別せずに数字の組み合わせのみを考え、かつ組み合わせ毎の登場回数を算出してみましょう。
SELECT number1, number2, COUNT(1) AS cnt FROM ( SELECT n1.symbol,n2.symbol, n1.number AS number1, n2.number AS number2 FROM ( SELECT number, symbol FROM trump_part WHERE symbol!='joker' ) n1 JOIN ( SELECT number, symbol FROM trump_part WHERE symbol!='joker' ) n2 ON n1.symbol = n2.symbol HAVING n1.number < n2.number ) tmp GROUP BY number1, number2 ORDER BY cnt DESC, number1, number2
number1 number2 cnt 1 8 3 3 8 3 3 13 3 8 9 3 8 11 3 8 13 3 1 3 2 1 9 2 1 11 2
数字のペア:{1,8}, {3,8}, {3,13} などは全体で3回登場していることがわかります。
さて,この課題において
絵柄 → レシートID(またはユーザーID)
数字 → アイテムID
とマッピングすると、課題7では「同じレシート内に存在するアイテムの全ての組み合わせ」を考えている事に等しくなります。
レシートID レシートID アイテムID アイテムID ♡ ♡ 1 4 ♡ ♡ 1 5 ♡ ♡ 1 8 ♡ ♡ 1 9 ♡ ♡ 1 11 ◆ ◆ 1 12 ◆ ◆ 4 5
そして課題8は「全レシートを通じて同時購入されたアイテムペアの登場頻度をカウントする」のと同義となります。
アイテム1 アイテム2 共起回数 1 8 3 3 8 3 3 13 3 8 9 3 8 11 3 8 13 3 1 3 2 1 9 2 1 11 2
3. 53枚のトランプのペアの組み合わせを全て考える。
今回の課題はシンプルに,53種のカードのペアが取り得る組み合わせ(順序は別とします)を考えてみましょう。クエリは課題1と同じです。
SELECT n1.number, n2.number, n1.symbol, n2.symbol FROM ( SELECT symbol,number FROM trump ) n1, ( SELECT symbol,number FROM trump ) n2 ORDER BY n1.number, n2.number, n1.symbol, n2.symbol
number number symbol symbol 0 0 joker joker 0 1 joker ♡ 0 1 joker ♣ 0 1 joker ♤ ...
さて,このクエリで全部で何種類のペアが生成されたのでしょうか?
答えは 2,809 通り,これは 53×53 の値となっています。一般に n 通りの値のバリエーションを持つテーブルに対して,全ての組み合わせを求めるような計算では,その計算過程で n × n の組み合わせが生成され,処理されていきます。
ECの場合では,アイテムの品揃えが 10 万点以上,なんてところもざらにあると思います。そのような場合でバスケット分析を行う事は,100億通りの組み合わせが計算過程で考慮されることになります。
従来のシングルノードのデータベースではこのオーダーの計算量は対応できるレベルのものではありませんでした。インプットデータは小さくても組み合わせで「爆発」してしまうバスケット分析などが,簡単に実現できなかった原因の1つにこの問題があったのです。
課題10:53枚のトランプのトリプレット(三つ組み)の組み合わせを全て考える。
蛇足ですが,トランプの可能な3枚の組み合わせを考えると何通りの組み合わせが生まれてくるのでしょうか?
SELECT n1.number, n2.number, n3.number, n1.symbol, n2.symbol, n3.symbol FROM ( SELECT symbol,number FROM trump ) n1, ( SELECT symbol,number FROM trump ) n2, ( SELECT symbol,number FROM trump ) n3 ORDER BY n1.number, n2.number, n3.number, n1.symbol, n2.symbol, n3.symbol
number number number symbol symbol symbol 0 0 0 joker joker joker 0 0 1 joker joker ♡ 0 0 1 joker joker ♣ ...
答えは 53×53×53 = 148,877 通りです。
もちろん,組み合わせの順序を区別しなかったり,同じカードの重複を避ける(いくつかの課題で求めてみましたね)ことで計算量は 1/2 〜 1/10 程度に減らすことができますが,逆に言えばその程度にしか工夫余地が無い事を示していることになります。