InfluxQL functions

Aggregate, select, transform, and predict data with InfluxQL functions.

Content

• Aggregations
  • COUNT()
  • DISTINCT()
  • INTEGRAL()
  • MEAN()
  • MEDIAN()
  • MODE()
  • SPREAD()
  • STDDEV()
  • SUM()
• Selectors
  • BOTTOM()
  • FIRST()
  • LAST()
  • MAX()
  • MIN()
  • PERCENTILE()
  • SAMPLE()
  • TOP()
• Transformations
  • ABS()
  • ACOS()
  • ASIN()
  • ATAN()
  • ATAN2()
  • CEIL()
  • COS()
  • CUMULATIVE_SUM()
  • DERIVATIVE()
  • DIFFERENCE()
  • ELAPSED()
  • EXP()
  • FLOOR()
  • HISTOGRAM()
  • LN()
  • LOG()
  • LOG2()
  • LOG10()
  • MOVING_AVERAGE()
  • NON_NEGATIVE_DERIVATIVE()
  • NON_NEGATIVE_DIFFERENCE()
  • POW()
  • ROUND()
  • SIN()
  • SQRT()
  • TAN()
• Predictors
  • HOLT_WINTERS()
• Technical Analysis
  • CHANDE_MOMENTUM_OSCILLATOR()
  • EXPONENTIAL_MOVING_AVERAGE()
  • DOUBLE_EXPONENTIAL_MOVING_AVERAGE()
  • KAUFMANS_EFFICIENCY_RATIO()
  • KAUFMANS_ADAPTIVE_MOVING_AVERAGE()
  • TRIPLE_EXPONENTIAL_MOVING_AVERAGE()
  • TRIPLE_EXPONENTIAL_DERIVATIVE()
  • RELATIVE_STRENGTH_INDEX()
• Other
  • Sample Data
  • General Syntax for Functions
    • Specify Multiple Functions in the SELECT clause
    • Rename the Output Field Key
    • Change the Values Reported for Intervals with no Data
  • Common Issues with Functions

Aggregations

COUNT()

Returns the number of non-null field values.

Syntax

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 Syntax

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

COUNT(field_key)
Returns the number of field values associated with the field key.

COUNT(/regular_expression/)
Returns the number of field values associated with each field key that matches the regular expression.

COUNT(*)
Returns the number of field values associated with each field key in the measurement.

COUNT() supports all field value data types. InfluxQL supports nesting DISTINCT() with COUNT().

Examples

Count the field values associated with a field key

> SELECT COUNT("water_level") FROM "h2o_feet"

name: h2o_feet
time                   count
----                   -----
1970-01-01T00:00:00Z   15258

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

Count the field values associated with each field key in a measurement

> SELECT COUNT(*) FROM "h2o_feet"

name: h2o_feet
time                   count_level description   count_water_level
----                   -----------------------   -----------------
1970-01-01T00:00:00Z   15258                     15258

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

Count the field values associated with each field key that matches a regular expression

> SELECT COUNT(/water/) FROM "h2o_feet"

name: h2o_feet
time                   count_water_level
----                   -----------------
1970-01-01T00:00:00Z   15258

The query returns the number of non-null field values for every field key that contains the word water in the h2o_feet measurement.

Count the field values associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   count
----                   -----
2015-08-17T23:48:00Z   200
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2
2015-08-18T00:36:00Z   2
2015-08-18T00:48:00Z   2

The query returns the number of non-null field values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 200 and limits the number of points and series returned to seven and one.

Count the distinct field values associated with a field key

> SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
----                   -----
1970-01-01T00:00:00Z   4

The query returns the number of unique field values for the level description field key and the h2o_feet measurement.

Common Issues with COUNT()

COUNT() and fill()

Most InfluxQL functions report null values for time intervals with no data, and fill(<fill_option>) replaces that null value with the fill_option. COUNT() reports 0 for time intervals with no data, and fill(<fill_option>) replaces any 0 values with the fill_option.

Example

The first query in the codeblock below does not include fill(). The last time interval has no data so the reported value for that time interval is zero. The second query includes fill(800000); it replaces the zero in the last interval with 800000.

> 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)

name: h2o_feet
time                   count
----                   -----
2015-09-18T21:24:00Z   2
2015-09-18T21:36:00Z   2
2015-09-18T21:48:00Z   0

> 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)

name: h2o_feet
time                   count
----                   -----
2015-09-18T21:24:00Z   2
2015-09-18T21:36:00Z   2
2015-09-18T21:48:00Z   800000

DISTINCT()

Returns the list of unique field values.

Syntax

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 Syntax

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

DISTINCT(field_key)
Returns the unique field values associated with the field key.

DISTINCT() supports all field value data types. InfluxQL supports nesting DISTINCT() with COUNT().

Examples

List the distinct field values associated with a field key

> SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
----                   --------
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

The query returns a tabular list of the unique field values in the level description field key in the h2o_feet measurement.

List the distinct field values associated with each field key in a measurement

> SELECT DISTINCT(*) FROM "h2o_feet"

name: h2o_feet
time                   distinct_level description   distinct_water_level
----                   --------------------------   --------------------
1970-01-01T00:00:00Z   between 6 and 9 feet         8.12
1970-01-01T00:00:00Z   between 3 and 6 feet         8.005
1970-01-01T00:00:00Z   at or greater than 9 feet    7.887
1970-01-01T00:00:00Z   below 3 feet                 7.762
[...]

