Influxdata
/
docs-v2
mirror of https://github.com/influxdata/docs-v2.git
1
0
Fork 0
Code Issues Projects Releases Wiki Activity
docs-v2/content/shared/influxql-v3-reference/functions/aggregates.md

21 KiB
Raw Permalink Blame History

Use aggregate functions to assess, aggregate, and return values in your data. Aggregate functions return one row containing the aggregate values from each InfluxQL group.

Examples use the sample data set provided in the Get started with InfluxDB tutorial.

[!Important]

Missing InfluxQL functions

Some InfluxQL functions are in the process of being rearchitected to work with the InfluxDB 3 storage engine. If a function you need is not here, check the InfluxQL feature support page for more information.

COUNT()

Returns the number of non-null field values.

COUNT(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports all field types.

Examples

{{< expand-wrapper >}} {{% expand "Count the number of non-null values in a field" %}}

SELECT COUNT(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time count
1970-01-01T00:00:00Z 26

{{% /expand %}}

{{% expand "Count the number of non-null values in each field" %}}

SELECT COUNT(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time count_co count_hum count_temp
1970-01-01T00:00:00Z 26 26 26

{{% /expand %}}

{{% expand "Count the number of non-null values in fields where the field key matches a regular expression" %}}

SELECT COUNT(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time count_hum count_temp
1970-01-01T00:00:00Z 26 26

{{% /expand %}}

{{% expand "Count distinct values for a field" %}}

InfluxQL supports nesting DISTINCT() in COUNT().

SELECT COUNT(DISTINCT(co)) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time count
1970-01-01T00:00:00Z 12

{{% /expand %}}

{{% expand "Count the number of non-null field values within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  COUNT(temp)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time count
2022-01-01T06:00:00Z 4
2022-01-01T12:00:00Z 6
2022-01-01T18:00:00Z 3

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

DISTINCT()

Returns the list of unique field values.

DISTINCT(field_key)

Arguments

  • field_key: Field key to return distinct values from. Supports all field types.

Notable behaviors

  • InfluxQL supports nesting DISTINCT() with COUNT().

Examples

{{< expand-wrapper >}} {{% expand "List the distinct field values" %}}

SELECT DISTINCT(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time distinct
1970-01-01T00:00:00Z 0
1970-01-01T00:00:00Z 1
1970-01-01T00:00:00Z 3
1970-01-01T00:00:00Z 4
1970-01-01T00:00:00Z 7
1970-01-01T00:00:00Z 5
1970-01-01T00:00:00Z 9
1970-01-01T00:00:00Z 18
1970-01-01T00:00:00Z 14
1970-01-01T00:00:00Z 22
1970-01-01T00:00:00Z 17
1970-01-01T00:00:00Z 26

{{% /expand %}} {{< /expand-wrapper >}}

MEAN()

Returns the arithmetic mean (average) of field values.

MEAN(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric fields.

Examples

{{< expand-wrapper >}} {{% expand "Calculate the mean value of a field" %}}

SELECT MEAN(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mean
1970-01-01T00:00:00Z 22.396153846153844

{{% /expand %}}

{{% expand "Calculate the mean value of each field" %}}

SELECT MEAN(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mean_co mean_hum mean_temp
1970-01-01T00:00:00Z 5.269230769230769 36.15 22.396153846153844

{{% /expand %}}

{{% expand "Calculate the mean value of fields where the field key matches a regular expression" %}}

SELECT MEAN(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mean_hum mean_temp
1970-01-01T00:00:00Z 36.15 22.396153846153844

{{% /expand %}}

{{% expand "Calculate the mean value of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  MEAN(temp)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mean
2022-01-01T06:00:00Z 22.275
2022-01-01T12:00:00Z 22.649999999999995
2022-01-01T18:00:00Z 23.033333333333335

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

MEDIAN()

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

MEDIAN(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric fields.

Notable behaviors

  • 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

{{< expand-wrapper >}} {{% expand "Calculate the median value of a field" %}}

SELECT MEDIAN(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time median
1970-01-01T00:00:00Z 22.45

{{% /expand %}}

{{% expand "Calculate the median value of each field" %}}

SELECT MEDIAN(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time median_co median_hum median_temp
1970-01-01T00:00:00Z 1 36.05 22.45

{{% /expand %}}

{{% expand "Calculate the median value of fields where the field key matches a regular expression" %}}

SELECT MEDIAN(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time median_hum median_temp
1970-01-01T00:00:00Z 36.05 22.45

{{% /expand %}}

{{% expand "Calculate the median value of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  MEDIAN(temp)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time median
2022-01-01T06:00:00Z 22.549999999999997
2022-01-01T12:00:00Z 22.7
2022-01-01T18:00:00Z 23.1

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

MODE()

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

MODE(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports all field types.

Notable behaviors

  • 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

{{< expand-wrapper >}}

{{% expand "Calculate the mode value of a field" %}}

SELECT MODE(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mode
1970-01-01T00:00:00Z 0

{{% /expand %}}

{{% expand "Calculate the mode value of each field" %}}

SELECT MODE(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mode_co mode_hum mode_temp
1970-01-01T00:00:00Z 0 36 22.7

{{% /expand %}}

{{% expand "Calculate the mode of field keys that match a regular expression" %}}

SELECT MODE(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mode_hum mode_temp
1970-01-01T00:00:00Z 36 22.7

{{% /expand %}}

{{% expand "Calculate the mode a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  MODE(co)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time mode
2022-01-01T06:00:00Z 0
2022-01-01T12:00:00Z 1
2022-01-01T18:00:00Z 18

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

SPREAD()

Returns the difference between the minimum and maximum field values.

SPREAD(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric fields.

Examples

{{< expand-wrapper >}}

{{% expand "Calculate the spread of a field" %}}

SELECT SPREAD(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time spread
1970-01-01T00:00:00Z 2.3000000000000007

{{% /expand %}}

{{% expand "Calculate the spread of each field" %}}

SELECT SPREAD(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time spread_co spread_hum spread_temp
1970-01-01T00:00:00Z 26 1 2.3000000000000007

{{% /expand %}}

{{% expand "Calculate the spread of field keys that match a regular expression" %}}

SELECT SPREAD(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time spread_hum spread_temp
1970-01-01T00:00:00Z 1 2.3000000000000007

{{% /expand %}}

{{% expand "Calculate the spread of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  SPREAD(co)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time spread
2022-01-01T06:00:00Z 0
2022-01-01T12:00:00Z 9
2022-01-01T18:00:00Z 8

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

STDDEV()

Returns the standard deviation of field values.

STDDEV(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric fields.

Examples

{{< expand-wrapper >}} {{% expand "Calculate the standard deviation of a field" %}}

SELECT STDDEV(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time stddev
1970-01-01T00:00:00Z 0.5553238833191091

{{% /expand %}}

{{% expand "Calculate the standard deviation of each field" %}}

SELECT STDDEV(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time stddev_co stddev_hum stddev_temp
1970-01-01T00:00:00Z 7.774613519951676 0.25495097567963926 0.5553238833191091

{{% /expand %}}

{{% expand "Calculate the standard deviation of fields where the field key matches a regular expression" %}}

SELECT STDDEV(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time stddev_hum stddev_temp
1970-01-01T00:00:00Z 0.25495097567963926 0.5553238833191091

{{% /expand %}}

{{% expand "Calculate the standard deviation of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  STDDEV(co)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time stddev
2022-01-01T06:00:00Z 0
2022-01-01T12:00:00Z 3.6742346141747673
2022-01-01T18:00:00Z 4

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

SUM()

Returns the sum of field values.

SUM(field_expression)

Arguments

  • field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric fields.

Examples

{{< expand-wrapper >}} {{% expand "Calculate the sum of values in a field" %}}

SELECT SUM(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time sum
1970-01-01T00:00:00Z 137

{{% /expand %}}

{{% expand "Calculate the sum of values in each field" %}}

SELECT SUM(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time sum_co sum_hum sum_temp
1970-01-01T00:00:00Z 137 939.9 582.3

{{% /expand %}}

{{% expand "Calculate the sum of values for fields where the field key matches a regular expression" %}}

SELECT SUM(/^[th]/) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time sum_hum sum_temp
1970-01-01T00:00:00Z 939.9 582.3

{{% /expand %}}

{{% expand "Calculate the sum of values in a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

SELECT
  SUM(co)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time sum
2022-01-01T06:00:00Z 0
2022-01-01T12:00:00Z 21
2022-01-01T18:00:00Z 66

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}