BigQuery技术需求：查找始终执行指定事件的Firebase设备记录

我明白你现在需要从导入BigQuery的Firebase分析数据里，筛选出那些每一条交互记录都至少包含某个指定事件的设备——也就是用app_instance_id标识的设备，它的每一行Firebase日志（比如app_events_20160607里的行）中，event_dim.name数组里至少有一个是你要找的事件类型。

核心实现思路 #

要搞定这个需求，我们需要分三步验证：

• 先算出每个设备在表中的总记录行数（也就是该设备有多少条交互日志）
• 再统计该设备的日志里，有多少行包含了你要找的目标事件
• 最后筛选出「总记录数 = 含目标事件的记录数」的设备，这些就是始终触发该事件的设备

完整查询示例（以事件purchase为例） #

你可以把下面的purchase替换成你实际要找的事件名称，直接在BigQuery里运行#standardSQL：

#standardSQL
-- 第一步：统计每个设备的总交互记录数
WITH device_total_records AS (
  SELECT
    user_dim.app_info.app_instance_id AS device_id,
    COUNT(*) AS total_logs
  FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`
  GROUP BY device_id
),
-- 第二步：统计每个设备中包含目标事件的记录数
device_target_event_logs AS (
  SELECT
    user_dim.app_info.app_instance_id AS device_id,
    COUNT(*) AS logs_with_target_event
  FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`,
    UNNEST(event_dim) AS single_event
  WHERE single_event.name = 'purchase' -- 替换成你的目标事件名称
  GROUP BY device_id
)
-- 第三步：筛选出所有记录都包含目标事件的设备
SELECT
  dr.device_id,
  dr.total_logs,
  dt.logs_with_target_event
FROM device_total_records dr
INNER JOIN device_target_event_logs dt
ON dr.device_id = dt.device_id
WHERE dr.total_logs = dt.logs_with_target_event

关键细节解释 #

• 用UNNEST(event_dim)把事件数组展开成单行，这样我们能逐个检查每个事件的名称
• 两个CTE（公共表表达式）分别统计总记录数和达标记录数，逻辑清晰易维护
• 如果需要查看这些设备的具体事件日志，可以在查询末尾加入关联原表的逻辑，比如：

-- 扩展：查看符合条件设备的所有原始事件数据
SELECT
  user_dim.app_info.app_instance_id AS device_id,
  event_dim
FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160607`
WHERE user_dim.app_info.app_instance_id IN (
  SELECT dr.device_id
  FROM device_total_records dr
  INNER JOIN device_target_event_logs dt
  ON dr.device_id = dt.device_id
  WHERE dr.total_logs = dt.logs_with_target_event
)

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