DML (Data Manipulation Language)
- INSERT
  - Insert Real-time Data
    - Use of INSERT Statements
- SELECT

DML (Data Manipulation Language)

INSERT

Insert Real-time Data

IoTDB provides users with a variety of ways to insert real-time data, such as directly inputting INSERT SQL statement in Client/Shell tools, or using Java JDBC to perform single or batch execution of INSERT SQL statement.

This section mainly introduces the use of INSERT SQL statement for real-time data import in the scenario.

Use of INSERT Statements

The INSERT SQL statement statement is used to insert data into one or more specified timeseries created. For each point of data inserted, it consists of a timestamp and a sensor acquisition value (see Data Type).

In the scenario of this section, take two timeseries root.ln.wf02.wt02.status and root.ln.wf02.wt02.hardware as an example, and their data types are BOOLEAN and TEXT, respectively.

The sample code for single column data insertion is as follows:

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")

The above example code inserts the long integer timestamp and the value “true” into the timeseries root.ln.wf02.wt02.status and inserts the long integer timestamp and the value “v1” into the timeseries root.ln.wf02.wt02.hardware. When the execution is successful, cost time is shown to indicate that the data insertion has been completed.

Note: In IoTDB, TEXT type data can be represented by single and double quotation marks. The insertion statement above uses double quotation marks for TEXT type data. The following example will use single quotation marks for TEXT type data.

The INSERT statement can also support the insertion of multi-column data at the same time point. The sample code of inserting the values of the two timeseries at the same time point ‘2’ is as follows:

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

After inserting the data, we can simply query the inserted data using the SELECT statement:

1. IoTDB > select * from root.ln.wf02 where time < 3

The result is shown below. The query result shows that the insertion statements of single column and multi column data are performed correctly.

1. +-----------------------------+--------------------------+------------------------+
2. |                         Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|
3. +-----------------------------+--------------------------+------------------------+
4. |1970-01-01T08:00:00.001+08:00|                        v1|                    true|
5. |1970-01-01T08:00:00.002+08:00|                        v2|                   false|
6. +-----------------------------+--------------------------+------------------------+
7. Total line number = 2
8. It costs 0.170s

SELECT

Time Slice Query

This chapter mainly introduces the relevant examples of time slice query using IoTDB SELECT statements. Detailed SQL syntax and usage specifications can be found in SQL Documentation. You can also use the Java JDBC standard interface to execute related queries.

Select a Column of Data Based on a Time Interval

The SQL statement is:

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

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is the temperature sensor (temperature). The SQL statement requires that all temperature sensor values before the time point of “2017-11-01T00:08:00.000” be selected.

The execution result of this SQL statement is as follows:

1. +-----------------------------+-----------------------------+
2. |                         Time|root.ln.wf01.wt01.temperature|
3. +-----------------------------+-----------------------------+
4. |2017-11-01T00:00:00.000+08:00|                        25.96|
5. |2017-11-01T00:01:00.000+08:00|                        24.36|
6. |2017-11-01T00:02:00.000+08:00|                        20.09|
7. |2017-11-01T00:03:00.000+08:00|                        20.18|
8. |2017-11-01T00:04:00.000+08:00|                        21.13|
9. |2017-11-01T00:05:00.000+08:00|                        22.72|
10. |2017-11-01T00:06:00.000+08:00|                        20.71|
11. |2017-11-01T00:07:00.000+08:00|                        21.45|
12. +-----------------------------+-----------------------------+
13. Total line number = 8
14. It costs 0.026s

Select Multiple Columns of Data Based on a Time Interval

The SQL statement is:

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

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is “status” and “temperature”. The SQL statement requires that the status and temperature sensor values between the time point of “2017-11-01T00:05:00.000” and “2017-11-01T00:12:00.000” be selected.

The execution result of this SQL statement is as follows:

