在BigQuery中使用CTE聚合报错：accounts未被识别，求解决方案

问题根源 #

CTE生成的临时表accounts_Opps是扁平化结构，不会保留原表的别名层级（比如Accounts或Opportunities）。你在后续查询中写accounts_Opps.accounts.name，相当于要找临时表里名为accounts的嵌套对象，但实际上临时表里只有Id、name（来自Account）、account_Id、name（来自Opportunity）这些独立列，自然会报错。

另外，两张表都有name列，直接SELECT会导致列名重复，BigQuery会自动重命名（比如name_1），可读性极差，建议给重复列加别名区分。

调整后的可行代码 #

WITH accounts_Opps AS (
  SELECT  
    Accounts.Id AS account_id,
    Accounts.name AS account_name,  -- 给Account的name加别名区分
    Accounts.sales_territory_c, 
    Accounts.cs_stage_c,
    opportunities.account_Id AS opp_account_id,
    opportunities.name AS opp_name,  -- 给Opportunity的name加别名区分
    opportunities.rep_projected_arr_c,
    opportunities.stage_name
  FROM `salesforce.account` AS Accounts
  LEFT JOIN `salesforce.opportunity` AS Opportunities
    ON Accounts.id = Opportunities.account_id
  WHERE Opportunities.account_Id IS NOT NULL  -- 此条件等价于INNER JOIN，若需保留所有Account请移除
)

SELECT 
  account_name,
  COUNT(rep_projected_arr_c) AS projected_arr_count  -- 聚合结果加别名提升可读性
FROM accounts_Opps
GROUP BY account_name

补充说明 #

• 你尝试的「CTE连接表后再聚合」的方法完全可行，只是需要注意CTE的列命名规范，避免重复和层级错误。
• 若你原本想保留所有Account记录（即使没有对应Opportunity），要去掉WHERE Opportunities.account_Id IS NOT NULL条件，否则会退化为内连接效果。

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