如何在Apache IoTDB中按设备展示多设备数据及变化率

场景与需求 #

在Apache IoTDB 2.0.5中存储了多设备电压数据，数据路径格式为root.plant.*.voltage。需要实现监控仪表盘式查询：

• 按设备分组展示数据
• 显示每个设备数据点的变化率（通过derivative函数计算）
• 每个设备返回最新的5条数据

原始数据示例 #

+-----------------------------+--------------------------+--------------------------+
| `Time`                      | `root.plant.device1.voltage` | `root.plant.device2.voltage` |
+-----------------------------+--------------------------+--------------------------+
|2023-10-01T12:00:00.000+08:00| 219.8                    | 218.5                    |
|2023-10-01T12:00:01.000+08:00| 220.0                    | 218.7                    |
|2023-10-01T12:00:02.000+08:00| 220.3                    | 219.0                    |
|2023-10-01T12:00:03.000+08:00| 220.1                    | 218.8                    |
|2023-10-01T12:00:04.000+08:00| 220.5                    | 219.2                    |
|2023-10-01T12:00:05.000+08:00| 220.7                    | 219.5                    |
|2023-10-01T12:00:06.000+08:00| 220.6                    | 219.3                    |
|2023-10-01T12:00:07.000+08:00| 220.9                    | 219.7                    |
|2023-10-01T12:00:08.000+08:00| 221.0                    | 219.9                    |
|2023-10-01T12:00:09.000+08:00| 220.8                    | 219.6                    |
+-----------------------------+--------------------------+--------------------------+

问题分析 #

尝试1：全局按时间排序 #

执行SQL：

SELECT voltage, derivative(voltage)
FROM `root.plant`.**
ORDER BY `time` DESC LIMIT 10 ALIGN BY DEVICE;

结果为全局最新的10条数据，设备数据按时间交替出现，无法实现分组展示：

+-----------------------------+------------------+-------+----------------------+
| `Time`                      | Device           |voltage| derivative(voltage)  |
+-----------------------------+------------------+-------+----------------------+
|2023-10-01T12:00:09.000+08:00|`root.plant.device1`| 220.8| `null`               |
|2023-10-01T12:00:09.000+08:00|`root.plant.device2`| 219.6| `null`               |
|2023-10-01T12:00:08.000+08:00|`root.plant.device1`| 221.0|-1.9999999999998863E-4|
|2023-10-01T12:00:08.000+08:00|`root.plant.device2`| 219.9|-3.0000000000001136E-4|
|2023-10-01T12:00:07.000+08:00|`root.plant.device1`| 220.9| 9.999999999999431E-5 |
|2023-10-01T12:00:07.000+08:00|`root.plant.device2`| 219.7| 2.0000000000001706E-4|
|2023-10-01T12:00:06.000+08:00|`root.plant.device1`| 220.6| 3.0000000000001136E-4|
|2023-10-01T12:00:06.000+08:00|`root.plant.device2`| 219.3| 3.9999999999997725E-4|
|2023-10-01T12:00:05.000+08:00|`root.plant.device1`| 220.7| -9.999999999999431E-5|
|2023-10-01T12:00:05.000+08:00|`root.plant.device2`| 219.5|-1.9999999999998863E-4|
+-----------------------------+------------------+-------+----------------------+

尝试2：直接按设备+时间排序 #

执行SQL：

SELECT voltage, derivative(voltage)
FROM `root.plant`.**
ORDER BY device,`time` DESC LIMIT 10 ALIGN BY DEVICE;

结果仅返回第一个设备的10条数据，无法获取所有设备的分组数据：

+-----------------------------+------------------+-------+----------------------+
| `Time`                      | Device           |voltage| derivative(voltage)  |
+-----------------------------+------------------+-------+----------------------+
|2023-10-01T12:00:09.000+08:00|`root.plant.device1`| 220.8| `null`               |
|2023-10-01T12:00:08.000+08:00|`root.plant.device1`| 221.0|-1.9999999999998863E-4|
|2023-10-01T12:00:07.000+08:00|`root.plant.device1`| 220.9| 9.999999999999431E-5 |
|2023-10-01T12:00:06.000+08:00|`root.plant.device1`| 220.6| 3.0000000000001136E-4|
|2023-10-01T12:00:05.000+08:00|`root.plant.device1`| 220.7| -9.999999999999431E-5|
|2023-10-01T12:00:04.000+08:00|`root.plant.device1`| 220.5| 1.9999999999998863E-4|
|2023-10-01T12:00:03.000+08:00|`root.plant.device1`| 220.1| 4.000000000000057E-4 |
|2023-10-01T12:00:02.000+08:00|`root.plant.device1`| 220.3|-2.0000000000001706E-4|
|2023-10-01T12:00:01.000+08:00|`root.plant.device1`| 220.0| 3.0000000000001136E-4|
|2023-10-01T12:00:00.000+08:00|`root.plant.device1`| 219.8| 1.9999999999998863E-4|
+-----------------------------+------------------+-------+----------------------+

解决方案 #

使用子查询先按设备获取最新N条数据，再在外层查询中按设备分组排序并计算变化率：

SELECT voltage, derivative(voltage)
FROM (
    -- 子查询：每个设备取最新5条电压数据
    SELECT voltage
    FROM `root.plant`.**
    ORDER BY `time` DESC LIMIT 5 ALIGN BY DEVICE
)
-- 外层按设备、时间降序排序，确保分组展示
ORDER BY device, `time` DESC ALIGN BY DEVICE;

预期结果 #

执行上述SQL后，将得到按设备分组、每个设备展示最新5条数据及对应变化率的结果：

+-----------------------------+------------------+-------+----------------------+
| `Time`                      | Device           |voltage| derivative(voltage)  |
+-----------------------------+------------------+-------+----------------------+
|2023-10-01T12:00:09.000+08:00|`root.plant.device1`| 220.8| `null`               |
|2023-10-01T12:00:08.000+08:00|`root.plant.device1`| 221.0|-1.9999999999998863E-4|
|2023-10-01T12:00:07.000+08:00|`root.plant.device1`| 220.9| 9.999999999999431E-5 |
|2023-10-01T12:00:06.000+08:00|`root.plant.device1`| 220.6| 3.0000000000001136E-4|
|2023-10-01T12:00:05.000+08:00|`root.plant.device1`| 220.7| -9.999999999999431E-5|
|2023-10-01T12:00:09.000+08:00|`root.plant.device2`| 219.6| `null`               |
|2023-10-01T12:00:08.000+08:00|`root.plant.device2`| 219.9|-3.0000000000001136E-4|
|2023-10-01T12:00:07.000+08:00|`root.plant.device2`| 219.7| 2.0000000000001706E-4|
|2023-10-01T12:00:06.000+08:00|`root.plant.device2`| 219.3| 3.9999999999997725E-4|
|2023-10-01T12:00:05.000+08:00|`root.plant.device2`| 219.5|-1.9999999999998863E-4|
+-----------------------------+------------------+-------+----------------------+

原理说明 #

• 子查询中的ALIGN BY DEVICE配合LIMIT 5，会对每个设备单独取最新的5条数据，而非全局限制条数。
• 外层查询通过ORDER BY device, time DESC将相同设备的数据聚合在一起，并按时间降序排列，实现分组展示的效果。

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