在大查询中使用指定的event.value.string_value计数事件

Question

这是this question的后续问题，我有一个查询：

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'       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

返回：

​

​

现在，我想更新查询，以便只计算那些有

event_params.value.string_value like '%subscription%'

该事件的存储方式如下：

​

​

我补充说：

...
MAX(CASE WHEN event_name = 'purchase_completed'  and  ep.value.string_value like '%subscription%'   THEN 1 ELSE 0 end)   AS `has_purchase_completed`
    
  FROM
    `project.dataset.*`
    CROSS JOIN
    UNNEST(event_params) ep
...

这是只计算具有指定event_params.value.string_value的事件的正确方法吗？

这里我不确定CROSS JOIN的使用情况，但是为了避免出现这个错误，我已经看到了到达event_params values和keys的例子：

无法在3:20访问具有ARRAY>>类型的值的字段值。

使用时：

SELECT *
FROM `project.dataset.events_20210207` 
WHERE event_params.value. string_value like '%subscription%';

Answer 1

使用子查询可以更快：

...
MAX(CASE WHEN event_name = 'purchase_completed' and exists(select 1 from UNNEST(event_params) as e where e.value.string_value like '%subscription%') THEN 1 ELSE 0 end)   AS `has_purchase_completed`
....