SQL Manual

DATABASE MANAGEMENT

For more details, see document Operate-Metadata.

Create Database

1. IoTDB > create database root.ln
2. IoTDB > create database root.sgcc

Show Databases

1. IoTDB> SHOW DATABASES
2. IoTDB> SHOW DATABASES root.**

Delete Database

1. IoTDB > DELETE DATABASE root.ln
2. IoTDB > DELETE DATABASE root.sgcc
3. // delete all data, all timeseries and all databases
4. IoTDB > DELETE DATABASE root.**

Count Databases

1. IoTDB> count databases
2. IoTDB> count databases root.*
3. IoTDB> count databases root.sgcc.*
4. IoTDB> count databases root.sgcc

Setting up heterogeneous databases (Advanced operations)

Set heterogeneous parameters when creating a Database

1. CREATE DATABASE root.db WITH SCHEMA_REPLICATION_FACTOR=1, DATA_REPLICATION_FACTOR=3, SCHEMA_REGION_GROUP_NUM=1, DATA_REGION_GROUP_NUM=2;

Adjust heterogeneous parameters at run time

1. ALTER DATABASE root.db WITH SCHEMA_REGION_GROUP_NUM=1, DATA_REGION_GROUP_NUM=2;

Show heterogeneous databases

1. SHOW DATABASES DETAILS

TTL

Set TTL

1. IoTDB> set ttl to root.ln 3600000
2. IoTDB> set ttl to root.sgcc.** 3600000
3. IoTDB> set ttl to root.** 3600000

Unset TTL

1. IoTDB> unset ttl from root.ln
2. IoTDB> unset ttl from root.sgcc.**
3. IoTDB> unset ttl from root.**

Show TTL

1. IoTDB> SHOW ALL TTL
2. IoTDB> SHOW TTL ON StorageGroupNames
3. IoTDB> SHOW DEVICES

DEVICE TEMPLATE

For more details, see document Operate-Metadata.

img

Create Device Template

Example 1: Create a template containing two non-aligned timeseires

1. IoTDB> create device template t1 (temperature FLOAT encoding=RLE, status BOOLEAN encoding=PLAIN compression=SNAPPY)

Example 2: Create a template containing a group of aligned timeseires

1. IoTDB> create device template t2 aligned (lat FLOAT encoding=Gorilla, lon FLOAT encoding=Gorilla)

The lat and lon measurements are aligned.

Set Device Template

1. IoTDB> set device template t1 to root.sg1.d1

Activate Device Template

1. IoTDB> set device template t1 to root.sg1.d1
2. IoTDB> set device template t2 to root.sg1.d2
3. IoTDB> create timeseries using device template on root.sg1.d1
4. IoTDB> create timeseries using device template on root.sg1.d2

Show Device Template

1. IoTDB> show device templates
2. IoTDB> show nodes in device template t1
3. IoTDB> show paths set device template t1
4. IoTDB> show paths using device template t1

Deactivate Device Template

1. IoTDB> delete timeseries of device template t1 from root.sg1.d1
2. IoTDB> deactivate device template t1 from root.sg1.d1
3. IoTDB> delete timeseries of device template t1 from root.sg1.*, root.sg2.*
4. IoTDB> deactivate device template t1 from root.sg1.*, root.sg2.*

Unset Device Template

1. IoTDB> unset device template t1 from root.sg1.d1

Drop Device Template

1. IoTDB> drop device template t1

Alter Device Template

1. IoTDB> alter device template t1 add (speed FLOAT encoding=RLE)

TIMESERIES MANAGEMENT

For more details, see document Operate-Metadata.

Create Timeseries

1. IoTDB > create timeseries root.ln.wf01.wt01.status with datatype=BOOLEAN,encoding=PLAIN
2. IoTDB > create timeseries root.ln.wf01.wt01.temperature with datatype=FLOAT,encoding=RLE
3. IoTDB > create timeseries root.ln.wf02.wt02.hardware with datatype=TEXT,encoding=PLAIN
4. IoTDB > create timeseries root.ln.wf02.wt02.status with datatype=BOOLEAN,encoding=PLAIN
5. IoTDB > create timeseries root.sgcc.wf03.wt01.status with datatype=BOOLEAN,encoding=PLAIN
6. IoTDB > create timeseries root.sgcc.wf03.wt01.temperature with datatype=FLOAT,encoding=RLE

From v0.13, you can use a simplified version of the SQL statements to create timeseries:

