如何将BigQuery中UNNESTED ARRAY转为带增量后缀的单行宽表

我有一个展开后的UNNESTED ARRAY数据，共5行，所有记录对应同一个id。需要将该扁平化数据转换为单行宽表，要求保留原列名并添加下划线加递增数字的后缀，且不使用pivot操作避免逻辑混乱。

原数据结构 #

目标结构 #

解决方案（非Pivot方式） #

通过窗口函数分配行号 + 条件聚合即可实现需求，以下是适配主流SQL引擎的代码示例（以BigQuery为例）：

WITH numbered_data AS (
  SELECT
    id,
    `fpe.graduate`,
    `fpe.type`,
    `fpe.institution`,
    `fpe.AttenedanceYear`,
    `fpe.degree`,
    -- 按id分组，给每行分配1-5的递增序号
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY Row) AS row_num
  FROM your_table_name  -- 替换为你的实际表名
)
SELECT
  id,
  -- 生成第一组带序号后缀的列
  MAX(CASE WHEN row_num = 1 THEN `fpe.graduate` END) AS `fpe.graduate_1`,
  MAX(CASE WHEN row_num = 1 THEN `fpe.type` END) AS `fpe.type_1`,
  MAX(CASE WHEN row_num = 1 THEN `fpe.institution` END) AS `fpe.institution_1`,
  MAX(CASE WHEN row_num = 1 THEN `fpe.AttenedanceYear` END) AS `fpe.AttenedanceYear_1`,
  MAX(CASE WHEN row_num = 1 THEN `fpe.degree` END) AS `fpe.degree_1`,
  
  -- 生成第二组列
  MAX(CASE WHEN row_num = 2 THEN `fpe.graduate` END) AS `fpe.graduate_2`,
  MAX(CASE WHEN row_num = 2 THEN `fpe.type` END) AS `fpe.type_2`,
  MAX(CASE WHEN row_num = 2 THEN `fpe.institution` END) AS `fpe.institution_2`,
  MAX(CASE WHEN row_num = 2 THEN `fpe.AttenedanceYear` END) AS `fpe.AttenedanceYear_2`,
  MAX(CASE WHEN row_num = 2 THEN `fpe.degree` END) AS `fpe.degree_2`,
  
  -- 生成第三组列
  MAX(CASE WHEN row_num = 3 THEN `fpe.graduate` END) AS `fpe.graduate_3`,
  MAX(CASE WHEN row_num = 3 THEN `fpe.type` END) AS `fpe.type_3`,
  MAX(CASE WHEN row_num = 3 THEN `fpe.institution` END) AS `fpe.institution_3`,
  MAX(CASE WHEN row_num = 3 THEN `fpe.AttenedanceYear` END) AS `fpe.AttenedanceYear_3`,
  MAX(CASE WHEN row_num = 3 THEN `fpe.degree` END) AS `fpe.degree_3`,
  
  -- 生成第四组列
  MAX(CASE WHEN row_num = 4 THEN `fpe.graduate` END) AS `fpe.graduate_4`,
  MAX(CASE WHEN row_num = 4 THEN `fpe.type` END) AS `fpe.type_4`,
  MAX(CASE WHEN row_num = 4 THEN `fpe.institution` END) AS `fpe.institution_4`,
  MAX(CASE WHEN row_num = 4 THEN `fpe.AttenedanceYear` END) AS `fpe.AttenedanceYear_4`,
  MAX(CASE WHEN row_num = 4 THEN `fpe.degree` END) AS `fpe.degree_4`,
  
  -- 生成第五组列
  MAX(CASE WHEN row_num = 5 THEN `fpe.graduate` END) AS `fpe.graduate_5`,
  MAX(CASE WHEN row_num = 5 THEN `fpe.type` END) AS `fpe.type_5`,
  MAX(CASE WHEN row_num = 5 THEN `fpe.institution` END) AS `fpe.institution_5`,
  MAX(CASE WHEN row_num = 5 THEN `fpe.AttenedanceYear` END) AS `fpe.AttenedanceYear_5`,
  MAX(CASE WHEN row_num = 5 THEN `fpe.degree` END) AS `fpe.degree_5`
FROM numbered_data
GROUP BY id;

代码说明 #

1. numbered_data 临时表：用ROW_NUMBER()窗口函数给同一id下的每行分配唯一序号，确保后续能精准定位每一行的数据。
2. 条件聚合：通过CASE WHEN筛选对应序号的行数据，再用MAX()聚合（同一id下每个序号仅对应一条数据，MAX等价于直接提取该值），生成带序号后缀的新列。
3. GROUP BY id：最终按id分组，将同一id的所有行合并为单行宽表。

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