1. +-----------------------------+------------------------+-----------------------------+
2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf01.wt01.temperature|
3. +-----------------------------+------------------------+-----------------------------+
4. |2017-11-01T00:06:00.000+08:00|                   false|                        20.71|
5. |2017-11-01T00:07:00.000+08:00|                   false|                        21.45|
6. |2017-11-01T00:08:00.000+08:00|                   false|                        22.58|
7. |2017-11-01T00:09:00.000+08:00|                   false|                        20.98|
8. |2017-11-01T00:10:00.000+08:00|                    true|                        25.52|
9. |2017-11-01T00:11:00.000+08:00|                   false|                        22.91|
10. +-----------------------------+------------------------+-----------------------------+
11. Total line number = 6
12. It costs 0.018s

Select Multiple Columns of Data for the Same Device According to Multiple Time Intervals

IoTDB supports specifying multiple time interval conditions in a query. Users can combine time interval conditions at will according to their needs. For example, the SQL statement is:

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

which means:

The selected device is ln group wf01 plant wt01 device; the selected timeseries is “status” and “temperature”; the statement specifies two different time intervals, namely “2017-11-01T00:05:00.000 to 2017-11-01T00:12:00.000” and “2017-11-01T16:35:00.000 to 2017-11-01T16:37:00.000”. The SQL statement requires that the values of selected timeseries satisfying any time interval be selected.

The execution result of this SQL statement is as follows:

1. +-----------------------------+------------------------+-----------------------------+
2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf01.wt01.temperature|
3. +-----------------------------+------------------------+-----------------------------+
4. |2017-11-01T00:06:00.000+08:00|                   false|                        20.71|
5. |2017-11-01T00:07:00.000+08:00|                   false|                        21.45|
6. |2017-11-01T00:08:00.000+08:00|                   false|                        22.58|
7. |2017-11-01T00:09:00.000+08:00|                   false|                        20.98|
8. |2017-11-01T00:10:00.000+08:00|                    true|                        25.52|
9. |2017-11-01T00:11:00.000+08:00|                   false|                        22.91|
10. |2017-11-01T16:35:00.000+08:00|                    true|                        23.44|
11. |2017-11-01T16:36:00.000+08:00|                   false|                        21.98|
12. |2017-11-01T16:37:00.000+08:00|                   false|                        21.93|
13. +-----------------------------+------------------------+-----------------------------+
14. Total line number = 9
15. It costs 0.018s

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

The system supports the selection of data in any column in a query, i.e., the selected columns can come from different devices. For example, the SQL statement is:

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

which means:

The selected timeseries are “the power supply status of ln group wf01 plant wt01 device” and “the hardware version of ln group wf02 plant wt02 device”; the statement specifies two different time intervals, namely “2017-11-01T00:05:00.000 to 2017-11-01T00:12:00.000” and “2017-11-01T16:35:00.000 to 2017-11-01T16:37:00.000”. The SQL statement requires that the values of selected timeseries satisfying any time interval be selected.

The execution result of this SQL statement is as follows:

1. +-----------------------------+------------------------+--------------------------+
2. |                         Time|root.ln.wf01.wt01.status|root.ln.wf02.wt02.hardware|
3. +-----------------------------+------------------------+--------------------------+
4. |2017-11-01T00:06:00.000+08:00|                   false|                        v1|
5. |2017-11-01T00:07:00.000+08:00|                   false|                        v1|
6. |2017-11-01T00:08:00.000+08:00|                   false|                        v1|
7. |2017-11-01T00:09:00.000+08:00|                   false|                        v1|
8. |2017-11-01T00:10:00.000+08:00|                    true|                        v2|
9. |2017-11-01T00:11:00.000+08:00|                   false|                        v1|
10. |2017-11-01T16:35:00.000+08:00|                    true|                        v2|
11. |2017-11-01T16:36:00.000+08:00|                   false|                        v1|
12. |2017-11-01T16:37:00.000+08:00|                   false|                        v1|
13. +-----------------------------+------------------------+--------------------------+
14. Total line number = 9
15. It costs 0.014s

