为什么CAST('NaN' AS DOUBLE) 会让MIN、MAX、AVG都返回NaN？

编程语言 2026-07-07

我在表模型中使用Apache IoTDB 2.0.8，在聚合前将上游的字符串读数转换为DOUBLE。该列包含 'NaN' 和正常值12.5。行级转换返回NaN和 12.5，但MIN、MAX和 AVG都变成NaN。

原始数据：

CREATE TABLE replacement_nan_cast (device_id STRING TAG, txt STRING FIELD);
INSERT INTO replacement_nan_cast(time, device_id, txt) VALUES (1000, 'D1', 'NaN');
INSERT INTO replacement_nan_cast(time, device_id, txt) VALUES (2000, 'D1', '12.5');

让我吃惊的聚合：在包含NaN的强制转换结果上进行MIN/MAX/AVG：

SELECT MIN(CAST(txt AS DOUBLE)) AS min_n, MAX(CAST(txt AS DOUBLE)) AS max_n, AVG(CAST(txt AS DOUBLE)) AS avg_n, COUNT(CAST(txt AS DOUBLE)) AS n FROM replacement_nan_cast;

结果：

+-----+-----+-----+-+
|min_n|max_n|avg_n|n|
+-----+-----+-----+-+
| NaN| NaN| NaN|2|
+-----+-----+-----+-+

检查行级转换和比较：

SELECT time, txt, CAST(txt AS DOUBLE) AS n, CAST(txt AS DOUBLE) = CAST(txt AS DOUBLE) AS self_eq, CAST(txt AS DOUBLE) > 0 AS gt_zero FROM replacement_nan_cast ORDER BY time;

结果：

+-----------------------------+----+----+-------+-------+
| time| txt| n|self_eq|gt_zero|
+-----------------------------+----+----+-------+-------+
|1970-01-01T08:00:01.000+08:00| NaN| NaN| false| false|
|1970-01-01T08:00:02.000+08:00|12.5|12.5| true| true|
+-----------------------------+----+----+-------+-------+

IoTDB的表模型聚合会将DOUBLE的 NaN传播到MIN、MAX和 AVG吗？如果上游的字符串可能包含 'NaN'，是否有办法在聚合前排除这些行，以免整个聚合结果被污染？

解决方案

尝试过滤NaN值。你可以使用一个 WHERE 子句，它在聚合之前生效

例如：

SELECT MIN(CAST(txt AS DOUBLE)) AS min_n, 
  MAX(CAST(txt AS DOUBLE)) AS max_n, 
  AVG(CAST(txt AS DOUBLE)) AS avg_n, 
  COUNT(CAST(txt AS DOUBLE)) AS n 
FROM replacement_nan_cast
WHERE txt != 'NaN';

SQL