跳至主要內容

IotDB-SQL手册

BladeX大约 13 分钟iotdbtime series database

手册说明

由于iotdb官网文档访问速度较慢，也没有较好的中文文档，特此整理iotdb的sql手册，方便大家查阅。

IotDB SQL手册

一、元数据操作

1、数据库管理

创建数据库

CREATE DATABASE root.ln

查看数据库

show databases
show databases root.*
show databases root.**

删除数据库

DELETE DATABASE root.ln
DELETE DATABASE root.sgcc
DELETE DATABASE root.**

统计数据库数量

count databases
count databases root.*
count databases root.sgcc.*
count databases root.sgcc

2、时间序列管理

创建时间序列

create timeseries root.ln.wf01.wt01.status with datatype=BOOLEAN,encoding=PLAIN
create timeseries root.ln.wf01.wt01.temperature with datatype=FLOAT,encoding=RLE
create timeseries root.ln.wf02.wt02.hardware with datatype=TEXT,encoding=PLAIN
create timeseries root.ln.wf02.wt02.status with datatype=BOOLEAN,encoding=PLAIN
create timeseries root.sgcc.wf03.wt01.status with datatype=BOOLEAN,encoding=PLAIN
create timeseries root.sgcc.wf03.wt01.temperature with datatype=FLOAT,encoding=RLE
  • 简化版
create timeseries root.ln.wf01.wt01.status BOOLEAN encoding=PLAIN
create timeseries root.ln.wf01.wt01.temperature FLOAT encoding=RLE
create timeseries root.ln.wf02.wt02.hardware TEXT encoding=PLAIN
create timeseries root.ln.wf02.wt02.status BOOLEAN encoding=PLAIN
create timeseries root.sgcc.wf03.wt01.status BOOLEAN encoding=PLAIN
create timeseries root.sgcc.wf03.wt01.temperature FLOAT encoding=RLE
  • 错误提示
create timeseries root.ln.wf02.wt02.status WITH DATATYPE=BOOLEAN, ENCODING=TS_2DIFF
> error: encoding TS_2DIFF does not support BOOLEAN

创建对齐时间序列

CREATE ALIGNED TIMESERIES root.ln.wf01.GPS(latitude FLOAT encoding=PLAIN compressor=SNAPPY, longitude FLOAT encoding=PLAIN compressor=SNAPPY)

删除时间序列

delete timeseries root.ln.wf01.wt01.status
delete timeseries root.ln.wf01.wt01.temperature, root.ln.wf02.wt02.hardware
delete timeseries root.ln.wf02.*
drop timeseries root.ln.wf02.*

查看时间序列

SHOW TIMESERIES
SHOW TIMESERIES <Path>
SHOW TIMESERIES root.**
SHOW TIMESERIES root.ln.**
SHOW TIMESERIES root.ln.** limit 10 offset 10
SHOW TIMESERIES root.ln.** where timeseries contains 'wf01.wt'
SHOW TIMESERIES root.ln.** where dataType=FLOAT
SHOW TIMESERIES root.ln.** where time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;
SHOW LATEST TIMESERIES

统计时间序列数量

COUNT TIMESERIES root.**
COUNT TIMESERIES root.ln.**
COUNT TIMESERIES root.ln.*.*.status
COUNT TIMESERIES root.ln.wf01.wt01.status
COUNT TIMESERIES root.** WHERE TIMESERIES contains 'sgcc' 
COUNT TIMESERIES root.** WHERE DATATYPE = INT64
COUNT TIMESERIES root.** WHERE TAGS(unit) contains 'c' 
COUNT TIMESERIES root.** WHERE TAGS(unit) = 'c' 
COUNT TIMESERIES root.** WHERE TIMESERIES contains 'sgcc' group by level = 1
COUNT TIMESERIES root.** WHERE time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;
COUNT TIMESERIES root.** GROUP BY LEVEL=1
COUNT TIMESERIES root.ln.** GROUP BY LEVEL=2
COUNT TIMESERIES root.ln.wf01.* GROUP BY LEVEL=2

3、时间序列路径管理

查看路径的所有子路径

