Query Data

OVERVIEW

Syntax Definition

In IoTDB, SELECT statement is used to retrieve data from one or more selected time series. Here is the syntax definition of SELECT statement:

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}]

Syntax Description

SELECT clause

  • The SELECT clause specifies the output of the query, consisting of several selectExpr.
  • Each selectExpr defines one or more columns in the query result, which is an expression consisting of time series path suffixes, constants, functions, and operators.
  • Supports using AS to specify aliases for columns in the query result set.
  • Use the LAST keyword in the SELECT clause to specify that the query is the last query.

INTO clause

  • SELECT INTO is used to write query results into a series of specified time series. The INTO clause specifies the target time series to which query results are written.

FROM clause

  • The FROM clause contains the path prefix of one or more time series to be queried, and wildcards are supported.
  • When executing a query, the path prefix in the FROM clause and the suffix in the SELECT clause will be concatenated to obtain a complete query target time series.

WHERE clause

  • The WHERE clause specifies the filtering conditions for data rows, consisting of a whereCondition.
  • whereCondition is a logical expression that evaluates to true for each row to be selected. If there is no WHERE clause, all rows will be selected.
  • In whereCondition, any IOTDB-supported functions and operators can be used except aggregate functions.

GROUP BY clause

  • The GROUP BY clause specifies how the time series are aggregated by segment or group.
  • Segmented aggregation refers to segmenting data in the row direction according to the time dimension, aiming at the time relationship between different data points in the same time series, and obtaining an aggregated value for each segment. Currently only group by time、group by variation、group by condition、group by session and group by count is supported, and more segmentation methods will be supported in the future.
  • Group aggregation refers to grouping the potential business attributes of time series for different time series. Each group contains several time series, and each group gets an aggregated value. Support group by path level and group by tag two grouping methods.
  • Segment aggregation and group aggregation can be mixed.

HAVING clause

  • The HAVING clause specifies the filter conditions for the aggregation results, consisting of a havingCondition.
  • havingCondition is a logical expression that evaluates to true for the aggregation results to be selected. If there is no HAVING clause, all aggregated results will be selected.
  • HAVING is to be used with aggregate functions and the GROUP BY clause.

ORDER BY clause

  • The ORDER BY clause is used to specify how the result set is sorted.
  • In ALIGN BY TIME mode: By default, they are sorted in ascending order of timestamp size, and ORDER BY TIME DESC can be used to specify that the result set is sorted in descending order of timestamp.
  • In ALIGN BY DEVICE mode: arrange according to the device first, and sort each device in ascending order according to the timestamp. The ordering and priority can be adjusted by ORDER BY clause.

FILL clause

  • The FILL clause is used to specify the filling mode in the case of missing data, allowing users to fill in empty values ​​for the result set of any query according to a specific method.

SLIMIT and SOFFSET clauses

  • SLIMIT specifies the number of columns of the query result, and SOFFSET specifies the starting column position of the query result display. SLIMIT and SOFFSET are only used to control value columns and have no effect on time and device columns.

LIMIT and OFFSET clauses

  • LIMIT specifies the number of rows of the query result, and OFFSET specifies the starting row position of the query result display.

ALIGN BY clause

  • The query result set is ALIGN BY TIME by default, including a time column and several value columns, and the timestamps of each column of data in each row are the same.
  • It also supports ALIGN BY DEVICE. The query result set contains a time column, a device column, and several value columns.

Basic Examples

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

Execution Interface

In IoTDB, there are two ways to execute data query:

  • Execute queries using IoTDB-SQL.
  • Efficient execution interfaces for common queries, including time-series raw data query, last query, and aggregation query.

Execute queries using IoTDB-SQL

Data query statements can be used in SQL command-line terminals, JDBC, JAVA / C++ / Python / Go and other native APIs, and RESTful APIs.

  • Execute the query statement in the SQL command line terminal: start the SQL command line terminal, and directly enter the query statement to execute, see SQL command line terminal.
  • Execute query statements in JDBC, see JDBC for details.
  • Execute query statements in native APIs such as JAVA / C++ / Python / Go. For details, please refer to the relevant documentation in the Application Programming Interface chapter. The interface prototype is as follows:

1. SessionDataSet executeQueryStatement(String sql) - Used in RESTful API, see HTTP API V1 or HTTP API V2 for details.

