同一 timestamp に分裂した画面遷移ログを BigQuery で 1 行に正規化する方法

🧩 実務で困ったこと

画面遷移ログ（page view ログ）を扱っていると、
同じユーザーが “同一時刻（マイクロ秒まで同一）” に複数のイベントを送ってくる
という現象が時々あります。

例として次のようなログが連続して飛んでくるケースです。

timestamp	referrer	url
2025-03-01 12:00:00.123	null	home
2025-03-01 12:00:00.123	home	content

本来であれば
「home → content」
という 1 回の画面遷移を表したいはずですが、実際には 2 行 のログとして観測されます。
アプリのログ送信タイミングやバッチ送信仕様により、複数イベントに同一 timestamp が付与されることは実務では珍しくありません。

このようなログをそのまま使うと、

• 「遷移元ページ（referrer）」が分からない行が混ざる
• 1 回の遷移を 2 回とカウントしてしまう
• ユーザーの行動経路（ファネル）が正しく再現されない

といった集計の不整合が生じます。

そこで、同一 timestamp のログを “1 つの遷移イベント” として正しくまとめる処理が必要になります。

💭 考えたこと

1 回の遷移の中で referrer → url が連続して飛ぶだけなので、
同一 timestamp のログをまとめて「1 回の遷移」として扱う
のが最も自然です。

やることは次の 3 ステップです：

1. 同一 timestamp のログを 1 グループにまとめる
2. referrer を配列化（referrers）、url を配列化（urls）する
3. • referrers の中で、urls に含まれないもの → 正しい遷移元
   • urls の中で、referrers に含まれないもの → 正しい遷移先

という形で最終的な遷移元・遷移先を決めます。

これは 「集合の差集合をとる」 と同じ考え方です。

🧮 最終的に採用した方法

WITH 
raw AS (
    SELECT
        user_id,
        ts,
        ARRAY_AGG(referrer IGNORE NULLS) AS referrers,
        ARRAY_AGG(url) AS urls
    FROM
        pageview_logs
    GROUP BY
        user_id,
        ts
),

normalized AS (
    SELECT
        user_id,
        ts,
        (
            SELECT
                r
            FROM
                UNNEST(referrers) AS r
            WHERE 
                r NOT IN UNNEST(urls)
            LIMIT 1
        ) AS final_referrer,

        (
            SELECT
                u
            FROM
                UNNEST(urls) AS u
            WHERE
                u NOT IN UNNEST(referrers)
            LIMIT 1
        ) AS final_url
    FROM
        raw
)

SELECT * FROM normalized;

この処理により、1 回の遷移を正しく復元できます。