The query returns a tabular list of the unique field values for each field key in the h2o_feet measurement. The h2o_feet measurement has two field keys: level description and water_level.

List the distinct field values associated with a field key and include several clauses

>  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

name: h2o_feet
tags: location=coyote_creek
time                   distinct
----                   --------
2015-08-18T00:00:00Z   between 6 and 9 feet
2015-08-18T00:12:00Z   between 6 and 9 feet
2015-08-18T00:24:00Z   between 6 and 9 feet
2015-08-18T00:36:00Z   between 6 and 9 feet
2015-08-18T00:48:00Z   between 6 and 9 feet

The query returns a tabular list of the unique field values in the level description field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query also limits the number of series returned to one.

Count the distinct field values associated with a field key

> SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
----                   -----
1970-01-01T00:00:00Z   4

The query returns the number of unique field values in the level description field key and the h2o_feet measurement.

Common Issues with DISTINCT()

DISTINCT() and the INTO clause

Using DISTINCT() with the INTO clause can cause InfluxDB to overwrite points in the destination measurement. DISTINCT() often returns several results with the same timestamp; InfluxDB assumes points with the same series and timestamp are duplicate points and simply overwrites any duplicate point with the most recent point in the destination measurement.

Example

The first query in the codeblock below uses the DISTINCT() function and returns four results. Notice that each result has the same timestamp. The second query adds an INTO clause to the initial query and writes the query results to the distincts measurement. The last query in the code block selects all the data in the distincts measurement.

The last query returns one point because the four initial results are duplicate points; they belong to the same series and have the same timestamp. When the system encounters duplicate points, it simply overwrites the previous point with the most recent point.

>  SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
----                   --------
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

>  SELECT DISTINCT("level description") INTO "distincts" FROM "h2o_feet"

name: result
time                   written
----                   -------
1970-01-01T00:00:00Z   4

> SELECT * FROM "distincts"

name: distincts
time                   distinct
----                   --------
1970-01-01T00:00:00Z   at or greater than 9 feet

INTEGRAL()

Returns the area under the curve for subsequent field values.

Syntax

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]

InfluxDB calculates the area under the curve for subsequent field values and converts those results into the summed area per unit. The unit argument is an integer followed by a duration literal and it is optional. If the query does not specify the unit, the unit defaults to one second (1s).

INTEGRAL(field_key)
Returns the area under the curve for subsequent field values associated with the field key.

INTEGRAL(/regular_expression/)
Returns the area under the curve for subsequent field values associated with each field key that matches the regular expression.

INTEGRAL(*)
Returns the average field value associated with each field key in the measurement.

INTEGRAL() does not support fill(). INTEGRAL() supports int64 and float64 field value data types.

Examples

Examples 1-5 use the following subsample of the NOAA_water_database data:

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

name: h2o_feet
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
2015-08-18T00:12:00Z   2.028
2015-08-18T00:18:00Z   2.126
2015-08-18T00:24:00Z   2.041
2015-08-18T00:30:00Z   2.051

Calculate the integral for the field values associated with a field key

> 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'

name: h2o_feet
time                 integral
----                 --------
1970-01-01T00:00:00Z 3732.66

The query returns the area under the curve (in seconds) for the field values associated with the water_level field key and in the h2o_feet measurement.

Calculate the integral for the field values associated with a field key and specify the unit option

> 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'

name: h2o_feet
time                 integral
----                 --------
1970-01-01T00:00:00Z 62.211

The query returns the area under the curve (in minutes) for the field values associated with the water_level field key and in the h2o_feet measurement.

Calculate the integral for the field values associated with each field key in a measurement and specify the unit option

> SELECT INTEGRAL(*,1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral_water_level
----                 --------------------
1970-01-01T00:00:00Z 62.211

The query returns the area under the curve (in minutes) for the field values associated with each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has on numerical field: water_level.

Calculate the integral for the field values associated with each field key that matches a regular expression and specify the unit option

> 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'

name: h2o_feet
time                 integral_water_level
----                 --------------------
1970-01-01T00:00:00Z 62.211

The query returns the area under the curve (in minutes) for the field values associated with each field key that stores numerical values includes the word water in the h2o_feet measurement.

Calculate the integral for the field values associated with a field key and include several clauses

> 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

name: h2o_feet
time                 integral
----                 --------
2015-08-18T00:00:00Z 24.972

The query returns the area under the curve (in minutes) for the field values associated with the water_level field key and in the h2o_feet measurement. It covers the time range between 2015-08-18T00:00:00Z and 2015-08-18T00:30:00Z, groups results into 12-minute intervals, and limits the number of results returned to one.

MEAN()

Returns the arithmetic mean (average) of field values.

Syntax

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]

MEAN(field_key)
Returns the average field value associated with the field key.

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

MEAN(*)
Returns the average field value associated with each field key in the measurement.

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

Examples

Calculate the mean field value associated with a field key

> SELECT MEAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   mean
----                   ----
1970-01-01T00:00:00Z   4.442107025822522

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

Calculate the mean field value associated with each field key in a measurement

