如何在BigQuery中对GA4事件的多个event_params进行过滤

你遇到的问题是SQL执行顺序导致的别名不可用：WHERE子句的执行优先级高于SELECT，所以在WHERE里无法直接引用SELECT中定义的page_path这类别名。下面提供三种可行的解决方案，按需选择：

方案一：WHERE子句中直接使用子查询匹配参数 #

直接在WHERE里重复SELECT中提取参数的逻辑，不需要额外嵌套：

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path
FROM `project.dataset.events_*`
WHERE (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') = "/"
  -- 可添加多个过滤条件
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') = "/about"

方案二：用CTE提前提取字段再过滤 #

通过公共表表达式（CTE）先把需要的参数提取为字段，再在后续查询中过滤，可读性更强：

WITH event_extracted AS (
  SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path') AS page_path,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_page_path') AS previous_page_path,
    event_date, event_name -- 保留需要的其他原始字段
  FROM `project.dataset.events_*`
)
SELECT page_path, previous_page_path, event_date, event_name
FROM event_extracted
WHERE page_path = "/"
  AND previous_page_path = "/about"

方案三：UNNEST结合条件聚合（高性能场景） #

如果需要处理大量event_params，这种方法通过提前筛选目标key减少数据量，性能更优：

SELECT 
  MAX(CASE WHEN ep.key = 'page_path' THEN ep.value.string_value END) AS page_path,
  MAX(CASE WHEN ep.key = 'previous_page_path' THEN ep.value.string_value END) AS previous_page_path,
  event_date, event_timestamp -- 按事件唯一标识分组
FROM `project.dataset.events_*`,
UNNEST(event_params) AS ep
-- 只展开需要的参数，降低处理数据量
WHERE ep.key IN ('page_path', 'previous_page_path')
GROUP BY event_date, event_timestamp, event_name
-- 用HAVING过滤聚合后的字段
HAVING page_path = "/"
  AND previous_page_path = "/about"

方法对比 #

• 方案一：写法简洁，但过滤条件较多时会有重复代码
• 方案二：逻辑清晰，便于维护，适合复杂过滤场景
• 方案三：性能最优，适合处理大规模GA4事件数据

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