数据查询
2025年1月3日大约 10 分钟
数据查询
1. 语法概览
SELECT ⟨select_list⟩
FROM ⟨tables⟩
[WHERE ⟨condition⟩]
[GROUP BY ⟨groups⟩]
[HAVING ⟨group_filter⟩]
[FILL ⟨fill_methods⟩]
[ORDER BY ⟨order_expression⟩]
[OFFSET ⟨n⟩]
[LIMIT ⟨n⟩];
IoTDB 查询语法提供以下子句:
- SELECT 子句:查询结果应包含的列。详细语法见:SELECT子句
- FROM 子句:指出查询的数据源,可以是单个表、多个通过
JOIN
子句连接的表,或者是一个子查询。详细语法见:FROM & JOIN 子句 - WHERE 子句:用于过滤数据,只选择满足特定条件的数据行。这个子句在逻辑上紧跟在 FROM 子句之后执行。详细语法见:WHERE 子句
- GROUP BY 子句:当需要对数据进行聚合时使用,指定了用于分组的列。详细语法见:GROUP BY 子句
- HAVING 子句:在 GROUP BY 子句之后使用,用于对已经分组的数据进行过滤。与 WHERE 子句类似,但 HAVING 子句在分组后执行。详细语法见:HAVING 子句
- FILL 子句:用于处理查询结果中的空值,用户可以使用 FILL 子句来指定数据缺失时的填充模式(如前一个非空值或线性插值)来填充 null 值,以便于数据可视化和分析。 详细语法见:FILL 子句
- ORDER BY 子句:对查询结果进行排序,可以指定升序(ASC)或降序(DESC),以及 NULL 值的处理方式(NULLS FIRST 或 NULLS LAST)。详细语法见:ORDER BY 子句
- OFFSET 子句:用于指定查询结果的起始位置,即跳过前 OFFSET 行。与 LIMIT 子句配合使用。详细语法见:LIMIT 和 OFFSET 子句
- LIMIT 子句:限制查询结果的行数,通常与 OFFSET 子句一起使用以实现分页功能。详细语法见:LIMIT 和 OFFSET 子句
2. 子句执行顺序

