如何在BigQuery中嵌套使用两次ARRAY_AGG函数？

BigQuery不允许在同一个SELECT层级中直接嵌套使用聚合函数（比如ARRAY_AGG套ARRAY_AGG），这就是你遇到Aggregations of aggregations are not allowed错误的原因。解决方法是拆分聚合步骤，先完成内层数据的聚合，再基于中间结果做外层聚合。

核心思路 #

1. 先用子查询或CTE（公共表达式）处理内层的数组聚合，得到每个底层分组的嵌套数组
2. 再基于这个中间结果，执行外层的数组聚合，最终得到嵌套的数组结构

示例1：订单与订单明细的嵌套结构 #

假设你有订单表和订单明细表，要生成包含订单明细数组的订单数组：

WITH order_items_agg AS (
  -- 第一步：聚合每个订单的明细数据，得到内层数组
  SELECT
    order_id,
    ARRAY_AGG(STRUCT(product_id, quantity, unit_price)) AS line_items
  FROM
    `your-project.your-dataset.order_items`
  GROUP BY
    order_id
)
-- 第二步：聚合订单数据，将内层数组嵌套进去
SELECT
  ARRAY_AGG(STRUCT(
    order_id,
    customer_id,
    order_date,
    total_amount,
    line_items  -- 引用第一步生成的内层数组
  )) AS customer_orders
FROM
  `your-project.your-dataset.orders` o
JOIN
  order_items_agg oi
ON
  o.order_id = oi.order_id
GROUP BY
  customer_id;

示例2：同一表内的层级分组聚合 #

如果是从单表中按多层维度聚合（比如分类→子分类→商品）：

WITH subcategory_products AS (
  -- 第一步：聚合每个子分类下的商品，生成内层商品数组
  SELECT
    category_id,
    subcategory_id,
    ARRAY_AGG(STRUCT(product_id, product_name, price)) AS products
  FROM
    `your-project.your-dataset.products`
  GROUP BY
    category_id, subcategory_id
),
category_subcategories AS (
  -- 第二步：聚合每个分类下的子分类，生成子分类数组（包含商品数组）
  SELECT
    c.category_id,
    c.category_name,
    ARRAY_AGG(STRUCT(subcategory_id, products)) AS subcategories
  FROM
    `your-project.your-dataset.categories` c
  JOIN
    subcategory_products sc
  ON
    c.category_id = sc.category_id
  GROUP BY
    c.category_id, c.category_name
)
-- 第三步：聚合所有分类，生成最终的分类数组
SELECT
  ARRAY_AGG(STRUCT(category_id, category_name, subcategories)) AS product_categories
FROM
  category_subcategories;

关键注意事项 #

• 每个聚合步骤必须搭配正确的GROUP BY子句，确保分组维度符合你想要的层级结构
• 如果不需要全局聚合（比如示例1中按customer_id分组），可以根据业务需求调整GROUP BY的字段
• 中间步骤的字段命名要清晰，避免在后续聚合中出现歧义

内容的提问来源于stack exchange，提问作者dimitri.marinho