如何在BigQuery中统计用户第二次登录前的总事件数

实现思路 #

1. 先通过CTE提取每个用户的第二次登录时间（仅针对有至少两次登录记录的用户）
2. 将原事件表与该CTE关联，筛选出事件时间早于第二次登录时间的记录，按用户分组计数

完整SQL代码 #

WITH user_second_login AS (
  -- 提取每个用户的第二次登录时间
  SELECT
    user_id,
    event_time AS second_login_time
  FROM (
    SELECT
      user_id,
      event_time,
      -- 给每个用户的登录事件按时间排序
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS login_rank
    FROM `your-project.your-dataset.user_events`
    WHERE event_type = 'login' -- 仅筛选登录类型事件
  )
  WHERE login_rank = 2 -- 锁定第二次登录的记录
)

-- 统计每个用户第二次登录前的所有事件数量
SELECT
  ue.user_id,
  -- 无第二次登录的用户计数为0，否则统计符合条件的事件数
  COUNT(CASE WHEN ue.event_time < usl.second_login_time THEN 1 END) AS events_before_second_login
FROM `your-project.your-dataset.user_events` ue
LEFT JOIN user_second_login usl
  ON ue.user_id = usl.user_id
GROUP BY ue.user_id
ORDER BY ue.user_id;

关键逻辑说明 #

• user_second_login CTE：利用ROW_NUMBER()窗口函数对每个用户的登录事件按时间排序，取排名为2的记录，得到第二次登录时间。若用户登录次数不足2次，该CTE中不会出现此用户的记录。
• 主查询关联：用LEFT JOIN确保所有用户都被纳入统计，对于没有第二次登录的用户，second_login_time为NULL，CASE WHEN会返回NULL，COUNT()会忽略NULL值，最终计数为0。
• 按需调整：如果只需要统计有第二次登录的用户，将LEFT JOIN改为INNER JOIN即可。

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