BigQuery中字符串转整数求平均及唯一值分组求平均的SQL实现

核心问题解答 #

a) 字符串类型字段转整数并计算平均值 #

使用CAST(字段名 AS INT64)将字符串转换为整数类型，再嵌套AVG()函数计算平均值。如果字段存在非数字内容，推荐用SAFE_CAST替代CAST，避免查询因数据异常中断（BigQuery计算平均值时会自动忽略NULL值）。示例写法：

AVG(SAFE_CAST(目标字符串字段 AS INT64))

b) 基于唯一值组合计算平均值 #

通过GROUP BY子句指定需要分组的字段组合，对分组后的目标字段执行平均值计算。比如按Product和Trouble的唯一组合分组，只需将这两个字段放入GROUP BY中即可。

满足期望结果的优化SQL #

你的原始SQL需要补充组合计数列，同时修正带空格的表名格式（BigQuery要求带空格的表名用反引号包裹），优化后的完整语句如下：

SELECT 
  PRODUCT_VAL, 
  TROUBLE,
  COUNT(*) AS 组合出现次数, -- 统计Product与Trouble唯一组合的出现次数
  AVG(SAFE_CAST(BUSINESS_DUR AS INT64)) AS 平均业务日周转天数,
  AVG(SAFE_CAST(CALENDAR_DUR AS INT64)) AS 平均日历日周转天数
FROM `CUSTOMER ASSURANCE TABLE` -- 带空格的表名必须用反引号包裹
GROUP BY PRODUCT_VAL, TROUBLE
ORDER BY PRODUCT_VAL, TROUBLE; -- 可选：按分组字段排序，结果更规整

优化说明 #

1. 表名格式修正：原SQL中CUSTOMER ASSURANCE TABLE包含空格，必须用反引号包裹才能被BigQuery识别。
2. 新增计数列：通过COUNT(*)直接统计每个唯一组合的出现次数，满足第一个期望结果。
3. 安全类型转换：用SAFE_CAST替代CAST，避免字段中存在非数字值时导致查询失败，提升语句鲁棒性。
4. 列别名优化：给计算列添加明确别名，让查询结果更易读。

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