Order By Time Query

IoTDB supports the ‘order by time’ statement since 0.11, it’s used to display results in descending order by time. For example, the SQL statement is:

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

The execution result of this SQL statement is as follows:

1. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
2. |                         Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|root.ln.wf01.wt01.temperature|root.ln.wf01.wt01.status|
3. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
4. |2017-11-07T23:59:00.000+08:00|                        v1|                   false|                        21.07|                   false|
5. |2017-11-07T23:58:00.000+08:00|                        v1|                   false|                        22.93|                   false|
6. |2017-11-07T23:57:00.000+08:00|                        v2|                    true|                        24.39|                    true|
7. |2017-11-07T23:56:00.000+08:00|                        v2|                    true|                        24.44|                    true|
8. |2017-11-07T23:55:00.000+08:00|                        v2|                    true|                         25.9|                    true|
9. |2017-11-07T23:54:00.000+08:00|                        v1|                   false|                        22.52|                   false|
10. |2017-11-07T23:53:00.000+08:00|                        v2|                    true|                        24.58|                    true|
11. |2017-11-07T23:52:00.000+08:00|                        v1|                   false|                        20.18|                   false|
12. |2017-11-07T23:51:00.000+08:00|                        v1|                   false|                        22.24|                   false|
13. |2017-11-07T23:50:00.000+08:00|                        v2|                    true|                         23.7|                    true|
14. +-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
15. Total line number = 10
16. It costs 0.016s

Time Series Generating Functions

The time series generating function takes several time series as input and outputs one time series. Unlike the aggregation function, the result set of the time series generating function has a timestamp column.

All time series generating functions can accept * as input.

IoTDB supports hybrid queries of time series generating function queries and raw data queries.

Mathematical Functions

Currently IoTDB supports the following mathematical functions. The behavior of these mathematical functions is consistent with the behavior of these functions in the Java Math standard library.

Function Name	Allowed Input Series Data Types	Output Series Data Type	Corresponding Implementation in the Java Standard Library
SIN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#sin(double)
COS	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#cos(double)
TAN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#tan(double)
ASIN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#asin(double)
ACOS	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#acos(double)
ATAN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#atan(double)
DEGREES	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#toDegrees(double)
RADIANS	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#toRadians(double)
ABS	INT32 / INT64 / FLOAT / DOUBLE	Same type as the input series	Math#abs(int) / Math#abs(long) /Math#abs(float) /Math#abs(double)
SIGN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#signum(double)
CEIL	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#ceil(double)
FLOOR	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#floor(double)
ROUND	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#rint(double)
EXP	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#exp(double)
LN	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#log(double)
LOG10	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#log10(double)
SQRT	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Math#sqrt(double)

Example:

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

Result:

1. +-----------------------------+-------------------+-------------------+--------------------+-------------------+
2. |                         Time|     root.sg1.d1.s1|sin(root.sg1.d1.s1)| cos(root.sg1.d1.s1)|tan(root.sg1.d1.s1)|
3. +-----------------------------+-------------------+-------------------+--------------------+-------------------+
4. |2020-12-10T17:11:49.037+08:00|7360723084922759782| 0.8133527237573284|  0.5817708713544664| 1.3980636773094157|
5. |2020-12-10T17:11:49.038+08:00|4377791063319964531|-0.8938962705202537|  0.4482738644511651| -1.994085181866842|
6. |2020-12-10T17:11:49.039+08:00|7972485567734642915| 0.9627757585308978|-0.27030138509681073|-3.5618602479083545|
7. |2020-12-10T17:11:49.040+08:00|2508858212791964081|-0.6073417341629443| -0.7944406950452296| 0.7644897069734913|
8. |2020-12-10T17:11:49.041+08:00|2817297431185141819|-0.8419358900502509| -0.5395775727782725| 1.5603611649667768|
9. +-----------------------------+-------------------+-------------------+--------------------+-------------------+
10. Total line number = 5
11. It costs 0.008s

