216 lines
7.1 KiB
Markdown
216 lines
7.1 KiB
Markdown
|
|
An InfluxQL query that aggregates data includes the following clauses:
|
|
|
|
{{< req type="key" >}}
|
|
|
|
- {{< 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-and-selector-functions)
|
|
- [Aggregate functions](#aggregate-functions)
|
|
- [Selector functions](#selector-functions)
|
|
- [Example aggregate queries](#example-aggregate-queries)
|
|
|
|
{{% influxql/v1-v3-data-model-note %}}
|
|
|
|
## 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.
|
|
|
|
<a href="/influxdb3/version/reference/influxql/functions/aggregates/" class="btn small">View InfluxQL aggregate functions</a>
|
|
|
|
##### Basic aggregate query
|
|
|
|
```sql
|
|
SELECT MEAN(co) from home
|
|
```
|
|
|
|
### Selector functions
|
|
|
|
Use **selector functions** to "select" a value from a specified field.
|
|
|
|
<a href="/influxdb3/version/reference/influxql/functions/selectors/" class="btn small">View InfluxQL selector functions</a>
|
|
|
|
##### Basic selector query
|
|
|
|
```sql
|
|
SELECT TOP(co, 3) from home
|
|
```
|
|
|
|
## Example aggregate queries
|
|
|
|
- [Perform an ungrouped aggregation](#perform-an-ungrouped-aggregation)
|
|
- [Group and aggregate data](#group-and-aggregate-data)
|
|
- [Downsample data by applying interval-based aggregates](#downsample-data-by-applying-interval-based-aggregates)
|
|
- [Query rows based on aggregate values](#query-rows-based-on-aggregate-values)
|
|
|
|
> [!Note]
|
|
>
|
|
> #### Sample data
|
|
>
|
|
> The following examples use the [Home sensor data](/influxdb3/version/reference/sample-data/#home-sensor-data).
|
|
> To run the example queries and return results,
|
|
> [write the sample data](/influxdb3/version/reference/sample-data/#write-the-home-sensor-data-to-influxdb)
|
|
> 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.
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
SELECT
|
|
MEAN(temp) AS "average temp"
|
|
FROM home
|
|
GROUP BY room
|
|
```
|
|
|
|
{{< expand-wrapper >}}
|
|
{{% expand "View example results" %}}
|
|
|
|
{{% 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](/influxdb3/version/reference/influxql/functions/aggregates/)
|
|
or [selector](/influxdb3/version/reference/influxql/functions/selectors/)
|
|
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](/influxdb3/version/reference/influxql/functions/date-time/#time)
|
|
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.
|
|
|
|
```sql
|
|
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
|
|
```
|
|
|
|
{{< expand-wrapper >}}
|
|
{{% expand "View example results" "1" %}}
|
|
{{% influxdb/custom-timestamps %}}
|
|
|
|
{{% influxql/table-meta %}}
|
|
name: home
|
|
tags: room=Kitchen
|
|
{{% /influxql/table-meta %}}
|
|
|
|
| time | max temp | min temp | average temp |
|
|
| :------------------- | -------: | -------: | -----------------: |
|
|
| 2022-01-01T08:00:00Z | 23 | 21 | 22 |
|
|
| 2022-01-01T10:00:00Z | 22.7 | 22.4 | 22.549999999999997 |
|
|
| 2022-01-01T12:00:00Z | 22.8 | 22.5 | 22.65 |
|
|
| 2022-01-01T14:00:00Z | 22.8 | 22.7 | 22.75 |
|
|
| 2022-01-01T16:00:00Z | 22.7 | 22.4 | 22.549999999999997 |
|
|
| 2022-01-01T18:00:00Z | 23.3 | 23.1 | 23.200000000000003 |
|
|
| 2022-01-01T20:00:00Z | 22.7 | 22.7 | 22.7 |
|
|
|
|
{{% influxql/table-meta %}}
|
|
name: home
|
|
tags: room=Living Room
|
|
{{% /influxql/table-meta %}}
|
|
|
|
| time | max temp | min temp | average temp |
|
|
| :------------------- | -------: | -------: | -----------------: |
|
|
| 2022-01-01T08:00:00Z | 21.4 | 21.1 | 21.25 |
|
|
| 2022-01-01T10:00:00Z | 22.2 | 21.8 | 22 |
|
|
| 2022-01-01T12:00:00Z | 22.4 | 22.2 | 22.299999999999997 |
|
|
| 2022-01-01T14:00:00Z | 22.3 | 22.3 | 22.3 |
|
|
| 2022-01-01T16:00:00Z | 22.6 | 22.4 | 22.5 |
|
|
| 2022-01-01T18:00:00Z | 22.8 | 22.5 | 22.65 |
|
|
| 2022-01-01T20:00:00Z | 22.2 | 22.2 | 22.2 |
|
|
|
|
{{% /influxdb/custom-timestamps %}}
|
|
{{% /expand %}}
|
|
{{< /expand-wrapper >}}
|