SHOW CHILD PATHS pathPattern
- 查询 root.ln 的下一层：show child paths root.ln
- 查询形如 root.xx.xx.xx 的路径：show child paths root.*.*

查看路径的所有子节点

SHOW CHILD NODES pathPattern

- 查询 root 的下一层：show child nodes root
- 查询 root.ln 的下一层 ：show child nodes root.ln

查看设备

IoTDB> show devices

IoTDB> show devices root.ln.**

IoTDB> show devices where time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;
查看设备及其 database 信息
IoTDB> show devices with database

IoTDB> show devices root.ln.** with database

统计节点数

IoTDB > COUNT NODES root.** LEVEL=2

IoTDB > COUNT NODES root.ln.** LEVEL=2

IoTDB > COUNT NODES root.ln.wf01.* LEVEL=3

IoTDB > COUNT NODES root.**.temperature LEVEL=3

统计设备数量

IoTDB> count devices

IoTDB> count devices root.ln.**

IoTDB> count devices where time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;

4、数据存活时间管理

设置 TTL

IoTDB> set ttl to root.ln 3600000
IoTDB> set ttl to root.sgcc.** 3600000
IoTDB> set ttl to root.** 3600000

取消 TTL

IoTDB> unset ttl to root.ln
IoTDB> unset ttl to root.sgcc.**
IoTDB> unset ttl to root.**

显示 TTL

IoTDB> SHOW ALL TTL
IoTDB> SHOW TTL ON StorageGroupNames

二、删除数据

1、删除单列数据

delete from root.ln.wf02.wt02.status where time<=2017-11-01T16:26:00;
delete from root.ln.wf02.wt02.status where time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;
delete from root.ln.wf02.wt02.status where time < 10
delete from root.ln.wf02.wt02.status where time <= 10
delete from root.ln.wf02.wt02.status where time < 20 and time > 10
delete from root.ln.wf02.wt02.status where time <= 20 and time >= 10
delete from root.ln.wf02.wt02.status where time > 20
delete from root.ln.wf02.wt02.status where time >= 20
delete from root.ln.wf02.wt02.status where time = 20

出错：

delete from root.ln.wf02.wt02.status where time > 4 or time < 0

Msg: 303: Check metadata error: For delete statement, where clause can only contain atomic

expressions like : time > XXX, time <= XXX, or two atomic expressions connected by 'AND'

删除时间序列中的所有数据：

delete from root.ln.wf02.wt02.status

2、删除多列数据

delete from root.ln.wf02.wt02.* where time <= 2017-11-01T16:26:00;

声明式的编程方式：

IoTDB> delete from root.ln.wf03.wt02.status where time < now()

Msg: The statement is executed successfully.

三、数据查询

1、基础查询

时间过滤查询

select temperature from root.ln.wf01.wt01 where time < 2017-11-01T00:08:00.000

根据一个时间区间选择多列数据

select status, temperature from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000;

按照多个时间区间选择同一设备的多列数据

select status, temperature from root.ln.wf01.wt01 where (time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000) or (time >= 2017-11-01T16:35:00.000 and time <= 2017-11-01T16:37:00.000);

按照多个时间区间选择不同设备的多列数据

select wf01.wt01.status, wf02.wt02.hardware from root.ln where (time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000) or (time >= 2017-11-01T16:35:00.000 and time <= 2017-11-01T16:37:00.000);

根据时间降序返回结果集

select * from root.ln.** where time > 1 order by time desc limit 10;

2、选择表达式

使用别名

select s1 as temperature, s2 as speed from root.ln.wf01.wt01;

运算符

函数

不支持：

select s1, count(s1) from root.sg.d1;

select sin(s1), count(s1) from root.sg.d1;

select s1, count(s1) from root.sg.d1 group by ([10,100),10ms);
时间序列查询嵌套表达式

示例 1：

select a,

​       b,

​       ((a + 1) * 2 - 1) % 2 + 1.5,

​       sin(a + sin(a + sin(b))),

​       -(a + b) * (sin(a + b) * sin(a + b) + cos(a + b) * cos(a + b)) + 1

from root.sg1;

示例 2：

select (a + b) * 2 + sin(a) from root.sg

示例 3：

select (a + *) / 2  from root.sg1