String Processing Functions

Currently IoTDB supports the following string processing functions:

Function Name	Allowed Input Series Data Types	Required Attributes	Output Series Data Type	Description
STRING_CONTAINS	TEXT	`s`: the sequence to search for	BOOLEAN	Determine whether `s` is in the string
STRING_MATCHES	TEXT	`regex`: the regular expression to which the string is to be matched	BOOLEAN	Determine whether the string can be matched by `regex`

Example：

1. select s1, string_contains(s1, "s"="warn"), string_matches(s1, "regex"="[^\\s]+37229") from root.sg1.d4;

Result：

1. +-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
2. |                         Time|root.sg1.d4.s1|string_contains(root.sg1.d4.s1, "s"="warn")|string_matches(root.sg1.d4.s1, "regex"="[^\\s]+37229")|
3. +-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
4. |1970-01-01T08:00:00.001+08:00|    warn:-8721|                                       true|                                                 false|
5. |1970-01-01T08:00:00.002+08:00|  error:-37229|                                      false|                                                  true|
6. |1970-01-01T08:00:00.003+08:00|     warn:1731|                                       true|                                                 false|
7. +-----------------------------+--------------+-------------------------------------------+------------------------------------------------------+
8. Total line number = 3
9. It costs 0.007s

Selector Functions

Currently IoTDB supports the following selector functions:

Function Name	Allowed Input Series Data Types	Required Attributes	Output Series Data Type	Description
TOP_K	INT32 / INT64 / FLOAT / DOUBLE / TEXT	`k`: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000	Same type as the input series	Returns `k` data points with the largest values in a time series.
BOTTOM_K	INT32 / INT64 / FLOAT / DOUBLE / TEXT	`k`: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000	Same type as the input series	Returns `k` data points with the smallest values in a time series.

Example：

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;

Result：

1. +-----------------------------+--------------------+------------------------------+---------------------------------+
2. |                         Time|      root.sg1.d2.s1|top_k(root.sg1.d2.s1, "k"="2")|bottom_k(root.sg1.d2.s1, "k"="2")|
3. +-----------------------------+--------------------+------------------------------+---------------------------------+
4. |2020-12-10T20:36:15.531+08:00| 1531604122307244742|           1531604122307244742|                             null|
5. |2020-12-10T20:36:15.532+08:00|-7426070874923281101|                          null|                             null|
6. |2020-12-10T20:36:15.533+08:00|-7162825364312197604|          -7162825364312197604|                             null|
7. |2020-12-10T20:36:15.534+08:00|-8581625725655917595|                          null|             -8581625725655917595|
8. |2020-12-10T20:36:15.535+08:00|-7667364751255535391|                          null|             -7667364751255535391|
9. +-----------------------------+--------------------+------------------------------+---------------------------------+
10. Total line number = 5
11. It costs 0.006s

Variation Trend Calculation Functions

Currently IoTDB supports the following variation trend calculation functions:

Function Name	Allowed Input Series Data Types	Output Series Data Type	Description
TIME_DIFFERENCE	INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT	INT64	Calculates the difference between the time stamp of a data point and the time stamp of the previous data point. There is no corresponding output for the first data point.
DIFFERENCE	INT32 / INT64 / FLOAT / DOUBLE	Same type as the input series	Calculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
NON_NEGATIVE_DIFFERENCE	INT32 / INT64 / FLOAT / DOUBLE	Same type as the input series	Calculates the absolute value of the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
DERIVATIVE	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Calculates the rate of change of a data point compared to the previous data point, the result is equals to DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.
NON_NEGATIVE_DERIVATIVE	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	Calculates the absolute value of the rate of change of a data point compared to the previous data point, the result is equals to NON_NEGATIVE_DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.

Example:

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;

Result:

1. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
2. |                         Time|     root.sg1.d1.s1|time_difference(root.sg1.d1.s1)|difference(root.sg1.d1.s1)|non_negative_difference(root.sg1.d1.s1)|derivative(root.sg1.d1.s1)|non_negative_derivative(root.sg1.d1.s1)|
3. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
4. |2020-12-10T17:11:49.037+08:00|7360723084922759782|                              1|      -8431715764844238876|                    8431715764844238876|    -8.4317157648442388E18|                  8.4317157648442388E18|
5. |2020-12-10T17:11:49.038+08:00|4377791063319964531|                              1|      -2982932021602795251|                    2982932021602795251|     -2.982932021602795E18|                   2.982932021602795E18|
6. |2020-12-10T17:11:49.039+08:00|7972485567734642915|                              1|       3594694504414678384|                    3594694504414678384|     3.5946945044146785E18|                  3.5946945044146785E18|
7. |2020-12-10T17:11:49.040+08:00|2508858212791964081|                              1|      -5463627354942678834|                    5463627354942678834|     -5.463627354942679E18|                   5.463627354942679E18|
8. |2020-12-10T17:11:49.041+08:00|2817297431185141819|                              1|        308439218393177738|                     308439218393177738|     3.0843921839317773E17|                  3.0843921839317773E17|
9. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
10. Total line number = 5
11. It costs 0.014s

User Defined Timeseries Generating Functions

Please refer to UDF (User Defined Function).

Aggregate Query

This section mainly introduces the related examples of aggregate query.

Count Points

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

Result:

1. +-------------------------------+
2. |count(root.ln.wf01.wt01.status)|
3. +-------------------------------+
4. |                          10080|
5. +-------------------------------+
6. Total line number = 1
7. It costs 0.016s

Aggregation By Level

Aggregation by level statement is used for aggregating upon specific hierarchical level of timeseries path. For all timeseries paths, by convention, “level=0” represents root level. That is, to tally the points of any measurements under “root.ln”, the level should be set to 1.

For example, there are multiple series under “root.ln.wf01”, such as “root.ln.wf01.wt01.status”,”root.ln.wf01.wt02.status”,”root.ln.wf01.wt03.status”. To count the number of “status” points of all these series, use query:

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

Result:

1. +----------------------------+
2. |COUNT(root.ln.wf01.*.status)|
3. +----------------------------+
4. |                       10080|
5. +----------------------------+
6. Total line number = 1
7. It costs 0.003s

Suppose we add another two timeseries, “root.ln.wf01.wt01.temperature” and “root.ln.wf02.wt01.temperature”. To query the count and the sum of “temperature” under path “root.ln..“, aggregating on level=2, use following statement:

1. select count(temperature), sum(temperature) from root.ln.*.* group by level=2

Result：

1. +---------------------------------+---------------------------------+-------------------------------+-------------------------------+
2. |count(root.ln.wf02.*.temperature)|count(root.ln.wf01.*.temperature)|sum(root.ln.wf02.*.temperature)|sum(root.ln.wf01.*.temperature)|
3. +---------------------------------+---------------------------------+-------------------------------+-------------------------------+
4. |                                8|                                4|                          228.0|              91.83000183105469|
5. +---------------------------------+---------------------------------+-------------------------------+-------------------------------+
6. Total line number = 1
7. It costs 0.013s

To query the count and the sum of path “root.ln.*.*.temperature” aggregating on “root.ln” level, simply set level=1

1. select count(temperature), sum(temperature) from root.ln.*.* group by level=1

Result：

1. +------------------------------+----------------------------+
2. |count(root.ln.*.*.temperature)|sum(root.ln.*.*.temperature)|
3. +------------------------------+----------------------------+
4. |                            12|           319.8300018310547|
5. +------------------------------+----------------------------+
6. Total line number = 1
7. It costs 0.013s

