代码之家 › 专栏 › 技术社区 › d_-

按用户Id分组的BigQuery Firebase事件日志

firebase-analytics google-bigquery firebase

d_- · 技术社区 · 7 年前

问题: 我想为我的android用户拉一个按用户id分组并按发生时间排序的Firebase事件数据流。我创建了下面的两个脚本,但不幸的是,在这两个脚本中,我似乎都无法得到正确的最后一部分,也就是说,成功地将所有app_instance_id先分组,不考虑时间戳。我是否应该考虑使用不同的用户id?

失败的尝试1:

SELECT
  d.userid,
  c.ev_timestamp,
  c.ev_name
FROM (SELECT 
        user_dim.app_info.app_instance_id as userid
      FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
      WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      GROUP BY 1) AS d
  LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
            event.timestamp_micros as ev_timestamp,
            event.name as ev_name
            FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
            WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
            AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
  ON d.userid = c.userid
ORDER BY 2 ASC
LIMIT 1000;

不成功的尝试2:

SELECT
  d.userid,
  d.ev_timestamp,
  c.ev_name
FROM (SELECT 
        user_dim.app_info.app_instance_id as userid,
        event.timestamp_micros as ev_timestamp
      FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
      WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      GROUP BY 1,2
      ORDER BY 2 ASC) AS d
  LEFT JOIN (SELECT user_dim.app_info.app_instance_id as userid,
            event.timestamp_micros as ev_timestamp,
            event.name as ev_name
            FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS event
            WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
            AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000) AS c
  ON d.userid = c.userid AND d.ev_timestamp = c.ev_timestamp
#ORDER BY 2 ASC
LIMIT 1000;

正确答案(Amod的答案转换为新的导出架构):

SELECT user_pseudo_id, event_timestamp, event_name
FROM `xxxx.analytics_xxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180702'
AND user_first_touch_timestamp BETWEEN 1530453600000000 AND 1530468000000000
AND platform = "ANDROID"
ORDER BY 1,2 ASC
LIMIT 1000

1 回复 | 直到 7 年前

Amod Gokhale 7 年前

试试这个

    SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name,X
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      and user_dim.app_info.app_instance_id like 'C4%'
      order by 1,2 ASC

或

SELECT user_dim.app_info.app_instance_id as userid, X.timestamp_micros,X.name as ev_name
FROM `firebase-analytics-sample-data.ios_dataset.app_events_*`, UNNEST(event_dim) AS X
WHERE _TABLE_SUFFIX BETWEEN '20160601' AND '20160603'
      AND user_dim.first_open_timestamp_micros BETWEEN 1464789600000000 AND 1464962400000000
      order by 1,2 ASC