如何通过子查询或其他方法为BigQuery/SQL查询新增列

最优方案：直接在原查询中计算s_u #

不用额外关联子查询，直接用条件聚合把s_u的逻辑加进原查询，既简洁又避免关联问题：

SELECT
  country,
  place,
  label_date,
  SUM(CASE WHEN Category = 'cat1' THEN Value ELSE 0 END) AS t_s,
  SUM(CASE WHEN Category = 'cat2' THEN Value ELSE 0 END) AS non_ps,
  SUM(CASE WHEN Category = 'cat3' THEN Value ELSE 0 END) AS f_h,
  SUM(CASE WHEN Category = 'cat4' THEN Value ELSE 0 END) AS f_s,
  SUM(CASE WHEN Category = 'cat5' THEN Value ELSE 0 END) AS o_hp,
  SUM(CASE WHEN Category = 'cat6' THEN Value ELSE 0 END) AS o_hps,
  SUM(CASE WHEN Category = 'cat7' THEN Value ELSE 0 END) AS a_s,
  SUM(CASE WHEN Category = 'cat8' THEN Value ELSE 0 END) AS u_s,
  -- 直接计算s_u：只统计cat9的数据
  CASE 
    WHEN SUM(CASE WHEN Category = 'cat9' THEN Value ELSE 0 END) = 0 THEN 1
    ELSE SUM(CASE WHEN Category = 'cat9' THEN Value ELSE 0 END) / COUNT(CASE WHEN Category = 'cat9' THEN Value ELSE NULL END)
  END AS s_u
FROM
  `my_database`
WHERE country IS NOT NULL 
GROUP BY country, place, label_date 
ORDER BY country DESC

这里用CASE WHEN Category = 'cat9'筛选目标数据，计算总和和有效计数（COUNT里用ELSE NULL，和原查询COUNT(Value)逻辑一致，自动忽略空值），再判断总和为0时返回1，否则返回平均值。

备选方案：用CTE关联两个聚合查询 #

如果一定要分开写两个查询再合并，必须用全部分组字段（country、place、label_date）作为关联条件，避免笛卡尔积和冗余报错：

WITH main_query AS (
  SELECT
    country,
    place,
    label_date,
    SUM(CASE WHEN Category = 'cat1' THEN Value ELSE 0 END) AS t_s,
    SUM(CASE WHEN Category = 'cat2' THEN Value ELSE 0 END) AS non_ps,
    SUM(CASE WHEN Category = 'cat3' THEN Value ELSE 0 END) AS f_h,
    SUM(CASE WHEN Category = 'cat4' THEN Value ELSE 0 END) AS f_s,
    SUM(CASE WHEN Category = 'cat5' THEN Value ELSE 0 END) AS o_hp,
    SUM(CASE WHEN Category = 'cat6' THEN Value ELSE 0 END) AS o_hps,
    SUM(CASE WHEN Category = 'cat7' THEN Value ELSE 0 END) AS a_s,
    SUM(CASE WHEN Category = 'cat8' THEN Value ELSE 0 END) AS u_s
  FROM
    `my_database`
  WHERE country IS NOT NULL 
  GROUP BY country, place, label_date
),
su_query AS (
  SELECT 
    country, 
    place, 
    label_date, 
    IF((SUM(Value)/COUNT(Value))=0,1,SUM(Value)/COUNT(Value)) AS s_u 
  FROM `my_database` 
  WHERE Category = 'cat9' 
  GROUP BY country, place, label_date
)
SELECT 
  m.*,
  COALESCE(s.s_u, 1) -- 没有cat9数据时默认返回1，可按需调整
FROM main_query m
LEFT JOIN su_query s 
  ON m.country = s.country 
  AND m.place = s.place 
  AND m.label_date = s.label_date
ORDER BY m.country DESC

用LEFT JOIN保证主查询的所有行都能保留，COALESCE处理没有cat9数据的情况，确保s_u不会出现空值。

之前报错的原因 #

• 仅用country关联：两个查询都是按三个字段分组，同一country下有多个place/label_date组合，关联后会产生大量重复行，数据库直接报错提示冗余。
• 嵌套聚合：SQL不允许聚合函数套聚合函数（比如SUM(SUM(Value))），这种写法本身就不合法。
• 不使用聚合函数：如果字段不在GROUP BY列表里，又没被聚合函数包裹，数据库不知道怎么分组计算，自然报错。

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