All Products
Search
Document Center

Influxql-Functions

Last Updated: Sep 10, 2020

You can use InfluxQL functions to aggregate, select, transform, and predict data.

Overview

Aggregations

COUNT()

Returns the number of non-null field values.

Syntax

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

Nested structure syntax

  1. SELECT COUNT(DISTINCT( [ * | <field_key> | /<regular_expression>/ ] )) [...]

Syntax description

COUNT(field_key)
Returns the number of non-null field values for the specified field key.

COUNT(/regular_expression/)
Returns the number of non-null field values for each field key that matches the specified regular expression.

COUNT(*)
Returns the number of non-null field values for each field key in the specified measurement.

The COUNT() function supports the field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

Examples

Example 1: Calculate the number of non-null field values for the specified field key

  1. > SELECT COUNT("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time count
  4. ---- -----
  5. 1970-01-01T00:00:00Z 15258

This query returns the number of non-null field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the number of non-null field values for each field key in the specified measurement

  1. > SELECT COUNT(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time count_level description count_water_level
  4. ---- ----------------------- -----------------
  5. 1970-01-01T00:00:00Z 15258 15258

This query returns the number of non-null field values for each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: Calculate the number of non-null field values for each field key that matches the specified regular expression

  1. > SELECT COUNT(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time count_water_level
  4. ---- -----------------
  5. 1970-01-01T00:00:00Z 15258

This query returns the number of non-null field values for each field key that contains water in the h2o_feet measurement.

Example 4: Calculate the number of non-null field values for the specified field key in each time interval by using a query that includes multiple clauses

  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(200) LIMIT 7 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time count
  5. ---- -----
  6. 2015-08-17T23:48:00Z 200
  7. 2015-08-18T00:00:00Z 2
  8. 2015-08-18T00:12:00Z 2
  9. 2015-08-18T00:24:00Z 2
  10. 2015-08-18T00:36:00Z 2
  11. 2015-08-18T00:48:00Z 2

This query returns the number of non-null field values for the water_level field key in each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 200. This query limits the number of returned points to 7, and limits the number of returned series to 1.

Example 5: Calculate the number of unique field values for the specified field key

  1. > SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"
  2. name: h2o_feet
  3. time count
  4. ---- -----
  5. 1970-01-01T00:00:00Z 4

This query returns the number of unique field values for the level description field key in the h2o_feet measurement.

COUNT() FAQ

Question: How does the fill() function affect the return values of the COUNT() function?

Answer: For time intervals in which no data is reported, most InfluxQL functions return null values. If you do not want to obtain null values, you can use the fill() function. The fill(<fill_option>) function replaces each null value with the value of the fill_option parameter. If no data is reported for certain time intervals, the COUNT() function returns 0 for the time intervals. If you use the fill(<fill_option>) function in the query, the function replaces 0 with the value of the fill_option parameter.

Examples
The first query in the following code block does not use the fill() function. In the query result, 0 is returned for the last time interval. This is because no data is reported for the last time interval. The second query uses the fill(800000) function. In this query, no data is reported for the last interval. For the last interval, the fill(800000) function returns 800000. If you do not use the fill() function, 0 is returned for the last time interval.

  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T21:24:00Z' AND time <= '2015-09-18T21:54:00Z' GROUP BY time(12m)
  2. name: h2o_feet
  3. time count
  4. ---- -----
  5. 2015-09-18T21:24:00Z 2
  6. 2015-09-18T21:36:00Z 2
  7. 2015-09-18T21:48:00Z 0
  8. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-09-18T21:24:00Z' AND time <= '2015-09-18T21:54:00Z' GROUP BY time(12m) fill(800000)
  9. name: h2o_feet
  10. time count
  11. ---- -----
  12. 2015-09-18T21:24:00Z 2
  13. 2015-09-18T21:36:00Z 2
  14. 2015-09-18T21:48:00Z 800000

DISTINCT()

Returns the unique field values of the specified field keys.

Syntax

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

Nested structure syntax

  1. SELECT COUNT(DISTINCT( [ * | <field_key> | /<regular_expression>/ ] )) [...]

Syntax description

DISTINCT(field_key)
Returns the unique field values of the specified field key.

DISTINCT(/regular_expression/)
Returns the unique field values of each field key that matches the specified regular expression.

DISTINCT(*)
Returns the unique field values of each field key in the specified measurement.

The DISTINCT() function supports the field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

Examples

Example 1: List the unique field values of the specified field key

  1. > SELECT DISTINCT("level description") FROM "h2o_feet"
  2. name: h2o_feet
  3. time distinct
  4. ---- --------
  5. 1970-01-01T00:00:00Z between 6 and 9 feet
  6. 1970-01-01T00:00:00Z below 3 feet
  7. 1970-01-01T00:00:00Z between 3 and 6 feet
  8. 1970-01-01T00:00:00Z at or greater than 9 feet

This query returns the unique field values of the level description field key in the h2o_feet measurement.

Example 2: List the unique field values of each field key in the specified measurement

  1. > SELECT DISTINCT(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time distinct_level description distinct_water_level
  4. ---- -------------------------- --------------------
  5. 1970-01-01T00:00:00Z between 6 and 9 feet 8.12
  6. 1970-01-01T00:00:00Z between 3 and 6 feet 8.005
  7. 1970-01-01T00:00:00Z at or greater than 9 feet 7.887
  8. 1970-01-01T00:00:00Z below 3 feet 7.762
  9. [...]

This query returns the unique field values of each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: List the unique field values of each field key that matches the specified regular expression

  1. > SELECT DISTINCT(/description/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time distinct_level description
  4. ---- --------------------------
  5. 1970-01-01T00:00:00Z below 3 feet
  6. 1970-01-01T00:00:00Z between 6 and 9 feet
  7. 1970-01-01T00:00:00Z between 3 and 6 feet
  8. 1970-01-01T00:00:00Z at or greater than 9 feet

This query returns the unique field values of each field key that contains description in the h2o_feet measurement.

Example 4: List the unique field values of the specified field key by using a query that includes multiple clauses

  1. > SELECT DISTINCT("level description") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time distinct
  5. ---- --------
  6. 2015-08-18T00:00:00Z between 6 and 9 feet
  7. 2015-08-18T00:12:00Z between 6 and 9 feet
  8. 2015-08-18T00:24:00Z between 6 and 9 feet
  9. 2015-08-18T00:36:00Z between 6 and 9 feet
  10. 2015-08-18T00:48:00Z between 6 and 9 feet

This query returns the unique field values of the level description field key. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. This query limits the number of returned series to 1.

Example 5: Calculate the number of unique field values for the specified field key

  1. > SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"
  2. name: h2o_feet
  3. time count
  4. ---- -----
  5. 1970-01-01T00:00:00Z 4

This query returns the number of unique field values for the level description field key in the h2o_feet measurement.

DISTINCT() FAQ

Question: What are the impacts if I use the DISTINCT() function in an INTO clause?

Answer: If you use the DISTINCT() function in an INTO clause, TSDB for InfluxDB® may overwrite certain points in the target measurement. In most cases, the DISTINCT() function returns multiple field values that have the same timestamp. TSDB for InfluxDB® considers the points that have the same timestamp in the same series as duplicate points, and uses the latest points to overwrite the duplicate points in the target measurement.

Examples
The first query in the following code block uses the DISTINCT() function and returns four field values. Note that the four field values have the same timestamp. The second query uses an INTO clause and writes the returned field values to the distincts measurement. The last query returns all the data in the distincts measurement.
The four initial points are considered as duplicate points because these points belong to the same series and have the same timestamp. Therefore, the last query returns only one point. If duplicate points exist, TSDB for InfluxDB® uses the latest point to overwrite the previous points.

  1. > SELECT DISTINCT("level description") FROM "h2o_feet"
  2. name: h2o_feet
  3. time distinct
  4. ---- --------
  5. 1970-01-01T00:00:00Z below 3 feet
  6. 1970-01-01T00:00:00Z between 6 and 9 feet
  7. 1970-01-01T00:00:00Z between 3 and 6 feet
  8. 1970-01-01T00:00:00Z at or greater than 9 feet
  9. > SELECT DISTINCT("level description") INTO "distincts" FROM "h2o_feet"
  10. name: result
  11. time written
  12. ---- -------
  13. 1970-01-01T00:00:00Z 4
  14. > SELECT * FROM "distincts"
  15. name: distincts
  16. time distinct
  17. ---- --------
  18. 1970-01-01T00:00:00Z at or greater than 9 feet

INTEGRAL()

Returns the area of the section that is located between the field value curve and the x-axis. The area indicates the integral of the field values.

Syntax

  1. SELECT INTEGRAL( [ * | <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]

Syntax description

TSDB for InfluxDB® calculates the areas of the sections that are located between the field value curves and the x-axis. Then, TSDB for InfluxDB® converts the results into the total areas based on the time granularity that is specified by the unit parameter. The value of the unit parameter is an integer that is followed by a unit of time. This parameter is optional. If you do not specify the unit parameter in your query, the default value 1s is used for the unit parameter.

INTEGRAL(field_key)
Returns the area of the section that is located between the field value curve and the x-axis for the specified field key.

INTEGRAL(/regular_expression/)
Returns the area of the section between the field value curve and the x-axis for each field key that matches the specified regular expression.

INTEGRAL(*)
Returns the area of the section that is located between the field value curve and the x-axis for each field key in the specified measurement.

The fill() function cannot be nested in the INTEGRAL() function. The INTEGRAL() function supports the field values of INT64 and FLOAT64 data types.

Examples

Examples 1 to 5 use a portion of the sample data in the NOAA_water_database database.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the integral of the field values for the specified field key

  1. > SELECT INTEGRAL("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time integral
  4. ---- --------
  5. 1970-01-01T00:00:00Z 3732.66

This query returns the area of the section that is located between the field value curve and the x-axis based on a 1-second interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the integral of the field values for the specified field key and specify the unit parameter

  1. > SELECT INTEGRAL("water_level",1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time integral
  4. ---- --------
  5. 1970-01-01T00:00:00Z 62.211

This query returns the area of the section that is located between the field value curve and the x-axis based on a 1-minute interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

Example 3: Calculate the integral of the field values for each field key in the specified measurement and specify the unit parameter

  1. > SELECT INTEGRAL(*,1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time integral_water_level
  4. ---- --------------------
  5. 1970-01-01T00:00:00Z 62.211

This query returns the area of the section that is located between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 4: Calculate the integral of the field values for each field key that matches the specified regular expression and specify the unit parameter

  1. > SELECT INTEGRAL(/water/,1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time integral_water_level
  4. ---- --------------------
  5. 1970-01-01T00:00:00Z 62.211

This query returns the area of the section that is located between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key contains water and stores numeric values in the h2o_feet measurement.

Example 5: Calculate the integral of the field values for the specified field key by using a query that includes multiple clauses

  1. > SELECT INTEGRAL("water_level",1m) 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) LIMIT 1
  2. name: h2o_feet
  3. time integral
  4. ---- --------
  5. 2015-08-18T00:00:00Z 24.972

This query returns the area of the section that is located between the field value curve and the x-axis based on a 1-minute interval for the water_level field key in the h2o_feet measurement. The specified time range is 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval. This query limits the number of returned points to 1.

MEAN()

Returns the average field values of the specified field keys.

Syntax

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

Syntax description

MEAN(field_key)
Returns the average field value of the specified field key.

MEAN(/regular_expression/)
Returns the average field value of each field key that matches the specified regular expression.

MEAN(*)
Returns the average field value of each field key in the specified measurement.

The MEAN() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Calculate the average field value of the specified field key

  1. > SELECT MEAN("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 1970-01-01T00:00:00Z 4.442107025822522

This query returns the average field value of the water_level field key in the h2o_feet measurement.

Example 2: Calculate the average field value of each field key in the specified measurement

  1. > SELECT MEAN(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time mean_water_level
  4. ---- ----------------
  5. 1970-01-01T00:00:00Z 4.442107025822522

This query returns the average field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Calculate the average field value of each field key that matches the specified regular expression

  1. > SELECT MEAN(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time mean_water_level
  4. ---- ----------------
  5. 1970-01-01T00:00:00Z 4.442107025822523

This query returns the average field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the average field value of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 7 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time mean
  5. ---- ----
  6. 2015-08-17T23:48:00Z 9.01
  7. 2015-08-18T00:00:00Z 8.0625
  8. 2015-08-18T00:12:00Z 7.8245
  9. 2015-08-18T00:24:00Z 7.5675
  10. 2015-08-18T00:36:00Z 7.303
  11. 2015-08-18T00:48:00Z 7.046

This query returns the average field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. This query limits the number of returned points to 7, and limits the number of returned series to 1.

MEDIAN()

Returns the median in a set of sorted field values. The median is the field value in the middle of a set of sorted field values.

Syntax

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

Syntax description

MEDIAN(field_key)
Returns the median of the field values for the specified field key.

MEDIAN(/regular_expression/)
Returns the median of the field values for each field key that matches the specified regular expression.

MEDIAN(*)
Returns the median of the field values for each field key in the specified measurement.

The MEDIAN() function supports the field values of INT64 and FLOAT64 data types.

Notes: The MEDIAN() function is equivalent to the PERCENTILE(field_key, 50) function except in scenarios where the specified field key has an even number of field values. In these scenarios, the MEDIAN() function returns the average of the two middle field values.

Examples

Example 1: Calculate the median of the field values for the specified field key

  1. > SELECT MEDIAN("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time median
  4. ---- ------
  5. 1970-01-01T00:00:00Z 4.124

This query returns the median of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the median of the field values for each field key in the specified measurement

  1. > SELECT MEDIAN(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time median_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 4.124

This query returns the median of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Calculate the median of the field values for each field key that matches the specified regular expression

  1. > SELECT MEDIAN(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time median_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 4.124

This query returns the median of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the median of the field values for the specified field key in each time interval by using a query that includes multiple clauses

  1. > SELECT MEDIAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(700) LIMIT 7 SLIMIT 1 SOFFSET 1
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time median
  5. ---- ------
  6. 2015-08-17T23:48:00Z 700
  7. 2015-08-18T00:00:00Z 2.09
  8. 2015-08-18T00:12:00Z 2.077
  9. 2015-08-18T00:24:00Z 2.0460000000000003
  10. 2015-08-18T00:36:00Z 2.0620000000000003
  11. 2015-08-18T00:48:00Z 700

This query returns the median of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 700. This query limits the number of returned points to 7, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

MODE()

Returns the most frequent field values of the specified field keys.

Syntax

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

Syntax description

MODE(field_key)
Returns the most frequent field value of the specified field key.

MODE(/regular_expression/)
Returns the most frequent field value of each field key that matches the specified regular expression.

MODE(*)
Returns the most frequent field value of each field key in the specified measurement.

The MODE() function supports the field values of all data types.

Notes: If two or more field values have the largest number of occurrences and relate to each other, the MODE() function returns the field value attached with the earliest timestamp.

Examples

Example 1: Obtain the most frequent value of the specified field key

  1. > SELECT MODE("level description") FROM "h2o_feet"
  2. name: h2o_feet
  3. time mode
  4. ---- ----
  5. 1970-01-01T00:00:00Z between 3 and 6 feet

This query returns the most frequent field value of the level description field key in the h2o_feet measurement.

Example 2: Obtain the most frequent field value of each field key in the specified measurement

  1. > SELECT MODE(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time mode_level description mode_water_level
  4. ---- ---------------------- ----------------
  5. 1970-01-01T00:00:00Z between 3 and 6 feet 2.69

This query returns the most frequent field value of each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: Obtain the most frequent field value of each field key that matches the specified regular expression

  1. > SELECT MODE(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time mode_water_level
  4. ---- ----------------
  5. 1970-01-01T00:00:00Z 2.69

This query returns the most frequent field value of each field key that contains water in the h2o_feet measurement.

Example 4: Obtain the most frequent field value of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT MODE("level description") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* LIMIT 3 SLIMIT 1 SOFFSET 1
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time mode
  5. ---- ----
  6. 2015-08-17T23:48:00Z
  7. 2015-08-18T00:00:00Z below 3 feet
  8. 2015-08-18T00:12:00Z below 3 feet

This query returns the most frequent field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

SPREAD()

Returns the difference between the largest and the smallest field values of the specified field keys.

Syntax

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

Syntax description

SPREAD(field_key)
Returns the difference between the largest and the smallest field values of the specified field key.

SPREAD(/regular_expression/)
Returns the difference between the largest and the smallest field values of each field key that matches the specified regular expression.

SPREAD(*)
Returns the difference between the largest and the smallest field values of each field key in the specified measurement.

The SPREAD() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Calculate the difference between the largest and the smallest field values of the specified field key

  1. > SELECT SPREAD("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time spread
  4. ---- ------
  5. 1970-01-01T00:00:00Z 10.574

This query returns the difference between the largest and the smallest field values of the water_level field key in the h2o_feet measurement.

Example 2: Calculate the difference between the largest and the smallest field values of each field key in the specified measurement

  1. > SELECT SPREAD(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time spread_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 10.574

This query returns the difference between the largest and the smallest field values of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Calculate the difference between the largest and the smallest field values of each field key that matches the specified regular expression

  1. > SELECT SPREAD(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time spread_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 10.574

This query returns the difference between the largest and the smallest 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 largest and the smallest field values of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT SPREAD("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18) LIMIT 3 SLIMIT 1 SOFFSET 1
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time spread
  5. ---- ------
  6. 2015-08-17T23:48:00Z 18
  7. 2015-08-18T00:00:00Z 0.052000000000000046
  8. 2015-08-18T00:12:00Z 0.09799999999999986

This query returns the difference between the largest and the smallest field values of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

STDDEV()

Returns the standard deviation of the specified field values.

Syntax

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

Syntax description

STDDEV(field_key)
Returns the standard deviation of the field values for the specified field key.

STDDEV(/regular_expression/)
Returns the standard deviation of the field values for each field key that matches the specified regular expression.

STDDEV(*)
Returns the standard deviation of the field values for each field key in the specified measurement.

The STDDEV() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Calculate the standard deviation of the field values for the specified field key

  1. > SELECT STDDEV("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time stddev
  4. ---- ------
  5. 1970-01-01T00:00:00Z 2.279144584196141

This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the standard deviation of the field values for each field key in the specified measurement

  1. > SELECT STDDEV(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time stddev_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 2.279144584196141

This query returns the standard deviation of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Calculate the standard deviation of the field values for each field key that matches the specified regular expression

  1. > SELECT STDDEV(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time stddev_water_level
  4. ---- ------------------
  5. 1970-01-01T00:00:00Z 2.279144584196141

This query returns the standard deviation of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Calculate the standard deviation of the field values for the specified field key in each time interval by using a query that includes multiple clauses

  1. > SELECT STDDEV("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 2 SLIMIT 1 SOFFSET 1
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time stddev
  5. ---- ------
  6. 2015-08-17T23:48:00Z 18000
  7. 2015-08-18T00:00:00Z 0.03676955262170051

This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 2, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

SUM()

Returns the sum of the specified field values.

Syntax

  1. SELECT 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]

Syntax description

SUM(field_key)
Returns the sum of the field values for the specified field key.

SUM(/regular_expression/)
Returns the sum of the field values for each field key that matches the specified regular expression.

SUM(*)
Returns the sum of the field values for each field key in the specified measurement.

The SUM() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Calculate the sum of the field values for the specified field key

  1. > SELECT SUM("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time sum
  4. ---- ---
  5. 1970-01-01T00:00:00Z 67777.66900000004

This query returns the sum of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the sum of the field values for each field key in the specified measurement

  1. > SELECT SUM(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time sum_water_level
  4. ---- ---------------
  5. 1970-01-01T00:00:00Z 67777.66900000004

This query returns the sum of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Calculate the sum of the field values for each field key that matches the specified regular expression

  1. > SELECT SUM(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time sum_water_level
  4. ---- ---------------
  5. 1970-01-01T00:00:00Z 67777.66900000004

This query returns the 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 sum of the field values for the specified field key in each time interval by using a query that includes multiple clauses

  1. > SELECT SUM("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 4 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time sum
  5. ---- ---
  6. 2015-08-17T23:48:00Z 18000
  7. 2015-08-18T00:00:00Z 16.125
  8. 2015-08-18T00:12:00Z 15.649
  9. 2015-08-18T00:24:00Z 15.135

This query returns the sum of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 4, and limits the number of returned series to 1.

Selectors

BOTTOM()

Returns the smallest N field values.

Syntax

  1. SELECT BOTTOM(<field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

BOTTOM(field_key,N)
Returns the smallest N values of the specified field key.

BOTTOM(field_key,tag_key(s),N)
Returns the smallest field value of the field key for each of the N tag values of the specified tag key.

BOTTOM(field_key,N),tag_key(s),field_key(s)
Returns the smallest N values for the specified field key enclosed in parentheses, and the relevant tag and field values.

The BOTTOM() function supports the field values of INT64 and FLOAT64 data types.

Notes:

  • If two or more duplicate smallest values exist, the BOTTOM() function returns the field value attached with the earliest timestamp.

  • If the BOTTOM() function is used in conjunction with an INTO clause, the BOTTOM() function differs from other InfluxQL functions in terms of the returned timestamps. For more information about the BOTTOM() function, see the “BOTTOM() FAQ” section.

Examples

Example 1: Obtain the smallest three field values of the specified field key

  1. > SELECT BOTTOM("water_level",3) FROM "h2o_feet"
  2. name: h2o_feet
  3. time bottom
  4. ---- ------
  5. 2015-08-29T14:30:00Z -0.61
  6. 2015-08-29T14:36:00Z -0.591
  7. 2015-08-30T15:18:00Z -0.594

This query returns the smallest three field values of the water_level field key in the h2o_feet measurement.

Example 2: Obtain the smallest field value of the field key for each of the two tag values

  1. > SELECT BOTTOM("water_level","location",2) FROM "h2o_feet"
  2. name: h2o_feet
  3. time bottom location
  4. ---- ------ --------
  5. 2015-08-29T10:36:00Z -0.243 santa_monica
  6. 2015-08-29T14:30:00Z -0.61 coyote_creek

This query returns the smallest field value of the water_level field key for each of the two tag values of the location tag key.

Example 3: Obtain the smallest four field values of the specified field key, and the relevant tag and field values

  1. > SELECT BOTTOM("water_level",4),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time bottom location level description
  4. ---- ------ -------- -----------------
  5. 2015-08-29T14:24:00Z -0.587 coyote_creek below 3 feet
  6. 2015-08-29T14:30:00Z -0.61 coyote_creek below 3 feet
  7. 2015-08-29T14:36:00Z -0.591 coyote_creek below 3 feet
  8. 2015-08-30T15:18:00Z -0.594 coyote_creek below 3 feet

This query returns the smallest four field values of the water_level field key. This query also returns the values of the location tag key and the level description field key.

Example 4: Obtain the smallest three field values of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT BOTTOM("water_level",3),"location" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(24m) ORDER BY time DESC
  2. name: h2o_feet
  3. time bottom location
  4. ---- ------ --------
  5. 2015-08-18T00:48:00Z 1.991 santa_monica
  6. 2015-08-18T00:54:00Z 2.054 santa_monica
  7. 2015-08-18T00:54:00Z 6.982 coyote_creek
  8. 2015-08-18T00:24:00Z 2.041 santa_monica
  9. 2015-08-18T00:30:00Z 2.051 santa_monica
  10. 2015-08-18T00:42:00Z 2.057 santa_monica
  11. 2015-08-18T00:00:00Z 2.064 santa_monica
  12. 2015-08-18T00:06:00Z 2.116 santa_monica
  13. 2015-08-18T00:12:00Z 2.028 santa_monica

This query returns the smallest three field values of the water_level field key for each 24-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z. The query results are sorted based on timestamps in descending order.

Note that the GROUP BY time() clause does not overwrite the original timestamps of points. For more information, see the following section.

BOTTOM() FAQ

Question 1: What are the impacts if I use BOTTOM() functions and GROUP BY time() clauses in queries?

Answer 1: If you use BOTTOM() functions and GROUP BY time() clauses in queries, the queries return the specified number of points based on the time intervals specified by the GROUP BY time() clauses. For the most queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval specified by the GROUP BY time() clauses. However, for the queries that use BOTTOM() functions and GROUP BY time() clauses, the original timestamps of the points are retained.

Example
The following query returns two points based on the 18-minute interval that is specified by the GROUP BY time() clause. Note that the original timestamps of the points are returned. The original timestamps are not forced to match the start time of the time interval that is specified by the GROUP BY time() clause.

  1. > SELECT BOTTOM("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)
  2. name: h2o_feet
  3. time bottom
  4. ---- ------
  5. __
  6. 2015-08-18T00:00:00Z 2.064 |
  7. 2015-08-18T00:12:00Z 2.028 | <------- Smallest points for the first time interval
  8. --
  9. __
  10. 2015-08-18T00:24:00Z 2.041 |
  11. 2015-08-18T00:30:00Z 2.051 | <------- Smallest points for the second time interval
  12. --

Question 2: What are the impacts if the number of tag values for the specified tag key is smaller than N specified in the BOTTOM() function?

Answer 2: The queries that use the SELECT BOTTOM(<field_key>,<tag_key>,<N>) syntax return N points, and N may be smaller than the number you specify in the queries. Assume that a tag key has X tag values. If you specify the number of tag values as N in a query and X is smaller than N, the query returns X points.

Example
The following query requests the smallest field value of the water_level field key for each of the three tag values of the location tag key. The query returns two points because the location tag key has only two tag values: santa_monica and coyote_creek.

  1. > SELECT BOTTOM("water_level","location",3) FROM "h2o_feet"
  2. name: h2o_feet
  3. time bottom location
  4. ---- ------ --------
  5. 2015-08-29T10:36:00Z -0.243 santa_monica
  6. 2015-08-29T14:30:00Z -0.61 coyote_creek

Question 3: What are the impacts if I use INTO clauses and BOTTOM() functions where tag keys are specified in queries?

Answer 3: If you use INTO clauses and do not use GROUP BY tag clauses in queries, most InfluxQL functions convert the tags in the raw data into the fields in the newly written data. This rule applies to the BOTTOM() function except in scenarios where tag keys are specified in the BOTTOM() function: BOTTOM(field_key,tag_key(s),N). In these scenarios, the specified tags are retained in the newly written data.

Examples
The first query in the following code block returns the smallest field value of the water_level field key for each of the two tag values of the location tag key. The query results are written to the bottom_water_levels measurement. In the second query, TSDB for InfluxDB® retains the location tag in the bottom_water_levels measurement.

  1. > SELECT BOTTOM("water_level","location",2) INTO "bottom_water_levels" FROM "h2o_feet"
  2. name: result
  3. time written
  4. ---- -------
  5. 1970-01-01T00:00:00Z 2
  6. > SHOW TAG KEYS FROM "bottom_water_levels"
  7. name: bottom_water_levels
  8. tagKey
  9. ------
  10. location

FIRST()

Returns the field value that is attached with the earliest timestamp.

Syntax

  1. SELECT FIRST(<field_key>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

FIRST(field_key)
Returns the field value that is attached with the earliest timestamp for the specified field key.

FIRST(/regular_expression/)
Returns the field value attached with the earliest timestamp for each field key that matches the specified regular expression.

FIRST(*)
Returns the field value that is attached with the earliest timestamp for each field key in the specified measurement.

FIRST(field_key),tag_key(s),field_key(s)
Returns the field value that is attached with the earliest timestamp for the specified field key, and the relevant tag and field values.

The FIRST() function supports the field values of all data types.

Examples

Example 1: Obtain the field value attached with the earliest timestamp for the specified field key

  1. > SELECT FIRST("level description") FROM "h2o_feet"
  2. name: h2o_feet
  3. time first
  4. ---- -----
  5. 2015-08-18T00:00:00Z between 6 and 9 feet

This query returns the field value that is attached with the earliest timestamp for the level description field key in the h2o_feet measurement.

Example 2: Obtain the field value attached with the earliest timestamp for each field key in the specified measurement

  1. > SELECT FIRST(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time first_level description first_water_level
  4. ---- ----------------------- -----------------
  5. 1970-01-01T00:00:00Z between 6 and 9 feet 8.12

This query returns the field value that is attached with the earliest timestamp for each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: Obtain the field value attached with the earliest timestamp for each field key that matches the specified regular expression

  1. > SELECT FIRST(/level/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time first_level description first_water_level
  4. ---- ----------------------- -----------------
  5. 1970-01-01T00:00:00Z between 6 and 9 feet 8.12

This query returns the field value attached with the earliest timestamp for each field key that contains level in the h2o_feet measurement.

Example 4: Obtain the field value attached with the earliest timestamp for the specified field key, and the relevant tag and field values

  1. > SELECT FIRST("level description"),"location","water_level" FROM "h2o_feet"
  2. name: h2o_feet
  3. time first location water_level
  4. ---- ----- -------- -----------
  5. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12

This query returns the field value that is attached with the earliest timestamp for the level description field key in the h2o_feet measurement. This query also returns the values of the location tag key and the water_level field key.

Example 5: Obtain the field value attached with the earliest timestamp in each time interval for the specified field key by using a query that includes multiple clauses

  1. > SELECT FIRST("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time first
  5. ---- -----
  6. 2015-08-17T23:48:00Z 9.01
  7. 2015-08-18T00:00:00Z 8.12
  8. 2015-08-18T00:12:00Z 7.887
  9. 2015-08-18T00:24:00Z 7.635

This query returns the field value attached with the earliest timestamp in each time interval for the water_level field key in the h2o_feet measurement. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and limits the number of returned series to 1.

Note that the GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval covered by the first point is 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval covered by the last point is 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

LAST()

Returns the field value that is attached with the latest timestamp.

Syntax

  1. SELECT LAST(<field_key>)[,<tag_key(s)>|<field_keys(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

LAST(field_key)
Returns the field value that is attached with the latest timestamp for the specified field key.

LAST(/regular_expression/)
Returns the field value attached with the latest timestamp for each field key that matches the specified regular expression.

LAST(*)
Returns the field value that is attached with the latest timestamp for each field key in the specified measurement.

LAST(field_key),tag_key(s),field_key(s)
Returns the field value that is attached with the latest timestamp for the specified field key, and the relevant tag and field values.

The LAST() function supports the field values of all data types.

Examples

Example 1: Obtain the field value attached with the latest timestamp for the specified field key

  1. > SELECT LAST("level description") FROM "h2o_feet"
  2. name: h2o_feet
  3. time last
  4. ---- ----
  5. 2015-09-18T21:42:00Z between 3 and 6 feet

This query returns the field value that is attached with the latest timestamp for the level description field key in the h2o_feet measurement.

Example 2: Obtain the field value attached with the latest timestamp for each field key in the specified measurement

  1. > SELECT LAST(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time last_level description last_water_level
  4. ---- ----------------------- -----------------
  5. 2015-09-18T21:42:00Z between 3 and 6 feet 4.938

This query returns the field value that is attached with the latest timestamp for each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: Obtain the field value attached with the latest timestamp for each field key that matches the specified regular expression

  1. > SELECT LAST(/level/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time last_level description last_water_level
  4. ---- ----------------------- -----------------
  5. 2015-09-18T21:42:00Z between 3 and 6 feet 4.938

This query returns the field value attached with the latest timestamp for each field key that contains level in the h2o_feet measurement.

Example 4: Obtain the field value attached with the latest timestamp for the specified field key, and the relevant tag and field values

  1. > SELECT LAST("level description"),"location","water_level" FROM "h2o_feet"
  2. name: h2o_feet
  3. time last location water_level
  4. ---- ---- -------- -----------
  5. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

This query returns the field value attached with the latest timestamp for the level description field key in the h2o_feet measurement. This query also returns the values of the location tag key and the water_level field key.

Example 5: Obtain the field value attached with the latest timestamp in each time interval for the specified field key by using a query that includes multiple clauses

  1. > SELECT LAST("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time last
  5. ---- ----
  6. 2015-08-17T23:48:00Z 9.01
  7. 2015-08-18T00:00:00Z 8.005
  8. 2015-08-18T00:12:00Z 7.762
  9. 2015-08-18T00:24:00Z 7.5

This query returns the field value that is attached with the latest timestamp in each time interval for the water_level field key in the h2o_feet measurement. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and limits the number of returned series to 1.

Note that the GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval covered by the first point is 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval covered by the last point is 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

MAX()

Returns the largest field values of the specified field keys.

Syntax

  1. SELECT MAX(<field_key>)[,<tag_key(s)>|<field__key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

MAX(field_key)
Returns the largest field value of the specified field key.

MAX(/regular_expression/)
Returns the largest field value of each field key that matches the specified regular expression.

MAX(*)
Returns the largest field value of each field key in the specified measurement.

MAX(field_key),tag_key(s),field_key(s)
Returns the largest field value of the specified field key, and the relevant tag and field values.

The MAX() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Obtain the largest field value of the specified field key

  1. > SELECT MAX("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time max
  4. ---- ---
  5. 2015-08-29T07:24:00Z 9.964

This query returns the largest field value of the water_level field key in the h2o_feet measurement.

Example 2: Obtain the largest field value of each field key in the specified measurement

  1. > SELECT MAX(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time max_water_level
  4. ---- ---------------
  5. 2015-08-29T07:24:00Z 9.964

This query returns the largest field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Obtain the largest field value of each field key that matches the specified regular expression

  1. > SELECT MAX(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time max_water_level
  4. ---- ---------------
  5. 2015-08-29T07:24:00Z 9.964

This query returns the largest field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Obtain the largest field value of the specified field key, and the relevant tag and field values

  1. > SELECT MAX("water_level"),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time max location level description
  4. ---- --- -------- -----------------
  5. 2015-08-29T07:24:00Z 9.964 coyote_creek at or greater than 9 feet

This query returns the largest field value of the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

Example 5: Obtain the largest field value of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time max
  5. ---- ---
  6. 2015-08-17T23:48:00Z 9.01
  7. 2015-08-18T00:00:00Z 8.12
  8. 2015-08-18T00:12:00Z 7.887
  9. 2015-08-18T00:24:00Z 7.635

This query returns the largest field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and limits the number of returned series to 1.

Note that the GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval covered by the first point is 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval covered by the last point is 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

MIN()

Returns the smallest field values of the specified field keys.

Syntax

  1. SELECT MIN(<field_key>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

MIN(field_key)
Returns the smallest field value of the specified field key.

MIN(/regular_expression/)
Returns the smallest field value of each field key that matches the specified regular expression.

MIN(*)
Returns the smallest field value of each field key in the specified measurement.

MIN(field_key),tag_key(s),field_key(s)
Returns the smallest field value of the specified field key, and the relevant tag and field values.

The MIN() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Obtain the smallest field value of the specified field key

  1. > SELECT MIN("water_level") FROM "h2o_feet"
  2. name: h2o_feet
  3. time min
  4. ---- ---
  5. 2015-08-29T14:30:00Z -0.61

This query returns the smallest field value of the water_level field key in the h2o_feet measurement.

Example 2: Obtain the smallest field value of each field key in the specified measurement

  1. > SELECT MIN(*) FROM "h2o_feet"
  2. name: h2o_feet
  3. time min_water_level
  4. ---- ---------------
  5. 2015-08-29T14:30:00Z -0.61

This query returns the smallest field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Obtain the smallest field value of each field key that matches the specified regular expression

  1. > SELECT MIN(/water/) FROM "h2o_feet"
  2. name: h2o_feet
  3. time min_water_level
  4. ---- ---------------
  5. 2015-08-29T14:30:00Z -0.61

This query returns the smallest field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

Example 4: Obtain the smallest field value of the specified field key, and the relevant tag and field values

  1. > SELECT MIN("water_level"),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time min location level description
  4. ---- --- -------- -----------------
  5. 2015-08-29T14:30:00Z -0.61 coyote_creek below 3 feet

This query returns the smallest field value of the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

Example 5: Obtain the smallest field value of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT MIN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time min
  5. ---- ---
  6. 2015-08-17T23:48:00Z 9.01
  7. 2015-08-18T00:00:00Z 8.005
  8. 2015-08-18T00:12:00Z 7.762
  9. 2015-08-18T00:24:00Z 7.5

This query returns the smallest field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and limits the number of returned series to 1.

Note that the GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval covered by the first point is 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval covered by the last point is 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

PERCENTILE()

Returns the field value of the Nth percentile.

Syntax

  1. SELECT PERCENTILE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

PERCENTILE(field_key,N)
Returns the field value of the Nth percentile for the specified field key.

PERCENTILE(/regular_expression/,N)
Returns the field value of the Nth percentile for each field key that matches the specified regular expression.

PERCENTILE(*,N)
Returns the field value of the Nth percentile for each field key in the specified measurement.

PERCENTILE(field_key,N),tag_key(s),field_key(s)
Returns the field value of the Nth percentile for the specified field key, and the relevant tag and field values.

The specified value for the N parameter must be an integer or a floating point number that ranges from 0 to 100. The PERCENTILE() function supports the field values of INT64 and FLOAT64 data types.

Examples

Example 1: Obtain the field value of the fifth percentile for the specified field key

  1. > SELECT PERCENTILE("water_level",5) FROM "h2o_feet"
  2. name: h2o_feet
  3. time percentile
  4. ---- ----------
  5. 2015-08-31T03:42:00Z 1.122

This query returns the smallest field value that is larger than 5% field values of the water_level field key in the h2o_feet measurement.

Example 2: Obtain the field value of the fifth percentile for each field key in the specified measurement

  1. > SELECT PERCENTILE(*,5) FROM "h2o_feet"
  2. name: h2o_feet
  3. time percentile_water_level
  4. ---- ----------------------
  5. 2015-08-31T03:42:00Z 1.122

This query returns the smallest field value that is larger than 5% field values of the field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Obtain the field value of the fifth percentile for each field key that matches the specified regular expression

  1. > SELECT PERCENTILE(/water/,5) FROM "h2o_feet"
  2. name: h2o_feet
  3. time percentile_water_level
  4. ---- ----------------------
  5. 2015-08-31T03:42:00Z 1.122

This query returns the smallest field value that is larger than 5% field values of each specified field key in the h2o_feet measurement. The specified field key contains water and stores numeric values.

Example 4: Obtain the field value of the fifth percentile for the specified field key, and the relevant tag and field values

  1. > SELECT PERCENTILE("water_level",5),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time percentile location level description
  4. ---- ---------- -------- -----------------
  5. 2015-08-31T03:42:00Z 1.122 coyote_creek below 3 feet

This query returns the smallest field value that is larger than 5% field values of the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

Example 5: Obtain the field value of the twentieth percentile for the specified field key in each time interval by using a query that includes multiple clauses

  1. > SELECT PERCENTILE("water_level",20) FROM "h2o_feet" WHERE time >= '2015-08-17T23:48:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(24m) fill(15) LIMIT 2
  2. name: h2o_feet
  3. time percentile
  4. ---- ----------
  5. 2015-08-17T23:36:00Z 15
  6. 2015-08-18T00:00:00Z 2.064

This query returns the smallest field value that is larger than 20% field values of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 24-minute interval. For intervals in which no data is reported, the fill() function returns 15. This query limits the number of returned points to 2.

Note that the GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 24-minute interval. The time interval covered by the first point is 2015-08-17T23:36:00Z to 2015-08-18T00:00:00Z. The time interval covered by the last point is 2015-08-18T00:00:00Z to 2015-08-18T00:24:00Z.

PERCENTILE() FAQ

Question: What are the relationships between the PERCENTILE() function and other InfluxQL functions?

  • Answer: The PERCENTILE(<field_key>,100) function is equivalent to the MAX(<field_key>) function.
  • The PERCENTILE(<field_key>, 50) function is equivalent to the MEDIAN(<field_key>) function except in scenarios where the specified field key has an even number of field values. In these scenarios, the MEDIAN() function returns the average of the two middle field values.
  • The PERCENTILE(<field_key>,0) function is not equivalent to the MIN(<field_key>) function. The PERCENTILE(<field_key>,0) function returns null.

SAMPLE()

Returns a sample of N random field values. The SAMPLE() function uses reservoir sampling to generate random points.

Syntax

  1. SELECT SAMPLE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

SAMPLE(field_key,N)
Returns N random field values of the specified field key.

SAMPLE(/regular_expression/,N)
Returns N random field values of each field key that matches the specified regular expression.

SAMPLE(*,N)
Returns N random field values of each field key in the specified measurement.

SAMPLE(field_key,N),tag_key(s),field_key(s)
Returns N random field values of the specified field key enclosed in parentheses, and the relevant tag and field values.

The value of the N parameter must be an integer. The SAMPLE() function supports the field values of all data types.

Examples

Example 1: Select random field values of the specified field key

  1. > SELECT SAMPLE("water_level",2) FROM "h2o_feet"
  2. name: h2o_feet
  3. time sample
  4. ---- ------
  5. 2015-09-09T21:48:00Z 5.659
  6. 2015-09-18T10:00:00Z 6.939

This query returns two random points for the water_level field key in the h2o_feet measurement.

Example 2: Select random field values of each field key in the specified measurement

  1. > SELECT SAMPLE(*,2) FROM "h2o_feet"
  2. name: h2o_feet
  3. time sample_level description sample_water_level
  4. ---- ------------------------ ------------------
  5. 2015-08-25T17:06:00Z 3.284
  6. 2015-09-03T04:30:00Z below 3 feet
  7. 2015-09-03T20:06:00Z between 3 and 6 feet
  8. 2015-09-08T21:54:00Z 3.412

This query returns two random points for each field key in the h2o_feet measurement. The h2o_feet measurement contains two field keys: level description and water_level.

Example 3: Select random field values of each field key that matches the specified regular expression

  1. > SELECT SAMPLE(/level/,2) FROM "h2o_feet"
  2. name: h2o_feet
  3. time sample_level description sample_water_level
  4. ---- ------------------------ ------------------
  5. 2015-08-30T05:54:00Z between 6 and 9 feet
  6. 2015-09-07T01:18:00Z 7.854
  7. 2015-09-09T20:30:00Z 7.32
  8. 2015-09-13T19:18:00Z between 3 and 6 feet

This query returns two random points for each field key that contains level in the h2o_feet measurement.

Example 4: Select random field values of the specified field key, and the relevant tag and field values

  1. > SELECT SAMPLE("water_level",2),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time sample location level description
  4. ---- ------ -------- -----------------
  5. 2015-08-29T10:54:00Z 5.689 coyote_creek between 3 and 6 feet
  6. 2015-09-08T15:48:00Z 6.391 coyote_creek between 6 and 9 feet

This query returns two random points for the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

Example 5: Select random field values of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT SAMPLE("water_level",1) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)
  2. name: h2o_feet
  3. time sample
  4. ---- ------
  5. 2015-08-18T00:12:00Z 2.028
  6. 2015-08-18T00:30:00Z 2.051

This query returns a random point for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 18-minute interval.

Note that the GROUP BY time() clause does not overwrite the original timestamps of points. For more information, see the following section.

SAMPLE() FAQ

Question: What are the impacts if I use SAMPLE() functions and GROUP BY time() clauses in queries?

Answer 1: If you use SAMPLE() functions and GROUP BY time() clauses in queries, the queries return the specified number (N) of points based on the time intervals specified by the GROUP BY time() clauses. For the most queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval specified by the GROUP BY time() clauses. However, for the queries that use SAMPLE() functions and GROUP BY time() clauses, the original timestamps of points are retained.

Example
The following query returns two random points based on the 18-minute interval that is specified by GROUP BY time() clause. Note that the original timestamps of the points are returned. The original timestamps are not forced to match the start time of the time interval that is specified by the GROUP BY time() clause.

  1. > SELECT SAMPLE("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)
  2. name: h2o_feet
  3. time sample
  4. ---- ------
  5. __
  6. 2015-08-18T00:06:00Z 2.116 |
  7. 2015-08-18T00:12:00Z 2.028 | <------- Randomly-selected points for the first time interval
  8. --
  9. __
  10. 2015-08-18T00:18:00Z 2.126 |
  11. 2015-08-18T00:30:00Z 2.051 | <------- Randomly-selected points for the second time interval
  12. --

TOP()

Returns the largest N field values.

Syntax

  1. SELECT TOP( <field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Syntax description

TOP(field_key,N)
Returns the largest N values of the specified field key.

TOP(field_key,tag_key(s),N)
Returns the largest field value of the field key for each of the N tag values of the specified tag key.

TOP(field_key,N),tag_key(s),field_key(s)
Returns the largest N values of the specified field key enclosed in parentheses, and the relevant tag and field values.

The TOP() function supports the field values of INT64 and FLOAT64 data types.

Notes:

  • If two or more duplicate largest field values exist and relate to each other, the TOP() function returns the field value attached with the earliest timestamp.

  • If the TOP() function is used in conjunction with an INTO clause, the TOP() function differs from other InfluxQL functions in terms of the returned timestamps. For more information, see the “TOP() FAQ” section.

Examples

Example 1: Obtain the largest three field values of the specified field key

  1. > SELECT TOP("water_level",3) FROM "h2o_feet"
  2. name: h2o_feet
  3. time top
  4. ---- ---
  5. 2015-08-29T07:18:00Z 9.957
  6. 2015-08-29T07:24:00Z 9.964
  7. 2015-08-29T07:30:00Z 9.954

This query returns the largest three field values of the water_level field key in the h2o_feet measurement.

Example 2: Obtain the largest field value of the field key for each of the two tag values

  1. > SELECT TOP("water_level","location",2) FROM "h2o_feet"
  2. name: h2o_feet
  3. time top location
  4. ---- --- --------
  5. 2015-08-29T03:54:00Z 7.205 santa_monica
  6. 2015-08-29T07:24:00Z 9.964 coyote_creek

This query returns the largest field value of the water_level field key for each of the two tag values of the location tag key.

Example 3: Obtain the largest four field values of the specified field key, and the relevant tag and field values

  1. > SELECT TOP("water_level",4),"location","level description" FROM "h2o_feet"
  2. name: h2o_feet
  3. time top location level description
  4. ---- --- -------- -----------------
  5. 2015-08-29T07:18:00Z 9.957 coyote_creek at or greater than 9 feet
  6. 2015-08-29T07:24:00Z 9.964 coyote_creek at or greater than 9 feet
  7. 2015-08-29T07:30:00Z 9.954 coyote_creek at or greater than 9 feet
  8. 2015-08-29T07:36:00Z 9.941 coyote_creek at or greater than 9 feet

This query returns the largest four field values of the water_level field key. This query also returns the values of the location tag key and the level description field key.

Example 4: Obtain the largest three values of the specified field key for each time interval by using a query that includes multiple clauses

  1. > SELECT TOP("water_level",3),"location" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(24m) ORDER BY time DESC
  2. name: h2o_feet
  3. time top location
  4. ---- --- --------
  5. 2015-08-18T00:48:00Z 7.11 coyote_creek
  6. 2015-08-18T00:54:00Z 6.982 coyote_creek
  7. 2015-08-18T00:54:00Z 2.054 santa_monica
  8. 2015-08-18T00:24:00Z 7.635 coyote_creek
  9. 2015-08-18T00:30:00Z 7.5 coyote_creek
  10. 2015-08-18T00:36:00Z 7.372 coyote_creek
  11. 2015-08-18T00:00:00Z 8.12 coyote_creek
  12. 2015-08-18T00:06:00Z 8.005 coyote_creek
  13. 2015-08-18T00:12:00Z 7.887 coyote_creek

This query returns the largest three values of the water_level field key for each 24-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z. The query results are sorted based on timestamps in descending order.

Note that the GROUP BY time() clause does not overwrite the original timestamps of points. For more information, see the following section.

TOP() FAQ

Question 1: What are the impacts if I use TOP() functions and GROUP BY time() clauses in queries?

Answer 1: If you use TOP() functions and GROUP BY time() clauses in queries, the queries return the specified number of points based on the time intervals specified by the GROUP BY time() clauses. For the most queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval specified by the GROUP BY time() clauses. However, for the queries that use TOP() functions and GROUP BY time() clauses, the original timestamps of points are retained.

Example
The following query returns two points based on the 18-minute interval that is specified by the GROUP BY time() clause. Note that the original timestamps of the points are returned. The original timestamps are not forced to match the start time of the time interval that is specified by the GROUP BY time() clause.

  1. > SELECT TOP("water_level",2) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)
  2. name: h2o_feet
  3. time top
  4. ---- ------
  5. __
  6. 2015-08-18T00:00:00Z 2.064 |
  7. 2015-08-18T00:06:00Z 2.116 | <------- Greatest points for the first time interval
  8. --
  9. __
  10. 2015-08-18T00:18:00Z 2.126 |
  11. 2015-08-18T00:30:00Z 2.051 | <------- Greatest points for the second time interval
  12. --

Question 2: What are the impacts if the number of tag values for the specified tag key is smaller than N specified in the TOP() function?

Answer 2: The queries that use the SELECT TOP(<field_key>,<tag_key>,<N>) syntax return N points, and N may be smaller than the number you specify in the function. Assume that a tag key has X tag values. If you specify the number of tag values as N in a query and X is smaller than N, the query returns X points.

Example
The following query requests the largest field value of the water_level field key for each of the three tag values of the location tag key. The query returns two points because the location tag key has only two tag values: santa_monica and coyote_creek.

  1. > SELECT TOP("water_level","location",3) FROM "h2o_feet"
  2. name: h2o_feet
  3. time top location
  4. ---- --- --------
  5. 2015-08-29T03:54:00Z 7.205 santa_monica
  6. 2015-08-29T07:24:00Z 9.964 coyote_creek

Question 3: What are the impacts if I use INTO clauses and TOP() functions where tag keys are specified in queries?

Answer 3: If you use INTO clauses and do not use GROUP BY tag clauses in queries, most InfluxQL functions convert the tags in the raw data into the fields in the newly written data. This rule applies to the TOP() function except in scenarios where tag keys are specified in the TOP()function: TOP(field_key,tag_key(s),N). In these scenarios, the specified tags are retained in the newly written data.

Examples
The first query in the following code block returns the largest field value of the water_level field key for each of the two tag values of the location tag key. The query results are written to the top_water_levels measurement. In the second query, TSDB for InfluxDB® retains the location tag in the top_water_levels measurement.

  1. > SELECT TOP("water_level","location",2) INTO "top_water_levels" FROM "h2o_feet"
  2. name: result
  3. time written
  4. ---- -------
  5. 1970-01-01T00:00:00Z 2
  6. > SHOW TAG KEYS FROM "top_water_levels"
  7. name: top_water_levels
  8. tagKey
  9. ------
  10. location

Transformations

ABS()

Returns the absolute values of the field values for the specified field keys.

Basic syntax

  1. 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 of the field values for the specified field key.

ABS(*)
Returns the absolute values of the field values for each field key in the specified measurement.

The ABS() function supports the field values of INT64 and FLOAT64 data types.

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 ABS() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT * FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-24T12:05:00Z'
  2. name: data
  3. time a b
  4. ---- - -
  5. 1529841600000000000 1.33909108671076 -0.163643058925645
  6. 1529841660000000000 -0.774984088561186 0.137034364053949
  7. 1529841720000000000 -0.921037167720451 -0.482943221384294
  8. 1529841780000000000 -1.73880754843378 -0.0729732928756677
  9. 1529841840000000000 -0.905980032168252 1.77857552719844
  10. 1529841900000000000 -0.891164752631417 0.741147445214238

Example 1: Calculate the absolute values of the field values for the specified field key

  1. > SELECT ABS("a") FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-24T12:05:00Z'
  2. name: data
  3. time abs
  4. ---- ---
  5. 1529841600000000000 1.33909108671076
  6. 1529841660000000000 0.774984088561186
  7. 1529841720000000000 0.921037167720451
  8. 1529841780000000000 1.73880754843378
  9. 1529841840000000000 0.905980032168252
  10. 1529841900000000000 0.891164752631417

This query returns the absolute values of the field values for the a field key in the data measurement.

Example 2: Calculate the absolute values of the field values for each field key in the specified measurement

  1. > SELECT ABS(*) FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-24T12:05:00Z'
  2. name: data
  3. time abs_a abs_b
  4. ---- ----- -----
  5. 1529841600000000000 1.33909108671076 0.163643058925645
  6. 1529841660000000000 0.774984088561186 0.137034364053949
  7. 1529841720000000000 0.921037167720451 0.482943221384294
  8. 1529841780000000000 1.73880754843378 0.0729732928756677
  9. 1529841840000000000 0.905980032168252 1.77857552719844
  10. 1529841900000000000 0.891164752631417 0.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 two fields of the numeric data type: a and b.

Example 3: Calculate the absolute values of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: data
  3. time abs
  4. ---- ---
  5. 1529841780000000000 1.73880754843378
  6. 1529841720000000000 0.921037167720451
  7. 1529841660000000000 0.774984088561186
  8. 1529841600000000000 1.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 2018-06-24T12:00:00Z to 2018-06-24T12:05:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 ABS() 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 absolute values of these results.

ABS() supports the following nested functions:

  • COUNT()
  • MEAN()
  • MEDIAN()
  • MODE()
  • SUM()
  • FIRST()
  • LAST()
  • MIN()
  • MAX()
  • PERCENTILE()

Examples

Example: Calculate the absolute values of the average field values for the specified field key

  1. > SELECT ABS(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-24T13:00:00Z' GROUP BY time(12m)
  2. time abs
  3. ---- ---
  4. 1529841600000000000 0.3960977256302787
  5. 1529842320000000000 0.0010541018316373302
  6. 1529843040000000000 0.04494733240283668
  7. 1529843760000000000 0.2553594777104415
  8. 1529844480000000000 0.20382988543108413
  9. 1529845200000000000 0.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, and does not use the ABS() function.

  1. > SELECT MEAN("a") FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-24T13:00:00Z' GROUP BY time(12m)
  2. name: data
  3. time mean
  4. ---- ----
  5. 1529841600000000000 -0.3960977256302787
  6. 1529842320000000000 0.0010541018316373302
  7. 1529843040000000000 0.04494733240283668
  8. 1529843760000000000 0.2553594777104415
  9. 1529844480000000000 0.20382988543108413
  10. 1529845200000000000 -0.790836070736962

Then, TSDB for InfluxDB® calculates the absolute value of each average field value.

ACOS()

Returns the arc cosines of the field values for the specified field keys. The arc cosines are returned in radians. The field values must range from -1 to +1.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types, and the field values must 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 conjunction 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 that all the field values fall within the valid range for the input values of the ACOS() function. The valid range is -1 to +1.

  1. > SELECT "of_capacity" FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time capacity
  4. ---- --------
  5. 2017-05-01T00:00:00Z 0.83
  6. 2017-05-02T00:00:00Z 0.3
  7. 2017-05-03T00:00:00Z 0.84
  8. 2017-05-04T00:00:00Z 0.22
  9. 2017-05-05T00:00:00Z 0.17
  10. 2017-05-06T00:00:00Z 0.77
  11. 2017-05-07T00:00:00Z 0.64
  12. 2017-05-08T00:00:00Z 0.72
  13. 2017-05-09T00:00:00Z 0.16

Example 1: Calculate the arc cosines of the field values for the specified field key

  1. > SELECT ACOS("of_capacity") FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time acos
  4. ---- ----
  5. 2017-05-01T00:00:00Z 0.591688642426544
  6. 2017-05-02T00:00:00Z 1.266103672779499
  7. 2017-05-03T00:00:00Z 0.5735131044230969
  8. 2017-05-04T00:00:00Z 1.3489818562981022
  9. 2017-05-05T00:00:00Z 1.399966657665792
  10. 2017-05-06T00:00:00Z 0.6919551751263169
  11. 2017-05-07T00:00:00Z 0.8762980611683406
  12. 2017-05-08T00:00:00Z 0.7669940078618667
  13. 2017-05-09T00:00:00Z 1.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

  1. > SELECT ACOS(*) FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time acos_of_capacity
  4. ---- -------------
  5. 2017-05-01T00:00:00Z 0.591688642426544
  6. 2017-05-02T00:00:00Z 1.266103672779499
  7. 2017-05-03T00:00:00Z 0.5735131044230969
  8. 2017-05-04T00:00:00Z 1.3489818562981022
  9. 2017-05-05T00:00:00Z 1.399966657665792
  10. 2017-05-06T00:00:00Z 0.6919551751263169
  11. 2017-05-07T00:00:00Z 0.8762980611683406
  12. 2017-05-08T00:00:00Z 0.7669940078618667
  13. 2017-05-09T00:00:00Z 1.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 one field of the numeric data type: of_capacity.

Example 3: Calculate the arc cosines of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: park_occupancy
  3. time acos
  4. ---- ----
  5. 2017-05-07T00:00:00Z 0.8762980611683406
  6. 2017-05-06T00:00:00Z 0.6919551751263169
  7. 2017-05-05T00:00:00Z 1.399966657665792
  8. 2017-05-04T00:00:00Z 1.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 2017-05-01T00:00:00Z to 2017-05-09T00:00:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the arc cosines of the average field values for the specified field key

  1. > 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)
  2. name: park_occupancy
  3. time acos
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.9703630732143733
  6. 2017-05-03T00:00:00Z 1.1483422646081407
  7. 2017-05-06T00:00:00Z 0.7812981174487247
  8. 2017-05-09T00:00:00Z 1.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 and the GROUP BY time() clause, and does not use the ACOS() function.

  1. > 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)
  2. name: park_occupancy
  3. time mean
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.565
  6. 2017-05-03T00:00:00Z 0.41
  7. 2017-05-06T00:00:00Z 0.71
  8. 2017-05-09T00:00:00Z 0.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 must range from -1 to +1.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types, and the field values must 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 ASIN() functions in conjunction 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 input values of the ASIN() function. The valid range is -1 to +1.

  1. > SELECT "of_capacity" FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time capacity
  4. ---- --------
  5. 2017-05-01T00:00:00Z 0.83
  6. 2017-05-02T00:00:00Z 0.3
  7. 2017-05-03T00:00:00Z 0.84
  8. 2017-05-04T00:00:00Z 0.22
  9. 2017-05-05T00:00:00Z 0.17
  10. 2017-05-06T00:00:00Z 0.77
  11. 2017-05-07T00:00:00Z 0.64
  12. 2017-05-08T00:00:00Z 0.72
  13. 2017-05-09T00:00:00Z 0.16

Example 1: Calculate the arcsines of the field values for the specified field key

  1. > SELECT ASIN("of_capacity") FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time asin
  4. ---- ----
  5. 2017-05-01T00:00:00Z 0.9791076843683526
  6. 2017-05-02T00:00:00Z 0.3046926540153975
  7. 2017-05-03T00:00:00Z 0.9972832223717997
  8. 2017-05-04T00:00:00Z 0.22181447049679442
  9. 2017-05-05T00:00:00Z 0.1708296691291045
  10. 2017-05-06T00:00:00Z 0.8788411516685797
  11. 2017-05-07T00:00:00Z 0.6944982656265559
  12. 2017-05-08T00:00:00Z 0.8038023189330299
  13. 2017-05-09T00:00:00Z 0.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

  1. > SELECT ASIN(*) FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time asin_of_capacity
  4. ---- -------------
  5. 2017-05-01T00:00:00Z 0.9791076843683526
  6. 2017-05-02T00:00:00Z 0.3046926540153975
  7. 2017-05-03T00:00:00Z 0.9972832223717997
  8. 2017-05-04T00:00:00Z 0.22181447049679442
  9. 2017-05-05T00:00:00Z 0.1708296691291045
  10. 2017-05-06T00:00:00Z 0.8788411516685797
  11. 2017-05-07T00:00:00Z 0.6944982656265559
  12. 2017-05-08T00:00:00Z 0.8038023189330299
  13. 2017-05-09T00:00:00Z 0.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 one field of the numeric data type: of_capacity.

Example 3: Calculate the arcsines of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: park_occupancy
  3. time asin
  4. ---- ----
  5. 2017-05-07T00:00:00Z 0.6944982656265559
  6. 2017-05-06T00:00:00Z 0.8788411516685797
  7. 2017-05-05T00:00:00Z 0.1708296691291045
  8. 2017-05-04T00:00:00Z 0.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 2017-05-01T00:00:00Z to 2017-05-09T00:00:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Calculate the arcsines of the average field values for the specified field key

  1. > 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)
  2. name: park_occupancy
  3. time asin
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.6004332535805232
  6. 2017-05-03T00:00:00Z 0.42245406218675574
  7. 2017-05-06T00:00:00Z 0.7894982093461719
  8. 2017-05-09T00:00:00Z 0.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 and the GROUP BY time() clause, and does not use the ASIN() function.

  1. > 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)
  2. name: park_occupancy
  3. time mean
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.565
  6. 2017-05-03T00:00:00Z 0.41
  7. 2017-05-06T00:00:00Z 0.71
  8. 2017-05-09T00:00:00Z 0.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 must range from -1 to +1.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types, and the field values must 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 ATAN() functions in conjunction 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 input values of the ATAN() function. The valid range is -1 to 1.

  1. > SELECT "of_capacity" FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time capacity
  4. ---- --------
  5. 2017-05-01T00:00:00Z 0.83
  6. 2017-05-02T00:00:00Z 0.3
  7. 2017-05-03T00:00:00Z 0.84
  8. 2017-05-04T00:00:00Z 0.22
  9. 2017-05-05T00:00:00Z 0.17
  10. 2017-05-06T00:00:00Z 0.77
  11. 2017-05-07T00:00:00Z 0.64
  12. 2017-05-08T00:00:00Z 0.72
  13. 2017-05-09T00:00:00Z 0.16

Example 1: Calculate the arc tangents of the field values for the specified field key

  1. > SELECT ATAN("of_capacity") FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time atan
  4. ---- ----
  5. 2017-05-01T00:00:00Z 0.6927678353971222
  6. 2017-05-02T00:00:00Z 0.2914567944778671
  7. 2017-05-03T00:00:00Z 0.6986598247214632
  8. 2017-05-04T00:00:00Z 0.2165503049760893
  9. 2017-05-05T00:00:00Z 0.16839015714752992
  10. 2017-05-06T00:00:00Z 0.6561787179913948
  11. 2017-05-07T00:00:00Z 0.5693131911006619
  12. 2017-05-08T00:00:00Z 0.6240230529767568
  13. 2017-05-09T00:00:00Z 0.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

  1. > SELECT ATAN(*) FROM "park_occupancy" WHERE time >= '2017-05-01T00:00:00Z' AND time <= '2017-05-09T00:00:00Z'
  2. name: park_occupancy
  3. time atan_of_capacity
  4. ---- -------------
  5. 2017-05-01T00:00:00Z 0.6927678353971222
  6. 2017-05-02T00:00:00Z 0.2914567944778671
  7. 2017-05-03T00:00:00Z 0.6986598247214632
  8. 2017-05-04T00:00:00Z 0.2165503049760893
  9. 2017-05-05T00:00:00Z 0.16839015714752992
  10. 2017-05-06T00:00:00Z 0.6561787179913948
  11. 2017-05-07T00:00:00Z 0.5693131911006619
  12. 2017-05-08T00:00:00Z 0.6240230529767568
  13. 2017-05-09T00:00:00Z 0.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 one field of the numeric data type: of_capacity.

Example 3: Calculate the arc tangents of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: park_occupancy
  3. time atan
  4. ---- ----
  5. 2017-05-07T00:00:00Z 0.5693131911006619
  6. 2017-05-06T00:00:00Z 0.6561787179913948
  7. 2017-05-05T00:00:00Z 0.16839015714752992
  8. 2017-05-04T00:00:00Z 0.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 2017-05-01T00:00:00Z to 2017-05-09T00:00:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Calculate the arc tangents of the average field values for the specified field key

  1. > 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)
  2. name: park_occupancy
  3. time atan
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.5142865412694495
  6. 2017-05-03T00:00:00Z 0.3890972310552784
  7. 2017-05-06T00:00:00Z 0.6174058917515726
  8. 2017-05-09T00:00:00Z 0.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 and the GROUP BY time() clause, and does not use the ATAN() function.

  1. > 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)
  2. name: park_occupancy
  3. time mean
  4. ---- ----
  5. 2017-04-30T00:00:00Z 0.565
  6. 2017-05-03T00:00:00Z 0.41
  7. 2017-05-06T00:00:00Z 0.71
  8. 2017-05-09T00:00:00Z 0.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 ATAN2() functions in conjunction 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:

  1. > SELECT "altitude_ft", "distance_ft" FROM "flight_data" WHERE time >= '2018-05-16T12:01:00Z' AND time <= '2018-05-16T12:10:00Z'
  2. name: flight_data
  3. time altitude_ft distance_ft
  4. ---- ----------- -----------
  5. 2018-05-16T12:01:00Z 1026 50094
  6. 2018-05-16T12:02:00Z 2549 53576
  7. 2018-05-16T12:03:00Z 4033 55208
  8. 2018-05-16T12:04:00Z 5579 58579
  9. 2018-05-16T12:05:00Z 7065 61213
  10. 2018-05-16T12:06:00Z 8589 64807
  11. 2018-05-16T12:07:00Z 10180 67707
  12. 2018-05-16T12:08:00Z 11777 69819
  13. 2018-05-16T12:09:00Z 13321 72452
  14. 2018-05-16T12:10:00Z 14885 75881

Example 1: Calculate 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

  1. > SELECT ATAN2("altitude_ft", "distance_ft") FROM "flight_data" WHERE time >= '2018-05-16T12:01:00Z' AND time <= '2018-05-16T12:10:00Z'
  2. name: flight_data
  3. time atan2
  4. ---- -----
  5. 2018-05-16T12:01:00Z 0.020478631571881498
  6. 2018-05-16T12:02:00Z 0.04754142349303296
  7. 2018-05-16T12:03:00Z 0.07292147724575364
  8. 2018-05-16T12:04:00Z 0.09495251193874832
  9. 2018-05-16T12:05:00Z 0.11490822875441563
  10. 2018-05-16T12:06:00Z 0.13176409347584003
  11. 2018-05-16T12:07:00Z 0.14923587589682233
  12. 2018-05-16T12:08:00Z 0.1671059946640312
  13. 2018-05-16T12:09:00Z 0.18182893717409565
  14. 2018-05-16T12:10:00Z 0.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

  1. > SELECT ATAN2(*, "distance_ft") FROM "flight_data" WHERE time >= '2018-05-16T12:01:00Z' AND time <= '2018-05-16T12:10:00Z'
  2. name: flight_data
  3. time atan2_altitude_ft atan2_distance_ft
  4. ---- ----------------- -----------------
  5. 2018-05-16T12:01:00Z 0.020478631571881498 0.7853981633974483
  6. 2018-05-16T12:02:00Z 0.04754142349303296 0.7853981633974483
  7. 2018-05-16T12:03:00Z 0.07292147724575364 0.7853981633974483
  8. 2018-05-16T12:04:00Z 0.09495251193874832 0.7853981633974483
  9. 2018-05-16T12:05:00Z 0.11490822875441563 0.7853981633974483
  10. 2018-05-16T12:06:00Z 0.13176409347584003 0.7853981633974483
  11. 2018-05-16T12:07:00Z 0.14923587589682233 0.7853981633974483
  12. 2018-05-16T12:08:00Z 0.1671059946640312 0.7853981633974483
  13. 2018-05-16T12:09:00Z 0.18182893717409565 0.7853981633974483
  14. 2018-05-16T12:10:00Z 0.19370286314952234 0.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 two fields of the numeric data type: altitude_ft and distance_ft.

Example 3: Calculate the arc tangents of the values by using a query that includes multiple clauses

  1. > 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
  2. name: flight_data
  3. time atan2
  4. ---- -----
  5. 2018-05-16T12:08:00Z 0.1671059946640312
  6. 2018-05-16T12:07:00Z 0.14923587589682233
  7. 2018-05-16T12:06:00Z 0.13176409347584003
  8. 2018-05-16T12:05:00Z 0.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 2018-05-16T12:10:00Z to 2018-05-16T12:10:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

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

  1. > 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)
  2. name: flight_data
  3. time atan2
  4. ---- -----
  5. 2018-05-16T12:00:00Z 0.133815587896842
  6. 2018-05-16T12:12:00Z 0.2662716308351908
  7. 2018-05-16T12:24:00Z 0.2958845306108965
  8. 2018-05-16T12:36:00Z 0.23783439588429497
  9. 2018-05-16T12:48:00Z 0.1906803720242831
  10. 2018-05-16T13:00:00Z 0.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 and the GROUP BY time() clause, and does not use the ATAN2() function.

  1. > 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)
  2. name: flight_data
  3. time mean mean_1
  4. ---- ---- ------
  5. 2018-05-16T12:00:00Z 8674 64433.181818181816
  6. 2018-05-16T12:12:00Z 26419.833333333332 96865.25
  7. 2018-05-16T12:24:00Z 40337.416666666664 132326.41666666666
  8. 2018-05-16T12:36:00Z 41149.583333333336 169743.16666666666
  9. 2018-05-16T12:48:00Z 41230.416666666664 213600.91666666666
  10. 2018-05-16T13:00:00Z 41184.5 235799

Then, TSDB for InfluxDB® calculates 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.

CEIL()

Rounds each specified field value up to the nearest integer.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types.

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 CEIL() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Round each field value of the specified field key up to the nearest integer

  1. > 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'
  2. name: h2o_feet
  3. time ceil
  4. ---- ----
  5. 2015-08-18T00:00:00Z 3
  6. 2015-08-18T00:06:00Z 3
  7. 2015-08-18T00:12:00Z 3
  8. 2015-08-18T00:18:00Z 3
  9. 2015-08-18T00:24:00Z 3
  10. 2015-08-18T00:30:00Z 3

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

  1. > SELECT CEIL(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time ceil_water_level
  4. ---- ----------------
  5. 2015-08-18T00:00:00Z 3
  6. 2015-08-18T00:06:00Z 3
  7. 2015-08-18T00:12:00Z 3
  8. 2015-08-18T00:18:00Z 3
  9. 2015-08-18T00:24:00Z 3
  10. 2015-08-18T00:30:00Z 3

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 one field of the numeric data type: water_level.

Example 3: Round each field value of the specified field key up to the nearest integer by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time ceil
  4. ---- ----
  5. 2015-08-18T00:18:00Z 3
  6. 2015-08-18T00:12:00Z 3
  7. 2015-08-18T00:06:00Z 3
  8. 2015-08-18T00:00:00Z 3

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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Round each average field value up to the nearest integer

  1. > 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)
  2. name: h2o_feet
  3. time ceil
  4. ---- ----
  5. 2015-08-18T00:00:00Z 3
  6. 2015-08-18T00:12:00Z 3
  7. 2015-08-18T00:24:00Z 3

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 and the GROUP BY time() clause, and does not use the CEIL() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 COS() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the cosines of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time cos
  4. ---- ---
  5. 2015-08-18T00:00:00Z -0.47345017433543124
  6. 2015-08-18T00:06:00Z -0.5185922462666872
  7. 2015-08-18T00:12:00Z -0.4414407189100776
  8. 2015-08-18T00:18:00Z -0.5271163912192579
  9. 2015-08-18T00:24:00Z -0.45306786455514825
  10. 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

  1. > SELECT COS(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time cos_water_level
  4. ---- ---------------
  5. 2015-08-18T00:00:00Z -0.47345017433543124
  6. 2015-08-18T00:06:00Z -0.5185922462666872
  7. 2015-08-18T00:12:00Z -0.4414407189100776
  8. 2015-08-18T00:18:00Z -0.5271163912192579
  9. 2015-08-18T00:24:00Z -0.45306786455514825
  10. 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 one field of the numeric data type: water_level.

Example 3: Calculate the cosines of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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' ORDER BY time DESC LIMIT 4 OFFSET 2
  2. name: h2o_feet
  3. time cos
  4. ---- ---
  5. 2015-08-18T00:18:00Z -0.5271163912192579
  6. 2015-08-18T00:12:00Z -0.4414407189100776
  7. 2015-08-18T00:06:00Z -0.5185922462666872
  8. 2015-08-18T00:00:00Z -0.47345017433543124

This query returns the cosines of the field values for the water_level field key in the h2o_feet measurement. The specified time range is 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the cosines of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time cos
  4. ---- ---
  5. 2015-08-18T00:00:00Z -0.49618891270599885
  6. 2015-08-18T00:12:00Z -0.4848605136571181
  7. 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 and the GROUP BY time() clause, and does not use the COS() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 CUMULATIVE_SUM() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the cumulative sum of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time cumulative_sum
  4. ---- --------------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 4.18
  7. 2015-08-18T00:12:00Z 6.208
  8. 2015-08-18T00:18:00Z 8.334
  9. 2015-08-18T00:24:00Z 10.375
  10. 2015-08-18T00:30:00Z 12.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

  1. > SELECT CUMULATIVE_SUM(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time cumulative_sum_water_level
  4. ---- --------------------------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 4.18
  7. 2015-08-18T00:12:00Z 6.208
  8. 2015-08-18T00:18:00Z 8.334
  9. 2015-08-18T00:24:00Z 10.375
  10. 2015-08-18T00:30:00Z 12.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 one field of the numeric data type: water_level.

Example 3: Calculate the cumulative sum of field values for each field key that matches the specified regular expression

  1. > 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'
  2. name: h2o_feet
  3. time cumulative_sum_water_level
  4. ---- --------------------------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 4.18
  7. 2015-08-18T00:12:00Z 6.208
  8. 2015-08-18T00:18:00Z 8.334
  9. 2015-08-18T00:24:00Z 10.375
  10. 2015-08-18T00:30:00Z 12.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

  1. > 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
  2. name: h2o_feet
  3. time cumulative_sum
  4. ---- --------------
  5. 2015-08-18T00:18:00Z 6.218
  6. 2015-08-18T00:12:00Z 8.246
  7. 2015-08-18T00:06:00Z 10.362
  8. 2015-08-18T00:00:00Z 12.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the cumulative sum of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time cumulative_sum
  4. ---- --------------
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 4.167
  7. 2015-08-18T00:24:00Z 6.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 and the GROUP BY time() clause, but does not use the CUMULATIVE_SUM() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.0460000000000003

Then, TSDB for InfluxDB® calculates the cumulative sum of the average field values. In the final 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

  1. 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 each two field values and converts the results into change rates. The change rates indicates how the field values have changed based on the time granularity specified by the unit parameter. The value of the unit parameter is an integer that is followed by a unit of time. This parameter is optional. If you do not specify the unit parameter in your query, the default value 1s is used for the unit parameter.

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 the field values of INT64 and FLOAT64 data types.

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 DERIVATIVE() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the derivatives between the field values of the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time derivative
  4. ---- ----------
  5. 2015-08-18T00:06:00Z 0.00014444444444444457
  6. 2015-08-18T00:12:00Z -0.00024444444444444465
  7. 2015-08-18T00:18:00Z 0.0002722222222222218
  8. 2015-08-18T00:24:00Z -0.000236111111111111
  9. 2015-08-18T00:30:00Z 2.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 the field values have changed every second.

The first result 0.00014444444444444457 is the change rate for the first two field values in raw data. The change rate indicates how the field values have changed within the 1-second period. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 1-second period.

  1. (2.116 - 2.064) / (360s / 1s)
  2. -------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the default unit
  5. 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

  1. > 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'
  2. name: h2o_feet
  3. time derivative
  4. ---- ----------
  5. 2015-08-18T00:06:00Z 0.052000000000000046
  6. 2015-08-18T00:12:00Z -0.08800000000000008
  7. 2015-08-18T00:18:00Z 0.09799999999999986
  8. 2015-08-18T00:24:00Z -0.08499999999999996
  9. 2015-08-18T00:30:00Z 0.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 the field values have changed every 6 minutes.

The first result 0.052000000000000046 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 6 minutes. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 6 minutes.

  1. (2.116 - 2.064) / (6m / 6m)
  2. -------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the specified unit
  5. 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

  1. > SELECT DERIVATIVE(*,3m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time derivative_water_level
  4. ---- ----------------------
  5. 2015-08-18T00:06:00Z 0.026000000000000023
  6. 2015-08-18T00:12:00Z -0.04400000000000004
  7. 2015-08-18T00:18:00Z 0.04899999999999993
  8. 2015-08-18T00:24:00Z -0.04249999999999998
  9. 2015-08-18T00:30:00Z 0.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 the field values have changed every 3 minutes. The h2o_feet measurement contains only one field of the numeric data type: water_level.

The first result 0.026000000000000023 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 3 minutes. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 3 minutes.

  1. (2.116 - 2.064) / (6m / 3m)
  2. -------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the specified unit
  5. 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

  1. > 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'
  2. name: h2o_feet
  3. time derivative_water_level
  4. ---- ----------------------
  5. 2015-08-18T00:06:00Z 0.01733333333333335
  6. 2015-08-18T00:12:00Z -0.02933333333333336
  7. 2015-08-18T00:18:00Z 0.03266666666666662
  8. 2015-08-18T00:24:00Z -0.02833333333333332
  9. 2015-08-18T00:30:00Z 0.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 the field values have changed every 2 minutes. The h2o_feet measurement contains only one field of the numeric data type: water_level.

The first result 0.01733333333333335 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 2 minutes. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 2 minutes.

  1. (2.116 - 2.064) / (6m / 2m)
  2. -------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the specified unit
  5. 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

  1. > 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
  2. name: h2o_feet
  3. time derivative
  4. ---- ----------
  5. 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 the field values have changed within the 1-second period. The specified time range is 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. The query limits the number of returned points to 1. The point offset is set to 2, which indicates that the first two points are not returned.

The only result -0.0002722222222222218 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 1-second period. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 1-second period.

  1. (2.126 - 2.028) / (360s / 1s)
  2. -------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the default unit
  5. second field value - first field value

Advanced syntax

  1. 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 calculate the derivatives of these results.

The value of the unit parameter is an integer that is followed by a unit of time. This parameter is optional. If you do not specify the unit parameter, the unit parameter is set to the time interval specified by the GROUP BY time() clause by default. Note that 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

  1. > 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)
  2. name: h2o_feet
  3. time derivative
  4. ---- ----------
  5. 2015-08-18T00:12:00Z -0.0129999999999999
  6. 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 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 and the GROUP BY time() clause, and does not use the DERIVATIVE() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.0460000000000003

Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how the field values have changed every 12 minutes. The first result -0.0129999999999999 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 12 minutes. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 12 minutes.

  1. (2.077 - 2.09) / (12m / 12m)
  2. ------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the default unit
  5. second field value - first field value

Example 2: Calculate the derivatives between average field values and specify the unit parameter

  1. > 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)
  2. name: h2o_feet
  3. time derivative
  4. ---- ----------
  5. 2015-08-18T00:12:00Z -0.00649999999999995
  6. 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 and the GROUP BY time() clause, and does not use the DERIVATIVE() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.0460000000000003

Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how the field values have changed every 6 minutes. The first result -0.00649999999999995 is the change rate of the first two field values in raw data. The change rate indicates how the field values have changed within the 6 minutes. TSDB for InfluxDB® calculates the difference between the two field values, and converts this difference to the change rate that indicates how the field values have changed within the 6 minutes.

  1. (2.077 - 2.09) / (12m / 6m)
  2. ------------- ----------
  3. | |
  4. | the difference between the field values' timestamps / the specified unit
  5. second field value - first field value

DIFFERENCE()

Returns the difference between field values.

Basic syntax

  1. 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 matched with the specified regular expression.

DIFFERENCE(*)
Returns the difference between the field values of each field key in the specified measurement.

The DIFFERENCE() function supports the field values of INT64 and FLOAT64 data types.

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 conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the difference between the field values of the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time difference
  4. ---- ----------
  5. 2015-08-18T00:06:00Z 0.052000000000000046
  6. 2015-08-18T00:12:00Z -0.08800000000000008
  7. 2015-08-18T00:18:00Z 0.09799999999999986
  8. 2015-08-18T00:24:00Z -0.08499999999999996
  9. 2015-08-18T00:30:00Z 0.010000000000000231

This query returns the difference between the field values of 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

  1. > SELECT DIFFERENCE(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time difference_water_level
  4. ---- ----------------------
  5. 2015-08-18T00:06:00Z 0.052000000000000046
  6. 2015-08-18T00:12:00Z -0.08800000000000008
  7. 2015-08-18T00:18:00Z 0.09799999999999986
  8. 2015-08-18T00:24:00Z -0.08499999999999996
  9. 2015-08-18T00:30:00Z 0.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 one field of the numeric data type: water_level.

Example 3: Calculate the difference between the field values of each field key that matches the specified regular expression

  1. > SELECT DIFFERENCE(/water/) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time difference_water_level
  4. ---- ----------------------
  5. 2015-08-18T00:06:00Z 0.052000000000000046
  6. 2015-08-18T00:12:00Z -0.08800000000000008
  7. 2015-08-18T00:18:00Z 0.09799999999999986
  8. 2015-08-18T00:24:00Z -0.08499999999999996
  9. 2015-08-18T00:30:00Z 0.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

  1. > 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
  2. name: h2o_feet
  3. time difference
  4. ---- ----------
  5. 2015-08-18T00:12:00Z -0.09799999999999986
  6. 2015-08-18T00:06:00Z 0.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Calculate the difference between the largest values

  1. > 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)
  2. name: h2o_feet
  3. time difference
  4. ---- ----------
  5. 2015-08-18T00:12:00Z 0.009999999999999787
  6. 2015-08-18T00:24:00Z -0.07499999999999973

This query returns the difference between the largest 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 and the GROUP BY time() clause, and does not use the DIFFERENCE() function.

  1. > 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)
  2. name: h2o_feet
  3. time max
  4. ---- ---
  5. 2015-08-18T00:00:00Z 2.116
  6. 2015-08-18T00:12:00Z 2.126
  7. 2015-08-18T00:24:00Z 2.051

Then, TSDB for InfluxDB® calculates the difference between these largest values. In the final 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

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

Syntax description

TSDB for InfluxDB® calculates the difference between timestamps. The value of the unit parameter is an integer that is followed by a unit of time. The parameter value determines the unit that measures the difference between timestamps. 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 the field values of all data types.

Examples

The following data in the NOAA_water_database data set is used in the examples that are provided in this section:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:12:00Z'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028

Example 1: Calculate the time interval between the field values of the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time elapsed
  4. ---- -------
  5. 2015-08-18T00:06:00Z 360000000000
  6. 2015-08-18T00:12:00Z 360000000000

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

  1. > 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'
  2. name: h2o_feet
  3. time elapsed
  4. ---- -------
  5. 2015-08-18T00:06:00Z 6
  6. 2015-08-18T00:12:00Z 6

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

  1. > SELECT ELAPSED(*,1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:12:00Z'
  2. name: h2o_feet
  3. time elapsed_level description elapsed_water_level
  4. ---- ------------------------- -------------------
  5. 2015-08-18T00:06:00Z 6 6
  6. 2015-08-18T00:12:00Z 6 6

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 two field keys: level description and water_level.

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

  1. > 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'
  2. name: h2o_feet
  3. time elapsed_level description elapsed_water_level
  4. ---- ------------------------- -------------------
  5. 2015-08-18T00:06:00Z 360 360
  6. 2015-08-18T00:12:00Z 360 360

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

  1. > 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
  2. name: h2o_feet
  3. time elapsed
  4. ---- -------
  5. 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 2015-08-18T00:00:00Z to 2015-08-18T00:12:00Z. The query results are sorted based on timestamps in descending order. 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.

Note that the query returns a negative value. This is because 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.

ELAPSED() FAQ

Question 1: What are the impacts if the value of the unit parameter in the ELAPSED() function is greater than the difference between the timestamps?

Answer 1: TSDB for InfluxDB® returns 0, if the value of the unit parameter is greater than the difference between the timestamps.

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® returns 0.

  1. > 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'
  2. name: h2o_feet
  3. time elapsed
  4. ---- -------
  5. 2015-08-18T00:06:00Z 0
  6. 2015-08-18T00:12:00Z 0

Question 2: What are the impacts if ELAPSED() functions are used in conjunction with GROUP BY time() clauses in queries?

Answer 2: ELAPSED() functions can be used in conjunction with GROUP BY time() clauses in queries. However, the query results may not be useful. If an ELAPSED() query contains a nested InfluxQL function and a GROUP BY time() clause, the query returns only the time interval 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 original timestamps. Therefore, the ELAPSED() function always returns the same value as the time interval specified by the GROUP BY time() clause.

Example
The first query in the following code block 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 smallest field values of the water_level field key based on a 12-minute interval. The second query in the following code block returns the smallest value for each time interval. This process returns the same result as a query if the query uses the MIN() function and the GROUP BY time() clause, and does not use the ELAPSED() function. Note that 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, but this original timestamp is overwritten by the GROUP BY time() clause. The returned timestamps are determined by the time interval specified by the GROUP BY time() clause instead of 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 specified by the GROUP BY time() clause.

  1. > 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)
  2. name: h2o_feet
  3. time elapsed
  4. ---- -------
  5. 2015-08-18T00:36:00Z 12
  6. 2015-08-18T00:48:00Z 12
  7. > 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)
  8. name: h2o_feet
  9. time min
  10. ---- ---
  11. 2015-08-18T00:36:00Z 2.057 <--- Actually occurs at 2015-08-18T00:42:00Z
  12. 2015-08-18T00:48:00Z 1.991

EXP()

Raises e to the power of each specified field value.

Basic syntax

  1. 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)
Raises e to the power of the field values for the specified field key.

EXP(*)
Raises e to the power of the field values for each field key in the specified measurement.

The EXP() function supports the field values of INT64 and FLOAT64 data types.

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 EXP() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Raise e to the power of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time exp
  4. ---- ---
  5. 2015-08-18T00:00:00Z 7.877416541092307
  6. 2015-08-18T00:06:00Z 8.297879498060171
  7. 2015-08-18T00:12:00Z 7.598873404088091
  8. 2015-08-18T00:18:00Z 8.381274573459967
  9. 2015-08-18T00:24:00Z 7.6983036546645645
  10. 2015-08-18T00:30:00Z 7.775672892658607

The EXP() function in this query raises e to the power of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Raise e to the power of the field values for each field key in the specified measurement

  1. > SELECT EXP(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time exp_water_level
  4. ---- ---------------
  5. 2015-08-18T00:00:00Z 7.877416541092307
  6. 2015-08-18T00:06:00Z 8.297879498060171
  7. 2015-08-18T00:12:00Z 7.598873404088091
  8. 2015-08-18T00:18:00Z 8.381274573459967
  9. 2015-08-18T00:24:00Z 7.6983036546645645
  10. 2015-08-18T00:30:00Z 7.775672892658607

The EXP() function in this query raises e to the power of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only one field of the numeric data type: water_level.

Example 3: Raise e to the power of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time exp
  4. ---- ---
  5. 2015-08-18T00:18:00Z 8.381274573459967
  6. 2015-08-18T00:12:00Z 7.598873404088091
  7. 2015-08-18T00:06:00Z 8.297879498060171
  8. 2015-08-18T00:00:00Z 7.877416541092307

The EXP() function in this query raises e to the power of the field values for the water_level field key in the h2o_feet measurement. The specified time range is 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 specified by the GROUP BY time() clauses. Then, the queries raise e to the power of these results.

EXP() supports the following nested functions:

  • COUNT()
  • MEAN()
  • MEDIAN()
  • MODE()
  • SUM()
  • FIRST()
  • LAST()
  • MIN()
  • MAX()
  • PERCENTILE()

Examples

Example: Raise e to the power of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time exp
  4. ---- ---
  5. 2015-08-18T00:00:00Z 8.084915164305059
  6. 2015-08-18T00:12:00Z 7.980491491670466
  7. 2015-08-18T00:24:00Z 7.736891562315577

The EXP() function in 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 and the GROUP BY time() clause, and does not use the EXP() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.0460000000000003

Then, TSDB for InfluxDB® raises e to the power of each average field value.

FLOOR()

Rounds each specified field value down to the nearest integer.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types.

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 FLOOR() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Round each field value of the specified field key down to the nearest integer

  1. > 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'
  2. name: h2o_feet
  3. time floor
  4. ---- -----
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:06:00Z 2
  7. 2015-08-18T00:12:00Z 2
  8. 2015-08-18T00:18:00Z 2
  9. 2015-08-18T00:24:00Z 2
  10. 2015-08-18T00:30:00Z 2

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

  1. > SELECT FLOOR(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time floor_water_level
  4. ---- -----------------
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:06:00Z 2
  7. 2015-08-18T00:12:00Z 2
  8. 2015-08-18T00:18:00Z 2
  9. 2015-08-18T00:24:00Z 2
  10. 2015-08-18T00:30:00Z 2

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 one field of the numeric data type: water_level.

Example 3: Round each field value of the specified field key down to the nearest integer by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time floor
  4. ---- -----
  5. 2015-08-18T00:18:00Z 2
  6. 2015-08-18T00:12:00Z 2
  7. 2015-08-18T00:06:00Z 2
  8. 2015-08-18T00:00:00Z 2

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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Round each average field value down to the nearest integer

  1. > 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)
  2. name: h2o_feet
  3. time floor
  4. ---- -----
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:12:00Z 2
  7. 2015-08-18T00:24:00Z 2

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 and the GROUP BY time() clause, and does not use the FLOOR() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 LN() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the natural logarithms of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time ln
  4. ---- --
  5. 2015-08-18T00:00:00Z 0.7246458476193163
  6. 2015-08-18T00:06:00Z 0.749527513996053
  7. 2015-08-18T00:12:00Z 0.7070500857289368
  8. 2015-08-18T00:18:00Z 0.7542422799197561
  9. 2015-08-18T00:24:00Z 0.7134398838277077
  10. 2015-08-18T00:30:00Z 0.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

  1. > SELECT LN(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time ln_water_level
  4. ---- --------------
  5. 2015-08-18T00:00:00Z 0.7246458476193163
  6. 2015-08-18T00:06:00Z 0.749527513996053
  7. 2015-08-18T00:12:00Z 0.7070500857289368
  8. 2015-08-18T00:18:00Z 0.7542422799197561
  9. 2015-08-18T00:24:00Z 0.7134398838277077
  10. 2015-08-18T00:30:00Z 0.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 one field of the numeric data type: water_level.

Example 3: Calculate the natural logarithms of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time exp
  4. ---- ---
  5. 2015-08-18T00:18:00Z 8.381274573459967
  6. 2015-08-18T00:12:00Z 7.598873404088091
  7. 2015-08-18T00:06:00Z 8.297879498060171
  8. 2015-08-18T00:00:00Z 7.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the natural logarithms of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time ln
  4. ---- --
  5. 2015-08-18T00:00:00Z 0.7371640659767196
  6. 2015-08-18T00:12:00Z 0.7309245448939752
  7. 2015-08-18T00:24:00Z 0.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 and the GROUP BY time() clause, and does not use the LN() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 LOG() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the base-4 logarithms of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time log
  4. ---- ---
  5. 2015-08-18T00:00:00Z 0.5227214853805835
  6. 2015-08-18T00:06:00Z 0.5406698137259695
  7. 2015-08-18T00:12:00Z 0.5100288261706268
  8. 2015-08-18T00:18:00Z 0.5440707984345088
  9. 2015-08-18T00:24:00Z 0.5146380911853161
  10. 2015-08-18T00:30:00Z 0.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

  1. > SELECT LOG(*, 4) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time log_water_level
  4. ---- ---------------
  5. 2015-08-18T00:00:00Z 0.5227214853805835
  6. 2015-08-18T00:06:00Z 0.5406698137259695
  7. 2015-08-18T00:12:00Z 0.5100288261706268
  8. 2015-08-18T00:18:00Z 0.5440707984345088
  9. 2015-08-18T00:24:00Z 0.5146380911853161
  10. 2015-08-18T00:30:00Z 0.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 one field of the numeric data type: water_level.

Example 3: Calculate the base-4 logarithms of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time log
  4. ---- ---
  5. 2015-08-18T00:18:00Z 0.5440707984345088
  6. 2015-08-18T00:12:00Z 0.5100288261706268
  7. 2015-08-18T00:06:00Z 0.5406698137259695
  8. 2015-08-18T00:00:00Z 0.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the base-4 logarithms of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time log
  4. ---- ---
  5. 2015-08-18T00:00:00Z 0.531751471153079
  6. 2015-08-18T00:12:00Z 0.5272506080912802
  7. 2015-08-18T00:24:00Z 0.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 and the GROUP BY time() clause, and does not use the LOG() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.0460000000000003

Then, TSDB for InfluxDB® calculates the base-4 logarithms of each average field value.

LOG2()

Returns the base-2 logarithms of the field values for the specified field keys.

Basic syntax

  1. 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 the field values of INT64 and FLOAT64 data types.

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 LOG2() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the base-2 logarithms of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time log2
  4. ---- ----
  5. 2015-08-18T00:00:00Z 1.045442970761167
  6. 2015-08-18T00:06:00Z 1.081339627451939
  7. 2015-08-18T00:12:00Z 1.0200576523412537
  8. 2015-08-18T00:18:00Z 1.0881415968690176
  9. 2015-08-18T00:24:00Z 1.0292761823706322
  10. 2015-08-18T00:30:00Z 1.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

  1. > SELECT LOG2(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time log2_water_level
  4. ---- ----------------
  5. 2015-08-18T00:00:00Z 1.045442970761167
  6. 2015-08-18T00:06:00Z 1.081339627451939
  7. 2015-08-18T00:12:00Z 1.0200576523412537
  8. 2015-08-18T00:18:00Z 1.0881415968690176
  9. 2015-08-18T00:24:00Z 1.0292761823706322
  10. 2015-08-18T00:30:00Z 1.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 one field of the numeric data type: water_level.

Example 3: Calculate the base-2 logarithms of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time log2
  4. ---- ----
  5. 2015-08-18T00:18:00Z 1.0881415968690176
  6. 2015-08-18T00:12:00Z 1.0200576523412537
  7. 2015-08-18T00:06:00Z 1.081339627451939
  8. 2015-08-18T00:00:00Z 1.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the base-2 logarithms of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time log2
  4. ---- ----
  5. 2015-08-18T00:00:00Z 1.063502942306158
  6. 2015-08-18T00:12:00Z 1.0545012161825604
  7. 2015-08-18T00:24:00Z 1.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 and the GROUP BY time() clause, and does not use the LOG2() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 LOG10() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the base-10 logarithms of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time log10
  4. ---- -----
  5. 2015-08-18T00:00:00Z 0.3147096929551737
  6. 2015-08-18T00:06:00Z 0.32551566336314813
  7. 2015-08-18T00:12:00Z 0.3070679506612984
  8. 2015-08-18T00:18:00Z 0.32756326018727794
  9. 2015-08-18T00:24:00Z 0.3098430047160705
  10. 2015-08-18T00:30:00Z 0.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

  1. > SELECT LOG10(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time log10_water_level
  4. ---- -----------------
  5. 2015-08-18T00:00:00Z 0.3147096929551737
  6. 2015-08-18T00:06:00Z 0.32551566336314813
  7. 2015-08-18T00:12:00Z 0.3070679506612984
  8. 2015-08-18T00:18:00Z 0.32756326018727794
  9. 2015-08-18T00:24:00Z 0.3098430047160705
  10. 2015-08-18T00:30:00Z 0.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 one field of the numeric data type: water_level.

Example 3: Calculate the base-10 logarithms of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time log10
  4. ---- -----
  5. 2015-08-18T00:18:00Z 0.32756326018727794
  6. 2015-08-18T00:12:00Z 0.3070679506612984
  7. 2015-08-18T00:06:00Z 0.32551566336314813
  8. 2015-08-18T00:00:00Z 0.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Calculate the base-10 logarithms of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time log10
  4. ---- -----
  5. 2015-08-18T00:00:00Z 0.32014628611105395
  6. 2015-08-18T00:12:00Z 0.3174364965350991
  7. 2015-08-18T00:24:00Z 0.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 and the GROUP BY time() clause, and does not use the LOG10() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 MOVING_AVERAGE() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the moving average for each window of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time moving_average
  4. ---- --------------
  5. 2015-08-18T00:06:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.072
  7. 2015-08-18T00:18:00Z 2.077
  8. 2015-08-18T00:24:00Z 2.0835
  9. 2015-08-18T00:30:00Z 2.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

  1. > 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'
  2. name: h2o_feet
  3. time moving_average_water_level
  4. ---- --------------------------
  5. 2015-08-18T00:12:00Z 2.0693333333333332
  6. 2015-08-18T00:18:00Z 2.09
  7. 2015-08-18T00:24:00Z 2.065
  8. 2015-08-18T00:30:00Z 2.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 one field of the numeric data type: water_level.

Example 3: Calculate the moving average for each window of the field values for each field key that matches the specified regular expression

  1. > 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'
  2. name: h2o_feet
  3. time moving_average_water_level
  4. ---- --------------------------
  5. 2015-08-18T00:18:00Z 2.0835
  6. 2015-08-18T00:24:00Z 2.07775
  7. 2015-08-18T00:30:00Z 2.0615

This query returns the moving average for each window of four field values for each sepcified 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

  1. > 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
  2. name: h2o_feet
  3. time moving_average
  4. ---- --------------
  5. 2015-08-18T00:06:00Z 2.072
  6. 2015-08-18T00:00:00Z 2.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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()

Examples

Example: Calculate the moving average of the largest values

  1. > 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)
  2. name: h2o_feet
  3. time moving_average
  4. ---- --------------
  5. 2015-08-18T00:12:00Z 2.121
  6. 2015-08-18T00:24:00Z 2.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 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 and the GROUP BY time() clause, and does not use the MOVING_AVERAGE() function.

  1. > 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)
  2. name: h2o_feet
  3. time max
  4. ---- ---
  5. 2015-08-18T00:00:00Z 2.116
  6. 2015-08-18T00:12:00Z 2.126
  7. 2015-08-18T00:24:00Z 2.051

Then, TSDB for InfluxDB® calculates the moving average for each window of two largest values. In the final query results, the first point 2.121 is the average of the first two largest 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

  1. 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 indicates how the field values have changed based on the time granularity specified by the unit parameter. The value of the unit parameter is an integer that is followed by a unit of time. This parameter is optional. If you do not specify the unit parameter in your query, the default value 1s is used for the unit parameter. The NON_NEGATIVE_DERIVATIVE() function 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 the field values of INT64 and FLOAT64 data types.

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 NON_NEGATIVE_DERIVATIVE() functions in conjunction 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

  1. 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 followed by a unit of time. This parameter is optional. If you do not specify the unit parameter, the unit parameter is set to the time interval specified by the GROUP BY time() clause by default. Note that the default value of the unit parameter in the advanced syntax is different from that of the unit parameter in the basic syntax. The NON_NEGATIVE_DERIVATIVE() function 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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 NON_NEGATIVE_DIFFERENCE() functions in conjunction 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

  1. 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 specified by the GROUP BY time() clauses. Then, the queries calculate the non-negative difference 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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 POW() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Raise each field value of the specified field key to the power of 4

  1. > 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'
  2. name: h2o_feet
  3. time pow
  4. ---- ---
  5. 2015-08-18T00:00:00Z 18.148417929216
  6. 2015-08-18T00:06:00Z 20.047612231936
  7. 2015-08-18T00:12:00Z 16.914992230656004
  8. 2015-08-18T00:18:00Z 20.429279055375993
  9. 2015-08-18T00:24:00Z 17.352898193760993
  10. 2015-08-18T00:30:00Z 17.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

  1. > SELECT POW(*, 4) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time pow_water_level
  4. ---- ---------------
  5. 2015-08-18T00:00:00Z 18.148417929216
  6. 2015-08-18T00:06:00Z 20.047612231936
  7. 2015-08-18T00:12:00Z 16.914992230656004
  8. 2015-08-18T00:18:00Z 20.429279055375993
  9. 2015-08-18T00:24:00Z 17.352898193760993
  10. 2015-08-18T00:30:00Z 17.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 one field of the numeric data type: water_level.

Example 3: Raise each field value of the specified field key to the power of 4 by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time pow
  4. ---- ---
  5. 2015-08-18T00:18:00Z 20.429279055375993
  6. 2015-08-18T00:12:00Z 16.914992230656004
  7. 2015-08-18T00:06:00Z 20.047612231936
  8. 2015-08-18T00:00:00Z 18.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Raise the average field values of the specified field key to the power of 4

  1. > 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)
  2. name: h2o_feet
  3. time pow
  4. ---- ---
  5. 2015-08-18T00:00:00Z 19.08029760999999
  6. 2015-08-18T00:12:00Z 18.609983417041
  7. 2015-08-18T00:24:00Z 17.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 and the GROUP BY time() clause, but does not use the POW() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 ROUND() functions in conjunction 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 is obtained from the sample data.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Round each field value of the specified field key to the nearest integer

  1. > 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'
  2. name: h2o_feet
  3. time round
  4. ---- -----
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:06:00Z 2
  7. 2015-08-18T00:12:00Z 2
  8. 2015-08-18T00:18:00Z 2
  9. 2015-08-18T00:24:00Z 2
  10. 2015-08-18T00:30:00Z 2

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

  1. > SELECT ROUND(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time round_water_level
  4. ---- -----------------
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:06:00Z 2
  7. 2015-08-18T00:12:00Z 2
  8. 2015-08-18T00:18:00Z 2
  9. 2015-08-18T00:24:00Z 2
  10. 2015-08-18T00:30:00Z 2

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 one field of the numeric data type: water_level.

Example 3: Round each field value of the specified field key to the nearest integer by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time round
  4. ---- -----
  5. 2015-08-18T00:18:00Z 2
  6. 2015-08-18T00:12:00Z 2
  7. 2015-08-18T00:06:00Z 2
  8. 2015-08-18T00:00:00Z 2

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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Round each average field value to the nearest integer

  1. > 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)
  2. name: h2o_feet
  3. time round
  4. ---- -----
  5. 2015-08-18T00:00:00Z 2
  6. 2015-08-18T00:12:00Z 2
  7. 2015-08-18T00:24:00Z 2

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 and the GROUP BY time() clause, but does not use the ROUND() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 SIN() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the sines of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time sin
  4. ---- ---
  5. 2015-08-18T00:00:00Z 0.8808206017241819
  6. 2015-08-18T00:06:00Z 0.8550216851706579
  7. 2015-08-18T00:12:00Z 0.8972904165810275
  8. 2015-08-18T00:18:00Z 0.8497930984115993
  9. 2015-08-18T00:24:00Z 0.8914760289023131
  10. 2015-08-18T00:30:00Z 0.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

  1. > SELECT SIN(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time sin_water_level
  4. ---- ---------------
  5. 2015-08-18T00:00:00Z 0.8808206017241819
  6. 2015-08-18T00:06:00Z 0.8550216851706579
  7. 2015-08-18T00:12:00Z 0.8972904165810275
  8. 2015-08-18T00:18:00Z 0.8497930984115993
  9. 2015-08-18T00:24:00Z 0.8914760289023131
  10. 2015-08-18T00:30:00Z 0.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 one field of the numeric data type: water_level.

Example 3: Calculate the sines of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time sin
  4. ---- ---
  5. 2015-08-18T00:18:00Z 0.8497930984115993
  6. 2015-08-18T00:12:00Z 0.8972904165810275
  7. 2015-08-18T00:06:00Z 0.8550216851706579
  8. 2015-08-18T00:00:00Z 0.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. 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 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()

Examples

Example: Calculate the sines of the average field values for the specified field key

  1. > 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)
  2. name: h2o_feet
  3. time sin
  4. ---- ---
  5. 2015-08-18T00:00:00Z 0.8682145834456126
  6. 2015-08-18T00:12:00Z 0.8745914945253902
  7. 2015-08-18T00:24:00Z 0.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 and the GROUP BY time() clause, but does not use the SIN() function.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:00:00Z 2.09
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:24:00Z 2.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

  1. 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 the field values of INT64 and FLOAT64 data types.

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 SQRT() functions in conjunction 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:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 2015-08-18T00:12:00Z 2.028
  8. 2015-08-18T00:18:00Z 2.126
  9. 2015-08-18T00:24:00Z 2.041
  10. 2015-08-18T00:30:00Z 2.051

Example 1: Calculate the square roots of the field values for the specified field key

  1. > 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'
  2. name: h2o_feet
  3. time sqrt
  4. ---- ----
  5. 2015-08-18T00:00:00Z 1.4366627996854378
  6. 2015-08-18T00:06:00Z 1.4546477236774544
  7. 2015-08-18T00:12:00Z 1.4240786495134319
  8. 2015-08-18T00:18:00Z 1.4580809305384939
  9. 2015-08-18T00:24:00Z 1.4286357128393508
  10. 2015-08-18T00:30:00Z 1.4321312788986909

This query returns the square roots of the field values for the water_level field key in the h2o_feet measurement.

Example 2: Calculate the square roots of the field values for each field key in the specified measurement

  1. > SELECT SQRT(*) FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' AND "location" = 'santa_monica'
  2. name: h2o_feet
  3. time sqrt_water_level
  4. ---- ----------------
  5. 2015-08-18T00:00:00Z 1.4366627996854378
  6. 2015-08-18T00:06:00Z 1.4546477236774544
  7. 2015-08-18T00:12:00Z 1.4240786495134319
  8. 2015-08-18T00:18:00Z 1.4580809305384939
  9. 2015-08-18T00:24:00Z 1.4286357128393508
  10. 2015-08-18T00:30:00Z 1.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 one field of the numeric data type: water_level.

Example 3: Calculate the square roots of the field values for the specified field key by using a query that includes multiple clauses

  1. > 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
  2. name: h2o_feet
  3. time sqrt
  4. ---- ----
  5. 2015-08-18T00:18:00Z 1.4580809305384939
  6. 2015-08-18T00:12:00Z 1.4240786495134319
  7. 2015-08-18T00:06:00Z 1.4546477236774544
  8. 2015-08-18T00:00:00Z 1.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 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are sorted based on timestamps in descending order. 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

  1. SELECT SQRT(<function>( [