如何在Apache IoTDB中通过GREATEST函数获取全局最大值？

在Apache IoTDB 2.0.6表模型中，GREATEST函数仅能获取单条记录内数值或时间戳字段的最大值，但业务需要针对指定设备计算两类全局唯一最大值：

• 所有非null单条记录数值最大值（max_num）中的全局最大值
• 所有单条记录时间戳最大值（max_time）中的全局最大值

由于GREATEST不支持跨数据类型比较，可通过嵌套子查询与聚合函数实现"先单条计算、再全局聚合"的逻辑。

原始数据 #

select * from testdata
+-----------------------------+---------+-----------+--------+-----------------------------+
| time                        |device_id|temperature|humidity| arrival_time                |
+-----------------------------+---------+-----------+--------+-----------------------------+
|2024-11-27T16:38:00.000+08:00|sensor_01| 35.1      | 60.0   |2024-11-27T16:38:00.000+08:00|
|2024-11-27T16:43:00.000+08:00|sensor_01| `null`    | 50.0   |2024-11-27T16:43:00.000+08:00|
|2024-11-27T16:44:00.000+08:00|sensor_01| `null`    | `null` |2024-11-27T16:44:00.000+08:00|
|2024-11-29T10:00:13.000+08:00|sensor_01| 85.0      | 75.0   |2024-11-29T10:00:13.000+08:00|
|2024-11-30T09:30:00.000+08:00|sensor_01| 95.0      | 85.0   |2024-11-30T09:30:00.000+08:00|
|2024-11-30T14:30:17.000+08:00|sensor_01| 90.0      | 80.0   |2024-11-30T14:30:17.000+08:00|
+-----------------------------+---------+-----------+--------+-----------------------------+

基础查询及局限 #

直接使用GREATEST只能返回单条记录内的最大值，无法得到全局结果：

SELECT greatest(temperature, humidity) AS max_num, greatest(`time`, arrival_time) AS max_time
FROM testdata
WHERE device_id = 'sensor_01';

查询结果：

+-------+-----------------------------+
|max_num| max_time                    |
+-------+-----------------------------+
| 60.0  |2024-11-27T16:38:00.000+08:00|
| 50.0  |2024-11-27T16:43:00.000+08:00|
| `null`|2024-11-27T16:44:00.000+08:00|
| 85.0  |2024-11-29T10:00:13.000+08:00|
| 95.0  |2024-11-30T09:30:00.000+08:00|
| 90.0  |2024-11-30T14:30:17.000+08:00|
+-------+-----------------------------+

期望结果 #

返回全局唯一的最大值：

+-------+-----------------------------+
|max_num| max_time                    |
+-------+-----------------------------+
| 95.0  |2024-11-30T14:30:17.000+08:00|
+-------+-----------------------------+

实现SQL语句 #

通过子查询嵌套聚合函数，先计算单条记录的最大值，再对结果集做全局聚合：

SELECT 
    MAX(max_num) AS max_num, 
    MAX(max_time) AS max_time
FROM (
    SELECT 
        greatest(temperature, humidity) AS max_num, 
        greatest(`time`, arrival_time) AS max_time
    FROM testdata
    WHERE device_id = 'sensor_01'
) AS temp
WHERE max_num IS NOT NULL;

逻辑说明 #

1. 子查询层：遍历sensor_01的所有记录，计算每条记录的数值最大值（max_num）和时间戳最大值（max_time）
2. 外层聚合层：对max_num使用MAX()聚合并过滤null值，对max_time直接使用MAX()聚合（时间戳字段无null），最终得到全局唯一的两类最大值

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