BigQuery如何将同一订单的所有产品拆分为独立列展示

问题背景 #

现有订单商品关联表结构如下：

需要将同一Order_ID下的所有商品拆分到独立列展示，期望输出格式：

如果用传统自连接实现，当单个订单对应商品数超过2个时，需要叠加多层JOIN逻辑，可维护性极差，以下是BigQuery环境下的最优实现方案。

实现方案 #

直接用BigQuery原生PIVOT语法实现，不需要写多层自连接，分两种场景可选：

方案1：固定列数版本（性能最好，推荐） #

提前按业务场景预估单个订单最多包含的商品数量，硬编码对应序号即可，代码逻辑简单、查询性能高：

WITH order_product_with_seq AS (
  SELECT
    Order_ID,
    Product_Name,
    -- 给同订单下的商品生成递增序号，可按需调整排序规则，比如按下单时间、商品ID排序
    ROW_NUMBER() OVER (
      PARTITION BY Order_ID 
      ORDER BY Product_Name -- 这里替换成你需要的排序字段
    ) AS product_seq
  FROM `替换为你的实际表路径`
)
SELECT * FROM order_product_with_seq
PIVOT (
  ANY_VALUE(Product_Name) 
  FOR product_seq IN (
    1 AS Product_1,
    2 AS Product_2,
    3 AS Product_3,
    4 AS Product_4,
    5 AS Product_5
    -- 按业务最大商品数继续往后加即可，比写多层自连接维护成本低很多
  )
)
ORDER BY Order_ID

运行后直接得到你需要的Product_1/Product_2格式列，商品数不足的列自动返回NULL。

方案2：动态列版本（无需提前预估商品数上限） #

如果业务上单笔订单商品数波动大、不想硬编码列数，可以用BigQuery动态SQL实现自动适配列数：

EXECUTE IMMEDIATE FORMAT("""
WITH order_product_with_seq AS (
  SELECT
    Order_ID,
    Product_Name,
    ROW_NUMBER() OVER (PARTITION BY Order_ID ORDER BY Product_Name) AS product_seq
  FROM `替换为你的实际表路径`
)
SELECT * FROM order_product_with_seq
PIVOT (
  ANY_VALUE(Product_Name) 
  FOR product_seq IN (%s)
)
ORDER BY Order_ID
""", (
  -- 自动计算当前表中单笔订单的最大商品数，生成对应序号和列别名
  SELECT STRING_AGG(
    CONCAT(CAST(rn AS STRING), ' AS Product_', CAST(rn AS STRING)) 
    ORDER BY rn
  )
  FROM (
    SELECT MAX(product_count) AS max_cnt
    FROM (
      SELECT COUNT(*) AS product_count 
      FROM `替换为你的实际表路径` 
      GROUP BY Order_ID
    )
  ),
  UNNEST(GENERATE_ARRAY(1, max_cnt)) AS rn
));

注意事项 #

• PIVOT里用ANY_VALUE()是因为每个Order_ID+product_seq组合只会对应唯一的Product_Name，不会出现重复值，聚合结果准确
• 动态SQL版本会根据当前表的数据自动生成列，如果后续表中出现商品数更多的订单，返回结果会自动新增对应列，不需要修改代码
• 两种方案的执行效率都远高于多层自连接实现，尤其是订单商品数较多的场景优势更明显

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