All supported aggregation functions are: count, sum, avg, last_value, first_value, min_time, max_time, min_value, max_value, extreme. When using four aggregations: sum, avg, min_value, max_value and extreme, please make sure all the aggregated series have exactly the same data type. Otherwise, it will generate a syntax error.

Down-Frequency Aggregate Query

This section mainly introduces the related examples of down-frequency aggregation query, using the GROUP BY clause, which is used to partition the result set according to the user’s given partitioning conditions and aggregate the partitioned result set. IoTDB supports partitioning result sets according to time interval and customized sliding step which should not be smaller than the time interval and defaults to equal the time interval if not set. And by default results are sorted by time in ascending order. You can also use the Java JDBC standard interface to execute related queries.

The GROUP BY statement provides users with three types of specified parameters:

Parameter 1: The display window on the time axis
Parameter 2: Time interval for dividing the time axis(should be positive)
Parameter 3: Time sliding step (optional and should not be smaller than the time interval and defaults to equal the time interval if not set)

The actual meanings of the three types of parameters are shown in Figure 5.2 below. Among them, the parameter 3 is optional. There are three typical examples of frequency reduction aggregation: parameter 3 not specified, parameter 3 specified, and value filtering conditions specified.

Figure 5.2 The actual meanings of the three types of parameters

Down-Frequency Aggregate Query without Specifying the Sliding Step Length

The SQL statement is:

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

which means:

Since the sliding step length is not specified, the GROUP BY statement by default set the sliding step the same as the time interval which is 1d.

The fist parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).

The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1d) as time interval and startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [0,1d), [1d, 2d), [2d, 3d), etc.

Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07 T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 1-day period from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).

Since there is data for each time period in the result range to be displayed, the execution result of the SQL statement is shown below:

1. +-----------------------------+-------------------------------+----------------------------------------+
2. |                         Time|count(root.ln.wf01.wt01.status)|max_value(root.ln.wf01.wt01.temperature)|
3. +-----------------------------+-------------------------------+----------------------------------------+
4. |2017-11-01T00:00:00.000+08:00|                           1440|                                    26.0|
5. |2017-11-02T00:00:00.000+08:00|                           1440|                                    26.0|
6. |2017-11-03T00:00:00.000+08:00|                           1440|                                   25.99|
7. |2017-11-04T00:00:00.000+08:00|                           1440|                                    26.0|
8. |2017-11-05T00:00:00.000+08:00|                           1440|                                    26.0|
9. |2017-11-06T00:00:00.000+08:00|                           1440|                                   25.99|
10. |2017-11-07T00:00:00.000+08:00|                           1380|                                    26.0|
11. +-----------------------------+-------------------------------+----------------------------------------+
12. Total line number = 7
13. It costs 0.024s

Down-Frequency Aggregate Query Specifying the Sliding Step Length

The SQL statement is:

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

which means:

Since the user specifies the sliding step parameter as 1d, the GROUP BY statement will move the time interval 1 day long instead of 3 hours as default.

That means we want to fetch all the data of 00:00:00 to 02:59:59 every day from 2017-11-01 to 2017-11-07.

The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).

The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (3h) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-11-01T03:00:00), [2017-11-02T00:00:00, 2017-11-02T03:00:00), [2017-11-03T00:00:00, 2017-11-03T03:00:00), etc.

The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.

Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 3-hour period for each day from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).

Since there is data for each time period in the result range to be displayed, the execution result of the SQL statement is shown below:

1. +-----------------------------+-------------------------------+----------------------------------------+
2. |                         Time|count(root.ln.wf01.wt01.status)|max_value(root.ln.wf01.wt01.temperature)|
3. +-----------------------------+-------------------------------+----------------------------------------+
4. |2017-11-01T00:00:00.000+08:00|                            180|                                   25.98|
5. |2017-11-02T00:00:00.000+08:00|                            180|                                   25.98|
6. |2017-11-03T00:00:00.000+08:00|                            180|                                   25.96|
7. |2017-11-04T00:00:00.000+08:00|                            180|                                   25.96|
8. |2017-11-05T00:00:00.000+08:00|                            180|                                    26.0|
9. |2017-11-06T00:00:00.000+08:00|                            180|                                   25.85|
10. |2017-11-07T00:00:00.000+08:00|                            180|                                   25.99|
11. +-----------------------------+-------------------------------+----------------------------------------+
12. Total line number = 7
13. It costs 0.006s

