运行BigQuery查询获取卸载前2个前置事件却无结果的技术求助

我帮你分析下为什么你的查询返回0条记录，以及如何调整来得到你想要的结果：

核心逻辑错误 #

你的查询逻辑搞反了！你写的WHERE eventName3 = "app_remove"是在找当前事件的前第二个事件是卸载事件，但你的需求是获取卸载事件发生前的两个关联事件——正确的逻辑应该是筛选当前事件就是app_remove，然后它的前1个和前2个事件才是你要的前置事件。卸载事件发生后用户通常不会再触发新事件，所以几乎不会有事件的前两个是卸载事件，这就是查询返回空的主要原因。

其他潜在问题 #

1. 过滤条件过于严格：原查询里的(eventParams.value.string_value = "app" OR event_name = "app_remove")会把很多卸载前的前置事件排除掉。这些前置事件可能不满足eventParams的条件，导致你根本获取不到卸载事件的完整前序序列。
2. 交叉UNNEST导致数据丢失：你直接用逗号连接UNNEST(user_properties)和UNNEST(event_params)，这属于交叉连接。如果某个事件没有符合account_id的userProp，或者没有符合条件的eventParams，这条记录会被直接丢弃，进一步减少了可匹配的数据。

调整后的查询 #

下面是修正后的SQL，完全贴合你的需求：

WITH user_event_sequence AS (
  SELECT 
    -- 处理accountID为空的情况，用unknown替代NULL
    COALESCE(userProp.value.string_value, 'unknown') AS accountID,
    app_info.firebase_app_id AS appInstanceID,
    event_timestamp AS eventTimestamp,
    event_name AS current_event,
    -- 取卸载事件的前1个关联事件
    LAG(event_name, 1) OVER (PARTITION BY app_info.firebase_app_id ORDER BY event_timestamp) AS prev_event_1,
    -- 取卸载事件的前2个关联事件
    LAG(event_name, 2) OVER (PARTITION BY app_info.firebase_app_id ORDER BY event_timestamp) AS prev_event_2
  FROM `app_name.app_events_20180303*`
  -- 用LEFT JOIN避免丢失没有account_id的用户记录
  LEFT JOIN UNNEST(user_properties) AS userProp 
    ON userProp.key = "account_id"
  -- 如果不需要过滤特定eventParams，直接去掉下面的JOIN；如果需要，改成LEFT JOIN
  -- LEFT JOIN UNNEST(event_params) AS eventParams
  --   ON eventParams.value.string_value = "app"
)
SELECT 
  prev_event_1,
  prev_event_2,
  COUNT(*) AS event_count
FROM user_event_sequence
-- 筛选当前事件为卸载事件
WHERE current_event = "app_remove"
-- 可选：如果只想统计有完整两个前置事件的卸载，加上下面的条件
-- AND prev_event_1 IS NOT NULL AND prev_event_2 IS NOT NULL
GROUP BY prev_event_1, prev_event_2
ORDER BY event_count DESC

验证步骤 #

你可以先单独运行CTE部分（user_event_sequence），查询是否存在current_event = "app_remove"的记录，以及它们的prev_event_1和prev_event_2是否有值，这样可以逐步确认数据是否符合预期。

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