Select Expression

The SELECT clause specifies the output of the query, consisting of several selectExpr. Each selectExpr defines one or more columns in the query result.

**selectExpr is an expression consisting of time series path suffixes, constants, functions, and operators. That is, selectExpr can contain: **

  • Time series path suffix (wildcards are supported)
  • operator
    • Arithmetic operators
    • comparison operators
    • Logical Operators
  • function
    • aggregate functions
    • Time series generation functions (including built-in functions and user-defined functions)
  • constant

Use Alias

Since the unique data model of IoTDB, lots of additional information like device will be carried before each sensor. Sometimes, we want to query just one specific device, then these prefix information show frequently will be redundant in this situation, influencing the analysis of result set. At this time, we can use AS function provided by IoTDB, assign an alias to time series selected in query.

For example：

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

The result set is：

Timetemperaturespeed
………

Operator

See the documentation Operators and Functions for a list of operators supported in IoTDB.

Function

aggregate functions

Aggregate functions are many-to-one functions. They perform aggregate calculations on a set of values, resulting in a single aggregated result.

A query that contains an aggregate function is called an aggregate query, otherwise, it is called a time series query.

Please note that mixed use of Aggregate Query and Timeseries Query is not allowed. Below are examples for queries that are not allowed.

  1. select a, count(a) from root.sg 
  2. select sin(a), count(a) from root.sg
  3. select a, count(a) from root.sg group by ([10,100),10ms)

For the aggregation functions supported by IoTDB, see the document Aggregation Functions.

Time series generation function

A time series generation function takes several raw time series as input and produces a list of time series as output. Unlike aggregate functions, time series generators have a timestamp column in their result sets.

All time series generation functions accept * as input, and all can be mixed with raw time series queries.

Built-in time series generation functions

See the documentation Operators and Functions for a list of built-in functions supported in IoTDB.

User-Defined time series generation function

IoTDB supports function extension through User Defined Function (click for User-Defined Function) capability.

Nested Expressions

IoTDB supports the calculation of arbitrary nested expressions. Since time series query and aggregation query can not be used in a query statement at the same time, we divide nested expressions into two types, which are nested expressions with time series query and nested expressions with aggregation query.

The following is the syntax definition of the select clause:

  1. selectClause
  2.     : SELECT resultColumn (',' resultColumn)*
  3.     ;
  5. resultColumn
  6.     : expression (AS ID)?
  7.     ;
  9. expression
  10.     : '(' expression ')'
  11.     | '-' expression
  12.     | expression ('*' | '/' | '%') expression
  13.     | expression ('+' | '-') expression
  14.     | functionName '(' expression (',' expression)* functionAttribute* ')'
  15.     | timeSeriesSuffixPath
  16.     | number
  17.     ;

Nested Expressions with Time Series Query

IoTDB supports the calculation of arbitrary nested expressions consisting of numbers, time series, time series generating functions (including user-defined functions) and arithmetic expressions in the select clause.

Example

Input1：

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

Result1：

  1. +-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. |                         Time|root.sg1.a|root.sg1.b|((((root.sg1.a + 1) * 2) - 1) % 2) + 1.5|sin(root.sg1.a + sin(root.sg1.a + sin(root.sg1.b)))|(-root.sg1.a + root.sg1.b * ((sin(root.sg1.a + root.sg1.b) * sin(root.sg1.a + root.sg1.b)) + (cos(root.sg1.a + root.sg1.b) * cos(root.sg1.a + root.sg1.b)))) + 1|
  3. +-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. |1970-01-01T08:00:00.010+08:00|         1|         1|                                     2.5|                                 0.9238430524420609|                                                                                                                      -1.0|
  5. |1970-01-01T08:00:00.020+08:00|         2|         2|                                     2.5|                                 0.7903505371876317|                                                                                                                      -3.0|
  6. |1970-01-01T08:00:00.030+08:00|         3|         3|                                     2.5|                                0.14065207680386618|                                                                                                                      -5.0|
  7. |1970-01-01T08:00:00.040+08:00|         4|      null|                                     2.5|                                               null|                                                                                                                      null|
  8. |1970-01-01T08:00:00.050+08:00|      null|         5|                                    null|                                               null|                                                                                                                      null|
  9. |1970-01-01T08:00:00.060+08:00|         6|         6|                                     2.5|                                -0.7288037411970916|                                                                                                                     -11.0|
  10. +-----------------------------+----------+----------+----------------------------------------+---------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. Total line number = 6
  12. It costs 0.048s