1. IoTDB > create timeseries root.ln.wf01.wt01.status with datatype=BOOLEAN,encoding=PLAIN
2. IoTDB > create timeseries root.ln.wf01.wt01.temperature with datatype=FLOAT,encoding=RLE
3. IoTDB > create timeseries root.ln.wf02.wt02.hardware with datatype=TEXT,encoding=PLAIN
4. IoTDB > create timeseries root.ln.wf02.wt02.status with datatype=BOOLEAN,encoding=PLAIN
5. IoTDB > create timeseries root.sgcc.wf03.wt01.status with datatype=BOOLEAN,encoding=PLAIN
6. IoTDB > create timeseries root.sgcc.wf03.wt01.temperature with datatype=FLOAT,encoding=RLE

Notice that when in the CREATE TIMESERIES statement the encoding method conflicts with the data type, the system gives the corresponding error prompt as shown below:

1. IoTDB > create timeseries root.ln.wf02.wt02.status WITH DATATYPE=BOOLEAN, ENCODING=TS_2DIFF
2. error: encoding TS_2DIFF does not support BOOLEAN

Create Aligned Timeseries

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

Delete Timeseries

1. IoTDB> delete timeseries root.ln.wf01.wt01.status
2. IoTDB> delete timeseries root.ln.wf01.wt01.temperature, root.ln.wf02.wt02.hardware
3. IoTDB> delete timeseries root.ln.wf02.*
4. IoTDB> drop timeseries root.ln.wf02.*

Show Timeseries

1. IoTDB> show timeseries root.**
2. IoTDB> show timeseries root.ln.**
3. IoTDB> show timeseries root.ln.** limit 10 offset 10
4. IoTDB> show timeseries root.ln.** where timeseries contains 'wf01.wt'
5. IoTDB> show timeseries root.ln.** where dataType=FLOAT

Count Timeseries

1. IoTDB > COUNT TIMESERIES root.**
2. IoTDB > COUNT TIMESERIES root.ln.**
3. IoTDB > COUNT TIMESERIES root.ln.*.*.status
4. IoTDB > COUNT TIMESERIES root.ln.wf01.wt01.status
5. IoTDB > COUNT TIMESERIES root.** WHERE TIMESERIES contains 'sgcc'
6. IoTDB > COUNT TIMESERIES root.** WHERE DATATYPE = INT64
7. IoTDB > COUNT TIMESERIES root.** WHERE TAGS(unit) contains 'c'
8. IoTDB > COUNT TIMESERIES root.** WHERE TAGS(unit) = 'c'
9. IoTDB > COUNT TIMESERIES root.** WHERE TIMESERIES contains 'sgcc' group by level = 1
10. IoTDB > COUNT TIMESERIES root.** GROUP BY LEVEL=1
11. IoTDB > COUNT TIMESERIES root.ln.** GROUP BY LEVEL=2
12. IoTDB > COUNT TIMESERIES root.ln.wf01.* GROUP BY LEVEL=2

Tag and Attribute Management

1. create timeseries root.turbine.d1.s1(temprature) with datatype=FLOAT, encoding=RLE, compression=SNAPPY tags(tag1=v1, tag2=v2) attributes(attr1=v1, attr2=v2)

Rename the tag/attribute key

1. ALTER timeseries root.turbine.d1.s1 RENAME tag1 TO newTag1

Reset the tag/attribute value

1. ALTER timeseries root.turbine.d1.s1 SET newTag1=newV1, attr1=newV1

Delete the existing tag/attribute

1. ALTER timeseries root.turbine.d1.s1 DROP tag1, tag2

Add new tags

1. ALTER timeseries root.turbine.d1.s1 ADD TAGS tag3=v3, tag4=v4

Add new attributes

1. ALTER timeseries root.turbine.d1.s1 ADD ATTRIBUTES attr3=v3, attr4=v4

Upsert alias, tags and attributes

add alias or a new key-value if the alias or key doesn’t exist, otherwise, update the old one with new value.

1. ALTER timeseries root.turbine.d1.s1 UPSERT ALIAS=newAlias TAGS(tag3=v3, tag4=v4) ATTRIBUTES(attr3=v3, attr4=v4)

Show timeseries using tags. Use TAGS(tagKey) to identify the tags used as filter key

1. SHOW TIMESERIES (<`PathPattern`>)? timeseriesWhereClause

returns all the timeseries information that satisfy the where condition and match the pathPattern. SQL statements are as follows:

1. ALTER timeseries root.ln.wf02.wt02.hardware ADD TAGS unit=c
2. ALTER timeseries root.ln.wf02.wt02.status ADD TAGS description=test1
3. show timeseries root.ln.** where TAGS(unit)='c'
4. show timeseries root.ln.** where TAGS(description) contains 'test1'

count timeseries using tags

1. COUNT TIMESERIES (<`PathPattern`>)? timeseriesWhereClause
2. COUNT TIMESERIES (<`PathPattern`>)? timeseriesWhereClause GROUP BY LEVEL=<INTEGER>