> SELECT MEAN(*) FROM "h2o_feet"

name: h2o_feet
time                   mean_water_level
----                   ----------------
1970-01-01T00:00:00Z   4.442107025822522

The query returns the average field value for every field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Calculate the mean field value associated with each field key that matches a regular expression

> SELECT MEAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mean_water_level
----                   ----------------
1970-01-01T00:00:00Z   4.442107025822523

The query returns the average field value for each field key that stores numerical values and includes the word water in the h2o_feet measurement.

Calculate the mean field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.0625
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:24:00Z   7.5675
2015-08-18T00:36:00Z   7.303
2015-08-18T00:48:00Z   7.046

The query returns the average of the values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 9.01 and limits the number of points and series returned to seven and one.

MEDIAN()

Returns the middle value from a sorted list of field values.

Syntax

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]

MEDIAN(field_key)
Returns the middle field value associated with the field key.

MEDIAN(/regular_expression/)
Returns the middle field value associated with each field key that matches the regular expression.

MEDIAN(*)
Returns the middle field value associated with each field key in the measurement.

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

Note: MEDIAN() is nearly equivalent to PERCENTILE(field_key, 50), except MEDIAN() returns the average of the two middle field values if the field contains an even number of values.

Examples

Calculate the median field value associated with a field key

> SELECT MEDIAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   median
----                   ------
1970-01-01T00:00:00Z   4.124

The query returns the middle field value in the water_level field key and in the h2o_feet measurement.

Calculate the median field value associated with each field key in a measurement

> SELECT MEDIAN(*) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----                   ------------------
1970-01-01T00:00:00Z   4.124

The query returns the middle field value for every field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Calculate the median field value associated with each field key that matches a regular expression

> SELECT MEDIAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----                   ------------------
1970-01-01T00:00:00Z   4.124

The query returns the middle field value for every field key that stores numerical values and includes the word water in the h2o_feet measurement.

Calculate the median field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=santa_monica
time                   median
----                   ------
2015-08-17T23:48:00Z   700
2015-08-18T00:00:00Z   2.09
2015-08-18T00:12:00Z   2.077
2015-08-18T00:24:00Z   2.0460000000000003
2015-08-18T00:36:00Z   2.0620000000000003
2015-08-18T00:48:00Z   700

The query returns the middle field value in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 700 , limits the number of points and series returned to seven and one, and offsets the series returned by one.

MODE()

Returns the most frequent value in a list of field values.

Syntax

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]

MODE(field_key)
Returns the most frequent field value associated with the field key.

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

MODE(*)
Returns the most frequent field value associated with each field key in the measurement.

MODE() supports all field value data types.

Note: MODE() returns the field value with the earliest timestamp if there’s a tie between two or more values for the maximum number of occurrences.

Examples

Calculate the mode field value associated with a field key

> SELECT MODE("level description") FROM "h2o_feet"

name: h2o_feet
time                   mode
----                   ----
1970-01-01T00:00:00Z   between 3 and 6 feet

The query returns the most frequent field value in the level description field key and in the h2o_feet measurement.

Calculate the mode field value associated with each field key in a measurement

> SELECT MODE(*) FROM "h2o_feet"

name: h2o_feet
time                   mode_level description   mode_water_level
----                   ----------------------   ----------------
1970-01-01T00:00:00Z   between 3 and 6 feet     2.69

The query returns the most frequent field value for every field key in the h2o_feet measurement. The h2o_feet measurement has two field keys: level description and water_level.

Calculate the mode field value associated with each field key that matches a regular expression

> SELECT MODE(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mode_water_level
----                   ----------------
1970-01-01T00:00:00Z   2.69

The query returns the most frequent field value for every field key that includes the word /water/ in the h2o_feet measurement.

Calculate the mode field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=santa_monica
time                   mode
----                   ----
2015-08-17T23:48:00Z
2015-08-18T00:00:00Z   below 3 feet
2015-08-18T00:12:00Z   below 3 feet

The query returns the mode of the values associated with the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query limits the number of points and series returned to three and one, and it offsets the series returned by one.

SPREAD()

Returns the difference between the minimum and maximum field values.

Syntax

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]

SPREAD(field_key)
Returns the difference between the minimum and maximum field values associated with the field key.

SPREAD(/regular_expression/)
Returns the difference between the minimum and maximum field values associated with each field key that matches the regular expression.

SPREAD(*)
Returns the difference between the minimum and maximum field values associated with each field key in the measurement.

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

Examples

Calculate the spread for the field values associated with a field key

> SELECT SPREAD("water_level") FROM "h2o_feet"

name: h2o_feet
time                   spread
----                   ------
1970-01-01T00:00:00Z   10.574

The query returns the difference between the minimum and maximum field values in the water_level field key and in the h2o_feet measurement.

Calculate the spread for the field values associated with each field key in a measurement

> SELECT SPREAD(*) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----                   ------------------
1970-01-01T00:00:00Z   10.574

The query returns the difference between the minimum and maximum field values for every field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Calculate the spread for the field values associated with each field key that matches a regular expression

> SELECT SPREAD(/water/) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----                   ------------------
1970-01-01T00:00:00Z   10.574

The query returns the difference between the minimum and maximum field values for every field key that stores numerical values and includes the word water in the h2o_feet measurement.

