如何在Google BigQuery中实现指定的数据透视转换？

核心方案 #

先通过窗口函数为每个a分组内的记录分配唯一序号，再利用条件聚合（比PIVOT更灵活通用）将同组内的x、y值映射到x1/x2、y1/y2字段。

具体实现代码 #

步骤1：为分组内记录添加序号 #

先给每个a对应的记录标记序号，确保同组内的x、y能被区分：

WITH numbered_data AS (
  SELECT
    x,
    y,
    a,
    -- 按x排序分配序号，可根据业务需求替换排序字段（如时间、y值等）
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY x) AS row_num
  FROM
    `你的项目ID.你的数据集ID.你的表名`
)

步骤2：条件聚合生成目标结构 #

基于编号后的数据集，提取不同序号对应的x、y值：

SELECT
  a,
  MAX(CASE WHEN row_num = 1 THEN x END) AS x1,
  MAX(CASE WHEN row_num = 2 THEN x END) AS x2,
  MAX(CASE WHEN row_num = 1 THEN y END) AS y1,
  MAX(CASE WHEN row_num = 2 THEN y END) AS y2
FROM
  numbered_data
GROUP BY
  a

完整可执行SQL #

WITH numbered_data AS (
  SELECT
    x,
    y,
    a,
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY x) AS row_num
  FROM
    `你的项目ID.你的数据集ID.你的表名`
)
SELECT
  a,
  MAX(CASE WHEN row_num = 1 THEN x END) AS x1,
  MAX(CASE WHEN row_num = 2 THEN x END) AS x2,
  MAX(CASE WHEN row_num = 1 THEN y END) AS y1,
  MAX(CASE WHEN row_num = 2 THEN y END) AS y2
FROM
  numbered_data
GROUP BY
  a

通用扩展说明 #

• 若每个a对应超过2条记录，只需继续添加MAX(CASE WHEN row_num = N THEN x END) AS xN、MAX(CASE WHEN row_num = N THEN y END) AS yN字段即可。
• 若存在a对应少于2条记录的情况，缺失的xN/yN会返回NULL，可通过COALESCE(MAX(CASE ...), 默认值)替换为指定默认值（如0、空字符串等）。
• 排序逻辑ORDER BY x可根据实际业务调整，确保分组内的记录顺序符合需求。

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