スキャン量を抑えるための TEMP TABLE 活用術

🧩 実務で困ったこと

BigQuery で「A を買った人はどんな商品を一緒に買いがちか？」といった、
いわゆるバスケット分析・共起分析をしたい場面があります。

たとえば、購買ログが次のような構造だとします。

• user_id
• order_id（またはセッションID）
• item_id
• purchased_at

ここから、

• 同じ user_id・同じ order_id の中で一緒に購入された商品のペア
• そのペアがどれくらいの頻度で登場するか

を集計したい場合、典型的には 自己結合（self join） を使ったクエリになります。

例:

SELECT
    a.item_id AS item_a,
    b.item_id AS item_b,
    COUNT(DISTINCT a.order_id) AS cooccurrence_count
FROM
    `project.dataset.orders` AS a
INNER JOIN
    `project.dataset.orders` AS b
ON
    a.user_id  = b.user_id
    AND a.order_id = b.order_id
    AND a.item_id < b.item_id   -- 同じペアを重複して数えないための条件
GROUP BY
    item_a,
    item_b;

このように 同じテーブルを 2 回参照する 必要があるため、
元テーブル orders が 200GB だと、何も工夫しないと 単純に 400GB 分のスキャン が発生します。

前後のログを 1行だけ見るのであればウィンドウ関数で代替できますが、
このように「同一バスケット内のすべての組み合わせ」を取りたいケースでは、
どうしても自己結合が必要になり、スキャン量が膨らみやすいのが課題でした。

💭 試したこと

最初に思いつくのは、CTE（WITH句）にテーブルを入れる方法です。

WITH base AS (
    SELECT
        *
    FROM
        `project.dataset.orders`
)
SELECT
    ...
FROM
    base AS a
INNER JOIN
    base AS b
...

しかし BigQuery の CTE は ビューのように毎回展開される（＝再利用されない） ため、スキャン量は 2 回分のまま変わりません。

🧮 最終的に採用した方法：CREATE TEMP TABLE を使う

そこで、実務では CTE ではなく CREATE TEMP TABLE を使うようにしています。

CREATE TEMP TABLE の場合はクエリ開始時に 1 回だけテーブルを読み込み、メモリ / ストレージに保持するため、後続で何回参照しても追加のスキャンは発生しません。

CREATE TEMP TABLE base AS 
SELECT
    *
FROM
    `project.dataset.orders`

SELECT
    ...
FROM 
    base AS a
INNER JOIN 
    base AS b
...

この方法だと：

• 元テーブルのスキャンは 1 回
• 以降は TEMP TABLE の参照だけなのでスキャン量 0
• 自己 JOIN が多いロジックほど効果が大きい

という形で、単純にスキャン量を半分に抑えられます。