Calculate the spread for the field values associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=santa_monica
time                   spread
----                   ------
2015-08-17T23:48:00Z   18
2015-08-18T00:00:00Z   0.052000000000000046
2015-08-18T00:12:00Z   0.09799999999999986

The query returns the difference between the minimum and maximum field values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 18, limits the number of points and series returned to three and one, and offsets the series returned by one.

STDDEV()

Returns the standard deviation of field values.

Syntax

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]

STDDEV(field_key)
Returns the standard deviation of field values associated with the field key.

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

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

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

Examples

Calculate the standard deviation for the field values associated with a field key

> SELECT STDDEV("water_level") FROM "h2o_feet"

name: h2o_feet
time                   stddev
----                   ------
1970-01-01T00:00:00Z   2.279144584196141

The query returns the standard deviation of the field values in the water_level field key and in the h2o_feet measurement.

Calculate the standard deviation for the field values associated with each field key in a measurement

> SELECT STDDEV(*) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----                   ------------------
1970-01-01T00:00:00Z   2.279144584196141

The query returns the standard deviation of the field values for each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Calculate the standard deviation for the field values associated with each field key that matches a regular expression

> SELECT STDDEV(/water/) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----                   ------------------
1970-01-01T00:00:00Z   2.279144584196141

The query returns the standard deviation of the field values for each field key that stores numerical values and includes the word water in the h2o_feet measurement.

Calculate the standard deviation for the field values associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=santa_monica
time                   stddev
----                   ------
2015-08-17T23:48:00Z   18000
2015-08-18T00:00:00Z   0.03676955262170051

The query returns the standard deviation of the field values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 18000, limits the number of points and series returned to two and one, and offsets the series returned by one.

SUM()

Returns the sum of field values.

Syntax

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]

SUM(field_key)
Returns the sum of field values associated with the field key.

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

SUM(*)
Returns the sums of field values associated with each field key in the measurement.

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

Examples

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

> SELECT SUM("water_level") FROM "h2o_feet"

name: h2o_feet
time                   sum
----                   ---
1970-01-01T00:00:00Z   67777.66900000004

The query returns the summed total of the field values in the water_level field key and in the h2o_feet measurement.

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

> SELECT SUM(*) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
----                   ---------------
1970-01-01T00:00:00Z   67777.66900000004

The query returns the summed total of the field values for each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Calculate the sum of the field values associated with each field key that matches a regular expression

> SELECT SUM(/water/) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
----                   ---------------
1970-01-01T00:00:00Z   67777.66900000004

The query returns the summed total of the field values for each field key that stores numerical values and includes the word water in the h2o_feet measurement.

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

> 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

name: h2o_feet
tags: location=coyote_creek
time                   sum
----                   ---
2015-08-17T23:48:00Z   18000
2015-08-18T00:00:00Z   16.125
2015-08-18T00:12:00Z   15.649
2015-08-18T00:24:00Z   15.135

The query returns the summed total of the field values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 18000, and it limits the number of points and series returned to four and one.

Selectors

BOTTOM()

Returns the smallest N field values.

Syntax

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]

BOTTOM(field_key,N)
Returns the smallest N field values associated with the field key.

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

BOTTOM(field_key,N),tag_key(s),field_key(s)
Returns the smallest N field values associated with the field key in the parentheses and the relevant tag and/or field.

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

Notes:

• BOTTOM() returns the field value with the earliest timestamp if there’s a tie between two or more values for the smallest value.
• BOTTOM() differs from other InfluxQL functions when combined with an INTO clause. See the Common Issues section for more information.

Examples

Select the bottom three field values associated with a field key

> SELECT BOTTOM("water_level",3) FROM "h2o_feet"

name: h2o_feet
time                   bottom
----                   ------
2015-08-29T14:30:00Z   -0.61
2015-08-29T14:36:00Z   -0.591
2015-08-30T15:18:00Z   -0.594

The query returns the smallest three field values in the water_level field key and in the h2o_feet measurement.

Select the bottom field value associated with a field key for two tags

> SELECT BOTTOM("water_level","location",2) FROM "h2o_feet"

name: h2o_feet
time                   bottom   location
----                   ------   --------
2015-08-29T10:36:00Z   -0.243   santa_monica
2015-08-29T14:30:00Z   -0.61    coyote_creek

The query returns the smallest field values in the water_level field key for two tag values associated with the location tag key.

Select the bottom four field values associated with a field key and the relevant tags and fields

> SELECT BOTTOM("water_level",4),"location","level description" FROM "h2o_feet"

name: h2o_feet
time                  bottom  location      level description
----                  ------  --------      -----------------
2015-08-29T14:24:00Z  -0.587  coyote_creek  below 3 feet
2015-08-29T14:30:00Z  -0.61   coyote_creek  below 3 feet
2015-08-29T14:36:00Z  -0.591  coyote_creek  below 3 feet
2015-08-30T15:18:00Z  -0.594  coyote_creek  below 3 feet

The query returns the smallest four field values in the water_level field key and the relevant values of the location tag key and the level description field key.

Select the bottom three field values associated with a field key and include several clauses

> 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

