如何在QuestDB/PostgreSQL中对含NaN、Inf的double列求和？

QuestDB 解决方案 #

QuestDB内置了专门判断无效浮点值的函数，结合SUM自动忽略NULL的特性，可通过两种方式实现有效求和：

• 方式一：CASE语句过滤无效值

  SELECT SUM(CASE 
              WHEN is_nan(col_name) OR is_infinite(col_name) THEN NULL 
              ELSE col_name 
            END) AS valid_sum
  FROM your_table;
  

  is_nan()识别NaN值，is_infinite()识别正负无穷大值，将这些无效值转为NULL后，SUM会自动跳过它们，只计算有效数值的和。

• 方式二：FILTER子句（更简洁）

  SELECT SUM(col_name) FILTER (WHERE NOT is_nan(col_name) AND NOT is_infinite(col_name)) AS valid_sum
  FROM your_table;
  

  直接在SUM函数后通过FILTER指定过滤条件，语法直观，效果和方式一完全一致。

PostgreSQL 解决方案 #

PostgreSQL处理浮点无效值的函数略有差异，同样通过过滤逻辑解决问题：

• 方式一：CASE语句过滤

  SELECT SUM(CASE 
              WHEN isnan(col_name) OR isinf(col_name) THEN NULL 
              ELSE col_name 
            END) AS valid_sum
  FROM your_table;
  

  用isnan()判断NaN，isinf()判断正负无穷大，逻辑和QuestDB一致。

• 方式二：FILTER子句

  SELECT SUM(col_name) FILTER (WHERE NOT isnan(col_name) AND NOT isinf(col_name)) AS valid_sum
  FROM your_table;
  

  支持PostgreSQL风格的FILTER子句，写法简洁高效。

• 补充：单独处理无穷大值（可选）
  如果仅需排除无穷大（注意NaN参与SUM仍会返回NaN，一般不建议保留），可使用NULLIF转换：

  SELECT SUM(NULLIF(NULLIF(col_name, 'Infinity'::double precision), '-Infinity'::double precision)) AS valid_sum
  FROM your_table;
  

  但该方法无法处理NaN，更推荐前两种全面过滤的方案。

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