BigQueryでコサイン距離やユークリッド距離を計算

BigQuery Advent Calendar 2020の6日目の記事です。

下記のようなテーブルデータで、各アイテム同士のコサイン距離やユークリッド距離をさくっとBigQueryで計算する方法を解説します。（セルフジョインっぽいやり方をしていて、BigQuery的に良いやり方ではないかもしれません..少量のデータでサクッと計算したいときにご参考ください。もっと良いやり方がありましたら、教えて下さい。）

今回のデータは、各アイテムに３つの属性値が付いていて、それを元に似ているをアイテムを抽出したい場合を考えます。また、各アイテムの属性は今後増えていくことも想定して、その場合でも対応できるクエリを考えます。

WITH
  item_vectors AS (
  SELECT
    item_id,
    ARRAY_AGG(attribute_value
    ORDER BY
      attribute_index) AS attribute_values
  FROM
    `xxx-yyy-zzz.sandbox.items`
  GROUP BY
    item_id )
SELECT
  item_vectors1.item_id,
  item_vectors2.item_id,
  (
  SELECT
    1-SUM(value1 * value2)/ SQRT(SUM(value1 * value1))/ SQRT(SUM(value2 * value2))
  FROM
    UNNEST(item_vectors1.attribute_values) AS value1
  WITH
  OFFSET
    pos1
  JOIN
    UNNEST(item_vectors2.attribute_values) AS value2
  WITH
  OFFSET
    pos2
  ON
    pos1 = pos2 ) AS cosine_distance,
  (
  SELECT
    SQRT( SUM((value1 - value2)*(value1 - value2)))
  FROM
    UNNEST(item_vectors1.attribute_values) AS value1
  WITH
  OFFSET
    pos1
  JOIN
    UNNEST(item_vectors2.attribute_values) AS value2
  WITH
  OFFSET
    pos2
  ON
    pos1 = pos2 ) AS euclidean_distance
FROM
  item_vectors item_vectors1
JOIN
  item_vectors item_vectors2
ON
  item_vectors1.item_id != item_vectors2.item_id
ORDER BY
  item_vectors1.item_id,
  item_vectors2.item_id

まずは、各アイテムごとに集約して、属性値をArrayとして持ちます。その後に、セルフジョインして、アイテム×アイテムの類似度を計算していきます。

コサイン距離やユークリッドの計算には、UNNESTとOFFSETを利用することで、計算を簡単にすることができます。