name: h2o_feet
time                  bottom  location
----                  ------  --------
2015-08-18T00:48:00Z  1.991   santa_monica
2015-08-18T00:54:00Z  2.054   santa_monica
2015-08-18T00:54:00Z  6.982   coyote_creek
2015-08-18T00:24:00Z  2.041   santa_monica
2015-08-18T00:30:00Z  2.051   santa_monica
2015-08-18T00:42:00Z  2.057   santa_monica
2015-08-18T00:00:00Z  2.064   santa_monica
2015-08-18T00:06:00Z  2.116   santa_monica
2015-08-18T00:12:00Z  2.028   santa_monica

The query returns the smallest three values in the water_level field key for each 24-minute interval between 2015-08-18T00:00:00Z and 2015-08-18T00:54:00Z. It also returns results in descending timestamp order.

Notice that the GROUP BY time() clause does not override the points’ original timestamps. See Issue 1 in the section below for a more detailed explanation of that behavior.

Common Issues with BOTTOM()

BOTTOM() with a GROUP BY time() clause

Queries with BOTTOM() and a GROUP BY time() clause return the specified number of points per GROUP BY time() interval. For most GROUP BY time() queries, the returned timestamps mark the start of the GROUP BY time() interval. GROUP BY time() queries with the BOTTOM() function behave differently; they maintain the timestamp of the original data point.

Example

The query below returns two points per 18-minute GROUP BY time() interval. Notice that the returned timestamps are the points' original timestamps; they are not forced to match the start of the GROUP BY time() intervals.

> 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)

name: h2o_feet
time                   bottom
----                   ------
                           __
2015-08-18T00:00:00Z  2.064 |
2015-08-18T00:12:00Z  2.028 | <------- Smallest points for the first time interval
                           --
                           __
2015-08-18T00:24:00Z  2.041 |
2015-08-18T00:30:00Z  2.051 | <------- Smallest points for the second time interval                      --

BOTTOM() and a tag key with fewer than N tag values

Queries with the syntax SELECT BOTTOM(<field_key>,<tag_key>,<N>) can return fewer points than expected. If the tag key has X tag values, the query specifies N values, and X is smaller than N, then the query returns X points.

Example

The query below asks for the smallest field values of water_level for three tag values of the location tag key. Because the location tag key has two tag values (santa_monica and coyote_creek), the query returns two points instead of three.

> SELECT BOTTOM("water_level","location",3) FROM "h2o_feet"

name: h2o_feet
time                   bottom   location
----                   ------   --------
2015-08-29T10:36:00Z   -0.243   santa_monica
2015-08-29T14:30:00Z   -0.61    coyote_creek

BOTTOM(), tags, and the INTO clause

When combined with an INTO clause and no GROUP BY tag clause, most InfluxQL functions convert any tags in the initial data to fields in the newly written data. This behavior also applies to the BOTTOM() function unless BOTTOM() includes a tag key as an argument: BOTTOM(field_key,tag_key(s),N). In those cases, the system preserves the specified tag as a tag in the newly written data.

Example

The first query in the codeblock below returns the smallest field values in the water_level field key for two tag values associated with the location tag key. It also writes those results to the bottom_water_levels measurement.

The second query shows that InfluxDB preserved the location tag as a tag in the bottom_water_levels measurement.

> SELECT BOTTOM("water_level","location",2) INTO "bottom_water_levels" FROM "h2o_feet"

name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 2

> SHOW TAG KEYS FROM "bottom_water_levels"

name: bottom_water_levels
tagKey
------
location

FIRST()

Returns the field value with the oldest timestamp.

Syntax

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]

FIRST(field_key)
Returns the oldest field value (determined by timestamp) associated with the field key.

FIRST(/regular_expression/)
Returns the oldest field value (determined by timestamp) associated with each field key that matches the regular expression.

FIRST(*)
Returns the oldest field value (determined by timestamp) associated with each field key in the measurement.

FIRST(field_key),tag_key(s),field_key(s)
Returns the oldest field value (determined by timestamp) associated with the field key in the parentheses and the relevant tag and/or field.

FIRST() supports all field value data types.

Examples

Select the first field value associated with a field key

> SELECT FIRST("level description") FROM "h2o_feet"

name: h2o_feet
time                   first
----                   -----
2015-08-18T00:00:00Z   between 6 and 9 feet

The query returns the oldest field value (determined by timestamp) associated with the level description field key and in the h2o_feet measurement.

Select the first field value associated with each field key in a measurement

> SELECT FIRST(*) FROM "h2o_feet"

name: h2o_feet
time                   first_level description   first_water_level
----                   -----------------------   -----------------
1970-01-01T00:00:00Z   between 6 and 9 feet      8.12

The query returns the oldest field value (determined by timestamp) for each field key in the h2o_feet measurement. The h2o_feet measurement has two field keys: level description and water_level.

Select the first field value associated with each field key that matches a regular expression

> SELECT FIRST(/level/) FROM "h2o_feet"

name: h2o_feet
time                   first_level description   first_water_level
----                   -----------------------   -----------------
1970-01-01T00:00:00Z   between 6 and 9 feet      8.12

The query returns the oldest field value for each field key that includes the word level in the h2o_feet measurement.

Select the first value associated with a field key and the relevant tags and fields

> SELECT FIRST("level description"),"location","water_level" FROM "h2o_feet"