returns all the number of timeseries that satisfy the where condition and match the pathPattern. SQL statements are as follows:

1. count timeseries
2. count timeseries root.** where TAGS(unit)='c'
3. count timeseries root.** where TAGS(unit)='c' group by level = 2

create aligned timeseries

1. create aligned timeseries root.sg1.d1(s1 INT32 tags(tag1=v1, tag2=v2) attributes(attr1=v1, attr2=v2), s2 DOUBLE tags(tag3=v3, tag4=v4) attributes(attr3=v3, attr4=v4))

The execution result is as follows:

1. IoTDB> show timeseries
2. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
3. |    timeseries|alias|     database|dataType|encoding|compression|                     tags|                 attributes|deadband|deadband parameters|
4. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
5. |root.sg1.d1.s1| null|     root.sg1|   INT32|     RLE|     SNAPPY|{"tag1":"v1","tag2":"v2"}|{"attr2":"v2","attr1":"v1"}|    null|               null|
6. |root.sg1.d1.s2| null|     root.sg1|  DOUBLE| GORILLA|     SNAPPY|{"tag4":"v4","tag3":"v3"}|{"attr4":"v4","attr3":"v3"}|    null|               null|
7. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+

Support query：

1. IoTDB> show timeseries where TAGS(tag1)='v1'
2. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
3. |    timeseries|alias|     database|dataType|encoding|compression|                     tags|                 attributes|deadband|deadband parameters|
4. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+
5. |root.sg1.d1.s1| null|     root.sg1|   INT32|     RLE|     SNAPPY|{"tag1":"v1","tag2":"v2"}|{"attr2":"v2","attr1":"v1"}|    null|               null|
6. +--------------+-----+-------------+--------+--------+-----------+-------------------------+---------------------------+--------+-------------------+

The above operations are supported for timeseries tag, attribute updates, etc.

NODE MANAGEMENT

For more details, see document Operate-Metadata.

Show Child Paths

1. SHOW CHILD PATHS pathPattern

Show Child Nodes

1. SHOW CHILD NODES pathPattern

Count Nodes

1. IoTDB > COUNT NODES root.** LEVEL=2
2. IoTDB > COUNT NODES root.ln.** LEVEL=2
3. IoTDB > COUNT NODES root.ln.wf01.** LEVEL=3
4. IoTDB > COUNT NODES root.**.temperature LEVEL=3

Show Devices

1. IoTDB> show devices
2. IoTDB> show devices root.ln.**
3. IoTDB> show devices root.ln.** where device contains 't'
4. IoTDB> show devices with database
5. IoTDB> show devices root.ln.** with database

Count Devices

1. IoTDB> show devices
2. IoTDB> count devices
3. IoTDB> count devices root.ln.**

INSERT & LOAD DATA

Insert Data

For more details, see document Write-Data.

Use of INSERT Statements

Insert Single Timeseries

1. IoTDB > insert into root.ln.wf02.wt02(timestamp,status) values(1,true)
2. IoTDB > insert into root.ln.wf02.wt02(timestamp,hardware) values(1, 'v1')

Insert Multiple Timeseries

1. IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (2, false, 'v2')
2. IoTDB > insert into root.ln.wf02.wt02(timestamp, status, hardware) VALUES (3, false, 'v3'),(4, true, 'v4')

Use the Current System Timestamp as the Timestamp of the Data Point

1. IoTDB > insert into root.ln.wf02.wt02(status, hardware) values (false, 'v2')

Insert Data Into Aligned Timeseries

1. IoTDB > create aligned timeseries root.sg1.d1(s1 INT32, s2 DOUBLE)
2. IoTDB > insert into root.sg1.d1(time, s1, s2) aligned values(1, 1, 1)
3. IoTDB > insert into root.sg1.d1(time, s1, s2) aligned values(2, 2, 2), (3, 3, 3)
4. IoTDB > select * from root.sg1.d1

Load External TsFile Tool

For more details, see document Data Import.

Load with SQL

Load a single tsfile by specifying a file path (absolute path).
load '/Users/Desktop/data/1575028885956-101-0.tsfile'
load '/Users/Desktop/data/1575028885956-101-0.tsfile' sglevel=1
load '/Users/Desktop/data/1575028885956-101-0.tsfile' onSuccess=delete
load '/Users/Desktop/data/1575028885956-101-0.tsfile' sglevel=1 onSuccess=delete
Load a batch of files by specifying a folder path (absolute path).
load '/Users/Desktop/data'
load '/Users/Desktop/data' sglevel=1
load '/Users/Desktop/data' onSuccess=delete
load '/Users/Desktop/data' sglevel=1 onSuccess=delete

