如何在BigQuery中将各品牌Top10畅销产品聚合为单行显示

针对你的需求，推荐两种高效实现方式，比自连接10次的性能提升明显，同时解决PIVOT使用不当的问题：

方式一：使用PIVOT实现字段拆分（每个产品字段单独成列） #

先通过窗口函数给每个品牌的产品按销量排名，再用PIVOT将Top10产品的字段分别转为列，每个品牌最终占一行：

WITH ranked_products AS (
  SELECT
    brand_id,
    brand_name,
    product_id,
    product_name,
    product_price,
    product_description,
    product_sales,
    positive_reviews,
    negative_reviews,
    -- 按品牌分组，销量降序排名
    ROW_NUMBER() OVER (PARTITION BY brand_id ORDER BY product_sales DESC) AS rank
  FROM
    `你的项目ID.你的数据集.产品表`
),
top10_products AS (
  -- 筛选每个品牌的Top10产品
  SELECT * FROM ranked_products WHERE rank <= 10
)
-- 用PIVOT将不同排名的产品字段转为列
SELECT * FROM top10_products
PIVOT (
  MAX(product_id) AS product_id,
  MAX(product_name) AS product_name,
  MAX(product_price) AS product_price,
  MAX(product_description) AS product_description,
  MAX(product_sales) AS product_sales,
  MAX(positive_reviews) AS positive_reviews,
  MAX(negative_reviews) AS negative_reviews
  -- 指定要透视的排名值（1到10）
  FOR rank IN (1,2,3,4,5,6,7,8,9,10)
);

说明 #

• 这里用MAX()作为聚合函数，因为每个rank在单个品牌下唯一，MAX结果就是对应排名的产品字段值
• 输出列会以排名_字段名命名，比如1_product_id对应品牌销量第1的产品ID，以此类推
• 相比自连接，PIVOT是BigQuery原生优化的操作，只需要扫描两次表（CTE一次，PIVOT一次），性能大幅提升

方式二：用STRUCT+ARRAY_AGG打包为数组（简洁灵活） #

如果不需要把每个产品字段拆成单独列，可以将每个产品的信息打包成STRUCT，再聚合为数组，每个品牌一行，包含Top10产品的完整数组：

WITH ranked_products AS (
  SELECT
    brand_id,
    brand_name,
    -- 将单个产品的所有字段打包为STRUCT
    STRUCT(
      product_id,
      product_name,
      product_price,
      product_description,
      product_sales,
      positive_reviews,
      negative_reviews
    ) AS product_info,
    ROW_NUMBER() OVER (PARTITION BY brand_id ORDER BY product_sales DESC) AS rank
  FROM
    `你的项目ID.你的数据集.产品表`
)
SELECT
  brand_id,
  brand_name,
  -- 按排名聚合Top10产品的STRUCT为数组
  ARRAY_AGG(product_info ORDER BY rank) AS top10_products
FROM
  ranked_products
WHERE
  rank <= 10
GROUP BY
  brand_id, brand_name;

说明 #

• 结果中top10_products是一个数组，每个元素是包含产品所有字段的STRUCT，后续可以在BigQuery中用UNNEST()展开，或在应用层解析数组
• 这种写法代码更简洁，避免重复写10次字段逻辑，查询性能最优

为什么之前的方法有问题 #

• 自连接10次：每次自连接都会扫描表，导致多次IO操作，执行时间随连接次数线性增长
• 之前的PIVOT错误：没有指定针对每个品牌的聚合逻辑，也没正确用排名作为透视列，导致没有按品牌合并行

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