Impala SQL中如何将DOUBLE类型列的NaN值转换为NULL值

问题根因：绝大多数SQL引擎（Spark SQL、Hive、Presto、PostgreSQL等）对NULL和NaN的判定逻辑不一致：NULL = NULL返回UNKNOWN，计算时会被归为同一类缺失值；但NaN = NaN在多数引擎中会返回TRUE，因此窗口分区时会将所有NaN值划分为独立子分组，不会和NULL合并。解决核心是先统一缺失值表示，再执行窗口、聚合计算，根据业务需求二选一即可。

方案1：统一将NaN转为NULL（推荐） #

该方案适配绝大多数数仓场景，SQL原生聚合、窗口逻辑默认会跳过NULL值，不会出现聚合结果意外返回NaN的问题，不需要每次写计算逻辑时单独兼容NaN。

• 不修改原表数据，仅在窗口计算时临时转换的写法：

-- 原错误写法：直接用col1分区会把NaN单独分组
-- SELECT *, sum(col1) OVER (PARTITION BY col1) FROM your_table;

-- 修正后写法
SELECT 
  *, 
  sum(CASE WHEN isnan(col1) THEN NULL ELSE col1 END) 
  OVER (PARTITION BY CASE WHEN isnan(col1) THEN NULL ELSE col1 END) 
FROM your_table;

• 需要永久统一列内缺失值类型时，直接执行更新语句即可：

UPDATE your_table 
SET col1 = NULL 
WHERE isnan(col1);

方案2：统一将NULL转为NaN #

如果业务侧要求缺失值统一用NaN表示，就将列内所有NULL替换为NaN，保证分区字段中没有残留NULL即可。

• 不修改原表数据，仅在窗口计算时临时转换的写法：

SELECT 
  *, 
  sum(COALESCE(col1, CAST('NaN' AS DOUBLE))) 
  OVER (PARTITION BY COALESCE(col1, CAST('NaN' AS DOUBLE))) 
FROM your_table;

• 需要永久统一列内缺失值类型时，执行更新语句：

UPDATE your_table
SET col1 = CAST('NaN' AS DOUBLE)
WHERE col1 IS NULL;

注意事项 #

• 禁止直接用col1 = NaN做值判断：绝大多数SQL引擎中该写法无法返回正确的布尔结果，判定NaN必须使用引擎内置的isnan()函数；如果使用的是MySQL（无内置isnan函数），可以用col1 = CAST('NaN' AS DOUBLE)替代判定。
• 无论选择哪种缺失值作为统一标准，必须保证参与分区、聚合计算的字段中仅保留一种缺失值类型，不要同时存在NULL和NaN，否则依然会出现独立子分组、聚合结果异常返回NaN的问题。
• 如果仅需要单次计算正确，不需要修改原始数据，直接在窗口函数的PARTITION BY子句、聚合参数中嵌入转换逻辑即可，不会影响原表字段取值。

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