stats_agg()

Toolkit

TimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.

An aggregate that produces a StatsSummary from DOUBLE PRECISION values.

Statistical aggregates can be done on either one or two variables. For a single variable, only one-dimensional aggregates are calculated. For two variables, one-dimensional aggregates are calculated for each variable, and linear regression is performed on both together.

For more information about statistical aggregation functions, see the hyperfunctions documentation.

1D statistical aggregates

When you perform a statistical aggregate on a single variable, a one-dimensional aggregate is calculated.

Required arguments

The value argument is currently only accepted as a DOUBLE PRECISION number. If you store a value as a different numeric type you can cast to DOUBLE PRECISION on input to the function.

Returns

2D statistical aggregates

When you perform a statistical aggregate on two variables, one-dimensional aggregates are calculated for each variable, and linear regression is performed on both together.

Required arguments

The Y and X arguments are currently only accepted as DOUBLE PRECISION numbers. If you store a value as a different numeric type you can cast to DOUBLE PRECISION on input to the function.

Note that the function is called with the dependent variable first (stats_agg(Y, X)), which could seem unusual because the independent variable is often first in non-SQL contexts. However, this function follows PostgreSQL and the SQL standard, which puts the dependent variable first in linear regression type functions.

Returns

Sample usage

This example produces one and two dimensional StatsSummaries in the CTE (WITH t as (...), and then uses the average and slope accessors for each type to calculate the corresponding values from each function:

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        stats_agg(val2, val1) AS stats2D, 
        stats_agg(val1) AS stats1D 
    FROM foo
    WHERE id = 'bar'
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    average_x(stats2D), -- use average_x on 2D summary
    average(stats1D), -- use normal average on 1D summary to get same value
    slope(stats2D) -- slope and other regression functions only work on 2D aggregates
FROM t;