如何在BigQuery的GROUP BY查询中返回嵌套数组形式的全量记录？

完全没问题，在BigQuery里你可以轻松把分组后的全量列以嵌套数组的形式返回，核心就是用ARRAY_AGG()函数结合STRUCT()来打包行数据。

基础实现方案 #

直接用ARRAY_AGG()聚合分组内的所有行，并用STRUCT(*)把每一行的所有列打包成结构体（如果不想包含分组列，就用STRUCT(* EXCEPT(GroupingColumn))）：

SELECT
  GroupingColumn,
  -- 把分组内的每一行打包成结构体，再聚合成数组
  ARRAY_AGG(STRUCT(* EXCEPT(GroupingColumn))) AS grouped_records
FROM
  `your-project.your-dataset.your-table`
GROUP BY
  GroupingColumn

示例效果 #

假设你的源表数据是这样的：

运行上面的查询后，输出会和你期望的一致：

进阶优化技巧 #

• 如果想去重数组内的重复行，可以给ARRAY_AGG()加上DISTINCT：

  ARRAY_AGG(DISTINCT STRUCT(* EXCEPT(GroupingColumn))) AS grouped_records
  

• 如果想对数组内的元素排序，可以在ARRAY_AGG()里添加ORDER BY子句：

  ARRAY_AGG(STRUCT(* EXCEPT(GroupingColumn)) ORDER BY Column2 DESC) AS grouped_records
  

• 如果需要限制数组的元素数量，可以用LIMIT：

  ARRAY_AGG(STRUCT(* EXCEPT(GroupingColumn)) LIMIT 10) AS grouped_records
  

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