[bigquery]商品IDから組合わせレコードを作る方法

2023-09-26 2023-10-20

WITH 
pairs AS (
  SELECT 
    a1.productId AS productId_1,
    a2.productId AS productId_2,
    COUNT(*) AS cnt
  FROM
    dataset.table AS a1
    JOIN 
      dataset.table AS a2
    ON 
      a1.userId = a2.userId
  WHERE 
    a1.productId < a2.productId
  GROUP BY
    1, 2
)

SELECT
  *
FROM
  pairs
ORDER BY
  3 DESC;

１つのテーブルから、同一userIdで複数のproductIdを購入している。といった組合わせ件数を抽出するクエリ

→ アソシエーション分析などをする場合のベースレコード作成用。

集計のポイントとして、13-14行目のWHERE句の部分で、レコードが重複集計されるのを避けるためにproductId_1がproductId_2より小さい組合わせのみを選択するようにしてます。