为何BigQuery中列有值时AVG()函数仍返回NULL？

1. 先确认气温列的数据类型 #

你的AVG()返回NULL最常见的原因之一是气温列不是数值类型（比如是STRING），BigQuery无法对字符串计算平均值，直接返回NULL。

运行以下查询确认数据类型：

SELECT typeof(temperature) AS temp_data_type
FROM `my_project.demos.nyc_weather`
WHERE date BETWEEN '2020-06-01' AND '2020-06-30'
LIMIT 1;

2. 针对字符串类型的修复 #

如果结果显示是STRING，需要先把字符串转成数值类型，同时排除用9999.9标记的缺失值：

SELECT AVG(CAST(temperature AS FLOAT64)) AS avg_temperature
FROM `my_project.demos.nyc_weather`
WHERE date BETWEEN '2020-06-01' AND '2020-06-30'
  AND temperature != '9999.9';

3. 数值类型但仍返回NULL的排查 #

如果数据类型是数值型（比如FLOAT64），先检查该日期范围内的所有气温值：

SELECT temperature
FROM `my_project.demos.nyc_weather`
WHERE date BETWEEN '2020-06-01' AND '2020-06-30';

如果发现所有行的气温都是9999.9，或者排除后没有有效数据，AVG()也会返回NULL。这种情况下，调整查询排除缺失值即可：

SELECT AVG(temperature) AS avg_temperature
FROM `my_project.demos.nyc_weather`
WHERE date BETWEEN '2020-06-01' AND '2020-06-30'
  AND temperature != 9999.9;

额外验证 #

可以先计算有效数据的行数，确认有可计算的样本：

SELECT COUNT(*) AS valid_temp_count
FROM `my_project.demos.nyc_weather`
WHERE date BETWEEN '2020-06-01' AND '2020-06-30'
  AND temperature != 9999.9; -- 如果是字符串就用'9999.9'

如果返回的行数大于0，再运行AVG查询就会得到正常结果。

内容的提问来源于stack exchange，提问作者Jesus Perez Frias