BigQuery SQL：按事件顺序过滤

Roger 发表于 Dev

罗杰

我想算，每个app_id，多次如何event_type：store_app_view其次是event_type：store_app_download对于同一用户（“后面”，这意味着event_time_utc的store_app_view是比旧event_time_utc的store_app_download）。

样本数据：

WITH 
`project.dataset.dummy_data_init` AS (SELECT event_id FROM UNNEST(GENERATE_ARRAY(1, 10000)) event_id),

`project.dataset.dummy_data_completed` AS (SELECT event_id,
    user_id[OFFSET(CAST(20 * RAND() - 0.5 AS INT64))] user_id,
    app_id[OFFSET(CAST(100 * RAND() - 0.5 AS INT64))] app_id,
    event_type[OFFSET(CAST(6 * RAND() - 0.5 AS INT64))] event_type,
    event_time_utc[OFFSET(CAST(26 * RAND() - 0.5 AS INT64))] event_time_utc
  FROM `project.dataset.dummy_data_init`, 
     (SELECT GENERATE_ARRAY(1, 20) user_id),
     (SELECT GENERATE_ARRAY(1, 100) app_id),
     (SELECT ['store_app_view', 'store_app_view', 'store_app_download','store_app_install','store_app_update','store_fetch_manifest'] event_type),
     (SELECT GENERATE_TIMESTAMP_ARRAY('2020-01-01 00:00:00', '2020-01-26 00:00:00',
                                INTERVAL 1 DAY) AS event_time_utc))

Select * FROM `project.dataset.dummy_data_completed`

谢谢！

戈登·利诺夫

我想为每个app_id计算event_type：store_app_view之后跟着event_type：store_app_download多少次。

您提供的查询似乎与此问题几乎没有任何联系，因此我将忽略它。

对于每个用户/应用对，您可以使用来获取符合条件的行GROUP BY：

select user_id, app_id
from t
group by user_id, app_id
having min(case when event_type = 'store_app_view' then event_time end) <
       max(case when event_type = 'store_app_download' then event_time end);

要获取每个的总计app，请使用子查询或CTE：

select app_id, count(*)
from (select user_id, app_id
      from t
      group by user_id, app_id
      having min(case when event_type = 'store_app_view' then event_time end) <
             max(case when event_type = 'store_app_download' then event_time end)
     ) ua
group by app_id;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。