InfluxQL transformation functions

InfluxQL transformation functions modify and return values each row of queried data.

Each transformation function below covers syntax, including parameters to pass to the function, and examples of how to use the function. Examples use NOAA water sample data and data from sample_test.txt.

• ABS()
• ACOS()
• ASIN()
• ATAN()
• ATAN2()
• CEIL()
• COS()
• CUMULATIVE_SUM()
• DERIVATIVE()
• DIFFERENCE()
• ELAPSED()
• EXP()
• FLOOR()
• HISTOGRAM()
• LN()
• LOG()
• LOG2()
• LOG10()
• MOVING_AVERAGE()
• NON_NEGATIVE_DERIVATIVE()
• NON_NEGATIVE_DIFFERENCE
• POW
• ROUND
• SIN
• SQRT
• TAN

ABS()

Returns the absolute value of the field value. Supports GROUP BY clauses that group by tags but not group by time.

Basic syntax

1. SELECT ABS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ABS(field_key) Returns the absolute values of field values associated with the field key.

ABS(*) Returns the absolute values of field values associated with each field key in the measurement.

ABS() supports int64 and float64 field value data types.

Examples

Calculate the absolute values of field values associated with a field key

Return the absolute values of field values in the water_level field key in the h2o_feet measurement.

1. SELECT ABS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:15:00Z'

name: data

Calculate the absolute values of field values associated with each field key in a measurement

Return the absolute values of field values for each field key that stores numeric values in the data measurement. The h2o_feet measurement has one numeric field water_level.

