1. はじめに 

バスケット分析（併売分析）は，アウトプットイメージやその分析の有用性についてはマーケティング業界の誰もが周知しているのに反して，実際にそれを実践できている所はそれ程多くはありません。

マーケット バスケット分析の使用 - Tableau

また、実践できているにしても上の 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 程度に減らすことができますが，逆に言えばその程度にしか工夫余地が無い事を示していることになります。

  • No labels

Copyright © 2021 Treasure Data, Inc. (or its affiliates). All rights reserved.