BigQuery中将零值转换为Null值时遇问题求助

先排查数据类型问题 #

如果wind_speed是字符串类型，直接用=0会触发类型不匹配错误。这时候要按字符串形式匹配，或者转成数值类型：

-- 针对字符串类型的wind_speed
SELECT 
  IF(wind_speed = '0' OR wind_speed = '', NULL, CAST(wind_speed AS FLOAT64)) AS cleaned_wind_speed
FROM 
  weather_data
WHERE 
  year = 2023;

换用CASE语句尝试 #

有时候IF函数的语法细节容易踩坑，CASE语句逻辑更直观，不容易出错：

SELECT 
  CASE 
    WHEN wind_speed = 0 THEN NULL
    ELSE wind_speed 
  END AS cleaned_wind_speed
FROM 
  weather_data
WHERE 
  year = 2023;

要是wind_speed是字符串，把条件改成WHEN wind_speed = '0'就行。

覆盖特殊零值场景 #

如果数据里有0.0、00这类变种零值，扩展判断条件就能处理：

SELECT 
  CASE 
    WHEN wind_speed IN (0, 0.0, '0', '00') THEN NULL
    ELSE wind_speed 
  END AS cleaned_wind_speed
FROM 
  weather_data
WHERE 
  year = 2023;

先确认数据类型和样本 #

拿这个查询先看看wind_speed的类型和实际数据，能更快定位问题：

SELECT 
  typeof(wind_speed),
  wind_speed
FROM 
  weather_data
WHERE 
  year = 2023
LIMIT 10;

内容的提问来源于stack exchange，提问作者Ronaria Elisabeth Buaton M004D