SQLで相関ルールを抽出する

購買分析 SQL

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

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

相関ルールとは

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

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

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

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

といった使い方もできそうですね。


対象データ形式

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

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

決済番号玉子牛乳パンお米
1010
1210
1001
トランザクションデータイメージ
各トランザクションに各アイテムがどのくらい含まれるかを表している

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

決済番号アイテム名購入数
玉子1
パン1
玉子1
牛乳2
パン1
玉子1
お米1
トランザクションデータの別形式

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

分析コンセプト

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

  • 支持度(support)
  • 確信度(confidence)
  • リフト(lift)

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

支持度

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

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

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

確信度

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

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

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

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

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

リフト

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

そこで、
 B⇒Aの確信度

 A単体の支持度
のどちらが多いかを比較したものがリフトです。

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

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

指標の使い方

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

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

SQLを用いた抽出方法

元テーブル定義

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

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

iditem
1egg
1milk
2milk
3break
data_tbl定義

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

全体のトランザクション数を数えます。
トランザクション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
),

itemgroup 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_tbl1行に1アイテムになっているため、ペアを作るためにdata_tbl同士でjoinしています。
これにより、join左側と右側それぞれに1アイテムある形になり、アイテムのペアが作られます。

using(id)は、lridが等しい行のみ残して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

タイトルとURLをコピーしました