Efficient execution interfaces

The native APIs provide efficient execution interfaces for commonly used queries, which can save time-consuming operations such as SQL parsing. include:

  • Time-series raw data query with time range:
  • The specified query time range is a left-closed right-open interval, including the start time but excluding the end time.
1. SessionDataSet executeRawDataQuery(List<String> paths, long startTime, long endTime);
  • Last query:
  • Query the last data, whose timestamp is greater than or equal LastTime.
1. SessionDataSet executeLastDataQuery(List<String> paths, long LastTime);
  • Aggregation query:
  • Support specified query time range: The specified query time range is a left-closed right-open interval, including the start time but not the end time.
  • Support GROUP BY TIME.
1. SessionDataSet executeAggregationQuery(List<String> paths, List<Aggregation> aggregations);

3. SessionDataSet executeAggregationQuery(
4. List<String> paths, List<Aggregation> aggregations, long startTime, long endTime);

6. SessionDataSet executeAggregationQuery(
7. List<String> paths,
8. List<Aggregation> aggregations,
9. long startTime,
10. long endTime,
11. long interval);

13. SessionDataSet executeAggregationQuery(
14. List<String> paths,
15. List<Aggregation> aggregations,
16. long startTime,
17. long endTime,
18. long interval,
19. long slidingStep);

SELECT CLAUSE

The SELECT clause specifies the output of the query, consisting of several selectExpr. Each selectExpr defines one or more columns in the query result. For select expression details, see document Operator-and-Expression.

  • Example 1:
1. select temperature from root.ln.wf01.wt01
  • Example 2:
1. select status, temperature from root.ln.wf01.wt01

Last Query

The last query is a special type of query in Apache IoTDB. It returns the data point with the largest timestamp of the specified time series. In other word, it returns the latest state of a time series. This feature is especially important in IoT data analysis scenarios. To meet the performance requirement of real-time device monitoring systems, Apache IoTDB caches the latest values of all time series to achieve microsecond read latency.

The last query is to return the most recent data point of the given timeseries in a three column format.

The SQL syntax is defined as:

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

which means: Query and return the last data points of timeseries prefixPath.path.

  • Only time filter is supported in <WhereClause>. Any other filters given in the <WhereClause> will give an exception. When the cached most recent data point does not satisfy the criterion specified by the filter, IoTDB will have to get the result from the external storage, which may cause a decrease in performance.
  • The result will be returned in a four column table format.

1. | Time | timeseries | value | dataType |

Note: The value colum will always return the value as string and thus also has TSDataType.TEXT. Therefore, the column dataType is returned also which contains the real type how the value should be interpreted. - We can use TIME/TIMESERIES/VALUE/DATATYPE (DESC | ASC) to specify that the result set is sorted in descending/ascending order based on a particular column. When the value column contains multiple types of data, the sorting is based on the string representation of the values.

Example 1: get the last point of root.ln.wf01.wt01.status:

1. IoTDB> select last status from root.ln.wf01.wt01
2. +-----------------------------+------------------------+-----+--------+
3. |                         Time|              timeseries|value|dataType|
4. +-----------------------------+------------------------+-----+--------+
5. |2017-11-07T23:59:00.000+08:00|root.ln.wf01.wt01.status|false| BOOLEAN|
6. +-----------------------------+------------------------+-----+--------+
7. Total line number = 1
8. It costs 0.000s

Example 2: get the last status and temperature points of root.ln.wf01.wt01, whose timestamp larger or equal to 2017-11-07T23:50:00。

1. IoTDB> select last status, temperature from root.ln.wf01.wt01 where time >= 2017-11-07T23:50:00
2. +-----------------------------+-----------------------------+---------+--------+
3. |                         Time|                   timeseries|    value|dataType|
4. +-----------------------------+-----------------------------+---------+--------+
5. |2017-11-07T23:59:00.000+08:00|     root.ln.wf01.wt01.status|    false| BOOLEAN|
6. |2017-11-07T23:59:00.000+08:00|root.ln.wf01.wt01.temperature|21.067368|  DOUBLE|
7. +-----------------------------+-----------------------------+---------+--------+
8. Total line number = 2
9. It costs 0.002s

Example 3: get the last points of all sensor in root.ln.wf01.wt01, and order the result by the timeseries column in descending order

