Google BigQuery如何计算多列间忽略NULL值的平均值？

完全不需要手动编写每列的非空判断、累加、计数逻辑，两种实现可以根据业务场景选用：

实现方式1：子查询内嵌聚合，无额外分组开销 #

直接在新列定义中把所有需要计算的估计列打包成数组，BigQuery内置的AVG()函数对数组元素计算时，会自动跳过NULL值，完全匹配计算规则：

SELECT
  Estimate_A,
  Estimate_B,
  Estimate_C,
  -- 控制保留2位小数，和示例输出精度对齐
  ROUND(
    (SELECT AVG(col_val)
     FROM UNNEST([Estimate_A, Estimate_B, Estimate_C]) AS col_val)
  , 2) AS AVG_ESTIMATE
FROM 你的业务表名

计算效果完全和预期一致：

• 全列非空的行：按全列值求和除以列数计算平均
• 存在NULL的行：自动剔除NULL值后，用剩余非空值求和除以非空值的数量计算平均
• 只有1个非空值的行：直接返回该非空值作为平均结果

实现方式2：数组展开分组，适配后续列数扩展场景 #

如果后续业务中要增加更多估计列（比如从3列扩展到实际业务的8列），可以用数组展开的方式计算，只需要补充数组里的列名即可，不用调整核心计算逻辑：

SELECT
  Estimate_A,
  Estimate_B,
  Estimate_C,
  AVG(est_val) AS AVG_ESTIMATE
FROM 你的业务表名,
-- 列扩展到8个的时候，直接在下方数组里补全对应列名即可
UNNEST([Estimate_A, Estimate_B, Estimate_C, Estimate_D, Estimate_E, Estimate_F, Estimate_G, Estimate_H]) AS est_val
GROUP BY Estimate_A, Estimate_B, Estimate_C

对比手动编写的逐列IFNULL累加、逐列判断非空计数的写法，这种内置数组聚合的方式不会出现漏算、非空计数错配的问题，8列规模下不需要写冗长的判断逻辑，维护成本极低。如果需要和示例输出的1.66精度完全对齐，只要调整ROUND函数的小数位截断规则即可，不影响核心计算逻辑。

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