Input2：

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

Result2：

  1. +-----------------------------+----------------------------------------------+
  2. |                         Time|((root.sg.a + root.sg.b) * 2) + sin(root.sg.a)|
  3. +-----------------------------+----------------------------------------------+
  4. |1970-01-01T08:00:00.010+08:00|                             59.45597888911063|
  5. |1970-01-01T08:00:00.020+08:00|                            100.91294525072763|
  6. |1970-01-01T08:00:00.030+08:00|                            139.01196837590714|
  7. |1970-01-01T08:00:00.040+08:00|                            180.74511316047935|
  8. |1970-01-01T08:00:00.050+08:00|                            219.73762514629607|
  9. |1970-01-01T08:00:00.060+08:00|                             259.6951893788978|
  10. |1970-01-01T08:00:00.070+08:00|                             300.7738906815579|
  11. |1970-01-01T08:00:00.090+08:00|                             39.45597888911063|
  12. |1970-01-01T08:00:00.100+08:00|                             39.45597888911063|
  13. +-----------------------------+----------------------------------------------+
  14. Total line number = 9
  15. It costs 0.011s

Input3：

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

Result3：

  1. +-----------------------------+-----------------------------+-----------------------------+
  2. |                         Time|(root.sg1.a + root.sg1.a) / 2|(root.sg1.a + root.sg1.b) / 2|
  3. +-----------------------------+-----------------------------+-----------------------------+
  4. |1970-01-01T08:00:00.010+08:00|                          1.0|                          1.0|
  5. |1970-01-01T08:00:00.020+08:00|                          2.0|                          2.0|
  6. |1970-01-01T08:00:00.030+08:00|                          3.0|                          3.0|
  7. |1970-01-01T08:00:00.040+08:00|                          4.0|                         null|
  8. |1970-01-01T08:00:00.060+08:00|                          6.0|                          6.0|
  9. +-----------------------------+-----------------------------+-----------------------------+
  10. Total line number = 5
  11. It costs 0.011s

Input4：

  1. select (a + b) * 3 from root.sg, root.ln

Result4：

  1. +-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
  2. |                         Time|(root.sg.a + root.sg.b) * 3|(root.sg.a + root.ln.b) * 3|(root.ln.a + root.sg.b) * 3|(root.ln.a + root.ln.b) * 3|
  3. +-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
  4. |1970-01-01T08:00:00.010+08:00|                       90.0|                      270.0|                      360.0|                      540.0|
  5. |1970-01-01T08:00:00.020+08:00|                      150.0|                      330.0|                      690.0|                      870.0|
  6. |1970-01-01T08:00:00.030+08:00|                      210.0|                      450.0|                      570.0|                      810.0|
  7. |1970-01-01T08:00:00.040+08:00|                      270.0|                      240.0|                      690.0|                      660.0|
  8. |1970-01-01T08:00:00.050+08:00|                      330.0|                       null|                       null|                       null|
  9. |1970-01-01T08:00:00.060+08:00|                      390.0|                       null|                       null|                       null|
  10. |1970-01-01T08:00:00.070+08:00|                      450.0|                       null|                       null|                       null|
  11. |1970-01-01T08:00:00.090+08:00|                       60.0|                       null|                       null|                       null|
  12. |1970-01-01T08:00:00.100+08:00|                       60.0|                       null|                       null|                       null|
  13. +-----------------------------+---------------------------+---------------------------+---------------------------+---------------------------+
  14. Total line number = 9
  15. It costs 0.014s
Explanation
  • Only when the left operand and the right operand under a certain timestamp are not null, the nested expressions will have an output value. Otherwise this row will not be included in the result.
    • In Result1 of the Example part, the value of time series root.sg.a at time 40 is 4, while the value of time series root.sg.b is null. So at time 40, the value of nested expressions (a + b) * 2 + sin(a) is null. So in Result2, this row is not included in the result.
  • If one operand in the nested expressions can be translated into multiple time series (For example, *), the result of each time series will be included in the result (Cartesian product). Please refer to Input3, Input4 and corresponding Result3 and Result4 in Example.