Load with Script

1. ./load-rewrite.bat -f D:\IoTDB\data -h 192.168.0.101 -p 6667 -u root -pw root

DELETE DATA

For more details, see document Write-Delete-Data.

Delete Single Timeseries

1. IoTDB > delete from root.ln.wf02.wt02.status where time<=2017-11-01T16:26:00;
2. IoTDB > delete from root.ln.wf02.wt02.status where time>=2017-01-01T00:00:00 and time<=2017-11-01T16:26:00;
3. IoTDB > delete from root.ln.wf02.wt02.status where time < 10
4. IoTDB > delete from root.ln.wf02.wt02.status where time <= 10
5. IoTDB > delete from root.ln.wf02.wt02.status where time < 20 and time > 10
6. IoTDB > delete from root.ln.wf02.wt02.status where time <= 20 and time >= 10
7. IoTDB > delete from root.ln.wf02.wt02.status where time > 20
8. IoTDB > delete from root.ln.wf02.wt02.status where time >= 20
9. IoTDB > delete from root.ln.wf02.wt02.status where time = 20
10. IoTDB > delete from root.ln.wf02.wt02.status where time > 4 or time < 0
11. Msg: 303: Check metadata error: For delete statement, where clause can only contain atomic
12. expressions like : time > XXX, time <= XXX, or two atomic expressions connected by 'AND'
13. IoTDB > delete from root.ln.wf02.wt02.status

Delete Multiple Timeseries

1. IoTDB > delete from root.ln.wf02.wt02 where time <= 2017-11-01T16:26:00;
2. IoTDB > delete from root.ln.wf02.wt02.* where time <= 2017-11-01T16:26:00;
3. IoTDB> delete from root.ln.wf03.wt02.status where time < now()
4. Msg: The statement is executed successfully.

Delete Time Partition (experimental)

1. IoTDB > DELETE PARTITION root.ln 0,1,2

QUERY DATA

For more details, see document Query-Data.

1. SELECT [LAST] selectExpr [, selectExpr] ...
2. [INTO intoItem [, intoItem] ...]
3. FROM prefixPath [, prefixPath] ...
4. [WHERE whereCondition]
5. [GROUP BY {
6. ([startTime, endTime), interval [, slidingStep]) |
7. LEVEL = levelNum [, levelNum] ... |
8. TAGS(tagKey [, tagKey] ... ) |
9. VARIATION(expression[,delta][,ignoreNull=true/false]) |
10. CONDITION(expression,[keep>/>=/=/</<=]threshold[,ignoreNull=true/false]) |
11. SESSION(timeInterval) |
12. COUNT(expression, size[,ignoreNull=true/false])
13. }]
14. [HAVING havingCondition]
15. [ORDER BY sortKey {ASC | DESC}]
16. [FILL ({PREVIOUS | LINEAR | constant} (, interval=DURATION_LITERAL)?)]
17. [SLIMIT seriesLimit] [SOFFSET seriesOffset]
18. [LIMIT rowLimit] [OFFSET rowOffset]
19. [ALIGN BY {TIME | DEVICE}]

Basic Examples

Select a Column of Data Based on a Time Interval

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

Select Multiple Columns of Data Based on a Time Interval

1. IoTDB > 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 Multiple Columns of Data for the Same Device According to Multiple Time Intervals

1. IoTDB > 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);

Choose Multiple Columns of Data for Different Devices According to Multiple Time Intervals

1. IoTDB > 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);

Order By Time Query

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

SELECT CLAUSE

Use Alias

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

Nested Expressions

Nested Expressions with Time Series Query

1. IoTDB > select a,
2. b,
3. ((a + 1) * 2 - 1) % 2 + 1.5,
4. sin(a + sin(a + sin(b))),
5. -(a + b) * (sin(a + b) * sin(a + b) + cos(a + b) * cos(a + b)) + 1
6. from root.sg1;

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

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

12. IoTDB > select (a + b) * 3 from root.sg, root.ln

Nested Expressions query with aggregations

1. IoTDB > select avg(temperature),
2. sin(avg(temperature)),
3. avg(temperature) + 1,
4. -sum(hardware),
5. avg(temperature) + sum(hardware)
6. from root.ln.wf01.wt01;

8. IoTDB > select avg(*),
9. (avg(*) + 1) * 3 / 2 -1
10. from root.sg1

12. IoTDB > select avg(temperature),
13. sin(avg(temperature)),
14. avg(temperature) + 1,
15. -sum(hardware),
16. avg(temperature) + sum(hardware) as custom_sum
17. from root.ln.wf01.wt01
18. GROUP BY([10, 90), 10ms);

Last Query

