こんにちは、自由になりたいデータサイエンティストです。

今回は、BigQueryでお手軽に実行できる分析手法、相関ルールをご紹介します。

相関ルールとは

相関ルールは、互いに関連が強い組み合わせを見つけるための方法の一つです。

簡単に言えば、
　　この商品（A）を買っているひとは、この商品（B）もよく買っています
というやつです。

もっと一般的に
　　AとBを買っている人はCとDも買っています
という形式も定義できますが、この記事ではペアについてのみ取り上げることにします。

相関ルールで抽出した組み合わせを使えば、
　　ECサイトなどでAをカートに入れた人にBを薦める
だけでなく、
　　実店舗でAのそばにBを置いておく
　　ブログ記事内で別の記事へ誘導する
といった使い方もできそうですね。

対象データ形式

対象となるデータは、トランザクションデータと言われる形式です。

通販サイトで例えると、各決済（トランザクション）にどんな商品（アイテム）が含まれるかを表したデータになります。

決済番号	玉子	牛乳	パン	お米
①	1	0	1	0
②	1	2	1	0
③	1	0	0	1

また、以下のような形式で書くこともできます。

実際の相関ルール分析では、購入有無のみを利用して上記のはじめの2列だけを用いることが多いと思います。
この記事でも、その形式で進めていきます。

分析コンセプト

各組合せを3つの指標で評価します。

• 支持度（support）
• 確信度（confidence）
• リフト（lift）

以下で簡単に説明していきます。
知ってるよ！って方は飛ばしてコードに進んでください。

支持度

全トランザクションのうち、対象の組み合わせが現れる割合を表します。

例えば「玉子とパン」の組み合わせは「3決済中2決済」で買われているため、
　　2÷3=0.666…
となります。

この値が大きいと、その組み合わせを買っている人が多いと判断することができます。

確信度

Bを含むトランザクションのうち、Aを含むトランザクション（B⇒A）の割合を表します。

「パンを含む決済は２つ、そのうち牛乳を含む決済は１つ」なので、
パンから牛乳（パン⇒牛乳）への確信度は
　　1÷2=0.5
となります。

一方、「牛乳を含む決済は１つ、そのうちパンを含む決済は１つ」なので、
牛乳からパン（牛乳⇒パン）への確信度は
　　1÷1=1.0
となります。

このように、確信度には向き（B⇒A、A⇒B）があるので注意してください。

この値が大きいと、AはBを買っている人から人気と考えることができます。

リフト

例えば、B⇒Aの確信度（Bを買った人のうち、Aを買った人の割合）が90%あったとしても、
ただ全体の90％の人がAを買っているだけという可能性もあります。

そこで、
　B⇒Aの確信度
と
　A単体の支持度
のどちらが多いかを比較したものがリフトです。

計算は、【B⇒Aの確信度÷A単体の支持度】で行われます。
値が１より大きい場合、Bを買っている人は特にAを買いやすいと判断することができます。

例えば、
　　パン⇒玉子の確信度は100％
と高いですが、
　　玉子単体の支持度も100％
のため、リフトは１です。
そもそも玉子が買われやすいため、
「パンを買った人が特に玉子を買いやすい」とは言えないわけです。

指標の使い方

よくある使い方は、
　　確信度とリフトが一定以上の組み合わせのみを抽出
　　→残った組み合わせを支持度が大きい順に並び替え
があります。

確信度とリフトは足切りを行い、あとは支持度で良さを判断ということですね。

SQLを用いた抽出方法

元テーブル定義

改めて元のテーブル形式を定義します。

テーブル（data_tbl）にはidカラムとitemカラムがあり、
それぞれトランザクションID、アイテム名が入っているものとします。

id	item
1	egg
1	milk
2	milk
3	break

全トランザクション数のカウント

全体のトランザクション数を数えます。
トランザクションIDのユニークカウントを取ればいいですね。