Note

Please note that Aligned Time Series has not been supported in Nested Expressions with Time Series Query yet. An error message is expected if you use it with Aligned Time Series selected in a query statement.

Nested Expressions query with aggregations

IoTDB supports the calculation of arbitrary nested expressions consisting of numbers, aggregations and arithmetic expressions in the select clause.

Example

Aggregation query without GROUP BY.

Input1:

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

Result1:

  1. +----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
  2. |avg(root.ln.wf01.wt01.temperature)|sin(avg(root.ln.wf01.wt01.temperature))|avg(root.ln.wf01.wt01.temperature) + 1|-sum(root.ln.wf01.wt01.hardware)|avg(root.ln.wf01.wt01.temperature) + sum(root.ln.wf01.wt01.hardware)|
  3. +----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
  4. |                15.927999999999999|                   -0.21826546964855045|                    16.927999999999997|                         -7426.0|                                                            7441.928|
  5. +----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+--------------------------------------------------------------------+
  6. Total line number = 1
  7. It costs 0.009s

Input2:

  1. select avg(*), 
  2.        (avg(*) + 1) * 3 / 2 -1 
  3. from root.sg1

Result2:

  1. +---------------+---------------+-------------------------------------+-------------------------------------+
  2. |avg(root.sg1.a)|avg(root.sg1.b)|(avg(root.sg1.a) + 1) * 3 / 2 - 1    |(avg(root.sg1.b) + 1) * 3 / 2 - 1    |
  3. +---------------+---------------+-------------------------------------+-------------------------------------+
  4. |            3.2|            3.4|                    5.300000000000001|                   5.6000000000000005|
  5. +---------------+---------------+-------------------------------------+-------------------------------------+
  6. Total line number = 1
  7. It costs 0.007s

Aggregation with GROUP BY.

Input3:

  1. select avg(temperature),
  2.        sin(avg(temperature)),
  3.        avg(temperature) + 1,
  4.        -sum(hardware),
  5.        avg(temperature) + sum(hardware) as custom_sum
  6. from root.ln.wf01.wt01
  7. GROUP BY([10, 90), 10ms);

Result3:

  1. +-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
  2. |                         Time|avg(root.ln.wf01.wt01.temperature)|sin(avg(root.ln.wf01.wt01.temperature))|avg(root.ln.wf01.wt01.temperature) + 1|-sum(root.ln.wf01.wt01.hardware)|custom_sum|
  3. +-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
  4. |1970-01-01T08:00:00.010+08:00|                13.987499999999999|                     0.9888207947857667|                    14.987499999999999|                         -3211.0| 3224.9875|
  5. |1970-01-01T08:00:00.020+08:00|                              29.6|                    -0.9701057337071853|                                  30.6|                         -3720.0|    3749.6|
  6. |1970-01-01T08:00:00.030+08:00|                              null|                                   null|                                  null|                            null|      null|
  7. |1970-01-01T08:00:00.040+08:00|                              null|                                   null|                                  null|                            null|      null|
  8. |1970-01-01T08:00:00.050+08:00|                              null|                                   null|                                  null|                            null|      null|
  9. |1970-01-01T08:00:00.060+08:00|                              null|                                   null|                                  null|                            null|      null|
  10. |1970-01-01T08:00:00.070+08:00|                              null|                                   null|                                  null|                            null|      null|
  11. |1970-01-01T08:00:00.080+08:00|                              null|                                   null|                                  null|                            null|      null|
  12. +-----------------------------+----------------------------------+---------------------------------------+--------------------------------------+--------------------------------+----------+
  13. Total line number = 8
  14. It costs 0.012s
Explanation
  • Only when the left operand and the right operand under a certain timestamp are not null, the nested expressions will have an output value. Otherwise this row will not be included in the result. But for nested expressions with GROUP BY clause, it is better to show the result of all time intervals. Please refer to Input3 and corresponding Result3 in Example.
  • If one operand in the nested expressions can be translated into multiple time series (For example, *), the result of each time series will be included in the result (Cartesian product). Please refer to Input2 and corresponding Result2 in Example.