示例 4：

select (a + b) * 3 from root.sg, root.ln
聚合查询嵌套表达式

示例 1：

select avg(temperature),

​       sin(avg(temperature)),

​       avg(temperature) + 1,

​       -sum(hardware),

​       avg(temperature) + sum(hardware)

from root.ln.wf01.wt01;

示例 2：

select avg(*), 

​           (avg(*) + 1) * 3 / 2 -1 

from root.sg1

示例 3：

select avg(temperature),

​       sin(avg(temperature)),

​       avg(temperature) + 1,

​       -sum(hardware),

​       avg(temperature) + sum(hardware) as custom_sum

from root.ln.wf01.wt01

GROUP BY([10, 90), 10ms);

最新点查询

SQL 语法：

select last <Path> [COMMA <Path>]* from < PrefixPath > [COMMA < PrefixPath >]* <whereClause> [ORDER BY TIMESERIES (DESC | ASC)?]

查询 root.ln.wf01.wt01.status 的最新数据点

IoTDB> select last status from root.ln.wf01.wt01

查询 root.ln.wf01.wt01 下 status，temperature 时间戳大于等于 2017-11-07T23:50:00 的最新数据点

IoTDB> select last status, temperature from root.ln.wf01.wt01 where time >= 2017-11-07T23:50:00

查询 root.ln.wf01.wt01 下所有序列的最新数据点，并按照序列名降序排列

IoTDB> select last * from root.ln.wf01.wt01 order by timeseries desc;

3、查询过滤条件

时间过滤条件

选择时间戳大于 2022-01-01T00:05:00.000 的数据：

select s1 from root.sg1.d1 where time > 2022-01-01T00:05:00.000;

选择时间戳等于 2022-01-01T00:05:00.000 的数据：

select s1 from root.sg1.d1 where time = 2022-01-01T00:05:00.000;

选择时间区间 [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000) 内的数据：

select s1 from root.sg1.d1 where time >= 2022-01-01T00:05:00.000 and time < 2017-11-01T00:12:00.000;

值过滤条件

选择值大于 36.5 的数据：

select temperature from root.sg1.d1 where temperature > 36.5;

选择值等于 true 的数据：

select status from root.sg1.d1 where status = true;

选择区间 [36.5,40] 内或之外的数据：

select temperature from root.sg1.d1 where temperature between 36.5 and 40;
select temperature from root.sg1.d1 where temperature not between 36.5 and 40;

选择值在特定范围内的数据：

select code from root.sg1.d1 where code in ('200', '300', '400', '500');

选择值在特定范围外的数据：

select code from root.sg1.d1 where code not in ('200', '300', '400', '500');

选择值为空的数据:

select code from root.sg1.d1 where temperature is null;

选择值为非空的数据:

select code from root.sg1.d1 where temperature is not null;

模糊查询

查询 root.sg.d1 下 value 含有'cc'的数据

IoTDB> select * from root.sg.d1 where value like '%cc%'

查询 root.sg.d1 下 value 中间为 'b'、前后为任意单个字符的数据

IoTDB> select * from root.sg.device where value like '_b_'

查询 root.sg.d1 下 value 值为26个英文字符组成的字符串

IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'

查询 root.sg.d1 下 value 值为26个小写英文字符组成的字符串且时间大于100的

IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100

4、分段分组聚合

未指定滑动步长的时间区间分组聚合查询

select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01T00:00:00, 2017-11-07T23:00:00),1d);

指定滑动步长的时间区间分组聚合查询

select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d);

滑动步长可以小于聚合窗口

select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-01 10:00:00), 4h, 2h);

按照自然月份的时间区间分组聚合查询

select count(status) from root.ln.wf01.wt01 where time > 2017-11-01T01:00:00 group by([2017-11-01T00:00:00, 2019-11-07T23:00:00), 1mo, 2mo);

每个时间间隔窗口内都有数据

select count(status) from root.ln.wf01.wt01 group by([2017-10-31T00:00:00, 2019-11-07T23:00:00), 1mo, 2mo);

左开右闭区间

select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d);

与分组聚合混合使用

统计降采样后的数据点个数

select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;

加上滑动 Step 的降采样后的结果也可以汇总

