履歴データから共起をSQLで算出する

概要

商品と購入者情報を含む商品購入履歴があった時、ユーザーベース協調フィルタリングを算出するためにSQLを作ってみました。レコメンドシステムに活用できます。

今回は商品product_id=123と、その他商品の共起を取得します。このSQLで取得できない商品は共起数0です。

SELECT
 t.id,
 count(*) AS cooccurrence, #共起している数
 (SELECT COUNT(DISTINCT(u.user_id)) FROM history u WHERE (u.product_id=123 OR u.product_id=t.product_id) AS base #母数
FROM history t
WHERE t.id IN (SELECT s.id from history s WHERE s.product_id=123)
AND t.product_id <> 123
GROUP BY t.product_id;

cooccurrence / baseでJaccard係数を計算できます。

※突っ込み所があったら、指摘していただけると助かります。

環境

• MySQL 5.6