1. IoTDB > select last status from root.ln.wf01.wt01
2. IoTDB > select last status, temperature from root.ln.wf01.wt01 where time >= 2017-11-07T23:50:00
3. IoTDB > select last * from root.ln.wf01.wt01 order by timeseries desc;
4. IoTDB > select last * from root.ln.wf01.wt01 order by dataType desc;

WHERE CLAUSE

Time Filter

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

Value Filter

1. IoTDB > select temperature from root.sg1.d1 where temperature > 36.5;
2. IoTDB > select status from root.sg1.d1 where status = true;
3. IoTDB > select temperature from root.sg1.d1 where temperature between 36.5 and 40;
4. IoTDB > select temperature from root.sg1.d1 where temperature not between 36.5 and 40;
5. IoTDB > select code from root.sg1.d1 where code in ('200', '300', '400', '500');
6. IoTDB > select code from root.sg1.d1 where code not in ('200', '300', '400', '500');
7. IoTDB > select code from root.sg1.d1 where temperature is null;
8. IoTDB > select code from root.sg1.d1 where temperature is not null;

Fuzzy Query

Fuzzy matching using Like

1. IoTDB > select * from root.sg.d1 where value like '%cc%'
2. IoTDB > select * from root.sg.device where value like '_b_'

Fuzzy matching using Regexp

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

GROUP BY CLAUSE

Aggregate By Time without Specifying the Sliding Step Length

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

Aggregate By Time Specifying the Sliding Step Length

1. IoTDB > 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);

Aggregate by Natural Month

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

Left Open And Right Close Range

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

Aggregation By Variation

1. IoTDB > select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)
2. IoTDB > select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
3. IoTDB > select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)
4. IoTDB > select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6+s5, 10)

Aggregation By Condition

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

Aggregation By Session

1. IoTDB > select __endTime,count(*) from root.** group by session(1d)
2. IoTDB > select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) having sum(hardware)>0 align by device

Aggregation By Count

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

Aggregation By Level

1. IoTDB > select count(status) from root.** group by level = 1
2. IoTDB > select count(status) from root.** group by level = 3
3. IoTDB > select count(status) from root.** group by level = 1, 3
4. IoTDB > select max_value(temperature) from root.** group by level = 0
5. IoTDB > select count(*) from root.ln.** group by level = 2

Aggregate By Time with Level Clause

1. IoTDB > select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;
2. IoTDB > 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;

Aggregation query by one single tag

1. IoTDB > SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);

Aggregation query by multiple tags

1. IoTDB > SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);

Downsampling Aggregation by tags based on Time Window

1. IoTDB > SELECT avg(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);

HAVING CLAUSE

Correct:

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

Incorrect:

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

FILL CLAUSE

PREVIOUS Fill

1. IoTDB > 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 FILL and specify the fill timeout threshold

1. 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 Fill

1. IoTDB > 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);

Constant Fill

1. IoTDB > 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);
2. IoTDB > 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);

LIMIT and SLIMIT CLAUSES (PAGINATION)

Row Control over Query Results

1. IoTDB > select status, temperature from root.ln.wf01.wt01 limit 10
2. IoTDB > select status, temperature from root.ln.wf01.wt01 limit 5 offset 3
3. IoTDB > 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 2 offset 3
4. IoTDB > 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 5 offset 3

Column Control over Query Results

1. IoTDB > select * from root.ln.wf01.wt01 where time > 2017-11-01T00:05:00.000 and time < 2017-11-01T00:12:00.000 slimit 1
2. IoTDB > 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
3. IoTDB > 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

Row and Column Control over Query Results

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

ORDER BY CLAUSE

Order by in ALIGN BY TIME mode

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

Order by in ALIGN BY DEVICE mode

1. IoTDB > select * from root.ln.** where time <= 2017-11-01T00:01:00 order by device desc,time asc align by device;
2. IoTDB > select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time asc,device desc align by device;
3. IoTDB > select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;
4. IoTDB > 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

Order by arbitrary expressions

1. IoTDB > select score from root.** order by score desc align by device
2. IoTDB > select score,total from root.one order by base+score+bonus desc
3. IoTDB > select score,total from root.one order by total desc
4. IoTDB > select base, score, bonus, total from root.** order by total desc NULLS Last,
5. score desc NULLS Last,
6. bonus desc NULLS Last,
7. time desc align by device
8. IoTDB > select min_value(total) from root.** order by min_value(total) asc align by device
9. IoTDB > select min_value(total),max_value(base) from root.** order by max_value(total) desc align by device
10. IoTDB > select score from root.** order by device asc, score desc, time asc align by device

ALIGN BY CLAUSE

Align by Device

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

INTO CLAUSE (QUERY WRITE-BACK)

