Apache IoTDB非等值关联子查询报“Unsupported Join criteria”错误

原始数据 #

CREATE TABLE sensors (device_id STRING Tag, temperature DOUBLE Field, humidity DOUBLE Field);
INSERT INTO sensors(`time`, device_id, temperature, humidity) VALUES 
(2024-09-24T14:00:00.000+08:00, 'd01', 25.5, 60.0),(2024-09-24T14:01:00.000+08:00, 
'd01', 26.0, 65.0),(2024-09-24T14:02:00.000+08:00, 'd01', 27.0, 70.0),
(2024-09-24T14:00:00.000+08:00, 'd02', 23.0, 55.0),(2024-09-24T14:01:00.000+08:00, 
'd02', 24.0, 58.0);

尝试的查询SQL #

需求为筛选d01中温度高于“所有湿度大于当前记录湿度的温度平均值”的记录，尝试用关联子查询实现：

SELECT *
FROM sensors t1
WHERE t1.device_id = 'd01' AND t1.temperature > (
    SELECT avg(temperature)
    FROM sensors t2
    WHERE t2.humidity > t1.humidity);

错误信息 #

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Unsupported Join creteria
[("humidity_3" > "humidity")] after predicate push down

已尝试的改写 #

查阅文档得知IoTDB关联子查询仅支持等值谓词，尝试改写为等值条件，但不符合原始业务逻辑：

SELECT *
FROM sensors t1
WHERE t1.device_id = 'd01' AND t1.humidity = (
    SELECT avg (humidity)
    FROM sensors t2
    WHERE t2.device_id = t1.device_id);
-- 返回结果: d01/26.0/65.0

核心问题 #

错误提示“Unsupported Join criteria”，IoTDB 2.0.6是否仅支持等值关联子查询？有没有其他方式实现非等值关联的业务需求？

解决方案 #

IoTDB 2.0.6的关联子查询确实仅支持等值谓词，这是当前版本的设计限制。可以通过交叉关联+分组计算的方式替代实现需求：

SELECT t.*
FROM sensors t
JOIN (
    -- 先计算d01每条记录湿度对应的、所有湿度大于该值的温度平均值
    SELECT t1.humidity, AVG(t2.temperature) AS avg_temp
    FROM sensors t1, sensors t2
    WHERE t1.device_id = 'd01' AND t2.humidity > t1.humidity
    GROUP BY t1.humidity
) ha ON t.humidity = ha.humidity
WHERE t.device_id = 'd01' AND t.temperature > ha.avg_temp;

预期结果 #

执行上述SQL后，将返回d01中温度高于对应湿度阈值平均温度的记录：

2024-09-24T14:02:00.000+08:00, 'd01', 27.0, 70.0

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