Select Expression

From this syntax definition, selectExpr can contain:

  • suffix of time series path
  • function
    • Built-in aggregation functions, see for details.
    • Time series generation function
    • User-defined functions, see UDF for details.
  • expressions
    • Arithmetic operation expressions
    • Time series generating nested expressions
    • Aggregate query nested expressions
  • Numeric constants (could be used in expressions only)

Unary Arithmetic Operators

Supported operators: +, -

Supported input data types: INT32, INT64, FLOAT and DOUBLE

Output data type: consistent with the input data type

Binary Arithmetic Operators

Supported operators: +, -, *, /, %

Supported input data types: INT32, INT64, FLOAT and DOUBLE

Output data type: DOUBLE

Note: Only when the left operand and the right operand under a certain timestamp are not null, the binary arithmetic operation will have an output value.

Example

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

Result:

  1. +-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
  2. |                         Time|root.sg.d1.s1|-root.sg.d1.s1|root.sg.d1.s2|root.sg.d1.s2|root.sg.d1.s1 + root.sg.d1.s2|root.sg.d1.s1 - root.sg.d1.s2|root.sg.d1.s1 * root.sg.d1.s2|root.sg.d1.s1 / root.sg.d1.s2|root.sg.d1.s1 % root.sg.d1.s2|
  3. +-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
  4. |1970-01-01T08:00:00.001+08:00|          1.0|          -1.0|          1.0|          1.0|                          2.0|                          0.0|                          1.0|                          1.0|                          0.0|
  5. |1970-01-01T08:00:00.002+08:00|          2.0|          -2.0|          2.0|          2.0|                          4.0|                          0.0|                          4.0|                          1.0|                          0.0|
  6. |1970-01-01T08:00:00.003+08:00|          3.0|          -3.0|          3.0|          3.0|                          6.0|                          0.0|                          9.0|                          1.0|                          0.0|
  7. |1970-01-01T08:00:00.004+08:00|          4.0|          -4.0|          4.0|          4.0|                          8.0|                          0.0|                         16.0|                          1.0|                          0.0|
  8. |1970-01-01T08:00:00.005+08:00|          5.0|          -5.0|          5.0|          5.0|                         10.0|                          0.0|                         25.0|                          1.0|                          0.0|
  9. +-----------------------------+-------------+--------------+-------------+-------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
  10. Total line number = 5
  11. It costs 0.014s

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.

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 NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
STRING_CONTAINSTEXTs: the sequence to search forBOOLEANDetermine whether s is in the string
STRING_MATCHESTEXTregex: the regular expression to which the string is to be matchedBOOLEANDetermine 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

Currently, IoTDB supports the following selector functions:

Function NameAllowed Input Series Data TypesRequired AttributesOutput Series Data TypeDescription
TOP_KINT32 / INT64 / FLOAT / DOUBLE / TEXTk: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000Same type as the input seriesReturns k data points with the largest values in a time series.
BOTTOM_KINT32 / INT64 / FLOAT / DOUBLE / TEXTk: the maximum number of selected data points, must be greater than 0 and less than or equal to 1000Same type as the input seriesReturns 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:

  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

Constant Timeseries Generating Functions

The constant timeseries generating function is used to generate a timeseries in which the values of all data points are the same.

The constant timeseries generating function accepts one or more timeseries inputs, and the timestamp set of the output data points is the union of the timestamp sets of the input timeseries.

Currently, IoTDB supports the following constant timeseries generating functions:

Function NameRequired AttributesOutput Series Data TypeDescription
CONSTvalue: the value of the output data point
type: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXT		Determined by the required attribute typeOutput the user-specified constant timeseries according to the attributes value and type.
PINoneDOUBLEData point value: a double value of π, the ratio of the circumference of a circle to its diameter, which is equals to Math.PI in the Java Standard Library.
ENoneDOUBLEData point value: a double value of e, the base of the natural logarithms, which is equals to Math.E in the Java Standard Library.

