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

この記事は最終更新から3年以上が経過しています。最新情報は担当のカスタマーサクセスにご確認ください。

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

• その１
• その２
• その３
• その4
• その５
• その６

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 距離なども求められます。