1. IoTDB> select last * from root.ln.wf01.wt01 order by timeseries desc;
2. +-----------------------------+-----------------------------+---------+--------+
3. |                         Time|                   timeseries|    value|dataType|
4. +-----------------------------+-----------------------------+---------+--------+
5. |2017-11-07T23:59:00.000+08:00|root.ln.wf01.wt01.temperature|21.067368|  DOUBLE|
6. |2017-11-07T23:59:00.000+08:00|     root.ln.wf01.wt01.status|    false| BOOLEAN|
7. +-----------------------------+-----------------------------+---------+--------+
8. Total line number = 2
9. It costs 0.002s

Example 4： get the last points of all sensor in root.ln.wf01.wt01, and order the result by the dataType column in descending order

1. IoTDB> select last * from root.ln.wf01.wt01 order by dataType desc;
2. +-----------------------------+-----------------------------+---------+--------+
3. |                         Time|                   timeseries|    value|dataType|
4. +-----------------------------+-----------------------------+---------+--------+
5. |2017-11-07T23:59:00.000+08:00|root.ln.wf01.wt01.temperature|21.067368|  DOUBLE|
6. |2017-11-07T23:59:00.000+08:00|     root.ln.wf01.wt01.status|    false| BOOLEAN|
7. +-----------------------------+-----------------------------+---------+--------+
8. Total line number = 2
9. It costs 0.002s

Note: The requirement to query the latest data point with other filtering conditions can be implemented through function composition. For example:

1. IoTDB> select max_time(*), last_value(*) from root.ln.wf01.wt01 where time >= 2017-11-07T23:50:00 and status = false align by device
2. +-----------------+---------------------+----------------+-----------------------+------------------+
3. |           Device|max_time(temperature)|max_time(status)|last_value(temperature)|last_value(status)|
4. +-----------------+---------------------+----------------+-----------------------+------------------+
5. |root.ln.wf01.wt01|        1510077540000|   1510077540000|              21.067368|             false|
6. +-----------------+---------------------+----------------+-----------------------+------------------+
7. Total line number = 1
8. It costs 0.021s

WHERE CLAUSE

In IoTDB query statements, two filter conditions, time filter and value filter, are supported.

The supported operators are as follows:

  • Comparison operators: greater than (>), greater than or equal ( >=), equal ( = or ==), not equal ( != or <>), less than or equal ( <=), less than ( <).
  • Logical operators: and ( AND or & or &&), or ( OR or | or ||), not ( NOT or !).
  • Range contains operator: contains ( IN ).
  • String matches operator: LIKE, REGEXP.

Time Filter

Use time filters to filter data for a specific time range. For supported formats of timestamps, please refer to Timestamp .

An example is as follows:

  1. Select data with timestamp greater than 2022-01-01T00:05:00.000:

1. select s1 from root.sg1.d1 where time > 2022-01-01T00:05:00.000; 2. Select data with timestamp equal to 2022-01-01T00:05:00.000:

1. select s1 from root.sg1.d1 where time = 2022-01-01T00:05:00.000; 3. Select the data in the time interval [2017-11-01T00:05:00.000, 2017-11-01T00:12:00.000):

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

Note: In the above example, time can also be written as timestamp.

Value Filter

Use value filters to filter data whose data values meet certain criteria. Allow to use a time series not selected in the select clause as a value filter.

An example is as follows:

  1. Select data with a value greater than 36.5:

1. select temperature from root.sg1.d1 where temperature > 36.5; 2. Select data with value equal to true:

1. select status from root.sg1.d1 where status = true; 3. Select data for the interval [36.5,40] or not:

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

1. select temperature from root.sg1.d1 where temperature not between 36.5 and 40; 4. Select data with values within a specific range:

1. select code from root.sg1.d1 where code in ('200', '300', '400', '500'); 5. Select data with values outside a certain range:

1. select code from root.sg1.d1 where code not in ('200', '300', '400', '500'); 6. Select data with values is null:

1. select code from root.sg1.d1 where temperature is null; 7. Select data with values is not null:

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

Fuzzy Query

Fuzzy query is divided into Like statement and Regexp statement, both of which can support fuzzy matching of TEXT type data.

Like statement:

Fuzzy matching using Like