3. 常见查询示例
3.1 示例数据
在示例数据页面中,包含了用于构建表结构和插入数据的SQL语句,下载并在IoTDB CLI中执行这些语句,即可将数据导入IoTDB,您可以使用这些数据来测试和执行示例中的SQL语句,并获得相应的结果。
3.2 原始数据查询
示例1:根据时间过滤
IoTDB> SELECT time, temperature, humidity
FROM table1
WHERE time >= 2024-11-27 00:00:00 and time <= 2024-11-29 00:00:00;
执行结果如下:
+-----------------------------+-----------+--------+
| time|temperature|humidity|
+-----------------------------+-----------+--------+
|2024-11-28T08:00:00.000+08:00| 85.0| null|
|2024-11-28T09:00:00.000+08:00| null| 40.9|
|2024-11-28T10:00:00.000+08:00| 85.0| 35.2|
|2024-11-28T11:00:00.000+08:00| 88.0| 45.1|
|2024-11-27T16:38:00.000+08:00| null| 35.1|
|2024-11-27T16:39:00.000+08:00| 85.0| 35.3|
|2024-11-27T16:40:00.000+08:00| 85.0| null|
|2024-11-27T16:41:00.000+08:00| 85.0| null|
|2024-11-27T16:42:00.000+08:00| null| 35.2|
|2024-11-27T16:43:00.000+08:00| null| null|
|2024-11-27T16:44:00.000+08:00| null| null|
+-----------------------------+-----------+--------+
Total line number = 11
It costs 0.075s
示例2:根据值过滤
IoTDB> SELECT time, temperature, humidity
FROM table1
WHERE temperature > 89.0;
执行结果如下:
+-----------------------------+-----------+--------+
| time|temperature|humidity|
+-----------------------------+-----------+--------+
|2024-11-29T18:30:00.000+08:00| 90.0| 35.4|
|2024-11-26T13:37:00.000+08:00| 90.0| 35.1|
|2024-11-26T13:38:00.000+08:00| 90.0| 35.1|
|2024-11-30T09:30:00.000+08:00| 90.0| 35.2|
|2024-11-30T14:30:00.000+08:00| 90.0| 34.8|
+-----------------------------+-----------+--------+
Total line number = 5
It costs 0.156s
示例3:根据属性过滤
IoTDB> SELECT time, temperature, humidity
FROM table1
WHERE model_id ='B';
执行结果如下:
+-----------------------------+-----------+--------+
| time|temperature|humidity|
+-----------------------------+-----------+--------+
|2024-11-27T16:38:00.000+08:00| null| 35.1|
|2024-11-27T16:39:00.000+08:00| 85.0| 35.3|
|2024-11-27T16:40:00.000+08:00| 85.0| null|
|2024-11-27T16:41:00.000+08:00| 85.0| null|
|2024-11-27T16:42:00.000+08:00| null| 35.2|
|2024-11-27T16:43:00.000+08:00| null| null|
|2024-11-27T16:44:00.000+08:00| null| null|
+-----------------------------+-----------+--------+
Total line number = 7
It costs 0.106s
示例4:多设备按时间对齐查询
IoTDB> SELECT date_bin_gapfill(1d, TIME) AS a_time,
device_id,
AVG(temperature) AS avg_temp
FROM table1
WHERE TIME >= 2024-11-26 13:00:00
AND TIME <= 2024-11-27 17:00:00
GROUP BY 1, device_id FILL METHOD PREVIOUS;
执行结果如下:
+-----------------------------+---------+--------+
| a_time|device_id|avg_temp|
+-----------------------------+---------+--------+
|2024-11-26T08:00:00.000+08:00| 100| 90.0|
|2024-11-27T08:00:00.000+08:00| 100| 90.0|
|2024-11-26T08:00:00.000+08:00| 101| 90.0|
|2024-11-27T08:00:00.000+08:00| 101| 85.0|
+-----------------------------+---------+--------+
Total line number = 4
It costs 0.048s
3.3 聚合查询
示例:查询计算了在指定时间范围内,每个device_id
的平均温度、最高温度和最低温度。
IoTDB> SELECT device_id, AVG(temperature) as avg_temp, MAX(temperature) as max_temp, MIN(temperature) as min_temp
FROM table1
WHERE time >= 2024-11-26 00:00:00 AND time <= 2024-11-29 00:00:00
GROUP BY device_id;
执行结果如下:
+---------+--------+--------+--------+
|device_id|avg_temp|max_temp|min_temp|
+---------+--------+--------+--------+
| 100| 87.6| 90.0| 85.0|
| 101| 85.0| 85.0| 85.0|
+---------+--------+--------+--------+
Total line number = 2
It costs 0.278s
3.4 最新点查询
示例:查询表中每个 device_id
返回最后一条记录,包含该记录的温度值以及在该设备中基于时间和温度排序的最后一条记录。
IoTDB> SELECT device_id,last(time),last_by(temperature,time)
FROM table1
GROUP BY device_id;
执行结果如下:
+---------+-----------------------------+-----+
|device_id| _col1|_col2|
+---------+-----------------------------+-----+
| 100|2024-11-29T18:30:00.000+08:00| 90.0|
| 101|2024-11-30T14:30:00.000+08:00| 90.0|
+---------+-----------------------------+-----+
Total line number = 2
It costs 0.090s
3.5 降采样查询(date_bin 函数)
示例:查询将时间按天分组,并计算每天的平均温度。
IoTDB> SELECT device_id,date_bin(1d ,time) as day_time, AVG(temperature) as avg_temp
FROM table1
WHERE time >= 2024-11-26 00:00:00 AND time <= 2024-11-30 00:00:00
GROUP BY device_id,date_bin(1d ,time);
执行结果如下:
+---------+-----------------------------+--------+
|device_id| day_time|avg_temp|
+---------+-----------------------------+--------+
| 100|2024-11-29T08:00:00.000+08:00| 90.0|
| 100|2024-11-28T08:00:00.000+08:00| 86.0|
| 100|2024-11-26T08:00:00.000+08:00| 90.0|
| 101|2024-11-29T08:00:00.000+08:00| 85.0|
| 101|2024-11-27T08:00:00.000+08:00| 85.0|
+---------+-----------------------------+--------+
Total line number = 5
It costs 0.066s
3.6 多设备降采样对齐查询
3.6.1 采样频率相同,时间不同
table1:采样频率:1s
Time | device_id | temperature |
---|---|---|
00:00:00.001 | d1 | 90.0 |
00:00:01.002 | d1 | 85.0 |
00:00:02.101 | d1 | 85.0 |
00:00:03.201 | d1 | null |
00:00:04.105 | d1 | 90.0 |
00:00:05.023 | d1 | 85.0 |
00:00:06.129 | d1 | 90.0 |
table2:采样频率:1s
Time | device_id | humidity |
---|---|---|
00:00:00.003 | d1 | 35.1 |
00:00:01.012 | d1 | 37.2 |
00:00:02.031 | d1 | null |
00:00:03.134 | d1 | 35.2 |
00:00:04.201 | d1 | 38.2 |
00:00:05.091 | d1 | 35.4 |
00:00:06.231 | d1 | 35.1 |
示例:查询table1
的降采样数据:
IoTDB> SELECT date_bin_gapfill(1s, TIME) AS a_time,
first(temperature) AS a_value
FROM table1
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS
结果:
+-----------------------------+-------+
| a_time|a_value|
+-----------------------------+-------+
|2025-05-13T00:00:00.000+08:00| 90.0|
|2025-05-13T00:00:01.000+08:00| 85.0|
|2025-05-13T00:00:02.000+08:00| 85.0|
|2025-05-13T00:00:03.000+08:00| 85.0|
|2025-05-13T00:00:04.000+08:00| 90.0|
|2025-05-13T00:00:05.000+08:00| 85.0|
|2025-05-13T00:00:06.000+08:00| 90.0|
+-----------------------------+-------+
示例:查询table2
的降采样数据:
IoTDB> SELECT date_bin_gapfill(1s, TIME) AS b_time,
first(humidity) AS b_value
FROM table2
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS
结果:
+-----------------------------+-------+
| b_time|b_value|
+-----------------------------+-------+
|2025-05-13T00:00:00.000+08:00| 35.1|
|2025-05-13T00:00:01.000+08:00| 37.2|
|2025-05-13T00:00:02.000+08:00| 37.2|
|2025-05-13T00:00:03.000+08:00| 35.2|
|2025-05-13T00:00:04.000+08:00| 38.2|
|2025-05-13T00:00:05.000+08:00| 35.4|
|2025-05-13T00:00:06.000+08:00| 35.1|
+-----------------------------+-------+
示例:按整点将多个序列进行时间对齐:
IoTDB> SELECT time,
a_value,
b_value
FROM
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(temperature) AS a_value
FROM table1
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS) A
JOIN
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(humidity) AS b_value
FROM table2
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS) B
USING (time)
结果:
+-----------------------------+-------+-------+
| time|a_value|b_value|
+-----------------------------+-------+-------+
|2025-05-13T00:00:00.000+08:00| 90.0| 35.1|
|2025-05-13T00:00:01.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:02.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:03.000+08:00| 85.0| 35.2|
|2025-05-13T00:00:04.000+08:00| 90.0| 38.2|
|2025-05-13T00:00:05.000+08:00| 85.0| 35.4|
|2025-05-13T00:00:06.000+08:00| 90.0| 35.1|
+-----------------------------+-------+-------+
- 保留空值:当
NULL
值本身具有特殊含义,或希望保留数据的 null 值时,可以选择去掉FILL METHOD PREVIOUS
不进行填充。
示例:
IoTDB> SELECT time,
a_value,
b_value
FROM
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(temperature) AS a_value
FROM table1
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1) A
JOIN
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(humidity) AS b_value
FROM table2
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1) B
USING (time)
结果:
+-----------------------------+-------+-------+
| time|a_value|b_value|
+-----------------------------+-------+-------+
|2025-05-13T00:00:00.000+08:00| 90.0| 35.1|
|2025-05-13T00:00:01.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:02.000+08:00| 85.0| null|
|2025-05-13T00:00:03.000+08:00| null| 35.2|
|2025-05-13T00:00:04.000+08:00| 90.0| 38.2|
|2025-05-13T00:00:05.000+08:00| 85.0| 35.4|
|2025-05-13T00:00:06.000+08:00| 90.0| 35.1|
+-----------------------------+-------+-------+
3.6.2 采样频率不同,时间不同
table1:采样频率:1s
Time | device_id | temperature |
---|---|---|
00:00:00.001 | d1 | 90.0 |
00:00:01.002 | d1 | 85.0 |
00:00:02.101 | d1 | 85.0 |
00:00:03.201 | d1 | null |
00:00:04.105 | d1 | 90.0 |
00:00:05.023 | d1 | 85.0 |
00:00:06.129 | d1 | 90.0 |
table3: 采样频率:2s
Time | device_id | humidity |
---|---|---|
00:00:00.005 | d1 | 35.1 |
00:00:02.106 | d1 | 37.2 |
00:00:04.187 | d1 | null |
00:00:06.156 | d1 | 35.1 |
示例:查询table1
的降采样数据:
IoTDB> SELECT date_bin_gapfill(1s, TIME) AS a_time,
first(temperature) AS a_value
FROM table1
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS
结果:
+-----------------------------+-------+
| a_time|a_value|
+-----------------------------+-------+
|2025-05-13T00:00:00.000+08:00| 90.0|
|2025-05-13T00:00:01.000+08:00| 85.0|
|2025-05-13T00:00:02.000+08:00| 85.0|
|2025-05-13T00:00:03.000+08:00| 85.0|
|2025-05-13T00:00:04.000+08:00| 90.0|
|2025-05-13T00:00:05.000+08:00| 85.0|
|2025-05-13T00:00:06.000+08:00| 90.0|
+-----------------------------+-------+
示例:查询table3
的降采样数据:
IoTDB> SELECT date_bin_gapfill(1s, TIME) AS c_time,
first(humidity) AS c_value
FROM table3
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS
结果:
+-----------------------------+-------+
| c_time|c_value|
+-----------------------------+-------+
|2025-05-13T00:00:00.000+08:00| 35.1|
|2025-05-13T00:00:01.000+08:00| 35.1|
|2025-05-13T00:00:02.000+08:00| 37.2|
|2025-05-13T00:00:03.000+08:00| 37.2|
|2025-05-13T00:00:04.000+08:00| 37.2|
|2025-05-13T00:00:05.000+08:00| 37.2|
|2025-05-13T00:00:06.000+08:00| 35.1|
+-----------------------------+-------+
示例:按照高采样频率进行对齐:
IoTDB> SELECT time,
a_value,
c_value
FROM
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(temperature) AS a_value
FROM table1
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS) A
JOIN
(SELECT date_bin_gapfill(1s, TIME) AS time,
first(humidity) AS c_value
FROM table3
WHERE device_id = 'd1'
AND TIME >= 2025-05-13 00:00:00.000
AND TIME <= 2025-05-13 00:00:07.000
GROUP BY 1 FILL METHOD PREVIOUS) C
USING (time)
结果:
+-----------------------------+-------+-------+
| time|a_value|c_value|
+-----------------------------+-------+-------+
|2025-05-13T00:00:00.000+08:00| 90.0| 35.1|
|2025-05-13T00:00:01.000+08:00| 85.0| 35.1|
|2025-05-13T00:00:02.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:03.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:04.000+08:00| 90.0| 37.2|
|2025-05-13T00:00:05.000+08:00| 85.0| 37.2|
|2025-05-13T00:00:06.000+08:00| 90.0| 35.1|
+-----------------------------+-------+-------+
3.7 数据填充
示例:查询指定时间范围内,满足 device_id
为 '100' 的记录,若存在缺失的数据点,则用前一个非空值进行填充。
IoTDB> SELECT time, temperature, humidity
FROM table1
WHERE time >= 2024-11-26 00:00:00 and time <= 2024-11-30 11:00:00
AND region='北京' AND plant_id='1001' AND device_id='101'
FILL METHOD PREVIOUS;
执行结果如下:
+-----------------------------+-----------+--------+
| time|temperature|humidity|
+-----------------------------+-----------+--------+
|2024-11-27T16:38:00.000+08:00| null| 35.1|
|2024-11-27T16:39:00.000+08:00| 85.0| 35.3|
|2024-11-27T16:40:00.000+08:00| 85.0| 35.3|
|2024-11-27T16:41:00.000+08:00| 85.0| 35.3|
|2024-11-27T16:42:00.000+08:00| 85.0| 35.2|
|2024-11-27T16:43:00.000+08:00| 85.0| 35.2|
|2024-11-27T16:44:00.000+08:00| 85.0| 35.2|
+-----------------------------+-----------+--------+
Total line number = 7
It costs 0.101s
3.8 排序&分页
示例:查询表中湿度降序排列且空值(NULL)排最后的记录,跳过前 2 条,只返回接下来的 8 条记录。
IoTDB> SELECT time, temperature, humidity
FROM table1
ORDER BY humidity desc NULLS LAST
OFFSET 2
LIMIT 10;
执行结果如下:
+-----------------------------+-----------+--------+
| time|temperature|humidity|
+-----------------------------+-----------+--------+
|2024-11-28T09:00:00.000+08:00| null| 40.9|
|2024-11-29T18:30:00.000+08:00| 90.0| 35.4|
|2024-11-27T16:39:00.000+08:00| 85.0| 35.3|
|2024-11-28T10:00:00.000+08:00| 85.0| 35.2|
|2024-11-30T09:30:00.000+08:00| 90.0| 35.2|
|2024-11-27T16:42:00.000+08:00| null| 35.2|
|2024-11-26T13:38:00.000+08:00| 90.0| 35.1|
|2024-11-26T13:37:00.000+08:00| 90.0| 35.1|
|2024-11-27T16:38:00.000+08:00| null| 35.1|
|2024-11-30T14:30:00.000+08:00| 90.0| 34.8|
+-----------------------------+-----------+--------+
Total line number = 10
It costs 0.093s