BigQuery报错：标量子查询返回多个结果——关联SQL语句问题排查求助

报错原因分析 #

你遇到的这个错误本质上是标量子查询返回了多行数据导致的。BigQuery里，嵌套在CASE或者SELECT语句中的标量子查询有严格要求：必须返回单行单列的结果。但你的SQL里，这个分支的子查询select (business_unit) from dgapbigquery.bi_reports.channel_campaign_exception_lookup是直接查询整个表的business_unit字段——如果这个表有超过1条记录，就会返回一堆值，而CASE语句在这个分支里只期待单个值，自然就触发报错了。

另外看你的逻辑，外层查询已经在遍历channel_campaign_exception_lookup表的每一行了，完全没必要再用子查询去查整个表，这属于逻辑冗余，也是问题的核心根源。

解决方案 #

直接把那个子查询替换成引用当前行的business_unit字段就行，修改后的SQL如下：

SELECT
  CASE LOWER(channel)
    WHEN 'sem' THEN
      CASE
        WHEN LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'us' THEN 'us independents'
        WHEN LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'intl' THEN 'intl independents'
        WHEN LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'part' THEN 'partners'
        WHEN LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'invest' THEN 'domain investors & registrars'
        WHEN campaign_name IS NOT NULL OR channel IS NOT NULL THEN business_unit -- 直接引用当前行字段，删除子查询
        ELSE 'unknown'
      END
  END AS campaign_category
FROM
  gdgapbigquery.bi_reports.channel_campaign_exception_lookup

额外优化建议 #

1. 处理分割后索引越界的情况：SAFE_OFFSET(9)会在campaign_name按_分割后不足10个元素时返回NULL，你可以在CASE里加个分支处理这种场景，避免逻辑漏洞：

   WHEN SPLIT(campaign_name,'_')[SAFE_OFFSET(9)] IS NULL THEN 'unknown'
   

2. 简化重复表达式：把重复出现的LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)])提取成一个计算字段，让SQL更简洁易读：

   SELECT
     CASE LOWER(channel)
       WHEN 'sem' THEN
         CASE
           WHEN campaign_segment = 'us' THEN 'us independents'
           WHEN campaign_segment = 'intl' THEN 'intl independents'
           WHEN campaign_segment = 'part' THEN 'partners'
           WHEN campaign_segment = 'invest' THEN 'domain investors & registrars'
           WHEN campaign_segment IS NULL THEN 'unknown' -- 新增处理NULL的分支
           WHEN campaign_name IS NOT NULL OR channel IS NOT NULL THEN business_unit
           ELSE 'unknown'
         END
     END AS campaign_category
   FROM (
     SELECT
       channel,
       campaign_name,
       business_unit,
       LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) AS campaign_segment
     FROM
       gdgapbigquery.bi_reports.channel_campaign_exception_lookup
   ) AS subquery
   

内容的提问来源于stack exchange，提问作者harsha k