In the value filter condition, for TEXT type data, use Like and Regexp operators to perform fuzzy matching on data.

Matching rules:

  • The percentage (%) wildcard matches any string of zero or more characters.
  • The underscore (_) wildcard matches any single character.

Example 1: Query data containing 'cc' in value under root.sg.d1.

1. IoTDB> select * from root.sg.d1 where value like '%cc%'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:00.000+08:00|        aabbccdd|
6. |2017-11-01T00:00:01.000+08:00|              cc|
7. +-----------------------------+----------------+
8. Total line number = 2
9. It costs 0.002s

Example 2: Query data that consists of 3 characters and the second character is 'b' in value under root.sg.d1.

1. IoTDB> select * from root.sg.device where value like '_b_'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:02.000+08:00|             abc|
6. +-----------------------------+----------------+
7. Total line number = 1
8. It costs 0.002s

Fuzzy matching using Regexp

The filter conditions that need to be passed in are regular expressions in the Java standard library style.

Examples of common regular matching:

1. All characters with a length of 3-20: ^.{3,20}$
2. Uppercase english characters: ^[A-Z]+$
3. Numbers and English characters: ^[A-Za-z0-9]+$
4. Beginning with a: ^a.*

Example 1: Query a string composed of 26 English characters for the value under root.sg.d1

1. IoTDB> select * from root.sg.d1 where value regexp '^[A-Za-z]+$'
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:00.000+08:00|        aabbccdd|
6. |2017-11-01T00:00:01.000+08:00|              cc|
7. +-----------------------------+----------------+
8. Total line number = 2
9. It costs 0.002s

Example 2: Query root.sg.d1 where the value value is a string composed of 26 lowercase English characters and the time is greater than 100

1. IoTDB> select * from root.sg.d1 where value regexp '^[a-z]+$' and time > 100
2. +-----------------------------+----------------+
3. |                         Time|root.sg.d1.value|
4. +-----------------------------+----------------+
5. |2017-11-01T00:00:00.000+08:00|        aabbccdd|
6. |2017-11-01T00:00:01.000+08:00|              cc|
7. +-----------------------------+----------------+
8. Total line number = 2
9. It costs 0.002s

GROUP BY CLAUSE

IoTDB supports using GROUP BY clause to aggregate the time series by segment and group.

Segmented aggregation refers to segmenting data in the row direction according to the time dimension, aiming at the time relationship between different data points in the same time series, and obtaining an aggregated value for each segment. Currently only group by time、group by variation、group by condition、group by session and group by count is supported, and more segmentation methods will be supported in the future.

Group aggregation refers to grouping the potential business attributes of time series for different time series. Each group contains several time series, and each group gets an aggregated value. Support group by path level and group by tag two grouping methods.

Aggregate By Segment

Aggregate By Time

Aggregate by time is a typical query method for time series data. Data is collected at high frequency and needs to be aggregated and calculated at certain time intervals. For example, to calculate the daily average temperature, the sequence of temperature needs to be segmented by day, and then calculated. average value.

Aggregate by time refers to a query method that uses a lower frequency than the time frequency of data collection, and is a special case of segmented aggregation. For example, the frequency of data collection is one second. If you want to display the data in one minute, you need to use time aggregagtion.

This section mainly introduces the related examples of time aggregation, using the GROUP BY clause. IoTDB supports partitioning result sets according to time interval and customized sliding step. And by default results are sorted by time in ascending order.

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 defaults to equal the time interval if not set)

The actual meanings of the three types of parameters are shown in Figure below. Among them, the parameter 3 is optional.

Query Data - 图1

There are three typical examples of frequency reduction aggregation:

Aggregate By Time 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
Aggregate By Time 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

The sliding step can be smaller than the interval, in which case there is overlapping time between the aggregation windows (similar to a sliding window).

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-01 10:00:00), 4h, 2h);

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-01T02:00:00.000+08:00|                            180|                                   25.98|
6. |2017-11-01T04:00:00.000+08:00|                            180|                                   25.96|
7. |2017-11-01T06:00:00.000+08:00|                            180|                                   25.96|
8. |2017-11-01T08:00:00.000+08:00|                            180|                                    26.0|
9. +-----------------------------+-------------------------------+----------------------------------------+
10. Total line number = 5
11. It costs 0.006s
Aggregate 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.00

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