如何在BigQuery中按账户分组生成多条件聚合列？

你的问题出在子查询未关联外层账户ID，导致计算的是所有符合条件机会的总金额，而非每个账户自身的金额。以下两种方案可以解决需求：

方案一：条件聚合（推荐） #

利用CASE语句筛选符合条件的记录，结合SUM按账户聚合计算，逻辑简洁且性能更优：

SELECT 
  A.Account_ID,
  -- 计算FY24在途Pipeline金额
  SUM(CASE 
    WHEN O.Segment = 'Enterprise' 
      AND O.Opportunity_type = 'Revenue Opportunity' 
      AND O.Stage IN ('Qualified', 'Proposal','Trial','Decision','Purchase','Closing') 
      AND O.Close_Year = 'FY24'
    THEN O.ACV_Bookings_USD_Converted 
    ELSE 0 
  END) AS FY24_Pipeline,
  -- 计算FY24已赢单金额
  SUM(CASE 
    WHEN O.Segment = 'Enterprise' 
      AND O.Opportunity_type = 'Revenue Opportunity' 
      AND O.Stage = 'Closed Won' 
      AND O.Close_Year = 'FY24'
    THEN O.ACV_Bookings_USD_Converted 
    ELSE 0 
  END) AS FY24_Closed_Won
FROM `Table 1` AS A
LEFT OUTER JOIN `Table 2` AS O
  ON A.Account_ID = O.Account_id
WHERE 
  A.Enterprise_Assignment IS NOT NULL
  AND A.Billing_Country = 'Canada'
GROUP BY A.Account_ID

方案二：修正子查询关联账户ID #

在子查询中添加与外层Account_ID的关联条件，确保每个子查询仅计算当前账户的对应金额：

SELECT 
  A.Account_ID,
  -- 子查询关联当前账户，计算该账户FY24 Pipeline
  (SELECT SUM(ACV_Bookings_USD_Converted) 
   FROM `Table 2` AS O_sub
   WHERE O_sub.Account_id = A.Account_ID
     AND O_sub.Segment = 'Enterprise' 
     AND O_sub.Opportunity_type = 'Revenue Opportunity' 
     AND O_sub.Stage IN ('Qualified', 'Proposal','Trial','Decision','Purchase','Closing') 
     AND O_sub.Billing_Country = 'Canada' 
     AND O_sub.Close_Year = 'FY24') AS FY24_Pipeline,
  -- 同理计算已赢单金额
  (SELECT SUM(ACV_Bookings_USD_Converted) 
   FROM `Table 2` AS O_sub
   WHERE O_sub.Account_id = A.Account_ID
     AND O_sub.Segment = 'Enterprise' 
     AND O_sub.Opportunity_type = 'Revenue Opportunity' 
     AND O_sub.Stage = 'Closed Won' 
     AND O_sub.Billing_Country = 'Canada' 
     AND O_sub.Close_Year = 'FY24') AS FY24_Closed_Won
FROM `Table 1` AS A
WHERE 
  A.Enterprise_Assignment IS NOT NULL
  AND A.Billing_Country = 'Canada'
GROUP BY A.Account_ID

说明 #

• 方案一通过一次关联+聚合完成计算，避免多次子查询的性能损耗，适合数据量较大的场景；
• 方案二保留了子查询的写法，但必须添加账户关联条件，否则会返回全局总和。

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