Example:

    Result:

    1. select s1, s2, const(s1, 'value'='1024', 'type'='INT64'), pi(s2), e(s1, s2) from root.sg1.d1; 
    2. +-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
    3. |                         Time|root.sg1.d1.s1|root.sg1.d1.s2|const(root.sg1.d1.s1, "value"="1024", "type"="INT64")|pi(root.sg1.d1.s2)|e(root.sg1.d1.s1, root.sg1.d1.s2)|
    4. +-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
    5. |1970-01-01T08:00:00.000+08:00|           0.0|           0.0|                                                 1024| 3.141592653589793|                2.718281828459045|
    6. |1970-01-01T08:00:00.001+08:00|           1.0|          null|                                                 1024|              null|                2.718281828459045|
    7. |1970-01-01T08:00:00.002+08:00|           2.0|          null|                                                 1024|              null|                2.718281828459045|
    8. |1970-01-01T08:00:00.003+08:00|          null|           3.0|                                                 null| 3.141592653589793|                2.718281828459045|
    9. |1970-01-01T08:00:00.004+08:00|          null|           4.0|                                                 null| 3.141592653589793|                2.718281828459045|
    10. +-----------------------------+--------------+--------------+-----------------------------------------------------+------------------+---------------------------------+
    11. Total line number = 5
    12. It costs 0.005s

    Data Type Conversion Function

    The IoTDB currently supports 6 data types, including INT32, INT64 ,FLOAT, DOUBLE, BOOLEAN, TEXT. When we query or evaluate data, we may need to convert data types, such as TEXT to INT32, or improve the accuracy of the data, such as FLOAT to DOUBLE. Therefore, IoTDB supports the use of cast functions to convert data types.

    Function NameRequired AttributesOutput Series Data TypeSeries Data Type Description
    CASTtype: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXTDetermined by the required attribute typeConverts data to the type specified by the type argument.

    Notes

    1. The value of type BOOLEAN is true, when data is converted to BOOLEAN if INT32 and INT64 are not 0, FLOAT and DOUBLE are not 0.0, TEXT is not empty string or “false”, otherwise .
    2. The value of type INT32, INT64, FLOAT, DOUBLE are 1 or 1.0 and TEXT is “true”, when BOOLEAN data is true, otherwise 0, 0.0 or “false”.
    3. When TEXT is converted to INT32, INT64, or FLOAT, the TEXT is first converted to DOUBLE and then to the corresponding type, which may cause loss of precision. It will skip directly if the data can not be converted.

    Syntax

    Example data:

    SQL:

    1. select cast(text, 'type'='BOOLEAN'), cast(text, 'type'='INT32'), cast(text, 'type'='INT64'), cast(text, 'type'='FLOAT'), cast(text, 'type'='DOUBLE') from root.test;

    Result:

    1. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
    2. |                         Time|cast(root.test.text, "type"="BOOLEAN")|cast(root.test.text, "type"="INT32")|cast(root.test.text, "type"="INT64")|cast(root.test.text, "type"="FLOAT")|cast(root.test.text, "type"="DOUBLE")|
    3. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
    4. |1970-01-01T08:00:00.001+08:00|                                  true|                                   1|                                   1|                                 1.1|                                  1.1|
    5. |1970-01-01T08:00:00.002+08:00|                                  true|                                   1|                                   1|                                 1.0|                                  1.0|
    6. |1970-01-01T08:00:00.003+08:00|                                  true|                                null|                                null|                                null|                                 null|
    7. |1970-01-01T08:00:00.004+08:00|                                 false|                                null|                                null|                                null|                                 null|
    8. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
    9. Total line number = 4
    10. It costs 0.078s

    Condition functions are used to check whether timeseries data points satisfy some specific condition.

    They return BOOLEANs.

    Currently, IoTDB supports the following condition functions:

    Example Data:

    1. IoTDB> select ts from root.test;
    2. +-----------------------------+------------+
    3. |                         Time|root.test.ts|
    4. +-----------------------------+------------+
    5. |1970-01-01T08:00:00.001+08:00|           1|
    6. |1970-01-01T08:00:00.002+08:00|           2|
    7. |1970-01-01T08:00:00.003+08:00|           3|
    8. |1970-01-01T08:00:00.004+08:00|           4|
    9. +-----------------------------+------------+
    Test 1

    SQL:

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

    Output:

    1. IoTDB> select ts, on_off(ts, 'threshold'='2') from root.test;
    2. +-----------------------------+------------+-------------------------------------+
    3. |                         Time|root.test.ts|on_off(root.test.ts, "threshold"="2")|
    4. +-----------------------------+------------+-------------------------------------+
    5. |1970-01-01T08:00:00.001+08:00|           1|                                false|
    6. |1970-01-01T08:00:00.002+08:00|           2|                                 true|
    7. |1970-01-01T08:00:00.003+08:00|           3|                                 true|
    8. |1970-01-01T08:00:00.004+08:00|           4|                                 true|
    9. +-----------------------------+------------+-------------------------------------+
    Test 2

    Sql:

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

    Output:

    1. IoTDB> select ts, in_range(ts,'lower'='2', 'upper'='3.1') from root.test;
    2. +-----------------------------+------------+--------------------------------------------------+
    3. |                         Time|root.test.ts|in_range(root.test.ts, "lower"="2", "upper"="3.1")|
    4. +-----------------------------+------------+--------------------------------------------------+
    5. |1970-01-01T08:00:00.001+08:00|           1|                                             false|
    6. |1970-01-01T08:00:00.002+08:00|           2|                                              true|
    7. |1970-01-01T08:00:00.003+08:00|           3|                                              true|
    8. |1970-01-01T08:00:00.004+08:00|           4|                                             false|
    9. +-----------------------------+------------+--------------------------------------------------+

    Continuous Interval Functions

    The continuous interval functions are used to query all continuous intervals that meet specified conditions. They can be divided into two categories according to return value:

    1. Returns the start timestamp and time span of the continuous interval that meets the conditions (a time span of 0 means that only the start time point meets the conditions)
    2. Returns the start timestamp of the continuous interval that meets the condition and the number of points in the interval (a number of 1 means that only the start time point meets the conditions)
    Function NameInput TSDatatypeParametersOutput TSDatatypeFunction Description
    ZERO_DURATIONINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 0L
    max:Optional with default value Long.MAX_VALUE    		LongReturn intervals’ start times and duration times in which the value is always 0(false), and the duration time t satisfy t >= min && t <= max. The unit of t is ms
    NON_ZERO_DURATIONINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 0L
    max:Optional with default value Long.MAX_VALUE    		LongReturn intervals’ start times and duration times in which the value is always not 0, and the duration time t satisfy t >= min && t <= max. The unit of t is ms
    ZERO_COUNTINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 1L
    max:Optional with default value Long.MAX_VALUE    		LongReturn intervals’ start times and the number of data points in the interval in which the value is always 0(false). Data points number n satisfy n >= min && n <= max
    NON_ZERO_COUNTINT32/ INT64/ FLOAT/ DOUBLE/ BOOLEANmin:Optional with default value 1L
    max:Optional with default value Long.MAX_VALUE    		LongReturn intervals’ start times and the number of data points in the interval in which the value is always not 0(false). Data points number n satisfy n >= min && n <= max
    Demonstrate

    Example data:

    1. IoTDB> select s1,s2,s3,s4,s5 from root.sg.d2;
    2. +-----------------------------+-------------+-------------+-------------+-------------+-------------+
    3. |                         Time|root.sg.d2.s1|root.sg.d2.s2|root.sg.d2.s3|root.sg.d2.s4|root.sg.d2.s5|
    4. +-----------------------------+-------------+-------------+-------------+-------------+-------------+
    5. |1970-01-01T08:00:00.000+08:00|            0|            0|          0.0|          0.0|        false|
    6. |1970-01-01T08:00:00.001+08:00|            1|            1|          1.0|          1.0|         true|
    7. |1970-01-01T08:00:00.002+08:00|            1|            1|          1.0|          1.0|         true|
    8. |1970-01-01T08:00:00.003+08:00|            0|            0|          0.0|          0.0|        false|
    9. |1970-01-01T08:00:00.004+08:00|            1|            1|          1.0|          1.0|         true|
    10. |1970-01-01T08:00:00.005+08:00|            0|            0|          0.0|          0.0|        false|
    11. |1970-01-01T08:00:00.006+08:00|            0|            0|          0.0|          0.0|        false|
    12. |1970-01-01T08:00:00.007+08:00|            1|            1|          1.0|          1.0|         true|
    13. +-----------------------------+-------------+-------------+-------------+-------------+-------------+

    Sql:

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

    Result:

    1. +-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+
    2. |                         Time|root.sg.d2.s1|zero_count(root.sg.d2.s1)|non_zero_count(root.sg.d2.s2)|zero_duration(root.sg.d2.s3)|non_zero_duration(root.sg.d2.s4)|
    3. +-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+
    4. |1970-01-01T08:00:00.000+08:00|            0|                        1|                         null|                           0|                            null|
    5. |1970-01-01T08:00:00.001+08:00|            1|                     null|                            2|                        null|                               1|
    6. |1970-01-01T08:00:00.002+08:00|            1|                     null|                         null|                        null|                            null|
    7. |1970-01-01T08:00:00.003+08:00|            0|                        1|                         null|                           0|                            null|
    8. |1970-01-01T08:00:00.004+08:00|            1|                     null|                            1|                        null|                               0|
    9. |1970-01-01T08:00:00.005+08:00|            0|                        2|                         null|                           1|                            null|
    10. |1970-01-01T08:00:00.006+08:00|            0|                     null|                         null|                        null|                            null|
    11. |1970-01-01T08:00:00.007+08:00|            1|                     null|                            1|                        null|                               0|
    12. +-----------------------------+-------------+-------------------------+-----------------------------+----------------------------+--------------------------------+

    User Defined Timeseries Generating Functions

    Please refer to UDF (User Defined Function).

    Known Implementation UDF Libraries:

    • , a UDF library about data quality, including data profiling, data quality evalution and data repairing, etc.

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

    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：

    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

    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.
    Note

    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.

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

    The result set is：

    Timetemperaturespeed
    ………