如何在BigQuery中使用PIVOT生成包含多列的ARRAY STRUCT

我在BigQuery中有一张包含fecData、total、name三列的表，想要对fecData列进行PIVOT操作，同时将另外两列组合成对应的RECORD结构，最终得到单一行、每个fecData值对应total和name字段的结果。

原始表结构及数据 #

当前查询语句 #

WITH initTable AS (
  SELECT '202301' AS fecData, 250 AS total, 'first' as name
  UNION ALL
  SELECT '202302' AS fecData, 50 AS total, 'sec' as name
  UNION ALL
  SELECT '202303' AS fecData, 100 AS total, 'th' as name
),
pivotTable AS (
  SELECT *
  FROM initTable
  PIVOT (
    MAX(total)
    FOR fecData IN ('202301', '202302', '202303')
  )
)
SELECT *
FROM pivotTable

当前执行结果 #

期望结果 #

我需要将fecData的各个值转换为包含对应total和name列值的RECORD结构，但无法在透视后的列上应用ARRAY(STRUCT)，请求可行的实现方案。

在BigQuery中，可以通过先将每个fecData对应的total和name封装成STRUCT，再进行PIVOT来实现需求。核心思路是在PIVOT的聚合函数中直接处理STRUCT，而不是单独聚合total。

实现代码 #

WITH initTable AS (
  SELECT '202301' AS fecData, 250 AS total, 'first' as name
  UNION ALL
  SELECT '202302' AS fecData, 50 AS total, 'sec' as name
  UNION ALL
  SELECT '202303' AS fecData, 100 AS total, 'th' as name
),
pivotTable AS (
  SELECT *
  FROM initTable
  PIVOT (
    -- 聚合时直接生成包含total和name的STRUCT
    ANY_VALUE(STRUCT(total, name))
    FOR fecData IN ('202301', '202302', '202303')
  )
)
-- 将每个fecData对应的STRUCT展开成独立字段
SELECT
  `202301`.total AS `202301.total`,
  `202301`.name AS `202301.name`,
  `202302`.total AS `202302.total`,
  `202302`.name AS `202302.name`,
  `202303`.total AS `202303.total`,
  `202303`.name AS `202303.name`
FROM pivotTable

逻辑说明 #

1. 封装STRUCT：在PIVOT的聚合函数中使用ANY_VALUE(STRUCT(total, name))，因为每个fecData对应唯一的total和name，ANY_VALUE可以确保正确获取对应的值（也可以用MAX或MIN，效果一致）。
2. 透视生成RECORD列：PIVOT后会生成202301、202302、202303三个RECORD类型的列，每个列包含total和name子字段。
3. 展开RECORD：通过.语法将每个RECORD的子字段提取为独立列，并重命名为期望的格式（如202301.total）。

执行结果 #

该查询会直接返回你期望的单一行结果，每个fecData对应的total和name字段清晰分开。

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