在BigQuery中统计含指定event_params.value.string_value的事件及查询修改正确性验证

你的思路方向是对的——因为event_params是一个STRUCT数组，直接访问event_params.value.string_value会报错（就像你遇到的那样），必须先将数组展开才能访问里面的元素。不过直接用CROSS JOIN UNNEST会有一些潜在问题，这里给你梳理下正确的实现方式和优化建议：

为什么直接查询会报错？ #

event_params的类型是ARRAY<STRUCT<key STRING, value STRUCT<...>>>，数组类型的字段不能直接通过.访问内部元素，必须先通过UNNEST将数组拆分成多行，才能单独处理每个STRUCT元素。这就是你之前执行SELECT * FROM ... WHERE event_params.value.string_value like '%subscription%'时出错的原因。

你的UNNEST方法的问题 #

使用CROSS JOIN UNNEST(event_params)确实能展开数组，但会导致同一个事件（比如一个purchase_completed事件有多个params）被拆分成多行。虽然最终通过MAX(CASE...)还是能得到正确的has_purchase_completed标记（只要有一个符合条件的param就会返回1），但这种方式会增加不必要的数据行数，降低查询效率，甚至可能在复杂场景下引发统计误差。

更高效且可靠的实现方式 #

推荐使用EXISTS子查询来检查数组中是否存在符合条件的元素，不需要展开整个数组，既高效又避免重复行问题。修改后的查询如下：

WITH user_summary AS (
 SELECT geo.country as country, platform, event_date, user_pseudo_id,
 MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 end) AS `has_session_start`,
 MAX(CASE WHEN event_name = 'purchase_preview_page' THEN 1 ELSE 0 end) AS `has_purchase_preview_page`,
 MAX(CASE WHEN event_name = 'purchase_trial_activated' THEN 1 ELSE 0 end) AS `has_purchase_trial_activated`,
 MAX(CASE 
        WHEN event_name = 'purchase_completed' 
        AND EXISTS (
          SELECT 1 FROM UNNEST(event_params) ep
          WHERE ep.value.string_value LIKE '%subscription%'
        ) 
        THEN 1 ELSE 0 
      END) AS `has_purchase_completed`
 FROM `project.dataset*`
 WHERE event_date > '20200101'
 GROUP BY geo.country, platform, event_date, user_pseudo_id
)
SELECT country, platform, event_date,
 SUM(has_session_start) AS count_session_start,
 SUM(has_purchase_preview_page) AS count_purchase_preview_page,
 SUM(has_purchase_trial_activated) AS count_purchase_trial_activated,
 SUM(has_purchase_completed) AS count_purchase_completed,
 SUM(has_purchase_trial_activated * has_purchase_completed) AS count_trial_activated_and_purchased
FROM user_summary
GROUP BY country, platform, event_date

这种方式的优势： #

• 效率更高：不需要展开整个数组，只通过子查询检查是否存在符合条件的元素，减少了数据处理量。
• 避免重复统计：不会因为数组展开导致同一用户/事件被多次计算，统计结果更准确。
• 代码更简洁：逻辑集中在CASE语句内部，不需要修改FROM子句的结构，可读性更好。

如果一定要用UNNEST怎么办？ #

如果你坚持使用UNNEST的方式，需要确保在分组时不会重复统计同一用户的事件。可以通过先过滤出符合条件的事件，再进行聚合：

WITH filtered_events AS (
  SELECT 
    geo.country, 
    platform, 
    event_date, 
    user_pseudo_id,
    event_name,
    -- 标记当前事件是否是符合条件的purchase_completed
    CASE 
      WHEN event_name = 'purchase_completed' 
        AND EXISTS (SELECT 1 FROM UNNEST(event_params) ep WHERE ep.value.string_value LIKE '%subscription%')
      THEN 1 
      ELSE 0 
    END AS is_valid_purchase
  FROM `project.dataset*`
  WHERE event_date > '20200101'
),
user_summary AS (
 SELECT country, platform, event_date, user_pseudo_id,
 MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 end) AS `has_session_start`,
 MAX(CASE WHEN event_name = 'purchase_preview_page' THEN 1 ELSE 0 end) AS `has_purchase_preview_page`,
 MAX(CASE WHEN event_name = 'purchase_trial_activated' THEN 1 ELSE 0 end) AS `has_purchase_trial_activated`,
 MAX(is_valid_purchase) AS `has_purchase_completed`
 FROM filtered_events
 GROUP BY country, platform, event_date, user_pseudo_id
)
-- 后续SELECT部分和原查询一致
SELECT country, platform, event_date,
 SUM(has_session_start) AS count_session_start,
 SUM(has_purchase_preview_page) AS count_purchase_preview_page,
 SUM(has_purchase_trial_activated) AS count_purchase_trial_activated,
 SUM(has_purchase_completed) AS count_purchase_completed,
 SUM(has_purchase_trial_activated * has_purchase_completed) AS count_trial_activated_and_purchased
FROM user_summary
GROUP BY country, platform, event_date

这种方式先在filtered_events里处理好标记，再进行聚合，避免了UNNEST带来的重复行问题。

总结一下：你的核心思路（通过UNNEST访问数组元素）是正确的，但用EXISTS子查询的方式是更优的选择，既高效又能保证统计准确性。

内容的提问来源于stack exchange，提问作者Jonas Palačionis