如何用Apache IoTDB的GROUP BY VARIATION识别传感器缺失数据时段

问题背景 #

需要识别root.sn3849320.dv.ml下各传感器（s1-s6）的连续Null数据时段，输出格式要求为：

Sensor | GapStartTime | GapEndTime
s1 | 2025-01-01T08:00:00.010+08:00 | 2025-01-01T08:00:00.010+08:00
s1 | 2025-01-01T08:00:00.050+08:00 | 2025-01-01T08:00:00.050+08:00
s3 | 2025-01-01T08:00:00.020+08:00 | 2025-01-01T08:00:00.020+08:00
...

解决方案 #

利用IoTDB的GROUP BY VARIATION功能，通过判断传感器值是否为Null作为分组依据，筛选出连续Null的时段。

1. 单个传感器的查询语句 #

以查询s1的连续Null时段为例：

SELECT
  's1' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s1), 0, ignoreNull=false)
HAVING is_null(first_value(s1))

2. 批量查询所有传感器的时段 #

通过UNION ALL将各传感器的查询结果合并，得到统一格式的输出：

-- s1的连续Null时段
SELECT
  's1' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s1), 0, ignoreNull=false)
HAVING is_null(first_value(s1))

UNION ALL

-- s2的连续Null时段
SELECT
  's2' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s2), 0, ignoreNull=false)
HAVING is_null(first_value(s2))

UNION ALL

-- s3的连续Null时段
SELECT
  's3' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s3), 0, ignoreNull=false)
HAVING is_null(first_value(s3))

UNION ALL

-- s4的连续Null时段
SELECT
  's4' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s4), 0, ignoreNull=false)
HAVING is_null(first_value(s4))

UNION ALL

-- s5的连续Null时段
SELECT
  's5' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s5), 0, ignoreNull=false)
HAVING is_null(first_value(s5))

UNION ALL

-- s6的连续Null时段
SELECT
  's6' AS Sensor,
  __startTime AS GapStartTime,
  __endTime AS GapEndTime
FROM `root.sn3849320.dv.ml`
GROUP BY VARIATION(is_null(s6), 0, ignoreNull=false)
HAVING is_null(first_value(s6))

逻辑说明 #

• VARIATION(is_null(传感器), 0, ignoreNull=false)：以传感器值是否为Null作为分组触发条件，当状态（Null/非Null）发生变化时自动创建新分组，确保每个分组内状态一致。
• HAVING is_null(first_value(传感器))：筛选出分组内第一个值为Null的组，这类分组对应连续的Null数据时段。
• __startTime和__endTime：IoTDB分组后自动生成的字段，分别代表当前分组的起始和结束时间。

示例输出 #

执行上述查询后，将得到符合需求的结果：

Sensor | GapStartTime | GapEndTime
s1 | 2025-01-01T08:00:00.010+08:00 | 2025-01-01T08:00:00.010+08:00
s1 | 2025-01-01T08:00:00.050+08:00 | 2025-01-01T08:00:00.050+08:00
s3 | 2025-01-01T08:00:00.020+08:00 | 2025-01-01T08:00:00.020+08:00
s3 | 2025-01-01T08:00:00.070+08:00 | 2025-01-01T08:00:00.070+08:00
s4 | 2025-01-01T08:00:00.070+08:00 | 2025-01-01T08:00:00.070+08:00
s5 | 2025-01-01T08:00:00.030+08:00 | 2025-01-01T08:00:00.030+08:00
s6 | 2025-01-01T08:00:00.020+08:00 | 2025-01-01T08:00:00.030+08:00
s6 | 2025-01-01T08:00:00.060+08:00 | 2025-01-01T08:00:00.060+08:00
s2 | 2025-01-01T08:00:00.030+08:00 | 2025-01-01T08:00:00.030+08:00

内容的提问来源于stack exchange，提问作者小林蓮