BigQuery关联子查询不支持问题：实现products数组嵌套于accounts行

你遇到的BigQuery错误提示是因为关联子查询引用其他表时无法自动解关联，需要通过JOIN重构查询来实现嵌套结构。以下是修改后的SQL代码，用聚合函数替代关联子查询，同时保留你需要的嵌套层级：

SELECT
  accounts.*,
  ARRAY_AGG(
    STRUCT(
      products.Account AS Licensed_Account,
      products.Country AS Licensed_Country,
      products.Exhibit_X_or_Y_Customer AS Agreement,
      products.Dedicated_or_Shared AS Usage_Type,
      products.Kyndryl_Account_ID,
      products.Item_Number AS SKU,
      products.License_Key_vSphere_8,
      products.Downgraded_vSphere_7_Keys,
      products.Downgraded_vSphere_6_Keys,
      CAST(products.Site_ID AS STRING) AS Site_ID,
      CAST(products.Contract_Number AS STRING) AS Contract_Number,
      cpm_data.CPM_Detail_Data
    )
  ) AS products
FROM
  `VCSP_Reporting.Accounts` AS accounts
INNER JOIN
  `VCSP_Reporting.License Tracker` AS products
ON
  accounts.Account = products.Account
LEFT JOIN (
  SELECT
    License_Key,
    Account,
    ARRAY_AGG(STRUCT(*)) AS CPM_Detail_Data
  FROM
    `VCSP_Reporting.CPM Detail Data`
  GROUP BY
    License_Key, Account
) AS cpm_data
ON
  products.Account = cpm_data.Account
  AND (
    cpm_data.License_Key = products.License_Key_vSphere_8
    OR cpm_data.License_Key = products.Downgraded_vSphere_7_Keys
    OR cpm_data.License_Key = products.Downgraded_vSphere_6_Keys
    OR products.SKU = 'VCF-CLD-FND-VSP-5'
  )
WHERE
  accounts.Account IS NOT NULL
GROUP BY
  accounts.Account, accounts.Country, accounts.Exhibit_X_or_Y_Customer, accounts.Dedicated_or_Shared, accounts.Kyndryl_Account_ID, accounts.Site_ID, accounts.Contract_Number
ORDER BY
  accounts.Account ASC

关键修改说明： #

• 替换外层关联子查询：用INNER JOIN关联Accounts和License Tracker，再通过ARRAY_AGG(STRUCT(...))将每个Account对应的所有Product聚合成嵌套数组，避免了原查询中的关联子查询。
• 预处理CPM数据：先对CPM Detail Data按License_Key和Account分组聚合，生成每个License对应的CPM数据数组，再通过LEFT JOIN关联到Product，彻底消除内层关联子查询。
• 修正逻辑条件：将原查询中OR条件用括号包裹，确保逻辑判断的正确性（原代码中OR会破坏AND的关联逻辑，导致不符合预期的匹配）。
• 明确GROUP BY字段：BigQuery要求SELECT中的非聚合字段必须出现在GROUP BY中，这里显式列出Accounts表的所有字段，避免隐式分组的问题。

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