データサイエンティストのためのHiveQL分析クエリテンプレート その４

*トレジャーデータはデータ収集、保存、分析のためのエンドツーエンドでサポートされたクラウドサービスです。

データサイエンティストのためのHiveQL分析クエリテンプレートシリーズ：

• その１, その２, その３, その４, その５, その６

C. バスケット分析

バスケット分析はアイテム間の関連度を知るための重要な指標であり，レコメンデーションのロジックとしても利用されています。ここでは最も単純な共起頻度による集計パターンを示します。

組み合わせ

まずは1つのテーブル内での組み合わせを求める事を考えます。以下の例は login ログに対して，同じ月で日が異なる日付の組み合わせ（同じ日付同志は除外）を全網羅しようとしたものです。この方法はかなり力業となりますのでレコード数が多いデータには適応できていないことに注意してください。

SELECT t1.d AS a, t2.d AS b
FROM
(
SELECT TD_TIME_FORMAT(time,'yyyy-MM-dd') AS d, TD_TIME_FORMAT(time,'yyyy-MM') AS m
FROM login
GROUP BY TD_TIME_FORMAT(time,'yyyy-MM-dd'), TD_TIME_FORMAT(time,'yyyy-MM')
) t1
JOIN
(
SELECT TD_TIME_FORMAT(time,'yyyy-MM-dd') AS d, TD_TIME_FORMAT(time,'yyyy-MM') AS m
FROM login
GROUP BY TD_TIME_FORMAT(time,'yyyy-MM-dd'), TD_TIME_FORMAT(time,'yyyy-MM')
) t2
ON (t1.m=t2.m)
GROUP BY t1.d, t2.d
HAVING t1.d!=t2.d
ORDER BY a, b

例 

上記では「グループ=月」・「グループ内アイテム=日」を考え，同グループ内のアイテムの組み合わせを考えたことになります。

POSデータなどにおいては「グループ=レシート」・「グループ内アイテム=レシート内の購入アイテム」となり，1つのレシートで購入されたアイテムの組み合わせを考え，全レシートで見たときにその組み合わせが何回登場したのかを求めるのが共起頻度を求めることになります。

定義

Basket('freq', item_col, receipt_col) をレシート単位によるアイテム間の「共起頻度」と定義する。

パターン：Basket< 'freq', item_col , receipt_col>

SELECT t1.item_id, t2.item_id, COUNT(1) AS cnt

FROM

(

  SELECT receipt_id, item_id

  FROM receipt

  WHERE condition

  GROUP BY receipt_id, item_id

) t1

JOIN

(

  SELECT receipt_id, item_id

  FROM receipt

  WHERE condition

  GROUP BY receipt_id, item_id

) t2

ON

(t1.receipt_id = t2.receipt_id)

GROUP BY t1.item_id, t2.item_id

HAVING t1.item_id != t2.item_id

ORDER BY cnt DESC

LIMIT n

例

上記の集計方法では (a,b) と (b,a) の両方を求めてしまうために，実際に結果を使用する際には奇数行または偶数行のみを使用します。

このアイデアを発展させていけば，共起頻度以外にも cos 距離なども求められます。

トレジャーデータに関するお問い合わせは support@treasure-data.com まで。