BigQuery查询结果分两行需合并为单行的技术求助

问题根源 #

你的查询之所以输出两行，是因为GROUP BY子句包含了acc1.account_type，导致数据按账户类型分组，每种类型单独生成一行。要把两种类型的计数合并到同一行，需要调整聚合逻辑，同时移除GROUP BY中的账户类型字段。

修改后的查询语句 #

SELECT 
  acc3.country,
  acc1.enroller_account_number,
  acc3.first_name,
  acc3.last_name,
  COUNT(DISTINCT acc1.account_number) AS total_enrollments,
  COUNT(DISTINCT CASE WHEN acc1.account_type = 'Preferred Customer' THEN acc1.account_number END) AS total_preferred_customers,
  COUNT(DISTINCT CASE WHEN acc1.account_type = 'Consultant' THEN acc1.account_number END) AS total_consultants
FROM `table1` acc1
LEFT JOIN `table1` acc2 ON acc1.account_number = acc2.account_number
LEFT JOIN `table1` acc3 ON acc1.enroller_account_number = acc3.account_number
WHERE 
  acc2.enrollment_period_id = 202307
  AND acc1.enroller_account_number = '011111111'
  AND acc1.is_current = 1
GROUP BY 1, 2, 3, 4
ORDER BY total_enrollments DESC 
LIMIT 5

关键调整说明 #

• 将CASE语句嵌套在COUNT(DISTINCT ...)内部：在同一分组内分别统计两种账户类型的唯一账户数，结果自然合并到同一行。
• 移除GROUP BY中的acc1.account_type：确保分组仅基于你需要的维度（国家、推荐人账号等），避免按账户类型拆分。
• 修正表名引号：BigQuery中引用表名需用反引号`，而非单引号'。
• 恢复total_enrollments字段：用于排序获取Top5总注册量数据。

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