name: h2o_feet
time                  first                 location      water_level
----                  -----                 --------      -----------
2015-08-18T00:00:00Z  between 6 and 9 feet  coyote_creek  8.12

The query returns the oldest field value (determined by timestamp) in the level description field key and the relevant values of the location tag key and the water_level field key.

Select the first field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   first
----                   -----
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.12
2015-08-18T00:12:00Z   7.887
2015-08-18T00:24:00Z   7.635

The query returns the oldest field value (determined by timestamp) in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 9.01, and it limits the number of points and series returned to four and one.

Notice that the GROUP BY time() clause overrides the points' original timestamps. The timestamps in the results indicate the the start of each 12-minute time interval; the first point in the results covers the time interval between 2015-08-17T23:48:00Z and just before 2015-08-18T00:00:00Z and the last point in the results covers the time interval between 2015-08-18T00:24:00Z and just before 2015-08-18T00:36:00Z.

LAST()

Returns the field value with the most recent timestamp.

Syntax

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]

LAST(field_key)
Returns the newest field value (determined by timestamp) associated with the field key.

LAST(/regular_expression/)
Returns the newest field value (determined by timestamp) associated with each field key that matches the regular expression.

LAST(*)
Returns the newest field value (determined by timestamp) associated with each field key in the measurement.

LAST(field_key),tag_key(s),field_key(s)
Returns the newest field value (determined by timestamp) associated with the field key in the parentheses and the relevant tag and/or field.

LAST() supports all field value data types.

Examples

Select the last field values associated with a field key

> SELECT LAST("level description") FROM "h2o_feet"

name: h2o_feet
time                   last
----                   ----
2015-09-18T21:42:00Z   between 3 and 6 feet

The query returns the newest field value (determined by timestamp) associated with the level description field key and in the h2o_feet measurement.

Select the last field values associated with each field key in a measurement

> SELECT LAST(*) FROM "h2o_feet"

name: h2o_feet
time                   last_level description   last_water_level
----                   -----------------------   -----------------
1970-01-01T00:00:00Z   between 3 and 6 feet      4.938

The query returns the newest field value (determined by timestamp) for each field key in the h2o_feet measurement. The h2o_feet measurement has two field keys: level description and water_level.

Select the last field value associated with each field key that matches a regular expression

> SELECT LAST(/level/) FROM "h2o_feet"

name: h2o_feet
time                   last_level description   last_water_level
----                   -----------------------   -----------------
1970-01-01T00:00:00Z   between 3 and 6 feet      4.938

The query returns the newest field value for each field key that includes the word level in the h2o_feet measurement.

Select the last field value associated with a field key and the relevant tags and fields

> SELECT LAST("level description"),"location","water_level" FROM "h2o_feet"

name: h2o_feet
time                  last                  location      water_level
----                  ----                  --------      -----------
2015-09-18T21:42:00Z  between 3 and 6 feet  santa_monica  4.938

The query returns the newest field value (determined by timestamp) in the level description field key and the relevant values of the location tag key and the water_level field key.

Select the last field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   last
----                   ----
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.005
2015-08-18T00:12:00Z   7.762
2015-08-18T00:24:00Z   7.5

The query returns the newest field value (determined by timestamp) in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 12-minute time intervals and per tag. The query fills empty time intervals with 9.01, and it limits the number of points and series returned to four and one.

Notice that the GROUP BY time() clause overrides the points' original timestamps. The timestamps in the results indicate the the start of each 12-minute time interval; the first point in the results covers the time interval between 2015-08-17T23:48:00Z and just before 2015-08-18T00:00:00Z and the last point in the results covers the time interval between 2015-08-18T00:24:00Z and just before 2015-08-18T00:36:00Z.

MAX()

Returns the greatest field value.

Syntax

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]

MAX(field_key)
Returns the greatest field value associated with the field key.

MAX(/regular_expression/)
Returns the greatest field value associated with each field key that matches the regular expression.

MAX(*)
Returns the greatest field value associated with each field key in the measurement.

MAX(field_key),tag_key(s),field_key(s)
Returns the greatest field value associated with the field key in the parentheses and the relevant tag and/or field.

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

Examples

Select the maximum field value associated with a field key

> SELECT MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                   max
----                   ---
2015-08-29T07:24:00Z   9.964

The query returns the greatest field value in the water_level field key and in the h2o_feet measurement.

Select the maximum field value associated with each field key in a measurement

> SELECT MAX(*) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
----                   ---------------
2015-08-29T07:24:00Z   9.964

The query returns the greatest field value for each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Select the maximum field value associated with each field key that matches a regular expression

> SELECT MAX(/level/) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
----                   ---------------
2015-08-29T07:24:00Z   9.964

The query returns the greatest field value for each field key that stores numerical values and includes the word water in the h2o_feet measurement.

Select the maximum field value associated with a field key and the relevant tags and fields

> SELECT MAX("water_level"),"location","level description" FROM "h2o_feet"

name: h2o_feet
time                  max    location      level description
----                  ---    --------      -----------------
2015-08-29T07:24:00Z  9.964  coyote_creek  at or greater than 9 feet

The query returns the greatest field value in the water_level field key and the relevant values of the location tag key and the level description field key.

Select the maximum field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   max
----                   ---
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.12
2015-08-18T00:12:00Z   7.887
2015-08-18T00:24:00Z   7.635