with
total_cnt_tbl as(
  select count(distinct id) as total_cnt
  from data_tbl
),

単体出現回数の集計

リフトの計算で用いる単体支持度を計算します。
各itemを含むトランザクション数÷全トランザクション数で計算できます。

single_tbl as(
  select item, count(distinct id) as single_cnt
  from data_tbl
  group by 1
),

itemでgroup byして、出現回数をcount(distinct id)でカウントしています。
（group by 1は、1番目のカラムでグループ化するという意味です。）

ペア出現回数の集計

各組み合わせが出現するトランザクション数をカウントします。

pair_tbl as(
  select l.item as item_A, r.item as item_B, count(distinct id) as pair_cnt
  from data_tbl as l
  inner join data_tbl as r
  using(id)
  where l.item != r.item
  group by 1, 2
),

すこし複雑になっています。

まず、data_tblは１行に１アイテムになっているため、ペアを作るためにdata_tbl同士でjoinしています。
これにより、joinの左側と右側それぞれに１アイテムある形になり、アイテムのペアが作られます。

using(id)は、lとrでidが等しい行のみ残してjoinするという意味です。
on l.id = r.idとほとんど一緒の挙動をしますが、結合後のidカラムは一つしか残りません。
いちいちl.idとか書かなくていいので便利です。

where l.item != r.itemで、同じitemをペアとすることを防止しています。

最後に、group by 1, 2で左側（item_A）と右側（item_B）のアイテムでグループ化し、
count(distinct id)でトランザクションを数えています。

これで、準備は整いました。

各指標の計算

ここまでで作った3つのテーブルを組み合わせて指標を計算していきます。

value_tbl as(
  select item_A, item_B,
    pair_cnt / total_cnt as support,
    pair_cnt / b.single_cnt as confidence_BtoA,
    (pair_cnt / b.single_cnt) / (a.single_cnt / total_cnt) as lift
  from pair_tbl
  left join single_tbl as a
    on item_A = a.item
  left join single_tbl as b
    on item_B = b.item
  cross join total_cnt_tbl
)

見た目は少し複雑ですが、一つ一つは難しくないので読み解いてみてください。

条件を指定して出力

あとは、好みの足切り条件を指定して最終アウトプットを出すだけです。

select *
from value_tbl
where  confidence >=  0.7
  and lift > 1.1
order by support desc

この辺りの数値感は実際のデータを眺めながら決めてもいいと思います。

最後に

いかがでしたでしょうか？

ちょっとややこしいクエリに見えるかもしれませんが、たった30行程度で相関ルールを抽出することができました！

計算量が大きいので、限られたリソースで実行する際にはメモリや実行時間に注意してください。
また、データ量課金以外のSQLサービスを利用する場合は料金にも注意が必要です。

基本的にはBigQueryやRedshiftといったサービス上で利用することをお勧めします。

相関ルール分析は手軽で良精度の推薦システムが作れるので、ぜひ試してみてください！

最後に、全部まとめたクエリを載せておきます。

with
total_cnt_tbl as(
  select count(distinct id) as total_cnt
  from data_tbl
),
single_tbl as(
  select item, count(distinct id) as single_cnt
  from data_tbl
  group by 1
),
pair_tbl as(
  select l.item as item_A, r.item as item_B, count(distinct id) as pair_cnt
  from data_tbl as l
  inner join data_tbl as r
  using(id)
  where l.item != r.item
  group by 1, 2
),
value_tbl as(
  select item_A, item_B,
    pair_cnt / total_cnt as support,
    pair_cnt / b.single_cnt as confidence_BtoA,
    (pair_cnt / b.single_cnt) / (a.single_cnt / total_cnt) as lift
  from pair_tbl
  left join single_tbl as a
    on item_A = a.item
  left join single_tbl as b
    on item_B = b.item
  cross join total_cnt_tbl
)

select *
from value_tbl
where  confidence >=  0.7
  and lift > 1.1
order by support desc