docs-v2

7.1 KiB

Raw Permalink Blame History

An InfluxQL query that aggregates data includes the following clauses:

{{< req "*">}} SELECT: Specify fields and calculations to output from a measurement or use the wildcard alias (*) to select all fields and tags from a measurement.
{{< req "*">}} FROM: Specify the measurement to query data from.
WHERE: Only retrieve data that meets the specified conditions--for example, time is in a time range, contains specific tag values, or contains a field value outside specified thresholds.
GROUP BY: Group data by tag values and time intervals.

[!Note] For simplicity, the term "aggregate" in this guide refers to applying both aggregate and selector functions to a dataset.

Learn how to apply aggregate operations to your queried data:

Aggregate and selector functions
- Aggregate functions
- Selector functions
Example aggregate queries

Aggregate and selector functions

Both aggregate and selector functions return a limited number of rows from each group. Aggregate functions return a single row, whereas some selector functions let you specify the number of rows to return from each group. For example, if you GROUP BY room and perform an aggregate operation in your SELECT clause, results include an aggregate value for each unique value of room.

Aggregate functions

Use aggregate functions to aggregate values in a specified field for each group and return a single row per group containing the aggregate field value.

View InfluxQL aggregate functions

Basic aggregate query

SELECT MEAN(co) from home

Selector functions

Use selector functions to "select" a value from a specified field.

View InfluxQL selector functions

Basic selector query

SELECT TOP(co, 3) from home

Example aggregate queries

Perform an ungrouped aggregation
Group and aggregate data
- Downsample data by applying interval-based aggregates
Query rows based on aggregate values

[!Note]

Sample data

The following examples use the Home sensor data. To run the example queries and return results, write the sample data to your {{% product-name %}} database before running the example queries.

Perform an ungrouped aggregation

To aggregate all queried values in a specified field:

Use aggregate or selector functions in your SELECT statement.
Do not include a GROUP BY clause to leave your data ungrouped.

SELECT MEAN(co) AS "average co" FROM home

{{< expand-wrapper >}} {{% expand "View example results" %}} {{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

time	average co
0	5.269230769230769
{{% /expand %}}
{{< /expand-wrapper >}}

Group and aggregate data

To apply aggregate or selector functions to grouped data:

Use aggregate or selector functions in your SELECT statement.
Include a GROUP BY clause with a comma-delimited list of tags to group by.

Keep the following in mind when using GROUP BY:

GROUP BY can use column aliases that are defined in the SELECT clause.

SELECT
  MEAN(temp) AS "average temp"
FROM home
GROUP BY room

{{% influxql/table-meta %}} name: home
tags: room=Kitchen {{% /influxql/table-meta %}}

time	average temp
0	22.623076923076926

{{% influxql/table-meta %}} name: home
tags: room=Living Room {{% /influxql/table-meta %}}

time	average temp
0	22.16923076923077
{{% /expand %}}
{{< /expand-wrapper >}}

Downsample data by applying interval-based aggregates

A common use case when querying time series is downsampling data by applying aggregates to time-based groups. To group and aggregate data into time-based groups:

In your SELECT clause, apply aggregate or selector functions to queried fields.
In your WHERE clause, include time bounds for the query. Interval-based aggregates produce a row for each specified time interval. If no time bounds are specified in the WHERE clause, the query uses the default time range (1970-01-01T00:00:00Z to now) and returns a row for each interval in that time range.
In your GROUP BY clause:
- Use the time() function to specify the time interval to group by.
- Optional: Specify other tags to group by.

The following example retrieves unique combinations of time intervals and rooms with their minimum, maximum, and average temperatures.

SELECT
  MAX(temp) AS "max temp",
  MIN(temp) AS "min temp",
  MEAN(temp) AS "average temp"
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T20:00:00Z'
GROUP BY time(2h), room