select count(status) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), level=1;

路径层级分组聚合

统计不同 database 下 status 序列的数据点个数

select count(status) from root.** group by level = 1

统计不同设备下 status 序列的数据点个数

select count(status) from root.** group by level = 3

统计不同 database 下的不同设备中 status 序列的数据点个数

select count(status) from root.** group by level = 1, 3

查询所有序列下温度传感器 temperature 的最大值

select max_value(temperature) from root.** group by level = 0

查询某一层级下所有传感器拥有的总数据点数

select count(*) from root.ln.** group by level = 2

标签分组聚合

单标签聚合查询
SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);
多标签聚合查询
SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);
基于时间区间的标签聚合查询
SELECT AVG(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);

差值分段聚合

group by variation(controlExpression[,delta][,ignoreNull=true/false])
delta=0时的等值事件分段
select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)

指定ignoreNull为false

select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
delta!=0时的差值事件分段
select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)

条件分段聚合

group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])

查询至少连续两行以上的charging_status=1的数据

select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoreNull=true)

当设置ignoreNull为false时，遇到null值为将其视为一个不满足条件的行，得到结果原先的分组被含null的行拆分

select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoreNull=false)

会话分段聚合

group by session(timeInterval)

按照不同的时间单位设定时间间隔

select __endTime,count(*) from root.** group by session(1d)

和HAVING、ALIGN BY DEVICE共同使用

select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) having sum(hardware)>0 align by device

点数分段聚合

group by count(controlExpression, size[,ignoreNull=true/false])

select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5)

当使用ignoreNull将null值也考虑进来

select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5,ignoreNull=false)

5、聚合结果过滤

不正确的：

select count(s1) from root.** group by ([1,3),1ms) having sum(s1) > s1

select count(s1) from root.** group by ([1,3),1ms) having s1 > 1

select count(s1) from root.** group by ([1,3),1ms), level=1 having sum(d1.s1) > 1

select count(d1.s1) from root.** group by ([1,3),1ms), level=1 having sum(s1) > 1

SQL 示例：

 select count(s1) from root.** group by ([1,11),2ms), level=1 having count(s2) > 2;

 select count(s1), count(s2) from root.** group by ([1,11),2ms) having count(s2) > 1 align by device;

6、结果集补空值

FILL '(' PREVIOUS | LINEAR | constant (, interval=DURATION_LITERAL)? ')'

PREVIOUS 填充

select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous);

PREVIOUS 填充并指定填充超时阈值

select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(previous, 2m);

LINEAR 填充

select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(linear);

常量填充

select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(2.0);

使用 BOOLEAN 类型的常量填充

select temperature, status from root.sgcc.wf03.wt01 where time >= 2017-11-01T16:37:00.000 and time <= 2017-11-01T16:40:00.000 fill(true);

7、查询结果分页

按行分页

基本的 LIMIT 子句

select status, temperature from root.ln.wf01.wt01 limit 10

带 OFFSET 的 LIMIT 子句

select status, temperature from root.ln.wf01.wt01 limit 5 offset 3

LIMIT 子句与 WHERE 子句结合

select status,temperature from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time< 2017-11-01T00:12:00.000 limit 5 offset 3

LIMIT 子句与 GROUP BY 子句组合

select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2017-11-01T00:00:00, 2017-11-07T23:00:00),1d) limit 4 offset 3

按列分页

基本的 SLIMIT 子句

select * from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000 slimit 1

带 SOFFSET 的 SLIMIT 子句

select * from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000 slimit 1 soffset 1

SLIMIT 子句与 GROUP BY 子句结合

select max_value(*) from root.ln.wf01.wt01 group by ([2017-11-01T00:00:00, 2017-11-07T23:00:00),1d) slimit 1 soffset 1

SLIMIT 子句与 LIMIT 子句结合

select * from root.ln.wf01.wt01 limit 10 offset 100 slimit 2 soffset 0

8、排序

时间对齐模式下的排序

select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time desc;

设备对齐模式下的排序

select * from root.ln.** where time <= 2017-11-01T00:01:00 order by device desc,time asc align by device;

在时间戳相等时按照设备名排序

