使用BigQuery的ARRAY_AGG(STRUCT)合并3张表生成无重复主数据集

直接同时关联主表和两张子表会触发笛卡尔积：比如一个商品对应2条交易记录、3条位置记录，关联后会生成2×3=6条重复组合数据，最终聚合出来的交易和位置集合里会塞满重复项。

正确做法是先分别对交易表、位置表按ItemCode预聚合，再和主表关联，彻底避免子表间的交叉重复：

WITH 预聚合交易数据 AS (
  SELECT
    ItemCode,
    ARRAY_AGG(STRUCT(Date, Quanitty)) AS Transactions
  FROM Transactions
  GROUP BY ItemCode
),
预聚合位置数据 AS (
  SELECT
    ItemCode,
    ARRAY_AGG(STRUCT(WHBin, Serial, Qty)) AS Locations
  FROM Location  -- 注意：你之前写的关联Transactions l是笔误，应该是位置表
  GROUP BY ItemCode
)
SELECT
  i.ItemCode,
  i.Manuf AS Manufacturer,
  i.ItemName AS Model,
  i.QtyonOrder AS `Qty on Order`,
  -- 用COALESCE确保没有数据时返回空数组，避免NULL
  COALESCE(预聚合交易数据.Transactions, []) AS Transactions,
  COALESCE(预聚合位置数据.Locations, []) AS Locations
FROM ItemMasterData i
LEFT JOIN 预聚合交易数据 ON i.ItemCode = 预聚合交易数据.ItemCode
LEFT JOIN 预聚合位置数据 ON i.ItemCode = 预聚合位置数据.ItemCode;

这样处理后，每个商品的交易集合和位置集合都是从各自子表独立聚合而来，不会互相干扰产生重复数据，完全符合你需要的嵌套结构，适配Looker Studio的展示需求。

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