1. 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;
2. 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);
3. 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;
4. 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;

Using variable placeholders:

1. IoTDB > select s1, s2
2. into root.sg_copy.d1(::), root.sg_copy.d2(s1), root.sg_copy.d1(${3}), root.sg_copy.d2(::)
3. from root.sg.d1, root.sg.d2;

5. IoTDB > select d1.s1, d1.s2, d2.s3, d3.s4
6. into ::(s1_1, s2_2), root.sg.d2_2(s3_3), root.${2}_copy.::(s4)
7. from root.sg;

9. IoTDB > select * into root.sg_bk.::(::) from root.sg.**;

11. IoTDB > select s1, s2, s3, s4
12. into root.backup_sg.d1(s1, s2, s3, s4), root.backup_sg.d2(::), root.sg.d3(backup_${4})
13. from root.sg.d1, root.sg.d2, root.sg.d3
14. align by device;

16. IoTDB > select avg(s1), sum(s2) + sum(s3), count(s4)
17. into root.agg_${2}.::(avg_s1, sum_s2_add_s3, count_s4)
18. from root.**
19. align by device;

21. IoTDB > select * into ::(backup_${4}) from root.sg.** align by device;

23. IoTDB > 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;

Maintennance

Generate the corresponding query plan:

1. explain select s1,s2 from root.sg.d1

Execute the corresponding SQL, analyze the execution and output:

1. explain analyze select s1,s2 from root.sg.d1 order by s1

OPERATOR

For more details, see document Operator-and-Expression.

Arithmetic Operators

For details and examples, see the document Arithmetic Operators and Functions.

1. select s1, - s1, s2, + s2, s1 + s2, s1 - s2, s1 * s2, s1 / s2, s1 % s2 from root.sg.d1

Comparison Operators

For details and examples, see the document Comparison Operators and Functions.

1. # Basic comparison operators
2. select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test;

4. # `BETWEEN ... AND ...` operator
5. select temperature from root.sg1.d1 where temperature between 36.5 and 40;
6. select temperature from root.sg1.d1 where temperature not between 36.5 and 40;

8. # Fuzzy matching operator: Use `Like` for fuzzy matching
9. select * from root.sg.d1 where value like '%cc%'
10. select * from root.sg.device where value like '_b_'

12. # Fuzzy matching operator: Use `Regexp` for fuzzy matching
13. select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
14. select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100
15. select b, b like '1%', b regexp '[0-2]' from root.test;

17. # `IS NULL` operator
18. select code from root.sg1.d1 where temperature is null;
19. select code from root.sg1.d1 where temperature is not null;

21. # `IN` operator
22. select code from root.sg1.d1 where code in ('200', '300', '400', '500');
23. select code from root.sg1.d1 where code not in ('200', '300', '400', '500');
24. select a, a in (1, 2) from root.test;

Logical Operators

For details and examples, see the document Logical Operators.

1. select a, b, a > 10, a <= b, !(a <= b), a > 10 && a > b from root.test;

BUILT-IN FUNCTIONS

For more details, see document Operator-and-Expression.

Aggregate Functions

For details and examples, see the document Aggregate Functions.

1. select count(status) from root.ln.wf01.wt01;

3. select count_if(s1=0 & s2=0, 3), count_if(s1=1 & s2=0, 3) from root.db.d1;
4. select count_if(s1=0 & s2=0, 3, 'ignoreNull'='false'), count_if(s1=1 & s2=0, 3, 'ignoreNull'='false') from root.db.d1;

6. select time_duration(s1) from root.db.d1;

Arithmetic Functions

For details and examples, see the document Arithmetic Operators and Functions.

1. select s1, sin(s1), cos(s1), tan(s1) from root.sg1.d1 limit 5 offset 1000;
2. select s4,round(s4),round(s4,2),round(s4,-1) from root.sg1.d1;

Comparison Functions

For details and examples, see the document Comparison Operators and Functions.

1. select ts, on_off(ts, 'threshold'='2') from root.test;
2. select ts, in_range(ts, 'lower'='2', 'upper'='3.1') from root.test;

String Processing Functions

For details and examples, see the document String Processing.

