CnosDB中使用timestamp_repair后过滤NaN结果异常的技术问询

问题场景 #

1. 创建表：

CREATE table wzz(value double);

2. 写入测试数据：

INSERT wzz VALUES 
('2024-01-01T00:00:00.000',1),
('2024-01-01T00:00:10.000',2),
('2024-01-01T00:00:19.000',3),
('2024-01-01T00:00:30.000',4),
('2024-01-01T00:00:40.000',5),
('2024-01-01T00:00:50.000',6),
('2024-01-01T00:01:01.000',7),
('2024-01-01T00:01:11.000',8),
('2024-01-01T00:01:21.000',9),
('2024-01-01T00:01:31.000',10);

3. 使用timestamp_repair修复时间序列数据：

SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') FROM wzz;

返回结果中出现一条冗余数据：

2024-01-01T00:01:40.300 | NaN 

4. 尝试两种过滤方式均失败：

• 直接在WHERE子句中调用timestamp_repair：

SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') FROM  wzz WHERE timestamp_repair(time, value, 'method=mode&start_mode=linear') != 'NaN';

报错信息：

422 Unprocessable Entity, details: {"error_code":"010001","error_message":"Datafusion: This feature is not implemented: timestamp_repair is not yet implemented"}

• 使用SELECT别名在WHERE中过滤：

SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') as f1 FROM  wzz WHERE f1 != 'NaN';

报错信息：

422 Unprocessable Entity, details: {"error_code":"010001","error_message":"Datafusion: Schema error: No field named f1. Valid fields are wzz.time, wzz.value."}

解决方案 #

由于CnosDB的WHERE子句不支持直接引用SELECT中的别名，也无法在WHERE中调用timestamp_repair函数，同时NaN是数值类型的特殊值，不能用字符串比较判断，因此需要先通过子查询或CTE生成修复后的数据集，再用isnan()函数过滤NaN值。

方法1：子查询 #

SELECT t.time, t.value FROM (
    SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') as (time, value)
    FROM wzz
) t
WHERE NOT isnan(t.value);

方法2：CTE（公共表表达式） #

WITH repaired_data AS (
    SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') as (time, value)
    FROM wzz
)
SELECT time, value FROM repaired_data
WHERE NOT isnan(value);

说明：isnan()是CnosDB专门用于判断double类型是否为NaN的函数，能准确识别数值类型的NaN值，避免字符串比较的错误。

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