生成UUID关联CTE返回空集及GA4事件参数筛选查询问题

问题背景 #

需要从GA4事件表 ga4-extract.analytics_1234567.events_20220831 中完成以下操作：

• 筛选出event_params里存在key = 'page_location'且value.string_value匹配%example.dev%的记录
• 同时提取对应记录中event_params里key = 'event_id'的value.string_value值

原查询通过生成UUID关联CTE不仅冗长，还返回空数据集，无法实现需求。

错误原因 #

原查询中使用GENERATE_UUID()生成临时UUID作为关联键完全没必要——GA4事件表本身可通过自带字段（如event_timestamp+user_pseudo_id）或直接在单查询中处理嵌套字段实现关联，额外生成UUID反而容易导致关联逻辑失效。

优化后的查询方案 #

方案一：简化CTE写法（无需手动生成UUID） #

WITH filtered_events AS (
  SELECT 
    *
  FROM `ga4-extract.analytics_1234567.events_20220831`
  WHERE 
    event_name IN ('trial', 'login')
    -- 先过滤出包含目标page_location的记录
    AND EXISTS (
      SELECT 1 
      FROM UNNEST(event_params) e
      WHERE e.key = 'page_location' 
        AND e.value.string_value LIKE '%example.dev%'
    )
),
event_id_extracted AS (
  SELECT
    -- 用GA4自带的唯一标识组合做关联键
    event_timestamp,
    user_pseudo_id,
    (SELECT e.value.string_value FROM UNNEST(event_params) e WHERE e.key = 'event_id') AS event_id
  FROM filtered_events
)

SELECT
  fe.*,
  eie.event_id
FROM filtered_events fe
JOIN event_id_extracted eie 
  ON fe.event_timestamp = eie.event_timestamp 
  AND fe.user_pseudo_id = eie.user_pseudo_id

方案二：更简洁的单查询写法 #

直接在主查询中完成筛选和字段提取，无需CTE：

SELECT
  *,
  -- 直接从event_params中提取event_id
  (SELECT e.value.string_value FROM UNNEST(event_params) e WHERE e.key = 'event_id') AS event_id
FROM `ga4-extract.analytics_1234567.events_20220831`
WHERE
  event_name IN ('trial', 'login')
  -- 筛选符合条件的page_location记录
  AND EXISTS (
    SELECT 1 
    FROM UNNEST(event_params) e
    WHERE e.key = 'page_location' 
      AND e.value.string_value LIKE '%example.dev%'
  )

说明 #

• 使用EXISTS子句先过滤目标记录，避免不必要的unnest操作，提升查询效率
• 通过子查询直接提取event_id，无需额外关联逻辑
• 如果表中存在原生event_id字段，可直接用它替代event_timestamp + user_pseudo_id作为关联键，逻辑更严谨

内容的提问来源于stack exchange，提问作者Doug Fir