1. select s1, string_contains(s1, 's'='warn') from root.sg1.d4;
2. select s1, string_matches(s1, 'regex'='[^\\s]+37229') from root.sg1.d4;
3. select s1, length(s1) from root.sg1.d1
4. select s1, locate(s1, "target"="1") from root.sg1.d1
5. select s1, locate(s1, "target"="1", "reverse"="true") from root.sg1.d1
6. select s1, startswith(s1, "target"="1") from root.sg1.d1
7. select s1, endswith(s1, "target"="1") from root.sg1.d1
8. select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB") from root.sg1.d1
9. select s1, s2, concat(s1, s2, "target1"="IoT", "target2"="DB", "series_behind"="true") from root.sg1.d1
10. select s1, substring(s1 from 1 for 2) from root.sg1.d1
11. select s1, replace(s1, 'es', 'tt') from root.sg1.d1
12. select s1, upper(s1) from root.sg1.d1
13. select s1, lower(s1) from root.sg1.d1
14. select s3, trim(s3) from root.sg1.d1
15. select s1, s2, strcmp(s1, s2) from root.sg1.d1
16. select strreplace(s1, "target"=",", "replace"="/", "limit"="2") from root.test.d1
17. select strreplace(s1, "target"=",", "replace"="/", "limit"="1", "offset"="1", "reverse"="true") from root.test.d1
18. select regexmatch(s1, "regex"="\d+\.\d+\.\d+\.\d+", "group"="0") from root.test.d1
19. select regexreplace(s1, "regex"="192\.168\.0\.(\d+)", "replace"="cluster-$1", "limit"="1") from root.test.d1
20. select regexsplit(s1, "regex"=",", "index"="-1") from root.test.d1
21. select regexsplit(s1, "regex"=",", "index"="3") from root.test.d1

Data Type Conversion Function

For details and examples, see the document Data Type Conversion Function.

1. SELECT cast(s1 as INT32) from root.sg

Constant Timeseries Generating Functions

For details and examples, see the document Constant Timeseries Generating Functions.

1. select s1, s2, const(s1, 'value'='1024', 'type'='INT64'), pi(s2), e(s1, s2) from root.sg1.d1;

Selector Functions

For details and examples, see the document Selector Functions.

1. select s1, top_k(s1, 'k'='2'), bottom_k(s1, 'k'='2') from root.sg1.d2 where time > 2020-12-10T20:36:15.530+08:00;

Continuous Interval Functions

For details and examples, see the document Continuous Interval Functions.

1. select s1, zero_count(s1), non_zero_count(s2), zero_duration(s3), non_zero_duration(s4) from root.sg.d2;

Variation Trend Calculation Functions

For details and examples, see the document Variation Trend Calculation Functions.

1. select s1, time_difference(s1), difference(s1), non_negative_difference(s1), derivative(s1), non_negative_derivative(s1) from root.sg1.d1 limit 5 offset 1000;

3. SELECT DIFF(s1), DIFF(s2) from root.test;
4. SELECT DIFF(s1, 'ignoreNull'='false'), DIFF(s2, 'ignoreNull'='false') from root.test;

Sample Functions

For details and examples, see the document Sample Functions.

1. select equal_size_bucket_random_sample(temperature,'proportion'='0.1') as random_sample from root.ln.wf01.wt01;
2. select equal_size_bucket_agg_sample(temperature, 'type'='avg','proportion'='0.1') as agg_avg, equal_size_bucket_agg_sample(temperature, 'type'='max','proportion'='0.1') as agg_max, equal_size_bucket_agg_sample(temperature,'type'='min','proportion'='0.1') as agg_min, equal_size_bucket_agg_sample(temperature, 'type'='sum','proportion'='0.1') as agg_sum, equal_size_bucket_agg_sample(temperature, 'type'='extreme','proportion'='0.1') as agg_extreme, equal_size_bucket_agg_sample(temperature, 'type'='variance','proportion'='0.1') as agg_variance from root.ln.wf01.wt01;
3. select equal_size_bucket_m4_sample(temperature, 'proportion'='0.1') as M4_sample from root.ln.wf01.wt01;
4. select equal_size_bucket_outlier_sample(temperature, 'proportion'='0.1', 'type'='avg', 'number'='2') as outlier_avg_sample, equal_size_bucket_outlier_sample(temperature, 'proportion'='0.1', 'type'='stendis', 'number'='2') as outlier_stendis_sample, equal_size_bucket_outlier_sample(temperature, 'proportion'='0.1', 'type'='cos', 'number'='2') as outlier_cos_sample, equal_size_bucket_outlier_sample(temperature, 'proportion'='0.1', 'type'='prenextdis', 'number'='2') as outlier_prenextdis_sample from root.ln.wf01.wt01;

6. select M4(s1,'timeInterval'='25','displayWindowBegin'='0','displayWindowEnd'='100') from root.vehicle.d1
7. select M4(s1,'windowSize'='10') from root.vehicle.d1

Change Points Function

For details and examples, see the document Time-Series.

1. select change_points(s1), change_points(s2), change_points(s3), change_points(s4), change_points(s5), change_points(s6) from root.testChangePoints.d1

DATA QUALITY FUNCTION LIBRARY

For more details, see document Operator-and-Expression.

Data Quality

