やりたいこと

BigQuery に GA4 のデータが蓄積されている状態で、以下のように user_id に null がある場合、同じ user_pseudo_id であれば、その直近の過去の user_id で置換をしたい。

実現方法

FIRST_VALUE、または LAST_VALUE を使用して以下のクエリで実現できた。

上記のsampleテーブル作成用クエリ

WITH sample AS (
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 10:00:00' AS event_datetime
  UNION ALL
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 11:00:00' AS event_datetime
  UNION ALL
  SELECT
    'A' AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 12:00:00' AS event_datetime
  UNION ALL
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 13:00:00' AS event_datetime
  UNION ALL
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 14:00:00' AS event_datetime
  UNION ALL
  SELECT
    'B' AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 15:00:00' AS event_datetime
  UNION ALL
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 16:00:00' AS event_datetime
  UNION ALL
  SELECT
    null AS user_id
    , '123456789' AS user_pseudo_id
    , '2022-08-03 17:00:00' AS event_datetime
)

SELECT * FROM sample

SELECT
  user_id
  , FIRST_VALUE(user_id IGNORE NULLS)
    OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_datetime ASC
      ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING
    ) AS most_recent_user_id_first_val
  , LAST_VALUE(user_id IGNORE NULLS)
    OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_datetime DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS most_recent_user_id_last_val
  , user_pseudo_id
  , event_datetime
FROM sample
ORDER BY event_datetime

user_id	most_recent_user_id_first_val	most_recent_user_id_last_val	user_pseudo_id	event_datetime
null	A	A	123456789	2023/05/01 10:00:00
null	A	A	123456789	2023/05/01 11:00:00
A	A	A	123456789	2023/05/01 12:00:00
null	B	B	123456789	2023/05/01 13:00:00
null	B	B	123456789	2023/05/01 14:00:00
B	B	B	123456789	2023/05/01 15:00:00
null	null	null	123456789	2023/05/01 16:00:00
null	null	null	123456789	2023/05/01 17:00:00

参考