如何在BigQuery中查询各用户ID的第二早登录时间

实现思路 #

核心是利用窗口函数对每个用户的登录时间分组排序，筛选出位次为2的记录。可根据业务场景（是否存在重复登录时间）选择ROW_NUMBER()、RANK()或NTH_VALUE()等函数。

解决方案1：使用ROW_NUMBER()窗口函数 #

该方法给每个用户的登录时间按升序分配唯一行号，即使存在相同登录时间，行号也不重复，适合严格区分登录顺序的场景。

WITH ranked_logins AS (
  SELECT
    User_id,
    LoginTime,
    ROW_NUMBER() OVER (PARTITION BY User_id ORDER BY LoginTime ASC) AS login_rank
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
)
SELECT
  User_id,
  LoginTime AS SecondLoginTime
FROM
  ranked_logins
WHERE
  login_rank = 2;

解决方案2：使用NTH_VALUE()窗口函数 #

无需额外子查询/CTE，直接在原查询中提取每个用户的第二个最小登录时间，写法更简洁。

SELECT DISTINCT
  User_id,
  NTH_VALUE(LoginTime, 2) OVER (
    PARTITION BY User_id
    ORDER BY LoginTime ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS SecondLoginTime
FROM
  `your-project.your-dataset.your-table` -- 替换为你的表路径
WHERE
  NTH_VALUE(LoginTime, 2) OVER (
    PARTITION BY User_id
    ORDER BY LoginTime ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) IS NOT NULL; -- 过滤登录次数不足2次的用户

重复登录时间场景说明 #

• 若用户存在重复的最早登录时间，ROW_NUMBER()会给这类记录分配不同行号（1和2），会返回其中一条作为第二早登录时间；
• 若希望这种场景下不返回结果（无真正“第二早”），可改用RANK()函数，此时重复的最早登录时间会被分配相同排名1，排名2的记录不存在，这类用户会被过滤。

测试结果验证 #

使用你提供的测试数据执行上述SQL，将得到期望结果：

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