如何让Apache IoTDB的COALESCE返回首个非NULL且大于0的值？

当前使用Apache IoTDB 2.0.6的表模型，原生COALESCE函数仅能返回首个非NULL值，无法过滤掉≤0的数值，需要实现返回首个非NULL且大于0的值，若所有参数均为NULL或≤0则返回NULL的逻辑。

示例数据定义与插入 #

CREATE TABLE sensor_data (device_id tag,real_temp FLOAT field, backup_temp FLOAT field, default_temp FLOAT field);

INSERT INTO sensor_data (`time`, device_id, real_temp, backup_temp, default_temp) 
VALUES 
(2024-11-30 00:00:00, 'sensor_01', NULL, -5.0, 25.0),
(2024-11-30 00:01:00, 'sensor_01', -10.0, NULL, -8.0),
(2024-11-30 00:02:00, 'sensor_01', 30.0, 28.0, 26.0);

原生COALESCE的不足 #

原查询语句仅返回首个非NULL值，未过滤负数，不符合需求：

SELECT COALESCE(real_temp, backup_temp, default_temp) AS final_temp FROM sensor_data;

解决方案：使用CASE WHEN实现自定义优先级判断 #

由于IoTDB原生COALESCE不支持附加数值过滤条件，可通过CASE WHEN按字段优先级依次判断，实现目标逻辑：

SELECT 
  CASE
    WHEN real_temp IS NOT NULL AND real_temp > 0 THEN real_temp
    WHEN backup_temp IS NOT NULL AND backup_temp > 0 THEN backup_temp
    WHEN default_temp IS NOT NULL AND default_temp > 0 THEN default_temp
    ELSE NULL
  END AS final_temp
FROM sensor_data;

执行结果 #

该查询会返回符合预期的结果：

+----------+
|final_temp|
+----------+
| 25.0     |
| NULL     |
| 30.0     |
+----------+

逻辑说明 #

• 按real_temp→backup_temp→default_temp的顺序依次检查每个字段
• 仅当字段非NULL且数值大于0时，返回该字段的值
• 若所有字段都不满足条件（NULL或≤0），则返回NULL

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