This topic describes the syntax and parameters of transformation functions. It also provides multiple examples.
ABS()
Returns the absolute values for field values.
Basic syntax
SELECT ABS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ABS(field_key)
: returns the absolute values for the field values of the specified field key.
ABS(*)
: returns the absolute values for the field values of each field key in the specified measurement.
The ABS()
function supports INT64 or FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, the basic syntax does not support the GROUP BY clauses that are used to group data by time.
Example 1: Calculate the absolute values for the field values of the specified field key
SELECT ABS("a") FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T12:05:00Z'
name: data
time abs
-------
15298416000000000001.33909108671076
15298416600000000000.774984088561186
15298417200000000000.921037167720451
15298417800000000001.73880754843378
15298418400000000000.905980032168252
15298419000000000000.891164752631417
This query returns the absolute values of the field values for the a
field key in thedata
measurement.
Example 2: Calculate the absolute values for the field values of each field key in the specified measurement
SELECT ABS(*) FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T12:05:00Z'
name: data
time abs_a abs_b
--------------
15298416000000000001.339091086710760.163643058925645
15298416600000000000.7749840885611860.137034364053949
15298417200000000000.9210371677204510.482943221384294
15298417800000000001.738807548433780.0729732928756677
15298418400000000000.9059800321682521.77857552719844
15298419000000000000.8911647526314170.741147445214238
This query returns the absolute values of the field values for each field key that stores numeric values in the data
measurement. The data
measurement contains the a
and b
fields.
Example 3: Calculate the absolute values for the field values of the specified field key by using a query that includes multiple clauses
SELECT ABS("a") FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T12:05:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2
name: data
time abs
-------
15298417800000000001.73880754843378
15298417200000000000.921037167720451
15298416600000000000.774984088561186
15298416000000000001.33909108671076
This query returns the absolute values of the field values for the a
field key in the data
measurement. The specified time range is from 2018-06-24T12:00:00Z
to 2018-06-24T12:05:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ABS(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The SIN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the absolute values of these results.
ABS() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the absolute values of the average field values for the specified field key
SELECT ABS(MEAN("a")) FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T13:00:00Z' GROUP BY time(12m)
time abs
-------
15298416000000000000.3960977256302787
15298423200000000000.0010541018316373302
15298430400000000000.04494733240283668
15298437600000000000.2553594777104415
15298444800000000000.20382988543108413
15298452000000000000.790836070736962
This query returns the absolute value of each average field value that is calculated based on a 12-minute interval for the a
field key.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the a
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function and the GROUP BY time()
clause, but does not use the ABS()
function.
SELECT MEAN("a") FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T13:00:00Z' GROUP BY time(12m)
name: data
time mean
--------
1529841600000000000-0.3960977256302787
15298423200000000000.0010541018316373302
15298430400000000000.04494733240283668
15298437600000000000.2553594777104415
15298444800000000000.20382988543108413
1529845200000000000-0.790836070736962
Then, TSDB for InfluxDB® calculates the absolute value of each average field value.
ACOS()
Returns the arc cosines for the field values. These arc cosines are returned in radians. The field values range from -1 to +1.
Basic syntax
SELECT ACOS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ACOS(field_key)
: returns the arc cosines of the field values for the specified field key.
ACOS(*)
: returns the arc cosines of the field values for each field key in the specified measurement.
The ACOS()
function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag, but does not support the GROUP BY clauses that are used to group data by time. For more information about how to use ACOS()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. Note: All the field values fall within the valid range for the field values of the ACOS()
function. The valid field values range from -1 to +1.
SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16
Example 1: Calculate the arc cosines of the field values for the specified field key
SELECT ACOS("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time acos
--------
2017-05-01T00:00:00Z0.591688642426544
2017-05-02T00:00:00Z1.266103672779499
2017-05-03T00:00:00Z0.5735131044230969
2017-05-04T00:00:00Z1.3489818562981022
2017-05-05T00:00:00Z1.399966657665792
2017-05-06T00:00:00Z0.6919551751263169
2017-05-07T00:00:00Z0.8762980611683406
2017-05-08T00:00:00Z0.7669940078618667
2017-05-09T00:00:00Z1.410105673842986
This query returns the arc cosines of the field values for the of_capacity
field key in the park_occupancy
measurement
Example 2: Calculate the arc cosines of the field values for each field key in the specified measurement
SELECT ACOS(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time acos_of_capacity
-----------------
2017-05-01T00:00:00Z0.591688642426544
2017-05-02T00:00:00Z1.266103672779499
2017-05-03T00:00:00Z0.5735131044230969
2017-05-04T00:00:00Z1.3489818562981022
2017-05-05T00:00:00Z1.399966657665792
2017-05-06T00:00:00Z0.6919551751263169
2017-05-07T00:00:00Z0.8762980611683406
2017-05-08T00:00:00Z0.7669940078618667
2017-05-09T00:00:00Z1.410105673842986
This query returns the arc cosines of the field values for each field key that stores numeric values in the park_occupancy
measurement. The park_occupancy
measurement contains only the numeric of_capacity
field.
Example 3: Calculate the arc cosines of the field values for the specified field key by using a query that includes multiple clauses
SELECT ACOS("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2
name: park_occupancy
time acos
--------
2017-05-07T00:00:00Z0.8762980611683406
2017-05-06T00:00:00Z0.6919551751263169
2017-05-05T00:00:00Z1.399966657665792
2017-05-04T00:00:00Z1.3489818562981022
This query returns the arc cosines of the field values for the of_capacity
field key in the park_occupancy
measurement. The specified time range is from 2017-05-01T00:00:00Z
to 2017-05-09T00:00:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ACOS(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The ACOS() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the arc cosines of these results.
ACOS() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the arc cosines of the average field values for the specified field key
SELECT ACOS(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time acos
--------
2017-04-30T00:00:00Z0.9703630732143733
2017-05-03T00:00:00Z1.1483422646081407
2017-05-06T00:00:00Z0.7812981174487247
2017-05-09T00:00:00Z1.410105673842986
This query returns the arc cosines of the average field values for the of_capacity
field key. The average field values are calculated based on a 3-day interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the of_capacity
field key based on a 3-day interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the ACOS()
function.
SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16
Then, TSDB for InfluxDB® calculates the arc cosine of each average field value.
ASIN()
Returns the arcsines of the field values for the specified field keys. The arcsines are returned in radians. The field values range from -1 to +1.
Basic syntax
SELECT ASIN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ASIN(field_key)
: returns the arcsines of the field values for the specified field key.
ASIN(*)
: returns the arcsines of the field values for each field key in the specified measurement.
The ASIN()
function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use ASIN()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. All the field values fall within the valid range for the field values of the ASIN()
function. The valid field values range from -1 to +1.
> SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16
Example 1: Calculate the arcsines of the field values for the specified field key
SELECT ASIN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time asin
--------
2017-05-01T00:00:00Z0.9791076843683526
2017-05-02T00:00:00Z0.3046926540153975
2017-05-03T00:00:00Z0.9972832223717997
2017-05-04T00:00:00Z0.22181447049679442
2017-05-05T00:00:00Z0.1708296691291045
2017-05-06T00:00:00Z0.8788411516685797
2017-05-07T00:00:00Z0.6944982656265559
2017-05-08T00:00:00Z0.8038023189330299
2017-05-09T00:00:00Z0.1606906529519106
This query returns the arcsines of the field values for the of_capacity
field key in the park_occupancy
measurement.
Example 2: Calculate the arcsines of the field values for each field key in the specified measurement
SELECT ASIN(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time asin_of_capacity
-----------------
2017-05-01T00:00:00Z0.9791076843683526
2017-05-02T00:00:00Z0.3046926540153975
2017-05-03T00:00:00Z0.9972832223717997
2017-05-04T00:00:00Z0.22181447049679442
2017-05-05T00:00:00Z0.1708296691291045
2017-05-06T00:00:00Z0.8788411516685797
2017-05-07T00:00:00Z0.6944982656265559
2017-05-08T00:00:00Z0.8038023189330299
2017-05-09T00:00:00Z0.1606906529519106
This query returns the arcsines of the field values for each field key that stores numeric values in the park_occupancy
measurement. The park_occupancy
measurement contains only the numeric of_capacity
field.
Example 3: Calculate the arcsines of the field values for the specified field key by using a query that includes multiple clauses
SELECT ASIN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2
name: park_occupancy
time asin
--------
2017-05-07T00:00:00Z0.6944982656265559
2017-05-06T00:00:00Z0.8788411516685797
2017-05-05T00:00:00Z0.1708296691291045
2017-05-04T00:00:00Z0.22181447049679442
This query returns the arcsines of the field values for the of_capacity
field key in the park_occupancy
measurement. The specified time range is from 2017-05-01T00:00:00Z
to 2017-05-09T00:00:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ASIN(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The ASIN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the arcsines of these results.
ASIN() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the arcsines of the average field values for the specified field key
> SELECT ASIN(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time asin
--------
2017-04-30T00:00:00Z0.6004332535805232
2017-05-03T00:00:00Z0.42245406218675574
2017-05-06T00:00:00Z0.7894982093461719
2017-05-09T00:00:00Z0.1606906529519106
This query returns the arcsines of the average field values for the of_capacity
field key. The average field values are calculated based on a 3-day interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the of_capacity
field key based on a 3-day interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the ASIN()
function.
> SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16
Then, TSDB for InfluxDB® calculates the arcsine of each average field value.
ATAN()
Returns the arc tangents of the field values for the specified field keys. The arc tangents are returned in radians. The field values range from -1 to +1.
Basic syntax
SELECT ATAN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ATAN(field_key)
: returns the arc tangents of the field values for the specified field key.
ATAN(*)
: returns the arc tangents of the field values for each field key in the specified measurement.
The ATAN()
function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use ATAN()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. Note: All the field values fall within the valid range for the field values of the ATAN()
function. The valid field values range from -1 to 1.
> SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16
Example 1: Calculate the arc tangents for the field values of the specified field key
SELECT ATAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time atan
--------
2017-05-01T00:00:00Z0.6927678353971222
2017-05-02T00:00:00Z0.2914567944778671
2017-05-03T00:00:00Z0.6986598247214632
2017-05-04T00:00:00Z0.2165503049760893
2017-05-05T00:00:00Z0.16839015714752992
2017-05-06T00:00:00Z0.6561787179913948
2017-05-07T00:00:00Z0.5693131911006619
2017-05-08T00:00:00Z0.6240230529767568
2017-05-09T00:00:00Z0.1586552621864014
This query returns the arc tangents of the field values for the of_capacity
field key in the park_occupancy
measurement.
Example 2: Calculate the arc tangents of the field values for each field key in the specified measurement
SELECT ATAN(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'
name: park_occupancy
time atan_of_capacity
-----------------
2017-05-01T00:00:00Z0.6927678353971222
2017-05-02T00:00:00Z0.2914567944778671
2017-05-03T00:00:00Z0.6986598247214632
2017-05-04T00:00:00Z0.2165503049760893
2017-05-05T00:00:00Z0.16839015714752992
2017-05-06T00:00:00Z0.6561787179913948
2017-05-07T00:00:00Z0.5693131911006619
2017-05-08T00:00:00Z0.6240230529767568
2017-05-09T00:00:00Z0.1586552621864014
This query returns the arc tangents of the field values for each field key that stores numeric values in the park_occupancy
measurement. The park_occupancy
measurement contains only the numeric of_capacity
field.
Example 3: Calculate the arc tangents of the field values for the specified field key by using a query that includes multiple clauses
SELECT ATAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2
name: park_occupancy
time atan
--------
2017-05-07T00:00:00Z0.5693131911006619
2017-05-06T00:00:00Z0.6561787179913948
2017-05-05T00:00:00Z0.16839015714752992
2017-05-04T00:00:00Z0.2165503049760893
This query returns the arc tangents of the field values for the of_capacity
field key in the park_occupancy
measurement. The specified time range is from 2017-05-01T00:00:00Z
to 2017-05-09T00:00:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ATAN(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The ATAN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the arc tangents of these results.
ATAN() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the arc tangents of the average field values for the specified field key
> SELECT ATAN(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time atan
--------
2017-04-30T00:00:00Z0.5142865412694495
2017-05-03T00:00:00Z0.3890972310552784
2017-05-06T00:00:00Z0.6174058917515726
2017-05-09T00:00:00Z0.1586552621864014
This query returns the arc tangents of the average field values for the of_capacity
field. The average field values are calculated based on a 3-day interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the of_capacity
field key based on a 3-day interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the ATAN()
function.
> SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)
name: park_occupancy
time mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16
Then, TSDB for InfluxDB® calculates the arc tangent of each average field value.
ATAN2()
Returns the arc tangents of the values that are calculated based on the y/x
formula. The arc tangents are returned in radians.
Basic syntax
SELECT ATAN2([*|<field_key>| num ],[<field_key>| num ])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ATAN2(field_key_y, field_key_x)
: returns the arc tangents of the values that are calculated based on the formula: Field values of Field key y/Field values of Field key x.
ATAN2(*, field_key_x)<br />
: returns the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x.
The ATAN2()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use ATAN2()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following simulated flight data is used in the examples that are provided in this section:
> SELECT "altitude_ft","distance_ft" FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'
name: flight_data
time altitude_ft distance_ft
--------------------------
2018-05-16T12:01:00Z102650094
2018-05-16T12:02:00Z254953576
2018-05-16T12:03:00Z403355208
2018-05-16T12:04:00Z557958579
2018-05-16T12:05:00Z706561213
2018-05-16T12:06:00Z858964807
2018-05-16T12:07:00Z1018067707
2018-05-16T12:08:00Z1177769819
2018-05-16T12:09:00Z1332172452
2018-05-16T12:10:00Z1488575881
Example 1: Calculate the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x
> SELECT ATAN2("altitude_ft","distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'
name: flight_data
time atan2
---------
2018-05-16T12:01:00Z0.020478631571881498
2018-05-16T12:02:00Z0.04754142349303296
2018-05-16T12:03:00Z0.07292147724575364
2018-05-16T12:04:00Z0.09495251193874832
2018-05-16T12:05:00Z0.11490822875441563
2018-05-16T12:06:00Z0.13176409347584003
2018-05-16T12:07:00Z0.14923587589682233
2018-05-16T12:08:00Z0.1671059946640312
2018-05-16T12:09:00Z0.18182893717409565
2018-05-16T12:10:00Z0.1937028631495223
This query returns the arc tangents of the values that are calculated based on the formula: Field values of the altitude_ft
field key/Field values of the distance_ft
field key. The two field keys are contained in the flight_data
measurement.
Example 2: Calculate the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x
> SELECT ATAN2(*,"distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'
name: flight_data
time atan2_altitude_ft atan2_distance_ft
--------------------------------------
2018-05-16T12:01:00Z0.0204786315718814980.7853981633974483
2018-05-16T12:02:00Z0.047541423493032960.7853981633974483
2018-05-16T12:03:00Z0.072921477245753640.7853981633974483
2018-05-16T12:04:00Z0.094952511938748320.7853981633974483
2018-05-16T12:05:00Z0.114908228754415630.7853981633974483
2018-05-16T12:06:00Z0.131764093475840030.7853981633974483
2018-05-16T12:07:00Z0.149235875896822330.7853981633974483
2018-05-16T12:08:00Z0.16710599466403120.7853981633974483
2018-05-16T12:09:00Z0.181828937174095650.7853981633974483
2018-05-16T12:10:00Z0.193702863149522340.7853981633974483
This query returns the arc tangents of the values that are calculated based on the formula: Field values of each field key that stores numeric values in the flight_data
measurement/Field values of the distance_ft
field key. The flight_data
measurement contains the numeric altitude_ft
and distance_ft
fields.
Example 3: Calculate the arc tangents of the values by using a query that includes multiple clauses
> SELECT ATAN2("altitude_ft","distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2
name: flight_data
time atan2
---------
2018-05-16T12:08:00Z0.1671059946640312
2018-05-16T12:07:00Z0.14923587589682233
2018-05-16T12:06:00Z0.13176409347584003
2018-05-16T12:05:00Z0.11490822875441563
This query returns the arc tangents of the values that are calculated based on the formula: Field values of the altitude_ft
field key/Field values of the distance_ft
field key. The specified time range is from 2018-05-16T12:10:00Z
to 2018-05-16T12:10:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ATAN2(<function()>,<function()>)[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The ATAN2() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the arc tangents (ATAN2()
) of these results.
ATAN2() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the arc tangents of the values that are calculated based on the formula: Average field values of Field key y/Average field values of Field key x
> SELECT ATAN2(MEAN("altitude_ft"), MEAN("distance_ft")) FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T13:01:00Z' GROUP BY time(12m)
name: flight_data
time atan2
---------
2018-05-16T12:00:00Z0.133815587896842
2018-05-16T12:12:00Z0.2662716308351908
2018-05-16T12:24:00Z0.2958845306108965
2018-05-16T12:36:00Z0.23783439588429497
2018-05-16T12:48:00Z0.1906803720242831
2018-05-16T13:00:00Z0.17291511946158172
This query returns the arc tangents of the values that are calculated based on the formula: Average field values of the altitude_ft
field key/Average field values of the distance_ft
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the altitude_ft
and distance_ft
field keys based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the ATAN2()
function.
> SELECT MEAN("altitude_ft"), MEAN("distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T13:01:00Z' GROUP BY time(12m)
name: flight_data
time mean mean_1
--------------
2018-05-16T12:00:00Z867464433.181818181816
2018-05-16T12:12:00Z26419.83333333333296865.25
2018-05-16T12:24:00Z40337.416666666664132326.41666666666
2018-05-16T12:36:00Z41149.583333333336169743.16666666666
2018-05-16T12:48:00Z41230.416666666664213600.91666666666
2018-05-16T13:00:00Z41184.5235799
Then, TSDB for InfluxDB® calculates the arc tangent of each average field value.
CEIL()
Rounds each specified field value up to the nearest integer.
Basic syntax
SELECT CEIL([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
CEIL(field_key)
: rounds each field value of the specified field key up to the nearest integer.
CEIL(*)
: rounds each field value of all the field keys in the specified measurement up to the nearest integer.
The CEIL()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use CEIL()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Round each field value of the specified field key up to the nearest integer
> SELECT CEIL("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time ceil
--------
2015-08-18T00:00:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:18:00Z3
2015-08-18T00:24:00Z3
2015-08-18T00:30:00Z3
The CEIL() function in this query rounds each field value of the water_level
field key in the h2o_feet
measurement up to the nearest integer.
Example 2: Round each field value of all the field keys in the specified measurement up to the nearest integer
> SELECT CEIL(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time ceil_water_level
--------------------
2015-08-18T00:00:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:18:00Z3
2015-08-18T00:24:00Z3
2015-08-18T00:30:00Z3
The CEIL() function in this query rounds each field value of all the specified field keys up to the nearest integer. The specified field keys store numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the water_level
field.
Example 3: Round each field value of the specified field key up to the nearest integer by using a query that includes multiple clauses
> SELECT CEIL("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time ceil
--------
2015-08-18T00:18:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:00:00Z3
The CEIL() function in this query rounds each field value of the water_level
field key up to the nearest integer. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT CEIL(<function>([*|<field_key>|/<regular_expression>/]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The CEIL() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries apply the CEIL()
function to these results.
CEIL() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Round each average field value up to the nearest integer
> SELECT CEIL(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time ceil
--------
2015-08-18T00:00:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:24:00Z3
The CEIL() function in this query rounds each average field value of the water_level
field key up to the nearest integer. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the CEIL()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® rounds each average field value up to the nearest integer.
COS()
Returns the cosines of the field values for the specified field keys.
Basic syntax
SELECT COS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
COS(field_key)
: returns the cosines of the field values for the specified field key.
COS(*)
: returns the cosines of the field values for each field key in the specified measurement.
The COS()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use COS()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the cosines of the field values for the specified field key
> SELECT COS("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cos
-------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262
This query returns the cosines of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the cosines of the field values for each field key in the specified measurement
> SELECT COS(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cos_water_level
-------------------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262
This query returns the cosines of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
COS()
Returns the cosines of the field values for the specified field keys.
Basic syntax
SELECT COS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
COS(field_key)
: returns the cosines of the field values for the specified field key.
COS(*)
: returns the cosines of the field values for each field key in the specified measurement.
The COS()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use COS()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the cosines of the field values for the specified field key
> SELECT COS("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cos
-------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262
This query returns the cosines of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the cosines of the field values for each field key in the specified measurement
> SELECT COS(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cos_water_level
-------------------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262
This query returns the cosines of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Advanced syntax
SELECT COS(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The COS() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the cosines of these results.
COS() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the cosines of the average field values for the specified field key
> SELECT COS(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time cos
-------
2015-08-18T00:00:00Z-0.49618891270599885
2015-08-18T00:12:00Z-0.4848605136571181
2015-08-18T00:24:00Z-0.4575195627907578
This query returns the cosines of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the COS()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the cosine of each average field value.
CUMULATIVE_SUM()
Returns the cumulative sum of the field values for each specified field key.
Basic syntax
SELECT CUMULATIVE_SUM([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
CUMULATIVE_SUM(field_key)
: returns the cumulative sum of the field values for the specified field key.
CUMULATIVE_SUM(/regular_expression/)
: returns the cumulative sum of the field values for each field key that matches the specified regular expression.
CUMULATIVE_SUM(*)
: returns the cumulative sum of the field values for each field key in the specified measurement.
The CUMULATIVE_SUM()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use CUMULATIVE_SUM()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
data set is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the cumulative sum of the field values for the specified field key
> SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cumulative_sum
------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426
This query returns the cumulative sum of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the cumulative sum of the field values for each field key in the specified measurement
> SELECT CUMULATIVE_SUM(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cumulative_sum_water_level
------------------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426
This query returns the cumulative sum of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the cumulative sum of field values for each field key that matches the specified regular expression
> SELECT CUMULATIVE_SUM(/water/) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time cumulative_sum_water_level
------------------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426
This query returns the cumulative sum of the field values for each field key that contains water
and stores numeric values in the h2o_feet
measurement.
Example 4: Calculate the cumulative sum of the field values for the specified field key by using a query that includes multiple clauses
> SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time cumulative_sum
------------------
2015-08-18T00:18:00Z6.218
2015-08-18T00:12:00Z8.246
2015-08-18T00:06:00Z10.362
2015-08-18T00:00:00Z12.426
This query returns the cumulative sum of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2. This indicates that the first two points are not returned.
Advanced syntax
SELECT CUMULATIVE_SUM(<function>([*|<field_key>|/<regular_expression>/]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The CUMULATIVE_SUM() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the cumulative sum of these results.
CUMULATIVE_SUM() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the cumulative sum of the average field values for the specified field key
> SELECT CUMULATIVE_SUM(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time cumulative_sum
------------------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z4.167
2015-08-18T00:24:00Z6.213
This query returns the cumulative sum of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the CUMULATIVE_SUM()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the cumulative sum of the average field values. In the query results, the second point 4.167
is the sum of 2.09
and 2.077
, and the third point 6.213
is the sum of 2.09
, 2.077
, and 2.0460000000000003
.
DERIVATIVE()
Returns the change rates or the derivatives for the field values of the specified field keys.
Basic syntax
SELECT DERIVATIVE([*|<field_key>|/<regular_expression>/][,<unit>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
TSDB for InfluxDB® calculates the difference between two field values and converts the results into change rates. The change rates indicate how much the field values have changed based on the time granularity that is specified by the unit
parameter. The value of the unit
parameter is an integer that is suffixed by a unit of time. This parameter is optional. If you do not specify the unit
parameter in your query, the unit
parameter is set to 1s
, which is the default value.
DERIVATIVE(field_key)
: returns the change rates of the field values for the specified field key.
DERIVATIVE(/regular_expression/)
: returns the change rates of the field values for each field key that matches the specified regular expression.
DERIVATIVE(*)
: returns the change rates of the field values for each field key in the specified measurement.
The DERIVATIVE()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use DERIVATIVE()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
data set is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the derivatives between the field values of the specified field key
> SELECT DERIVATIVE("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time derivative
--------------
2015-08-18T00:06:00Z0.00014444444444444457
2015-08-18T00:12:00Z-0.00024444444444444465
2015-08-18T00:18:00Z0.0002722222222222218
2015-08-18T00:24:00Z-0.000236111111111111
2015-08-18T00:30:00Z2.777777777777842e-05
This query returns the change rates of the field values for the water_level
field key in the h2o_feet
measurement. The change rates indicate how much the field values have changed every second.
The first result 0.00014444444444444457
is the one-second rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 1-second rate of change.
(2.116-2.064)/(360s/1s)
------------------------
||
| the difference between the field values' timestamps / the default unit
second field value - first field value
Example 2: Calculate the derivatives between the field values of the specified field key and specify the unit parameter
> SELECT DERIVATIVE("water_level",6m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time derivative
--------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231
This query returns the change rates of the field values for the water_level
field key in the h2o_feet
measurement. The change rates indicate how much the field values have changed every 6 minutes.
The first result 0.052000000000000046
is the 6-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 6-minute rate of change.
(2.116-2.064)/(6m/6m)
------------------------
||
| the difference between the field values' timestamps / the specified unit
second field value - first field value
Example 3: Calculate the derivatives between the field values of each field key in the specified measurement and specify the unit parameter
> SELECT DERIVATIVE(*,3m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time derivative_water_level
--------------------------
2015-08-18T00:06:00Z0.026000000000000023
2015-08-18T00:12:00Z-0.04400000000000004
2015-08-18T00:18:00Z0.04899999999999993
2015-08-18T00:24:00Z-0.04249999999999998
2015-08-18T00:30:00Z0.0050000000000001155
This query returns the change rates of the field values for each field key that stores numeric values in the h2o_feet
measurement. The change rates indicate how much the field values have changed every 3 minutes. The h2o_feet
measurement contains only the numeric water_level
field.
The first result 0.026000000000000023
is the 3-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 3-minute rate of change.
(2.116-2.064)/(6m/3m)
------------------------
||
| the difference between the field values' timestamps / the specified unit
second field value - first field value
Example 4: Calculate the derivatives between the field values of each field key that matches the specified regular expression and specify the unit parameter
> SELECT DERIVATIVE(/water/,2m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time derivative_water_level
--------------------------
2015-08-18T00:06:00Z0.01733333333333335
2015-08-18T00:12:00Z-0.02933333333333336
2015-08-18T00:18:00Z0.03266666666666662
2015-08-18T00:24:00Z-0.02833333333333332
2015-08-18T00:30:00Z0.0033333333333334103
This query returns the change rates of the field values for each field key that contains water
and stores numeric values in the h2o_feet
measurement. The change rates indicate how much the field values have changed every 2 minutes. The h2o_feet
measurement contains only the numeric water_level
field.
The first result 0.01733333333333335
is the 2-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 2-minute rate of change.
(2.116-2.064)/(6m/2m)
------------------------
||
| the difference between the field values' timestamps / the specified unit
second field value - first field value
Example 5: Calculate the derivative between the field values of the specified field key by using a query that includes multiple clauses
> SELECT DERIVATIVE("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' ORDER BY time DESC LIMIT 1 OFFSET 2
name: h2o_feet
time derivative
--------------
2015-08-18T00:12:00Z-0.0002722222222222218
This query returns the change rate of the field values for the water_level
field key in the h2o_feet
measurement. The change rate indicates how much the field values have changed within 1 second. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 1. The point offset is set to 2. This indicates that the first two points are not returned.
The only result -0.0002722222222222218
is the 1-second rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 1-second rate of change.
(2.126-2.028)/(360s/1s)
------------------------
||
| the difference between the field values' timestamps / the default unit
second field value - first field value
Advanced syntax
SELECT DERIVATIVE(<function>([*|<field_key>|/<regular_expression>/])[,<unit>])[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The DERIVATIVE() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries are used to calculate the derivatives of these results.
The value of the unit
parameter is an integer that is suffixed by a unit of time. This parameter is optional. You do not need to specify the unit
parameter. By default, the unit
parameter is set to the time interval that is specified by the GROUP BY time()
clause. Note: The default value of the unit
parameter in the advanced syntax is different from that of the unit
parameter in the basic syntax.
DERIVATIVE() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Examples
Example 1: Calculate the derivatives between average field values
> SELECT DERIVATIVE(MEAN("water_level")) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time derivative
--------------
2015-08-18T00:12:00Z-0.0129999999999999
2015-08-18T00:24:00Z-0.030999999999999694
This query returns the change rates of the average field values that are calculated based on a 12-minute interval for the water_level
field key. The change rates indicate how much the field values have changed every 12 minutes.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the DERIVATIVE()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how much the field values have changed every 12 minutes. The first result -0.0129999999999999
is the 12-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 12-minute rate of change
(2.077-2.09)/(12m/12m)
-----------------------
||
| the difference between the field values' timestamps / the default unit
second field value - first field value
Example 2: Calculate the derivatives between average field values and specify the unit parameter
> SELECT DERIVATIVE(MEAN("water_level"),6m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time derivative
--------------
2015-08-18T00:12:00Z-0.00649999999999995
2015-08-18T00:24:00Z-0.015499999999999847
This query returns the change rates of the average field values that are calculated based on a 12-minute interval for the water_level
field key. The change rates indicate how the field values have changed every 6 minutes.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the DERIVATIVE()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how much the field values have changed every 6 minutes. The first result -0.00649999999999995
is the 6-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 6-minute rate of change.
(2.077-2.09)/(12m/6m)
-----------------------
||
| the difference between the field values' timestamps / the specified unit
second field value - first field value
DIFFERENCE()
Returns the difference between field values.
Basic syntax
SELECT DIFFERENCE([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
DIFFERENCE(field_key)
: returns the difference between the field values of the specified field key.
DIFFERENCE(/regular_expression/)
: returns the difference between the field values of each field key that matches the specified regular expression.
DIFFERENCE(*)
: returns the difference between the field values of each field key in the specified measurement.
The DIFFERENCE()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag, but does not support the GROUP BY clauses that are used to group data by time. For more information about how to use DIFFERENCE()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the difference between the field values of the specified field key
> SELECT DIFFERENCE("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time difference
--------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231
This query returns the difference between the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the difference between the field values of each field key in the specified measurement
> SELECT DIFFERENCE(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time difference_water_level
--------------------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231
This query returns the difference between the field values of each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the difference between the field values of each field key that matches the specified regular expression
> SELECT DIFFERENCE(/water/) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time difference_water_level
--------------------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231
This query returns the difference between the field values of each field key that contains water
and stores numeric values in the h2o_feet
measurement.
Example 4: Calculate the difference between the field values of the specified field key by using a query that includes multiple clauses
> SELECT DIFFERENCE("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 2 OFFSET 2
name: h2o_feet
time difference
--------------
2015-08-18T00:12:00Z-0.09799999999999986
2015-08-18T00:06:00Z0.08800000000000008
This query returns the difference between the field values of the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 2. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT DIFFERENCE(<function>([*|<field_key>|/<regular_expression>/]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The DIFFERENCE() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the difference between the results.
DIFFERENCE() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the difference between the maximum values
> SELECT DIFFERENCE(MAX("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time difference
--------------
2015-08-18T00:12:00Z0.009999999999999787
2015-08-18T00:24:00Z-0.07499999999999973
This query returns the difference between the maximum values that are calculated based on a 12-minute interval for the water_level
field key.
To obtain the results, TSDB for InfluxDB® first calculates the largest field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MAX()
function in combination with the GROUP BY time()
clause, and does not use the DIFFERENCE()
function.
> SELECT MAX("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time max
-------
2015-08-18T00:00:00Z2.116
2015-08-18T00:12:00Z2.126
2015-08-18T00:24:00Z2.051
Then, TSDB for InfluxDB® calculates the difference between these maximum values. In the query results, the first point 0.009999999999999787
is the difference between 2.126
and 2.116
, and the second point -0.07499999999999973
is the difference between 2.051
and 2.126
.
ELAPSED()
Returns the difference between the timestamps of the field values for the specified field keys.
Syntax
SELECT ELAPSED([*|<field_key>|/<regular_expression>/][,<unit>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
TSDB for InfluxDB® calculates the difference between timestamps. The value of the unit
parameter is an integer that is suffixed by a unit of time. The difference between timestamps is measured in the time unit. This parameter is optional. If you do not specify the unit
parameter in queries, the difference between timestamps is measured in nanoseconds.
ELAPSED(field_key)
: returns the difference between the timestamps of the field values for the specified field key.
ELAPSED(/regular_expression/)
: returns the difference between the timestamps of the field values for each field key that matches the specified regular expression.
ELAPSED(*)
: returns the difference between the timestamps of the field values for each field key in the specified measurement.
The ELAPSED()
function supports field values of all data types.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
Example 1: Calculate the time interval between the field values of the specified field key
> SELECT ELAPSED("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time elapsed
-----------
2015-08-18T00:06:00Z360000000000
2015-08-18T00:12:00Z360000000000
This query returns the difference between the timestamps of the field values for the water_level
field key in the h2o_feet
measurement. The difference between the timestamps is measured in nanoseconds.
Example 2: Calculate the time interval between the field values of the specified field key and specify the unit parameter
> SELECT ELAPSED("water_level",1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time elapsed
-----------
2015-08-18T00:06:00Z6
2015-08-18T00:12:00Z6
This query returns the difference between the timestamps of the field values of the water_level
field key in the h2o_feet
measurement. The difference between the timestamps is measured in minutes.
Example 3: Calculate the time interval between the field values of each field key in the specified measurement and specify the unit parameter
> SELECT ELAPSED(*,1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time elapsed_level description elapsed_water_level
------------------------------------------------
2015-08-18T00:06:00Z66
2015-08-18T00:12:00Z66
This query returns the difference between the timestamps of the field values of each field key in the h2o_feet
measurement. The difference between the timestamps is measured in minutes. The h2o_feet
measurement contains the level description
and water_level
field keys.
Example 4: Calculate the time interval between the field values of each field key that matches the specified regular expression, and specify the unit parameter.
> SELECT ELAPSED(/level/,1s) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time elapsed_level description elapsed_water_level
------------------------------------------------
2015-08-18T00:06:00Z360360
2015-08-18T00:12:00Z360360
This query returns the difference between the timestamps of the field values for each field key that contains level
in the h2o_feet
measurement. The difference between the timestamps is measured in seconds.
Example 5: Calculate the time interval between the field values of the specified field key by using a query that includes multiple clauses
> SELECT ELAPSED("water_level",1ms) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z' ORDER BY time DESC LIMIT 1 OFFSET 1
name: h2o_feet
time elapsed
-----------
2015-08-18T00:00:00Z-360000
This query returns the difference between the timestamps of the field values for the water_level
field key in the h2o_feet
measurement. The difference between the timestamps is measured in milliseconds. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:12:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 1. The point offset is set to 1, which indicates that the first point is not returned.
The query returns a negative value. This occurs when the ORDER BY time DESC
clause sorts the timestamps in descending order. Therefore, the ELAPSED()
function calculates the difference between the timestamps that are sorted in reverse order.
FAQ
What are the impacts if the value of the unit parameter in the ELAPSED() function is greater than the difference between the timestamps?
If the value of the unit
parameter is greater than the difference between the timestamps, TSDB for InfluxDB® returns 0
.
Example: One point is generated for the h2o_feet
measurement every 6 minutes. If you set the unit
parameter in queries to 1 hour, TSDB for InfluxDB® returns0
.
> SELECT ELAPSED("water_level",1h) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:12:00Z'
name: h2o_feet
time elapsed
-----------
2015-08-18T00:06:00Z0
2015-08-18T00:12:00Z0
What are the impacts if ELAPSED() functions are used in combination with GROUP BY time() clauses in queries?
ELAPSED()
functions can be used in combination with GROUP BY time()
clauses in queries. However, the query results are used for reference only. If an ELAPSED()
query contains a nested InfluxQL function and a GROUP BY time()
clause, the query returns only the time interval that is specified by the GROUP BY time()
clause.
The GROUP BY time()
clause determines the timestamps that are returned in the query results. Each timestamp indicates the start time of each time interval. This rule also applies to nested selector functions, such as FIRST()
and MAX()
. If the GROUP BY time() clause is not used, these functions return the original timestamps of raw data. The GROUP BY time()
clause overwrites the original timestamps. Therefore, the ELAPSED()
function always returns the same value as the time interval that is specified by the GROUP BY time()
clause.
Example: The first query in the following code uses the ELAPSED()
function and the GROUP BY time()
clause. This query returns the difference between the timestamps of the smallest field values for the water_level
field key. The difference is measured in minutes. In the query results, the difference between the timestamps for each of the two time intervals is 12 minutes.
To obtain the results, TSDB for InfluxDB® first calculates the minimum field values of the water_level
field key based on a 12-minute interval. The second query in the following code returns the minimum value for each time interval. This process returns the same result as a query if the query uses the MIN()
function in combination with the GROUP BY time()
clause, and does not use the ELAPSED()
function. Note: The difference between the timestamps returned in the second query is 12 minutes. In raw data, the first result 2.057
occurs at 2015-08-18T00:42:00Z
. However, this original timestamp is overwritten by the GROUP BY time()
clause. The returned timestamps are determined by the time interval that is specified by the GROUP BY time()
clause rather than the original timestamps in raw data. Therefore, the ELAPSED()
function always returns the difference between the timestamps as the same value as the time interval that is specified by the GROUP BY time() clause.
> SELECT ELAPSED(MIN("water_level"),1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:36:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m)
name: h2o_feet
time elapsed
-----------
2015-08-18T00:36:00Z12
2015-08-18T00:48:00Z12
> SELECT MIN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:36:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m)
name: h2o_feet
time min
-------
2015-08-18T00:36:00Z2.057<---Actually occurs at 2015-08-18T00:42:00Z
2015-08-18T00:48:00Z1.991
EXP()
Returns the exponential of a field value.
Basic syntax
SELECT EXP([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
EXP(field_key)
: returns the exponential of the field values for the specified field key.
EXP(*)
: returns the exponentials of the field values for each field key in the specified measurement.
The EXP()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use EXP()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the exponential of the field values for the specified field key
> SELECT EXP("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time exp
-------
2015-08-18T00:00:00Z7.877416541092307
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:24:00Z7.6983036546645645
2015-08-18T00:30:00Z7.775672892658607
This query returns the exponential of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the exponential of the field values for each field key in the specified measurement
> SELECT EXP(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time exp_water_level
-------------------
2015-08-18T00:00:00Z7.877416541092307
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:24:00Z7.6983036546645645
2015-08-18T00:30:00Z7.775672892658607
This query returns the exponential of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the exponential of the field values for the specified field key by using a query that includes multiple clauses
> SELECT EXP("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time exp
-------
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:00:00Z7.877416541092307
This query returns the exponential of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT EXP(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The EXP() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the exponentials of these results.
EXP() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the exponential of the average field values for the specified field key
> SELECT EXP(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time exp
-------
2015-08-18T00:00:00Z8.084915164305059
2015-08-18T00:12:00Z7.980491491670466
2015-08-18T00:24:00Z7.736891562315577
This query raises e to the power of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the EXP()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the exponential of each average field value.
FLOOR()
Rounds each specified field value down to the nearest integer.
Basic syntax
SELECT FLOOR([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
FLOOR(field_key)
: rounds each field value of the specified field key down to the nearest integer.
FLOOR(*)
: rounds each field value of all the field keys in the specified measurement down to the nearest integer.
The FLOOR()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use FLOOR()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Round each field value of the specified field key down to the nearest integer
> SELECT FLOOR("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time floor
---------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2
The FLOOR() function in this query rounds each field value of the water_level
field key in the h2o_feet
measurement down to the nearest integer.
Example 2: Round each field value of all the field keys in the specified measurement down to the nearest integer
> SELECT FLOOR(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time floor_water_level
---------------------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2
The FLOOR() function in this query rounds each field value of the specified field keys down to the nearest integer. The specified field keys store numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Round each field value of the specified field key down to the nearest integer by using a query that includes multiple clauses
> SELECT FLOOR("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time floor
---------
2015-08-18T00:18:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:00:00Z2
The FLOOR() function in this query rounds each field value of the water_level
field key down to the nearest integer. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT FLOOR(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The FLOOR() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries apply the FLOOR()
function to these results.
FLOOR() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Round each average field value down to the nearest integer
> SELECT FLOOR(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time floor
---------
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2
The FLOOR() function in this query rounds each average field value of the water_level
field key down to the nearest integer. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the FLOOR()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® rounds each average field value down to the nearest integer.
LN()
Returns the natural logarithms of the field values for the specified field keys.
Basic syntax
SELECT LN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
LN(field_key)
: returns the natural logarithms of the field values for the specified field key.
LN(*)
: returns the natural logarithms of the field values for each field key in the specified measurement.
The LN()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use LN()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples of basic syntax
The following sample data is used in the examples provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the natural logarithms of the field values for the specified field key
> SELECT LN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time ln
------
2015-08-18T00:00:00Z0.7246458476193163
2015-08-18T00:06:00Z0.749527513996053
2015-08-18T00:12:00Z0.7070500857289368
2015-08-18T00:18:00Z0.7542422799197561
2015-08-18T00:24:00Z0.7134398838277077
2015-08-18T00:30:00Z0.7183274790902436
This query returns the natural logarithms of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the natural logarithms of the field values for each field key in the specified measurement
> SELECT LN(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time ln_water_level
------------------
2015-08-18T00:00:00Z0.7246458476193163
2015-08-18T00:06:00Z0.749527513996053
2015-08-18T00:12:00Z0.7070500857289368
2015-08-18T00:18:00Z0.7542422799197561
2015-08-18T00:24:00Z0.7134398838277077
2015-08-18T00:30:00Z0.7183274790902436
This query returns the natural logarithms of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the natural logarithms of the field values for the specified field key by using a query that includes multiple clauses
> SELECT EXP("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time exp
-------
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:00:00Z7.877416541092307
This query returns the natural logarithms of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT LN(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The LN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the natural logarithms of these results.
LN() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the natural logarithms of the average field values for the specified field key
> SELECT LN(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time ln
------
2015-08-18T00:00:00Z0.7371640659767196
2015-08-18T00:12:00Z0.7309245448939752
2015-08-18T00:24:00Z0.7158866675294349
This query returns the natural logarithms of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the LN()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the natural logarithms of each average field value.
LOG()
Returns the base-b
logarithms of the field values for the specified field keys.
Basic syntax
SELECT LOG([*|<field_key>],<b>)[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
LOG(field_key, b)
: returns the base-b
logarithms of the field values for the specified field key.
LOG(*, b)
: returns the base-b
logarithms of the field values for each field key in the specified measurement.
The LOG()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use LOG()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the base-4 logarithms of the field values for the specified field key
> SELECT LOG("water_level",4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log
-------
2015-08-18T00:00:00Z0.5227214853805835
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:24:00Z0.5146380911853161
2015-08-18T00:30:00Z0.5181637459088826
This query returns the base-4 logarithms of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the base-4 logarithms of the field values for each field key in the specified measurement
> SELECT LOG(*,4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log_water_level
-------------------
2015-08-18T00:00:00Z0.5227214853805835
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:24:00Z0.5146380911853161
2015-08-18T00:30:00Z0.5181637459088826
This query returns the base-4 logarithms of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the base-4 logarithms of the field values for the specified field key by using a query that includes multiple clauses
> SELECT LOG("water_level",4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time log
-------
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:00:00Z0.5227214853805835
This query returns the base-4 logarithms of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT LOG(<function>([*|<field_key>]),<b>)[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The LOG() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the logarithms of these results.
LOG() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the base-4 logarithms of the average field values for the specified field key
> SELECT LOG(MEAN("water_level"),4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time log
-------
2015-08-18T00:00:00Z0.531751471153079
2015-08-18T00:12:00Z0.5272506080912802
2015-08-18T00:24:00Z0.5164030725416209
This query returns the base-4 logarithms of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the LOG()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the base-4 logarithm of each average field value.
LOG2()
Returns the base-2 logarithms of the field values for the specified field keys.
Basic syntax
SELECT LOG2([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
LOG2(field_key)
: returns the base-2 logarithms of the field values for the specified field key.
LOG2(*)
: returns the base-2 logarithms of the field values for each field key in the specified measurement.
The LOG2()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use LOG2()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the base-2 logarithms of the field values for the specified field key
> SELECT LOG2("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log2
--------
2015-08-18T00:00:00Z1.045442970761167
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:24:00Z1.0292761823706322
2015-08-18T00:30:00Z1.0363274918177652
This query returns the base-2 logarithms of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the base-2 logarithms of the field values for each field key in the specified measurement
> SELECT LOG2(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log2_water_level
--------------------
2015-08-18T00:00:00Z1.045442970761167
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:24:00Z1.0292761823706322
2015-08-18T00:30:00Z1.0363274918177652
This query returns the base-2 logarithms of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the base-2 logarithms of the field values for the specified field key by using a query that includes multiple clauses
> SELECT LOG2("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time log2
--------
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:00:00Z1.045442970761167
This query returns the base-2 logarithms of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT LOG2(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The LOG2() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the base-2 logarithms of these results.
LOG2() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the base-2 logarithms of the average field values for the specified field key
> SELECT LOG2(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time log2
--------
2015-08-18T00:00:00Z1.063502942306158
2015-08-18T00:12:00Z1.0545012161825604
2015-08-18T00:24:00Z1.0328061450832418
This query returns the base-2 logarithms of the average field values that are calculated based on a 12-minute interval for the water_level
field key.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the LOG2()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the base-2 logarithms of each average field value.
LOG10()
Returns the base-10 logarithms of the field values for the specified field keys.
Basic syntax
SELECT LOG10([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
LOG10(field_key)
: returns the base-10 logarithms of the field values for the specified field key.
LOG10(*)
: returns the base-10 logarithms of the field values for each field key in the specified measurement.
The LOG10()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use LOG10()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the base-10 logarithms of the field values for the specified field key
> SELECT LOG10("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log10
---------
2015-08-18T00:00:00Z0.3147096929551737
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:24:00Z0.3098430047160705
2015-08-18T00:30:00Z0.3119656603683663
This query returns the base-10 logarithms of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the base-10 logarithms of the field values for each field key in the specified measurement
> SELECT LOG10(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time log10_water_level
---------------------
2015-08-18T00:00:00Z0.3147096929551737
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:24:00Z0.3098430047160705
2015-08-18T00:30:00Z0.3119656603683663
This query returns the base-10 logarithms of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the base-10 logarithms of the field values for the specified field key by using a query that includes multiple clauses
> SELECT LOG10("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time log10
---------
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:00:00Z0.3147096929551737
This query returns the base-10 logarithms of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT LOG10(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The LOG10() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the base-10 logarithms of these results.
LOG10() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the base-10 logarithms of the average field values for the specified field key
> SELECT LOG10(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time log10
---------
2015-08-18T00:00:00Z0.32014628611105395
2015-08-18T00:12:00Z0.3174364965350991
2015-08-18T00:24:00Z0.3109056293761414
This query returns the base-10 logarithms of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the LOG10()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the base-10 logarithms of each average field value.
MOVING_AVERAGE()
Returns the moving average for each specified window of field values.
Basic syntax
SELECT MOVING_AVERAGE([*|<field_key>|/<regular_expression>/],<N>)[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
MOVING_AVERAGE()
: calculates the moving average of each window that contains N
consecutive field values. The N
parameter is required, and the value of this parameter must be an integer.
MOVING_AVERAGE(field_key,N)
: returns the moving average of N field values for the specified field key.
MOVING_AVERAGE(/regular_expression/,N)
: returns the moving average of N field values for each field key that matches the specified regular expression.
MOVING_AVERAGE(*,N)
: returns the moving average of N field values for each field key in the specified measurement.
The MOVING_AVERAGE()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use MOVING_AVERAGE()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the moving average for each window of the field values for the specified field key
> SELECT MOVING_AVERAGE("water_level",2) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time moving_average
------------------
2015-08-18T00:06:00Z2.09
2015-08-18T00:12:00Z2.072
2015-08-18T00:18:00Z2.077
2015-08-18T00:24:00Z2.0835
2015-08-18T00:30:00Z2.0460000000000003
This query returns the moving average for each window of two field values for the water_level
field key in the h2o_feet
measurement. The first result 2.09
is the average of the first two field values in raw data, and is calculated based on the formula: (2.064 + 2.116)/2. The second result 2.072
is the average of the second and third field values in raw data, and is calculated based on the formula: (2.116 + 2.028)/2.
Example 2: Calculate the moving average for each window of the field values for each field key in the specified measurement
> SELECT MOVING_AVERAGE(*,3) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time moving_average_water_level
------------------------------
2015-08-18T00:12:00Z2.0693333333333332
2015-08-18T00:18:00Z2.09
2015-08-18T00:24:00Z2.065
2015-08-18T00:30:00Z2.0726666666666667
This query returns the moving average for each window of three field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the moving average for each window of the field values for each field key that matches the specified regular expression
> SELECT MOVING_AVERAGE(/level/,4) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'
name: h2o_feet
time moving_average_water_level
------------------------------
2015-08-18T00:18:00Z2.0835
2015-08-18T00:24:00Z2.07775
2015-08-18T00:30:00Z2.0615
This query returns the moving average for each window of four field values for each specified field key. The specified field key contains level
and stores numeric values in the h2o_feet
measurement.
Example 4: Calculate the moving average for each window of the field values for the specified field key by using a query that includes multiple clauses
> SELECT MOVING_AVERAGE("water_level",2) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' ORDER BY time DESC LIMIT 2 OFFSET 3
name: h2o_feet
time moving_average
------------------
2015-08-18T00:06:00Z2.072
2015-08-18T00:00:00Z2.09
This query returns the moving average for each window of two field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 2. The point offset is set to 3, which indicates that the first three points are not returned.
Advanced syntax
SELECT MOVING_AVERAGE(<function>([*|<field_key>|/<regular_expression>/]), N )[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The MOVING_AVERAGE() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the moving averages of these results.
MOVING_AVERAGE() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the moving average of the maximum values
> SELECT MOVING_AVERAGE(MAX("water_level"),2) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time moving_average
------------------
2015-08-18T00:12:00Z2.121
2015-08-18T00:24:00Z2.0885
This query returns the moving average for each window of the two largest values for the water_level
field key. The largest values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the maximum field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MAX()
function in combination with the GROUP BY time()
clause, and does not use the MOVING_AVERAGE()
function.
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m)
name: h2o_feet
time max
-------
2015-08-18T00:00:00Z2.116
2015-08-18T00:12:00Z2.126
2015-08-18T00:24:00Z2.051
Then, TSDB for InfluxDB® calculates the moving average for each window of two maximum values. In the query results, the first point 2.121
is the average of the first two maximum values, and is calculated based on the formula: (2.116 + 2.126) / 2
.
NON_NEGATIVE_DERIVATIVE()
Returns the non-negative change rate between field values. A non-negative change rate is a positive number or 0.
Basic syntax
SELECT NON_NEGATIVE_DERIVATIVE([*|<field_key>|/<regular_expression>/][,<unit>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
TSDB for InfluxDB® calculates the difference between each two field values and converts the results into change rates. The change rates indicate how much the field values have changed based on the time granularity that is specified by the unit
parameter. The value of the unit
parameter is an integer that is suffixed by a unit of time. This parameter is optional. If you do not specify the unit
parameter in your query, the unit
parameter is set to the default value 1s
. NON_NEGATIVE_DERIVATIVE()
: returns only the change rates that are positive numbers or 0.
NON_NEGATIVE_DERIVATIVE(field_key)
: returns the non-negative change rates for the field values of the specified field key.
NON_NEGATIVE_DERIVATIVE(/regular_expression/)
: returns the non-negative change rates for the field values of each field key that matches the specified regular expression.
NON_NEGATIVE_DERIVATIVE(*)
: returns the non-negative change rates for the field values of each field key in the specified measurement.
The NON_NEGATIVE_DERIVATIVE()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use NON_NEGATIVE_DERIVATIVE()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
For more information about the syntax examples, see the DERIVATIVE()
section in this topic. The NON_NEGATIVE_DERIVATIVE()
function runs in the same way as the DERIVATIVE()
function. The difference is that the NON_NEGATIVE_DERIVATIVE()
function returns only the change rates that are positive numbers or 0.
Advanced syntax
SELECT NON_NEGATIVE_DERIVATIVE(<function>([*|<field_key>|/<regular_expression>/])[,<unit>])[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The NON_NEGATIVE_DERIVATIVE() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clause. Then, the queries calculate the non-negative derivatives of these results.
The value of the unit
parameter is an integer that is suffixed by a unit of time. This parameter is optional. You do not need to specify the unit
parameter. By default, the unit
parameter is set to the time interval that is specified by the GROUP BY time()
clause. Note: The default value of the unit
parameter in the advanced syntax is different from that of the unit
parameter in the basic syntax. NON_NEGATIVE_DERIVATIVE()
: returns only the change rates that are positive numbers or 0.
NON_NEGATIVE_DERIVATIVE() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Examples
For more information about the syntax examples, see the DERIVATIVE()
section in this topic. The NON_NEGATIVE_DERIVATIVE()
function runs in the same way as the DERIVATIVE()
function. The difference is that the NON_NEGATIVE_DERIVATIVE()
function returns only the change rates that are positive numbers or 0.
NON_NEGATIVE_DIFFERENCE()
Returns the non-negative difference between field values. A non-negative difference is a positive number or 0.
Basic syntax
SELECT NON_NEGATIVE_DIFFERENCE([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
NON_NEGATIVE_DIFFERENCE(field_key)
: returns the non-negative difference between the field values of the specified field key.
NON_NEGATIVE_DIFFERENCE(/regular_expression/)
: returns the non-negative difference between the field values of each field key that matches the specified regular expression.
NON_NEGATIVE_DIFFERENCE(*)
: returns the non-negative difference between the field values of each field key in the specified measurement.
The NON_NEGATIVE_DIFFERENCE()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use NON_NEGATIVE_DIFFERENCE()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
For more information about the syntax examples, see the DIFFERENCE()
section in this topic. The NON_NEGATIVE_DIFFERENCE()
function runs in the same way as the DIFFERENCE()
function. The difference is that the NON_NEGATIVE_DIFFERENCE()
function returns only the differences that are positive numbers or 0.
Advanced syntax
SELECT NON_NEGATIVE_DIFFERENCE(<function>([*|<field_key>|/<regular_expression>/]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The NON_NEGATIVE_DIFFERENCE() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the non-negative differences between these results.
NON_NEGATIVE_DIFFERENCE() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Examples
For more information about the syntax examples, see the DIFFERENCE()
section in this topic. The NON_NEGATIVE_DIFFERENCE()
function runs in the same way as the DIFFERENCE()
function. The difference is that the NON_NEGATIVE_DIFFERENCE()
function returns only the differences that are positive numbers or 0.
POW()
Raises the specified field values to the power of x
.
Basic syntax
SELECT POW([*|<field_key>],<x>)[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
POW(field_key, x)
: raises each field value of the specified field key to the power of x
.
POW(*, x)
: raises each field value of all the field keys in the specified measurement to the power of x
.
The POW()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use POW()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Raise each field value of the specified field key to the power of 4
> SELECT POW("water_level",4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time pow
-------
2015-08-18T00:00:00Z18.148417929216
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:24:00Z17.352898193760993
2015-08-18T00:30:00Z17.69549197320101
This query returns the results of raising each field value of the water_level
field key in the h2o_feet
measurement to the power of 4.
Example 2: Raise each field value of all the field keys in the specified measurement to the power of 4
> SELECT POW(*,4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time pow_water_level
-------------------
2015-08-18T00:00:00Z18.148417929216
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:24:00Z17.352898193760993
2015-08-18T00:30:00Z17.69549197320101
This query returns the results of raising the field values of each specified field key to the power of 4. The specified field key stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Raise each field value of the specified field key to the power of 4 by using a query that includes multiple clauses
> SELECT POW("water_level",4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time pow
-------
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:00:00Z18.148417929216
This query returns the results of raising each field value of the water_level
field key in the h2o_feet
measurement to the power of 4. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT POW(<function>([*|<field_key>]),<x>)[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The POW() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries raise these results to the power of x
.
POW() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Raise the average field values of the specified field key to the power of 4
> SELECT POW(MEAN("water_level"),4) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time pow
-------
2015-08-18T00:00:00Z19.08029760999999
2015-08-18T00:12:00Z18.609983417041
2015-08-18T00:24:00Z17.523567165456008
This query returns the results of raising each average field value to the power of 4 for the water_level
field key. The average field values are calculated based a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the POW()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® raises each average field value to the power of 4.
ROUND()
Rounds the specified field values to the nearest integers.
Basic syntax
SELECT ROUND([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
ROUND(field_key)
: rounds each field value of the specified field key to the nearest integer.
ROUND(*)
: rounds each field value of all the field keys in the specified measurement to the nearest integer.
The ROUND()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use ROUND()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following sample data is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Round each field value of the specified field key to the nearest integer
> SELECT ROUND("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time round
---------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2
The ROUND() function in this query rounds each field value of the water_level
field key in the h2o_feet
measurement to the nearest integer.
Example 2: Round each field value of all the field keys in the specified measurement to the nearest integer
> SELECT ROUND(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time round_water_level
---------------------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2
The ROUND() function in this query rounds each field value of the specified field keys to the nearest integer. The specified field keys store numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Round each field value of the specified field key to the nearest integer by using a query that includes multiple clauses
> SELECT ROUND("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time round
---------
2015-08-18T00:18:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:00:00Z2
The ROUND() function in this query rounds each field value of the water_level
field key in the h2o_feet
measurement to the nearest integer. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT ROUND(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The ROUND() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries apply the ROUND() function to these results.
ROUND() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Round each average field value to the nearest integer
> SELECT ROUND(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time round
---------
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2
The ROUND() function in this query rounds each average field value of the water_level
field key to the nearest integer. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the ROUND()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® rounds each average field value to the nearest integer.
SIN()
Returns the sines of the field values for the specified field keys.
Basic syntax
SELECT SIN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
SIN(field_key)
: returns the sines of the field values for the specified field key.
SIN(*)
: returns the sines of the field values for each field key in the specified measurement.
The SIN()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use SIN()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the sines of the field values for the specified field key
> SELECT SIN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time sin
-------
2015-08-18T00:00:00Z0.8808206017241819
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:24:00Z0.8914760289023131
2015-08-18T00:30:00Z0.8869008523376968
This query returns the sines of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the sines of the field values for each field key in the specified measurement
> SELECT SIN(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time sin_water_level
-------------------
2015-08-18T00:00:00Z0.8808206017241819
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:24:00Z0.8914760289023131
2015-08-18T00:30:00Z0.8869008523376968
This query returns the sines of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the sines of the field values for the specified field key by using a query that includes multiple clauses
> SELECT SIN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time sin
-------
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:00:00Z0.8808206017241819
This query returns the sines of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT SIN(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The SIN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the GROUP BY time()
clauses. Then, the queries calculate the sines of these results.
SIN() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the sines of the average field values for the specified field key
> SELECT SIN(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time sin
-------
2015-08-18T00:00:00Z0.8682145834456126
2015-08-18T00:12:00Z0.8745914945253902
2015-08-18T00:24:00Z0.8891995555912935
This query returns the sines of the average field values for the water_level
field key. The average field values are calculated based on a 12-minute interval.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the SIN()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the sines of the average field values.
SQRT()
Returns the square roots of the field values for the specified field keys.
Basic syntax
SELECT SQRT([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
SQRT(field_key)
: returns the square roots of the field values for the specified field key.
SQRT(*)
: returns the square roots of the field values for each field key in the specified measurement.
The SQRT()
function supports INT64 and FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use SQRT()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the square roots of the field values for the specified field key
> SELECT SQRT("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time sqrt
--------
2015-08-18T00:00:00Z1.4366627996854378
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:24:00Z1.4286357128393508
2015-08-18T00:30:00Z1.4321312788986909
This query returns the square roots of the field values for the h2o_feet
field key in the water_level
measurement.
Example 2: Calculate the square roots of the field values for each field key in the specified measurement
> SELECT SQRT(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time sqrt_water_level
--------------------
2015-08-18T00:00:00Z1.4366627996854378
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:24:00Z1.4286357128393508
2015-08-18T00:30:00Z1.4321312788986909
This query returns the square roots of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the square roots of the field values for the specified field key by using a query that includes multiple clauses
> SELECT SQRT("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time sqrt
--------
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:00:00Z1.4366627996854378
This query returns the square roots of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT SQRT(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The SQRT() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the square roots of these results.
SQRT() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the square roots of the average field values for the specified field key
> SELECT SQRT(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time sqrt
--------
2015-08-18T00:00:00Z1.445683229480096
2015-08-18T00:12:00Z1.4411800720243115
2015-08-18T00:24:00Z1.430384563675098
This query returns the square roots of the average field values that are calculated based on a 12-minute interval for the water_level
field key.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the SQRT()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the square roots of the average field values.
TAN()
Returns the tangents of the field values for the specified field keys.
Basic syntax
SELECT TAN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
TAN(field_key)
: returns the tangents of the field values for the specified field key.
TAN(*)
: returns the tangents of the field values for each field key in the specified measurement.
The TAN()
function supports INT64 or FLOAT64 field values.
The basic syntax supports the GROUP BY
clauses that are used to group data by tag. However, the basic syntax does not support the GROUP BY clauses that are used to group data by time. For more information about how to use TAN()
functions in combination with GROUP BY time()
clauses, see the "Advanced syntax" section.
Examples
The following data in the NOAA_water_database
dataset is used in the examples that are provided in this section:
> SELECT "water_level" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051
Example 1: Calculate the tangents of the field values for the specified field key
> SELECT TAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time tan
-------
2015-08-18T00:00:00Z-1.8604293534384375
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:24:00Z-1.9676434782626282
2015-08-18T00:30:00Z-1.9198657720074992
This query returns the tangents of the field values for the water_level
field key in the h2o_feet
measurement.
Example 2: Calculate the tangents of the field values for each field key in the specified measurement
> SELECT TAN(*) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica'
name: h2o_feet
time tan_water_level
-------------------
2015-08-18T00:00:00Z-1.8604293534384375
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:24:00Z-1.9676434782626282
2015-08-18T00:30:00Z-1.9198657720074992
This query returns the tangents of the field values for each field key that stores numeric values in the h2o_feet
measurement. The h2o_feet
measurement contains only the numeric water_level
field.
Example 3: Calculate the tangents of the field values for the specified field key by using a query that includes multiple clauses
> SELECT TAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2
name: h2o_feet
time tan
-------
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:00:00Z-1.8604293534384375
This query returns the tangents of the field values for the water_level
field key in the h2o_feet
measurement. The specified time range is from 2015-08-18T00:00:00Z
to 2015-08-18T00:30:00Z
. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.
Advanced syntax
SELECT TAN(<function>([*|<field_key>]))[INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]
Description
The TAN() advanced syntax requires a GROUP BY time()
clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the GROUP BY time()
clauses. Then, the queries calculate the tangents of these results.
TAN() supports the following nested functions:
COUNT()
MEAN()
MEDIAN()
MODE()
SUM()
FIRST()
LAST()
MIN()
MAX()
PERCENTILE()
Example: Calculate the sines of the average field values for the specified field key
> SELECT TAN(MEAN("water_level")) FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time tan
-------
2015-08-18T00:00:00Z-1.7497661902817365
2015-08-18T00:12:00Z-1.8038002062256624
2015-08-18T00:24:00Z-1.9435224805850773
This query returns the tangents of the average field values that are calculated based on a 12-minute interval for the water_level
field key.
To obtain the results, TSDB for InfluxDB® first calculates the average field values of the water_level
field key based on a 12-minute interval. This process returns the same result as a query if the query uses the MEAN()
function in combination with the GROUP BY time()
clause, and does not use the TAN()
function.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' AND "location"='santa_monica' GROUP BY time(12m)
name: h2o_feet
time mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
Then, TSDB for InfluxDB® calculates the tangents of the average field values.