Down-Frequency Aggregate Query by Natural Month

The SQL statement is:

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

which means:

Since the user specifies the sliding step parameter as 2mo, the GROUP BY statement will move the time interval 2 months long instead of 1 month as default.

The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2019-11-07T23:00:00).

The start time is 2017-11-01T00:00:00. The sliding step will increment monthly based on the start date, and the 1st day of the month will be used as the time interval’s start time.

The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-12-01T00:00:00), [2018-02-01T00:00:00, 2018-03-01T00:00:00), [2018-05-03T00:00:00, 2018-06-01T00:00:00)), etc.

The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.

Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of (2017-11-01T00:00:00, 2019-11-07T23:00:00], and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-11-01T00:00:00 to 2019-11-07T23:00:00).

The SQL execution result is:

1. +-----------------------------+-------------------------------+
2. |                         Time|count(root.ln.wf01.wt01.status)|
3. +-----------------------------+-------------------------------+
4. |2017-11-01T00:00:00.000+08:00|                            259|
5. |2018-01-01T00:00:00.000+08:00|                            250|
6. |2018-03-01T00:00:00.000+08:00|                            259|
7. |2018-05-01T00:00:00.000+08:00|                            251|
8. |2018-07-01T00:00:00.000+08:00|                            242|
9. |2018-09-01T00:00:00.000+08:00|                            225|
10. |2018-11-01T00:00:00.000+08:00|                            216|
11. |2019-01-01T00:00:00.000+08:00|                            207|
12. |2019-03-01T00:00:00.000+08:00|                            216|
13. |2019-05-01T00:00:00.000+08:00|                            207|
14. |2019-07-01T00:00:00.000+08:00|                            199|
15. |2019-09-01T00:00:00.000+08:00|                            181|
16. |2019-11-01T00:00:00.000+08:00|                             60|
17. +-----------------------------+-------------------------------+

The SQL statement is:

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

which means:

Since the user specifies the sliding step parameter as 2mo, the GROUP BY statement will move the time interval 2 months long instead of 1 month as default.

The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-10-31T00:00:00, 2019-11-07T23:00:00).

Different from the previous example, the start time is set to 2017-10-31T00:00:00. The sliding step will increment monthly based on the start date, and the 31st day of the month meaning the last day of the month will be used as the time interval’s start time. If the start time is set to the 30th date, the sliding step will use the 30th or the last day of the month.

The start time is 2017-10-31T00:00:00. The sliding step will increment monthly based on the start time, and the 1st day of the month will be used as the time interval’s start time.

The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-10-31T00:00:00, 2017-11-31T00:00:00), [2018-02-31T00:00:00, 2018-03-31T00:00:00), [2018-05-31T00:00:00, 2018-06-31T00:00:00), etc.

The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.

Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-10-31T00:00:00, 2019-11-07T23:00:00) and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-10-31T00:00:00 to 2019-11-07T23:00:00).

The SQL execution result is:

1. +-----------------------------+-------------------------------+
2. |                         Time|count(root.ln.wf01.wt01.status)|
3. +-----------------------------+-------------------------------+
4. |2017-10-31T00:00:00.000+08:00|                            251|
5. |2017-12-31T00:00:00.000+08:00|                            250|
6. |2018-02-28T00:00:00.000+08:00|                            259|
7. |2018-04-30T00:00:00.000+08:00|                            250|
8. |2018-06-30T00:00:00.000+08:00|                            242|
9. |2018-08-31T00:00:00.000+08:00|                            225|
10. |2018-10-31T00:00:00.000+08:00|                            216|
11. |2018-12-31T00:00:00.000+08:00|                            208|
12. |2019-02-28T00:00:00.000+08:00|                            216|
13. |2019-04-30T00:00:00.000+08:00|                            208|
14. |2019-06-30T00:00:00.000+08:00|                            199|
15. |2019-08-31T00:00:00.000+08:00|                            181|
16. |2019-10-31T00:00:00.000+08:00|                             69|
17. +-----------------------------+-------------------------------+

