Apache IoTDB 2.0.6关联子查询报错及获取设备最新记录问询

问题场景 #

使用Apache IoTDB 2.0.6的表模型，想要实现获取每个设备最新记录的需求，执行关联子查询SQL时报错：

SELECT * FROM sensors t1 WHERE ts = (SELECT ts FROM sensors t2 WHERE t2.device_id = t1.device_id ORDER BY ts DESC LIMIT 1);

报错信息：

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Given correlated subquery is not supported

数据定义与插入语句 #

CREATE TABLE sensors (device_id STRING Tag, ts INT64 Field, temperature DOUBLE Field);

INSERT INTO sensors(`time`, device_id, ts, temperature) VALUES (2024-09-24T14:00:00.000+08:00, 'd01', 1, 25.5);

INSERT INTO sensors(`time`, device_id, ts, temperature) VALUES (2024-09-24T14:01:00.000+08:00, 'd01', 2, 26.0);

INSERT INTO sensors(`time`, device_id, ts, temperature) VALUES (2024-09-24T14:02:00.000+08:00, 'd01', 3, 27.0);

INSERT INTO sensors(`time`, device_id, ts, temperature) VALUES (2024-09-24T14:00:00.000+08:00, 'd02', 1, 23.5);

INSERT INTO sensors(`time`, device_id, ts, temperature) VALUES (2024-09-24T14:01:00.000+08:00, 'd02', 2, 24.0);

已尝试的方法及局限 #

曾使用max_by聚合函数间接实现：

SELECT device_id, max(temperature), max_by(ts, temperature) AS ts FROM sensors `GROUP BY` device_id;

但该方式仅返回device_id、temperature和ts列，无法获取完整原始记录（比如time列）。

疑问解答与可行方案 #

为何IoTDB不支持这类关联子查询？ #

IoTDB作为时序数据库，核心优化方向是针对时序数据的高效读写与聚合操作。带LIMIT的关联子查询在时序场景下执行效率较低，且IoTDB的SQL解析优化器暂未实现对这类关联子查询的支持，因此会抛出不支持的报错。

获取每个设备最新完整记录的SQL写法 #

可以利用IoTDB 2.0及以上版本支持的ROW_NUMBER()窗口函数实现，该方法能获取包含所有字段的完整原始记录：

SELECT device_id, ts, temperature, `time`
FROM (
    SELECT 
        device_id, 
        ts, 
        temperature, 
        `time`,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY ts DESC) AS rn
    FROM sensors
) t
WHERE rn = 1;

该语句通过PARTITION BY device_id按设备分组，每组内按ts降序排列，用ROW_NUMBER()为每组记录编号，最后筛选出编号为1的记录，即每个设备ts最大的最新记录。

如果只需要预期结果中的列，可调整SELECT子句：

SELECT device_id, ts, temperature
FROM (
    SELECT 
        device_id, 
        ts, 
        temperature,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY ts DESC) AS rn
    FROM sensors
) t
WHERE rn = 1;

内容的提问来源于stack exchange，提问作者ごまめとにまめ