BigQuery中多字段聚合并生成对象数组列的实现问题

根据你的需求，以下是修正后的SQL方案，实现按brand和type分组，统计匹配记录总数并生成对象数组列：

方案一：按id聚合country-color信息，再按brand/type汇总 #

适用于需要将每个id对应的所有country-color组合作为一个对象，汇总到brand/type组中的场景：

WITH id_level_agg AS (
  -- 按id、brand、type分组，聚合每个id关联的所有country-color对
  SELECT
    id,
    brand,
    type,
    ARRAY_AGG(STRUCT(country, color)) AS country_color_array
  FROM MY_TABLE
  GROUP BY id, brand, type
),
final_agg AS (
  -- 按brand和type分组，生成最终结果
  SELECT
    brand,
    type,
    -- 统计该brand+type下的总记录数（源表总行数）
    (SELECT COUNT(*) FROM MY_TABLE t WHERE t.brand = a.brand AND t.type = a.type) AS total,
    -- 聚合所有id的匹配信息为对象数组
    ARRAY_AGG(STRUCT(id, country_color_array)) AS matching_items
  FROM id_level_agg a
  GROUP BY brand, type
)
SELECT * FROM final_agg;

方案二：按country-color/brand/type统计匹配数，再按brand/type汇总 #

适用于需要统计每个country-color组合在brand/type下的匹配次数，并汇总到同一组的场景：

WITH match_group_stats AS (
  -- 按country-color、brand、type分组，统计每组的记录数和匹配的id
  SELECT
    STRUCT(country, color) AS country_color,
    brand,
    type,
    COUNT(*) AS match_count,
    ARRAY_AGG(DISTINCT id) AS matched_ids
  FROM MY_TABLE
  GROUP BY country, color, brand, type
),
final_agg AS (
  -- 按brand和type分组，汇总所有匹配组的信息
  SELECT
    brand,
    type,
    -- 该brand+type下所有匹配组的记录数总和
    SUM(match_count) AS total,
    -- 聚合所有匹配组的详细信息为对象数组
    ARRAY_AGG(STRUCT(country_color, match_count, matched_ids)) AS matching_details
  FROM match_group_stats
  GROUP BY brand, type
)
SELECT * FROM final_agg;

原代码问题说明 #

你之前的尝试已经完成了id维度的country-color数组聚合，但缺少了按brand和type分组聚合不同id的关键步骤，也没有通过聚合函数或子查询计算总记录数。上述方案直接在最终聚合阶段完成了数组拼接和总数统计，满足“不按country_color分组”的要求。

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