如何在BigQuery中将重复状态值转换为列（登录登出示例）

原始数据 #

期望输出 #

实现方法 #

方法1：使用LEAD()窗口函数（适用于严格成对的登录登出记录） #

利用窗口函数获取每条登录记录的下一条时间（即对应的登出时间），筛选登录状态的记录即可完成配对：

WITH sorted_logs AS (
  SELECT 
    ID,
    status,
    login_logout_time,
    -- 按用户分组、时间排序，获取下一条记录的时间
    LEAD(login_logout_time) OVER (PARTITION BY ID ORDER BY login_logout_time) AS next_time
  FROM `你的项目ID.你的数据集ID.你的表名`
)
SELECT 
  ID,
  login_logout_time AS logged_in,
  next_time AS logged_out
FROM sorted_logs
WHERE status = 'loggedin'
ORDER BY logged_in;

方法2：会话分组聚合（鲁棒性更强，支持处理未登出的异常记录） #

通过统计登录次数为每个会话分配唯一ID，再按会话分组聚合提取登录和登出时间：

WITH grouped_logs AS (
  SELECT 
    ID,
    status,
    login_logout_time,
    -- 按用户分组，累计登录次数作为会话ID
    COUNT(IF(status = 'loggedin', 1, NULL)) OVER (PARTITION BY ID ORDER BY login_logout_time) AS session_id
  FROM `你的项目ID.你的数据集ID.你的表名`
)
SELECT 
  ID,
  MAX(IF(status = 'loggedin', login_logout_time, NULL)) AS logged_in,
  MAX(IF(status = 'loggedout', login_logout_time, NULL)) AS logged_out
FROM grouped_logs
GROUP BY ID, session_id
ORDER BY logged_in;

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