select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time asc,device desc align by device;

没有显式指定时

select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;

对聚合后的结果进行排序

select count(*) from root.ln.** group by ((2017-11-01T00:00:00.000+08:00,2017-11-01T00:03:00.000+08:00],1m) order by device asc,time asc align by device

9、查询对齐模式

按设备对齐

select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;

10、查询写回（SELECT INTO）

整体描述

selectIntoStatement

​    : SELECT

​        resultColumn [, resultColumn] ...

​        INTO intoItem [, intoItem] ...

​        FROM prefixPath [, prefixPath] ...

​        [WHERE whereCondition]

​        [GROUP BY groupByTimeClause, groupByLevelClause]

​        [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)]

​        [LIMIT rowLimit OFFSET rowOffset]

​        [ALIGN BY DEVICE]

​    ;



intoItem

​    : [ALIGNED] intoDevicePath '(' intoMeasurementName [',' intoMeasurementName]* ')'

​    ;

按时间对齐，将 root.sg database 下四条序列的查询结果写入到 root.sg_copy database 下指定的四条序列中

IoTDB> select s1, s2 into root.sg_copy.d1(t1), root.sg_copy.d2(t1, t2), root.sg_copy.d1(t2) from root.sg.d1, root.sg.d2;

按时间对齐，将聚合查询的结果存储到指定序列中

IoTDB> select count(s1 + s2), last_value(s2) into root.agg.count(s1_add_s2), root.agg.last_value(s2) from root.sg.d1 group by ([0, 100), 10ms);

按设备对齐

IoTDB> select s1, s2 into root.sg_copy.d1(t1, t2), root.sg_copy.d2(t1, t2) from root.sg.d1, root.sg.d2 align by device;

按设备对齐，将表达式计算的结果存储到指定序列中

IoTDB> select s1 + s2 into root.expr.add(d1s1_d1s2), root.expr.add(d2s1_d2s2) from root.sg.d1, root.sg.d2 align by device;

使用变量占位符

按时间对齐（默认）
（1）目标设备不使用变量占位符 & 目标物理量列表使用变量占位符
select s1, s2

into root.sg_copy.d1(::), root.sg_copy.d2(s1), root.sg_copy.d1(${3}), root.sg_copy.d2(::)

from root.sg.d1, root.sg.d2;

该语句等价于：

select s1, s2

into root.sg_copy.d1(s1), root.sg_copy.d2(s1), root.sg_copy.d1(s2), root.sg_copy.d2(s2)

from root.sg.d1, root.sg.d2;
（2）目标设备使用变量占位符 & 目标物理量列表不使用变量占位符
select d1.s1, d1.s2, d2.s3, d3.s4 

into ::(s1_1, s2_2), root.sg.d2_2(s3_3), root.${2}_copy.::(s4)

from root.sg;
（3）目标设备使用变量占位符 & 目标物理量列表使用变量占位符
select * into root.sg_bk.::(::) from root.sg.**;
按设备对齐（使用 ALIGN BY DEVICE）
（1）目标设备不使用变量占位符 & 目标物理量列表使用变量占位符
select s1, s2, s3, s4

into root.backup_sg.d1(s1, s2, s3, s4), root.backup_sg.d2(::), root.sg.d3(backup_${4})

from root.sg.d1, root.sg.d2, root.sg.d3

align by device;
（2）目标设备使用变量占位符 & 目标物理量列表不使用变量占位符
select avg(s1), sum(s2) + sum(s3), count(s4)

into root.agg_${2}.::(avg_s1, sum_s2_add_s3, count_s4)

from root.**

align by device;
（3）目标设备使用变量占位符 & 目标物理量列表使用变量占位符
select * into ::(backup_${4}) from root.sg.** align by device;

指定目标序列为对齐序列

select s1, s2 into root.sg_copy.d1(t1, t2), aligned root.sg_copy.d2(t1, t2) from root.sg.d1, root.sg.d2 align by device;

四、运维语句

生成对应的查询计划

explain select s1,s2 from root.sg.d1

执行对应的查询语句，并获取分析结果

explain analyze select s1,s2 from root.sg.d1 order by s1
上次编辑于:
贡献者: smallchill