6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Last updated at Posted at 2020-12-06

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

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

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

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を利用することで、計算を簡単にすることができます。

6
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?