Left Open And Right Close Range

The SQL statement is:

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

In this sql, the time interval is left open and right close, so we won’t include the value of timestamp 2017-11-01T00:00:00 and instead we will include the value of timestamp 2017-11-07T23:00:00.

We will get the result like following:

1. +-----------------------------+-------------------------------+
2. |                         Time|count(root.ln.wf01.wt01.status)|
3. +-----------------------------+-------------------------------+
4. |2017-11-02T00:00:00.000+08:00|                           1440|
5. |2017-11-03T00:00:00.000+08:00|                           1440|
6. |2017-11-04T00:00:00.000+08:00|                           1440|
7. |2017-11-05T00:00:00.000+08:00|                           1440|
8. |2017-11-06T00:00:00.000+08:00|                           1440|
9. |2017-11-07T00:00:00.000+08:00|                           1440|
10. |2017-11-07T23:00:00.000+08:00|                           1380|
11. +-----------------------------+-------------------------------+
12. Total line number = 7
13. It costs 0.004s

Down-Frequency Aggregate Query with Level Clause

Level could be defined to show count the number of points of each node at the given level in current Metadata Tree.

This could be used to query the number of points under each device.

The SQL statement is:

Get down-frequency aggregate query by level.

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

Result:

1. +-----------------------------+-------------------------+
2. |                         Time|COUNT(root.ln.*.*.status)|
3. +-----------------------------+-------------------------+
4. |2017-11-02T00:00:00.000+08:00|                     1440|
5. |2017-11-03T00:00:00.000+08:00|                     1440|
6. |2017-11-04T00:00:00.000+08:00|                     1440|
7. |2017-11-05T00:00:00.000+08:00|                     1440|
8. |2017-11-06T00:00:00.000+08:00|                     1440|
9. |2017-11-07T00:00:00.000+08:00|                     1440|
10. |2017-11-07T23:00:00.000+08:00|                     1380|
11. +-----------------------------+-------------------------+
12. Total line number = 7
13. It costs 0.006s

Down-frequency aggregate query with sliding step and by level.

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

Result:

1. +-----------------------------+-------------------------+
2. |                         Time|COUNT(root.ln.*.*.status)|
3. +-----------------------------+-------------------------+
4. |2017-11-01T00:00:00.000+08:00|                      180|
5. |2017-11-02T00:00:00.000+08:00|                      180|
6. |2017-11-03T00:00:00.000+08:00|                      180|
7. |2017-11-04T00:00:00.000+08:00|                      180|
8. |2017-11-05T00:00:00.000+08:00|                      180|
9. |2017-11-06T00:00:00.000+08:00|                      180|
10. |2017-11-07T00:00:00.000+08:00|                      180|
11. +-----------------------------+-------------------------+
12. Total line number = 7
13. It costs 0.004s

Down-Frequency Aggregate Query with Fill Clause

In group by fill, sliding step is not supported in group by clause

Now, only last_value aggregation function is supported in group by fill.

Linear fill is not supported in group by fill.

Difference Between PREVIOUSUNTILLAST And PREVIOUS

PREVIOUS will fill any null value as long as there exist value is not null before it.
PREVIOUSUNTILLAST won’t fill the result whose time is after the last time of that time series.

first, we check value root.ln.wf01.wt01.temperature when time after 2017-11-07T23:50:00.

``` 1. IoTDB> SELECT temperature FROM root.ln.wf01.wt01 wher

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