The query returns the greatest field value in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results in to 12-minute time intervals and per tag. The query fills empty time intervals with 9.01, and it limits the number of points and series returned to four and one.

Notice that the GROUP BY time() clause overrides the points’ original timestamps. The timestamps in the results indicate the the start of each 12-minute time interval; the first point in the results covers the time interval between 2015-08-17T23:48:00Z and just before 2015-08-18T00:00:00Z and the last point in the results covers the time interval between 2015-08-18T00:24:00Z and just before 2015-08-18T00:36:00Z.

MIN()

Returns the lowest field value.

Syntax

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]

MIN(field_key)
Returns the lowest field value associated with the field key.

MIN(/regular_expression/)
Returns the lowest field value associated with each field key that matches the regular expression.

MIN(*)
Returns the lowest field value associated with each field key in the measurement.

MIN(field_key),tag_key(s),field_key(s)
Returns the lowest field value associated with the field key in the parentheses and the relevant tag and/or field.

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

Examples

Select the minimum field value associated with a field key

> SELECT MIN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   min
----                   ---
2015-08-29T14:30:00Z   -0.61

The query returns the lowest field value in the water_level field key and in the h2o_feet measurement.

Select the minimum field value associated with each field key in a measurement

> SELECT MIN(*) FROM "h2o_feet"

name: h2o_feet
time                   min_water_level
----                   ---------------
2015-08-29T14:30:00Z   -0.61

The query returns the lowest field value for each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Select the minimum field value associated with each field key that matches a regular expression

> SELECT MIN(/level/) FROM "h2o_feet"

name: h2o_feet
time                   min_water_level
----                   ---------------
2015-08-29T14:30:00Z   -0.61

The query returns the lowest field value for each field key that stores numerical values and includes the word water in the h2o_feet measurement.

Select the minimum field value associated with a field key and the relevant tags and fields

> SELECT MIN("water_level"),"location","level description" FROM "h2o_feet"

name: h2o_feet
time                  min    location      level description
----                  ---    --------      -----------------
2015-08-29T14:30:00Z  -0.61  coyote_creek  below 3 feet

The query returns the lowest field value in the water_level field key and the relevant values of the location tag key and the level description field key.

Select the minimum field value associated with a field key and include several clauses

> 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

name: h2o_feet
tags: location=coyote_creek
time                   min
----                   ---
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.005
2015-08-18T00:12:00Z   7.762
2015-08-18T00:24:00Z   7.5

The query returns the lowest field value in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results in to 12-minute time intervals and per tag. The query fills empty time intervals with 9.01, and it limits the number of points and series returned to four and one.

Notice that the GROUP BY time() clause overrides the points’ original timestamps. The timestamps in the results indicate the the start of each 12-minute time interval; the first point in the results covers the time interval between 2015-08-17T23:48:00Z and just before 2015-08-18T00:00:00Z and the last point in the results covers the time interval between 2015-08-18T00:24:00Z and just before 2015-08-18T00:36:00Z.

PERCENTILE()

Returns the Nth percentile field value.

Syntax

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]

PERCENTILE(field_key,N)
Returns the Nth percentile field value associated with the field key.

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

PERCENTILE(*,N)
Returns the Nth percentile field value associated with each field key in the measurement.

PERCENTILE(field_key,N),tag_key(s),field_key(s)
Returns the Nth percentile field value associated with the field key in the parentheses and the relevant tag and/or field.

N must be an integer or floating point number between 0 and 100, inclusive. PERCENTILE() supports int64 and float64 field value data types.

Examples

Select the fifth percentile field value associated with a field key

> SELECT PERCENTILE("water_level",5) FROM "h2o_feet"

name: h2o_feet
time                   percentile
----                   ----------
2015-08-31T03:42:00Z   1.122

The query returns the field value that is larger than five percent of the field values in the water_level field key and in the h2o_feet measurement.

Select the fifth percentile field value associated with each field key in a measurement

> SELECT PERCENTILE(*,5) FROM "h2o_feet"

name: h2o_feet
time                   percentile_water_level
----                   ----------------------
2015-08-31T03:42:00Z   1.122

The query returns the field value that is larger than five percent of the field values in each field key that stores numerical values in the h2o_feet measurement. The h2o_feet measurement has one numerical field: water_level.

Select fifth percentile field value associated with each field key that matches a regular expression

> SELECT PERCENTILE(/level/,5) FROM "h2o_feet"

name: h2o_feet
time                   percentile_water_level
----                   ----------------------
2015-08-31T03:42:00Z   1.122

The query returns the field value that is larger than five percent of the field values in each field key that stores numerical values and includes the word water in the h2o_feet measurement.

Select the fifth percentile field values associated with a field key and the relevant tags and fields

> SELECT PERCENTILE("water_level",5),"location","level description" FROM "h2o_feet"

name: h2o_feet
time                  percentile  location      level description
----                  ----------  --------      -----------------
2015-08-31T03:42:00Z  1.122       coyote_creek  below 3 feet

The query returns the field value that is larger than five percent of the field values in the water_level field key and the relevant values of the location tag key and the level description field key.

Select the twentieth percentile field value associated with a field key and include several clauses

> 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

