Variation Trend Calculation Functions

Currently, IoTDB supports the following variation trend calculation functions:

Function NameAllowed Input Series Data TypesOutput Series Data TypeDescription
TIME_DIFFERENCEINT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXTINT64Calculates the difference between the time stamp of a data point and the time stamp of the previous data point. There is no corresponding output for the first data point.
DIFFERENCEINT32 / INT64 / FLOAT / DOUBLESame type as the input seriesCalculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
NON_NEGATIVE_DIFFERENCEINT32 / INT64 / FLOAT / DOUBLESame type as the input seriesCalculates the absolute value of the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point.
DERIVATIVEINT32 / INT64 / FLOAT / DOUBLEDOUBLECalculates the rate of change of a data point compared to the previous data point, the result is equals to DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.
NON_NEGATIVE_DERIVATIVEINT32 / INT64 / FLOAT / DOUBLEDOUBLECalculates the absolute value of the rate of change of a data point compared to the previous data point, the result is equals to NON_NEGATIVE_DIFFERENCE / TIME_DIFFERENCE. There is no corresponding output for the first data point.

Example:

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

Result:

  1. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
  2. |                         Time|     root.sg1.d1.s1|time_difference(root.sg1.d1.s1)|difference(root.sg1.d1.s1)|non_negative_difference(root.sg1.d1.s1)|derivative(root.sg1.d1.s1)|non_negative_derivative(root.sg1.d1.s1)|
  3. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
  4. |2020-12-10T17:11:49.037+08:00|7360723084922759782|                              1|      -8431715764844238876|                    8431715764844238876|    -8.4317157648442388E18|                  8.4317157648442388E18|
  5. |2020-12-10T17:11:49.038+08:00|4377791063319964531|                              1|      -2982932021602795251|                    2982932021602795251|     -2.982932021602795E18|                   2.982932021602795E18|
  6. |2020-12-10T17:11:49.039+08:00|7972485567734642915|                              1|       3594694504414678384|                    3594694504414678384|     3.5946945044146785E18|                  3.5946945044146785E18|
  7. |2020-12-10T17:11:49.040+08:00|2508858212791964081|                              1|      -5463627354942678834|                    5463627354942678834|     -5.463627354942679E18|                   5.463627354942679E18|
  8. |2020-12-10T17:11:49.041+08:00|2817297431185141819|                              1|        308439218393177738|                     308439218393177738|     3.0843921839317773E17|                  3.0843921839317773E17|
  9. +-----------------------------+-------------------+-------------------------------+--------------------------+---------------------------------------+--------------------------+---------------------------------------+
  10. Total line number = 5
  11. It costs 0.014s
Function NameAllowed Input Series Data TypesAttributesOutput Series Data TypeDescription
DIFFINT32 / INT64 / FLOAT / DOUBLEignoreNull：optional，default is true. If is true, the previous data point is ignored when it is null and continues to find the first non-null value forwardly. If the value is false, previous data point is not ignored when it is null, the result is also null because null is used for subtractionDOUBLECalculates the difference between the value of a data point and the value of the previous data point. There is no corresponding output for the first data point, so output is null

Example

RawData

  1. +-----------------------------+------------+------------+
  2. |                         Time|root.test.s1|root.test.s2|
  3. +-----------------------------+------------+------------+
  4. |1970-01-01T08:00:00.001+08:00|           1|         1.0|
  5. |1970-01-01T08:00:00.002+08:00|           2|        null|
  6. |1970-01-01T08:00:00.003+08:00|        null|         3.0|
  7. |1970-01-01T08:00:00.004+08:00|           4|        null|
  8. |1970-01-01T08:00:00.005+08:00|           5|         5.0|
  9. |1970-01-01T08:00:00.006+08:00|        null|         6.0|
  10. +-----------------------------+------------+------------+

Not use ignoreNull attribute (Ignore Null)

SQL:

  1. SELECT DIFF(s1), DIFF(s2) from root.test;

Result:

  1. +-----------------------------+------------------+------------------+
  2. |                         Time|DIFF(root.test.s1)|DIFF(root.test.s2)|
  3. +-----------------------------+------------------+------------------+
  4. |1970-01-01T08:00:00.001+08:00|              null|              null|
  5. |1970-01-01T08:00:00.002+08:00|               1.0|              null|
  6. |1970-01-01T08:00:00.003+08:00|              null|               2.0|
  7. |1970-01-01T08:00:00.004+08:00|               2.0|              null|
  8. |1970-01-01T08:00:00.005+08:00|               1.0|               2.0|
  9. |1970-01-01T08:00:00.006+08:00|              null|               1.0|
  10. +-----------------------------+------------------+------------------+

Use ignoreNull attribute

SQL:

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

Result:

  1. +-----------------------------+----------------------------------------+----------------------------------------+
  2. |                         Time|DIFF(root.test.s1, "ignoreNull"="false")|DIFF(root.test.s2, "ignoreNull"="false")|
  3. +-----------------------------+----------------------------------------+----------------------------------------+
  4. |1970-01-01T08:00:00.001+08:00|                                    null|                                    null|
  5. |1970-01-01T08:00:00.002+08:00|                                     1.0|                                    null|
  6. |1970-01-01T08:00:00.003+08:00|                                    null|                                    null|
  7. |1970-01-01T08:00:00.004+08:00|                                    null|                                    null|
  8. |1970-01-01T08:00:00.005+08:00|                                     1.0|                                    null|
  9. |1970-01-01T08:00:00.006+08:00|                                    null|                                     1.0|
  10. +-----------------------------+----------------------------------------+----------------------------------------+