如何在IoTDB 2.0.5中实现多条件聚合统计及状态运行时长计算

需求 #

在IoTDB 2.0.5中对工业设备温度状态做小时级统计，需同时获取：

• 低温（<20℃）、正常（20-30℃）、高温（>30℃）三个状态的数据点数量
• 各状态的累计运行时长

原始数据示例 #

+-----------------------------+---------------------------------------+
| `Time`                      | `root.factory.line1.machine1.temperature` |
+-----------------------------+---------------------------------------+
|2024-01-01T08:00:00.000+08:00| 18.5                                  |
|2024-01-01T08:01:00.000+08:00| 22.3                                  |
|2024-01-01T08:02:00.000+08:00| 25.1                                  |
|2024-01-01T08:03:00.000+08:00| 23.4                                  |
|2024-01-01T08:04:00.000+08:00| 28.9                                  |
|2024-01-01T08:05:00.000+08:00| 32.5                                  |
|2024-01-01T08:06:00.000+08:00| 19.8                                  |
|2024-01-01T08:07:00.000+08:00| 31.8                                  |
+-----------------------------+---------------------------------------+

现有实现（仅统计数据点数量） #

已完成各状态数据点数量统计的SQL：

SELECT 
  count(CASE WHEN temperature between 20 and 30 THEN 1 END) as normal_count, 
  count(CASE WHEN temperature > 30 THEN 1 END) as high_count, 
  count(CASE WHEN temperature < 20 THEN 1 END) as low_count 
FROM `root.factory`.** 
GROUP BY([2024-01-01T08:00:00, 2024-01-01T09:00:00),1h)

查询结果：

+-----------------------------+-------------+------------+-----------+
| `Time`                      | normal_count| high_count | low_count |
+-----------------------------+-------------+------------+-----------+
|2024-01-01T08:00:00.000+08:00| 4           | 2          | 2         |
+-----------------------------+-------------+------------+-----------+

单条SQL实现双指标统计 #

要计算运行时长，需结合LEAD函数获取当前数据点的下一条记录时间，计算状态持续时长后按状态累加。以下是完整SQL：

SELECT 
  -- 统计各状态数据点数量
  count(CASE WHEN temperature between 20 and 30 THEN 1 END) as normal_count,
  -- 统计正常状态总运行时长（毫秒）
  sum(CASE WHEN temperature between 20 and 30 THEN 
    (COALESCE(LEAD(time) OVER (ORDER BY time), '2024-01-01T09:00:00.000+08:00') - time) 
  ELSE 0 END) as normal_duration_ms,
  
  count(CASE WHEN temperature > 30 THEN 1 END) as high_count,
  sum(CASE WHEN temperature > 30 THEN 
    (COALESCE(LEAD(time) OVER (ORDER BY time), '2024-01-01T09:00:00.000+08:00') - time) 
  ELSE 0 END) as high_duration_ms,
  
  count(CASE WHEN temperature < 20 THEN 1 END) as low_count,
  sum(CASE WHEN temperature < 20 THEN 
    (COALESCE(LEAD(time) OVER (ORDER BY time), '2024-01-01T09:00:00.000+08:00') - time) 
  ELSE 0 END) as low_duration_ms
FROM `root.factory`.** 
WHERE time >= '2024-01-01T08:00:00.000+08:00' AND time < '2024-01-01T09:00:00.000+08:00'
GROUP BY([2024-01-01T08:00:00, 2024-01-01T09:00:00),1h)

关键逻辑说明 #

1. LEAD(time) OVER (ORDER BY time)：获取当前数据点的下一条记录时间，用于计算当前状态的持续时长
2. COALESCE(..., 小时结束时间)：处理最后一条数据的状态时长，确保从最后一个数据点时间到统计周期结束的时长也被计入对应状态
3. 时间差单位为毫秒，如需转换为分钟/小时，可对结果做除法（如除以60000得到分钟数）

预期结果示例 #

基于提供的原始数据，运行上述SQL后会得到如下结果：

+-----------------------------+-------------+-------------------+------------+------------------+-----------+------------------+
| `Time`                      | normal_count| normal_duration_ms| high_count | high_duration_ms | low_count | low_duration_ms  |
+-----------------------------+-------------+-------------------+------------+------------------+-----------+------------------+
|2024-01-01T08:00:00.000+08:00| 4           | 240000            | 2          | 378000           | 2         | 120000           |
+-----------------------------+-------------+-------------------+------------+------------------+-----------+------------------+

注：示例中高温状态总时长包含8:05-8:06（60000ms）和8:07-09:00（318000ms）两部分，合计378000ms。

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