Versions:

To make common statistical aggregates easier to work with in window functions and continuous aggregates, TimescaleDB provides common statistical aggregates in a slightly different form than otherwise available in PostgreSQL and TimescaleDB.

In this example, we calculate the average, standard deviation, and kurtosis of a value in the `measurements`

table:

```
SELECT
time_bucket('10 min'::interval, ts),
average(stats_agg(val)),
stddev(stats_agg(val), 'pop'),
kurtosis(stats_agg(val), 'pop')
FROM measurements
GROUP BY 1;
```

This uses a two-step aggregation process. The first step is an aggregation step (`stats_agg(val)`

),
which creates a machine-readable form of the aggregate. The second step is an accessor.
The available accessors are `average`

, `stddev`

, and `kurtosis`

. The accessors
run final calculations and output the calculated value in a human-readable way. This makes it
easier to construct your queries, because it distinguishes the parameters, and
makes it clear which aggregates are being re-aggregated or rolled up.
Additionally, because this query syntax is used in all Timescale Toolkit
queries, when you are used to it, you can use it to construct more and more
complicated queries.

A more complex example uses window functions to calculate tumbling window statistical aggregates.
The statistical aggregate is first calculated over each minute in the subquery
and then the `rolling`

aggregate is used to re-aggregate it over each 15 minute period preceding.
The accessors remain the same as the previous example:

```
SELECT
bucket,
average(rolling(stats_agg) OVER fifteen_min),
stddev(rolling(stats_agg) OVER fifteen_min, 'pop'),
kurtosis(rolling(stats_agg) OVER fifteen_min, 'pop')
FROM (SELECT
time_bucket('1 min'::interval, ts) AS bucket,
stats_agg(val)
FROM measurements
GROUP BY 1) AS stats
WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING);
```

For some more technical details and usage examples of the two-step aggregation method, see the blog post on aggregates or the developer documentation.

The `stats_agg`

aggregate is available in two forms, a one-dimensional
aggregate shown earlier in this section, and a two-dimensional aggregate.
The two-dimensional aggregate takes in two variables `(Y, X)`

, which are
dependent and independent variables respectively. The two-dimensional
aggregate performs all the same calculations on each individual variable
as performing separate one-dimensional aggregates would, and
additionally performs linear regression on the two variables. Accessors
for one-dimensional values append a `_y`

or `_x`

to the name. For
example:

```
SELECT
average_y(stats_agg(val2, val1)), -- equivalent to average(stats_agg(val2))
stddev_x(stats_agg(val2, val1)), -- equivalent to stddev(stats_agg(val1))
slope(stats_agg(val2, val1)) -- the slope of the least squares fit line of the values in val2 & val1
FROM measurements_multival;
```

For more information about statistical aggregation API calls, see the hyperfunction API documentation.

Found an issue on this page?

Report an issue!Keywords