如何在Google Big Query中使用SQL完成表结构打平操作？

我拥有如下所示的原表：

希望通过数据处理得到如下预期输出结果：

我查找了多篇相关教程（包括Stack Overflow上的相关讨论帖）尝试实现需求，但现有方案都不适用于我的表，我的表结构详情如下：

以下为通用实现逻辑，默认适配BigQuery环境，其他SQL引擎可对应调整语法：

• 第一步用ROW_NUMBER()窗口函数，按用户ID、会话ID分组，按事件发生时间升序排序，给同一会话内的每个事件标注顺序编号
• 第二步按用户ID、会话ID分组聚合，通过数组取对应下标的方式获取每个位次的事件属性，实现行转列平铺

示例代码如下：

WITH ranked_events AS (
  SELECT
    user_id,
    session_id,
    event_time,
    event_type,
    -- 此处补充你需要提取的其他事件属性字段
    ROW_NUMBER() OVER(PARTITION BY user_id, session_id ORDER BY event_time ASC) AS event_order
  FROM `你的项目名.你的数据集名.你的表名`
)
SELECT
  user_id,
  session_id,
  -- 提取第1个事件的对应属性
  ARRAY_AGG(event_type ORDER BY event_order ASC)[SAFE_OFFSET(0)] AS event_1_type,
  ARRAY_AGG(event_time ORDER BY event_order ASC)[SAFE_OFFSET(0)] AS event_1_time,
  -- 提取第2个事件的对应属性，需要更多事件直接按规律往下加即可
  ARRAY_AGG(event_type ORDER BY event_order ASC)[SAFE_OFFSET(1)] AS event_2_type,
  ARRAY_AGG(event_time ORDER BY event_order ASC)[SAFE_OFFSET(1)] AS event_2_time
FROM ranked_events
GROUP BY user_id, session_id

如果你的场景里每个会话对应的事件数量不固定，可改用动态SQL自动生成查询列，无需手动重复编写每一列的逻辑。

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