name: h2o_feet
time                   percentile
----                   ----------
2015-08-17T23:36:00Z   15
2015-08-18T00:00:00Z   2.064

The query returns the field value that is larger than 20 percent of the values in the water_level field key. It covers the time range between 2015-08-17T23:48:00Z and 2015-08-18T00:54:00Z and groups results into 24-minute intervals. It fills empty time intervals with 15 and it limits the number of points returned to two.

Notice that the GROUP BY time() clause overrides the points’ original timestamps. The timestamps in the results indicate the the start of each 24-minute time interval; the first point in the results covers the time interval between 2015-08-17T23:36:00Z and just before 2015-08-18T00:00:00Z and the last point in the results covers the time interval between 2015-08-18T00:00:00Z and just before 2015-08-18T00:24:00Z.

Common Issues with PERCENTILE()

PERCENTILE() compared to other InfluxQL functions

• PERCENTILE(<field_key>,100) is equivalent to MAX(<field_key>).
• PERCENTILE(<field_key>, 50) is nearly equivalent to MEDIAN(<field_key>), except the MEDIAN() function returns the average of the two middle values if the field key contains an even number of field values.
• PERCENTILE(<field_key>,0) is not equivalent to MIN(<field_key>). This is a known issue.

SAMPLE()

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

Syntax

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]

SAMPLE(field_key,N)
Returns N randomly selected field values associated with the field key.

SAMPLE(/regular_expression/,N)
Returns N randomly selected field values associated with each field key that matches the regular expression.

SAMPLE(*,N)
Returns N randomly selected field values associated with each field key in the measurement.

SAMPLE(field_key,N),tag_key(s),field_key(s)
Returns N randomly selected field values associated with the field key in the parentheses and the relevant tag and/or field.

N must be an integer. SAMPLE() supports all field value data types.

Examples

Select a sample of the field values associated with a field key

> SELECT SAMPLE("water_level",2) FROM "h2o_feet"

name: h2o_feet
time                   sample
----                   ------
2015-09-09T21:48:00Z   5.659
2015-09-18T10:00:00Z   6.939

The query returns two randomly selected points from the water_level field key and in the h2o_feet measurement.

Select a sample of the field values associated with each field key in a measurement

> SELECT SAMPLE(*,2) FROM "h2o_feet"

name: h2o_feet
time                   sample_level description   sample_water_level
----                   ------------------------   ------------------
2015-08-25T17:06:00Z                              3.284
2015-09-03T04:30:00Z   below 3 feet
2015-09-03T20:06:00Z   between 3 and 6 feet
2015-09-08T21:54:00Z                              3.412

The query returns two randomly selected points for each field key in the h2o_feet measurement. The h2o_feet measurement has two field keys: level description and water_level.

Select a sample of the field values associated with each field key that matches a regular expression

> SELECT SAMPLE(/level/,2) FROM "h2o_feet"

name: h2o_feet
time                   sample_level description   sample_water_level
----                   ------------------------   ------------------
2015-08-30T05:54:00Z   between 6 and 9 feet
2015-09-07T01:18:00Z                              7.854
2015-09-09T20:30:00Z                              7.32
2015-09-13T19:18:00Z   between 3 and 6 feet

The query returns two randomly selected points for each field key that includes the word level in the h2o_feet measurement.

Select a sample of the field values associated with a field key and the relevant tags and fields

> SELECT SAMPLE("water_level",2),"location","level description" FROM "h2o_feet"

name: h2o_feet
time                  sample  location      level description
----                  ------  --------      -----------------
2015-08-29T10:54:00Z  5.689   coyote_creek  between 3 and 6 feet
2015-09-08T15:48:00Z  6.391   coyote_creek  between 6 and 9 feet

The query returns two randomly selected points from the water_level field key and the relevant values of the location tag and the level description field.

Select a sample of the field values associated with a field key and include several clauses

> 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)

name: h2o_feet
time                   sample
----                   ------
2015-08-18T00:12:00Z   2.028
2015-08-18T00:30:00Z   2.051

The query returns one randomly selected point from the water_level field key. It covers the time range between 2015-08-18T00:00:00Z and 2015-08-18T00:30:00Z and groups results into 18-minute intervals.

Notice that the GROUP BY time() clause does not override the points' original timestamps. See Issue 1 in the section below for a more detailed explanation of that behavior.

Common Issues with SAMPLE()

SAMPLE() with a GROUP BY time() clause

Queries with SAMPLE() and a GROUP BY time() clause return the specified number of points (N) per GROUP BY time() interval. For most GROUP BY time() queries, the returned timestamps mark the start of the GROUP BY time() interval. GROUP BY time() queries with the SAMPLE() function behave differently; they maintain the timestamp of the original data point.

Example

The query below returns two randomly selected points per 18-minute GROUP BY time() interval. Notice that the returned timestamps are the points' original timestamps; they are not forced to match the start of the GROUP BY time() intervals.

> 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)

name: h2o_feet
time                   sample
----                   ------
                           __
2015-08-18T00:06:00Z   2.116 |
2015-08-18T00:12:00Z   2.028 | <------- Randomly-selected points for the first time interval
                           --
                           __
2015-08-18T00:18:00Z   2.126 |
2015-08-18T00:30:00Z   2.051 | <------- Randomly-selected points for the second time interval
                           --