For details and examples, see the document Data-Quality.

1. # Completeness
2. select completeness(s1) from root.test.d1 where time <= 2020-01-01 00:00:30
3. select completeness(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

5. # Consistency
6. select consistency(s1) from root.test.d1 where time <= 2020-01-01 00:00:30
7. select consistency(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

9. # Timeliness
10. select timeliness(s1) from root.test.d1 where time <= 2020-01-01 00:00:30
11. select timeliness(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

13. # Validity
14. select Validity(s1) from root.test.d1 where time <= 2020-01-01 00:00:30
15. select Validity(s1,"window"="15") from root.test.d1 where time <= 2020-01-01 00:01:00

17. # Accuracy
18. select Accuracy(t1,t2,t3,m1,m2,m3) from root.test

Data Profiling

For details and examples, see the document Data-Profiling.

1. # ACF
2. select acf(s1) from root.test.d1 where time <= 2020-01-01 00:00:05

4. # Distinct
5. select distinct(s2) from root.test.d2

7. # Histogram
8. select histogram(s1,"min"="1","max"="20","count"="10") from root.test.d1

10. # Integral
11. select integral(s1) from root.test.d1 where time <= 2020-01-01 00:00:10
12. select integral(s1, "unit"="1m") from root.test.d1 where time <= 2020-01-01 00:00:10

14. # IntegralAvg
15. select integralavg(s1) from root.test.d1 where time <= 2020-01-01 00:00:10

17. # Mad
18. select mad(s0) from root.test
19. select mad(s0, "error"="0.01") from root.test

21. # Median
22. select median(s0, "error"="0.01") from root.test

24. # MinMax
25. select minmax(s1) from root.test

27. # Mode
28. select mode(s2) from root.test.d2

30. # MvAvg
31. select mvavg(s1, "window"="3") from root.test

33. # PACF
34. select pacf(s1, "lag"="5") from root.test

36. # Percentile
37. select percentile(s0, "rank"="0.2", "error"="0.01") from root.test

39. # Quantile
40. select quantile(s0, "rank"="0.2", "K"="800") from root.test

42. # Period
43. select period(s1) from root.test.d3

45. # QLB
46. select QLB(s1) from root.test.d1

48. # Resample
49. select resample(s1,'every'='5m','interp'='linear') from root.test.d1
50. select resample(s1,'every'='30m','aggr'='first') from root.test.d1
51. select resample(s1,'every'='30m','start'='2021-03-06 15:00:00') from root.test.d1

53. # Sample
54. select sample(s1,'method'='reservoir','k'='5') from root.test.d1
55. select sample(s1,'method'='isometric','k'='5') from root.test.d1

57. # Segment
58. select segment(s1, "error"="0.1") from root.test

60. # Skew
61. select skew(s1) from root.test.d1

63. # Spline
64. select spline(s1, "points"="151") from root.test

66. # Spread
67. select spread(s1) from root.test.d1 where time <= 2020-01-01 00:00:30

69. # Stddev
70. select stddev(s1) from root.test.d1

72. # ZScore
73. select zscore(s1) from root.test

Anomaly Detection

For details and examples, see the document Anomaly-Detection.

1. # IQR
2. select iqr(s1) from root.test

4. # KSigma
5. select ksigma(s1,"k"="1.0") from root.test.d1 where time <= 2020-01-01 00:00:30

7. # LOF
8. select lof(s1,s2) from root.test.d1 where time<1000
9. select lof(s1, "method"="series") from root.test.d1 where time<1000

11. # MissDetect
12. select missdetect(s2,'minlen'='10') from root.test.d2

14. # Range
15. select range(s1,"lower_bound"="101.0","upper_bound"="125.0") from root.test.d1 where time <= 2020-01-01 00:00:30

17. # TwoSidedFilter
18. select TwoSidedFilter(s0, 'len'='5', 'threshold'='0.3') from root.test

20. # Outlier
21. select outlier(s1,"r"="5.0","k"="4","w"="10","s"="5") from root.test

23. # MasterTrain
24. select MasterTrain(lo,la,m_lo,m_la,'p'='3','eta'='1.0') from root.test

26. # MasterDetect
27. select MasterDetect(lo,la,m_lo,m_la,model,'output_type'='repair','p'='3','k'='3','eta'='1.0') from root.test
28. select MasterDetect(lo,la,m_lo,m_la,model,'output_type'='anomaly','p'='3','k'='3','eta'='1.0') from root.test

Frequency Domain

For details and examples, see the document Frequency-Domain.

``` 1. # Conv 2. select conv(s1,s2) from root.test.d2

Deconv
select deconv(s3,s2) fr

（本文超出站点单文档体积上限，已截断）

SQL Manual

Deconv