1. SELECT ABS(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:15:00Z'

name: data

Calculate the absolute values of field values associated with a field key and include several clauses

Return the absolute values of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT ABS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

1. SELECT ABS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ABS() function to those results.

ABS() supports the following nested functions:

COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the absolute values of mean values

Return the absolute values of mean water_levels that are calculated at 12-minute intervals.

1. SELECT ABS(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m)

name: data

ACOS()

Returns the arccosine (in radians) of the field value. Field values must be between -1 and 1. Supports GROUP BY clauses that group by tags but does not support group by time.

Basic syntax

1. SELECT ACOS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ACOS(field_key) Returns the arccosine of field values associated with the field key.

ACOS(*) Returns the arccosine of field values associated with each field key in the measurement.

ACOS() supports int64 and float64 field value data types with values between -1 and 1.

Examples

The examples below use a subset of data from sample_test.txt, which only includes field values within the calculable range (-1 to 1). This value range is required for the ACOS() function:

Calculate the arccosine of field values associated with a field key

Return the arccosine of field values in the a field key in the data measurement.

1. SELECT ACOS("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arccosine of field values associated with each field key in a measurement

Return the arccosine of field values for each field key that stores numeric values in the data measurement, field a and b.

1. SELECT ACOS(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arccosine of field values associated with a field key and include several clauses

Return the arccosine of field values associated with the a field key in the time range between 2018-06-24T00:00:00Z and 2018-06-25T00:00:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT ACOS("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

1. SELECT ACOS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ACOS() function to those results.

ACOS() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the arccosine of mean values

Return the arccosine of mean a that are calculated at 3 hour intervals.

1. SELECT ACOS(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ASIN()

Returns the arcsine (in radians) of the field value. Field values must be between -1 and 1.

Basic syntax

1. SELECT ASIN( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ASIN(field_key) Returns the arcsine of field values associated with the field key.

ASIN(*) Returns the arcsine of field values associated with each field key in the measurement.

ASIN() supports int64 and float64 field value data types with values between -1 and 1.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ASIN() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following data from sample_test.txt.

The following data from this data set only includes field value within the calculable range (-1 to 1) required for the ASIN() function:

Calculate the arcsine of field values associated with a field key

Return the arcsine of field values in the a field key in the data measurement.

1. SELECT ASIN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arcsine of field values associated with each field key in a measurement

Return the arcsine of field values for each field key that stores numeric values in the data measurement. The data measurement has one numeric field: a.

1. SELECT ASIN(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arcsine of field values associated with a field key and include several clauses

Return the arcsine of field values associated with the a field key in the time range between 2018-06-24T00:00:00Z and 2018-06-25T00:00:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT ASIN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

1. SELECT ASIN(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ASIN() function to those results.

ASIN() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the arcsine of mean values

Return the arcsine of mean as that are calculated at 3-hour intervals.

1. SELECT ASIN(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ATAN()

Returns the arctangent (in radians) of the field value. Field values must be between -1 and 1.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ATAN() with a GROUP BY time() clause, see the Advanced syntax.

Basic syntax

1. SELECT ATAN( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ATAN(field_key) Returns the arctangent of field values associated with the field key.

ATAN(*) Returns the arctangent of field values associated with each field key in the measurement.

ATAN() supports int64 and float64 field value data types with values between -1 and 1.

Examples

The examples below use a subset of data from sample_test.txt that only includes field values within the calculable range (-1 to 1) required for the of the ATAN() function.

Calculate the arctangent of field values associated with a field key

Return the arctangent of field values in the a field key in the data measurement.

1. SELECT ATAN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of field values associated with each field key in a measurement

Return the arctangent of field values for each field key that stores numeric values in the data measurement–fields a and b.

1. SELECT ATAN(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of field values associated with a field key and include several clauses

Return the arctangent of field values associated with the a field key in time range between 2017-05-01T00:00:00Z and 2017-05-09T00:00:00Z and returns results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT ATAN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

1. SELECT ATAN(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ATAN() function to those results.

ATAN() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples of advanced syntax

Calculate the arctangent of mean values

Return the arctangent of mean as that are calculated at 3-hour intervals.

1. SELECT ATAN(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ATAN2()

Returns the the arctangent of y/x in radians.

Basic syntax

1. SELECT ATAN2( [ * | <field_key> | num ], [ <field_key> | num ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ATAN2(field_key_y, field_key_x) Returns the arctangent of field values associated with the field key, field_key_y, divided by field values associated with field_key_x.

ATAN2(*, field_key_x) Returns the field values associated with each field key in the measurement divided by field values associated with field_key_x.

ATAN2() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ATAN2() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use sample_test.txt.

Calculate the arctangent of field_key_b over field_key_a

Return the arctangents of field values in the a field key divided by values in the b field key. Both are part of the data measurement.

1. SELECT ATAN2("a", "b") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of values associated with each field key in a measurement divided by field_key_a

Return the arctangents of all numeric field values in the data measurement divided by values in the a field key. The data measurement has two numeric fields: a and b.

1. SELECT ATAN2(*, "a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangents of field values and include several clauses

Return the arctangent of field values associated with the a field key divided by the b field key in the time range between 2018-05-16T12:10:00Z and 2018-05-16T12:10:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT ATAN2("a", "b") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

1. SELECT ATAN2(<function()>, <function()>) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ATAN2() function to those results.

ATAN2() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate arctangents of mean values

Return the arctangents of mean as divided by average bs. Averages are calculated at 2-hour intervals.

1. SELECT ATAN2(MEAN("b"), MEAN("a")) FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(2h)

name: data

CEIL()

Returns the subsequent value rounded up to the nearest integer.

Basic syntax

1. SELECT CEIL( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

CEIL(field_key) Returns the field values associated with the field key rounded up to the nearest integer.

CEIL(*) Returns the field values associated with each field key in the measurement rounded up to the nearest integer.

CEIL() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use CEIL() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

1. SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with a field key

Return field values in the water_level field key in the h2o_feet measurement rounded up to the nearest integer.

1. SELECT CEIL("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with each field key in a measurement

Return field values for each field key that stores numeric values in the h2o_feet measurement rounded up to the nearest integer. The h2o_feet measurement has one numeric field: water_level.

1. SELECT CEIL(*) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with a field key and include several clauses

Return field values associated with the water_level field key rounded up to the nearest integer in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT CEIL("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

1. SELECT CEIL(<function>( [ * | <field_key> | /<regular_expression>/ ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the CEIL() function to those results.

CEIL() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate mean values rounded up to the nearest integer

Return the mean water_levels that are calculated at 12-minute intervals and rounds them up to the nearest integer.

1. SELECT CEIL(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

COS()

Returns the cosine of the field value.

Basic syntax

1. SELECT COS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

COS(field_key) Returns the cosine of field values associated with the field key.

COS(*) Returns the cosine of field values associated with each field key in the measurement.

COS() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use COS() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

1. SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with a field key

Return the cosine of field values in the water_level field key in the h2o_feet measurement.

1. SELECT COS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with each field key in a measurement

Return the cosine of field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

1. SELECT COS(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with a field key and include several clauses

Return the cosine of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

1. SELECT COS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

1. SELECT COS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the COS() function to those results.

COS() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the cosine of mean values

Return the cosine of mean water_levels that are calculated at 12-minute intervals.

1. SELECT COS(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

CUMULATIVE_SUM()

Returns the running total of subsequent field values.

Basic syntax

1. SELECT CUMULATIVE_SUM( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

CUMULATIVE_SUM(field_key) Returns the running total of subsequent field values associated with the field key.

CUMULATIVE_SUM(/regular_expression/) Returns the running total of subsequent field values associated with each field key that matches the regular expression.

CUMULATIVE_SUM(*) Returns the running total of subsequent field values associated with each field key in the measurement.

CUMULATIVE_SUM() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use CUMULATIVE_SUM() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

1. SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with a field key

Return the running total of the field values in the water_level field key and in the h2o_feet measurement.

1. SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with each field key in a measurement

Return the running total of the field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

1. SELECT CUMULATIVE_SUM(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

| time |

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