stats_agg() (one variable) functions

Introduction

Perform common statistical analyses, such as calculating averages and standard deviations, using this group of functions. These functions are similar to the PostgreSQL statistical aggregates, but they include more features and are easier to use in continuous aggregates and window functions.

These functions work on one-dimensional data. To work with two-dimensional data, for example to perform linear regression, see the two-dimensional stats_agg functions.

Related hyperfunction groups

• stats_agg() (two variables)

Two-step aggregation

Hide content

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
4. Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Functions in this group

Aggregate

stats_agg (one variable)

Aggregate data into an intermediate statistical aggregate form for further calculation

Accessor

average

Calculate the average from a one-dimensional statistical aggregate

kurtosis

Calculate the kurtosis from a one-dimensional statistical aggregate

num_vals

Calculate the number of values in a one-dimensional statistical aggregate

skewness

Calculate the skewness from a one-dimensional statistical aggregate

stddev

Calculate the standard deviation from a one-dimensional statistical aggregate

sum

Calculate the sum from a one-dimensional statistical aggregate

variance

Calculate the variance from a one-dimensional statistical aggregate

Rollup

rolling

Combine multiple one-dimensional statistical aggregates to calculate rolling window aggregates

rollup

Combine multiple one-dimensional statistical aggregates

Function details

stats_agg() (one variable)

Stabilized in Toolkit v1.3.0

Hide content

`

stats_agg(

`

    value DOUBLE PRECISION

`

) RETURNS StatsSummary1D

`

This is the first step for performing any statistical aggregate calculations on one-dimensional data. Use stats_agg to create an intermediate aggregate (StatsSummary1D) from your data. This intermediate form can then be used by one or more accessors in this group to compute final results. Optionally, multiple such intermediate aggregate objects can be combined using rollup() or rolling() before an accessor is applied.

stats_agg is well suited for creating a continuous aggregate that can serve multiple purposes later. For example, you can create a continuous aggregate using stats_agg to calculate average and sum. Later, you can reuse the same StatsSummary1D objects to calculate standard deviation from the same continuous aggregate.

Required arguments

Returns

Examples

Create a one-dimensional statistical aggregate from column x in table foo:

SELECT stats_agg(x) FROM foo;

average()

Stabilized in Toolkit v1.3.0

Hide content

`

average(

`

  summary StatsSummary1D

`

) RETURNS DOUBLE PRECISION

`

Calculate a simple average (or mean) from the values in a statistical aggregate.

Required arguments

Returns

Examples

Calculate the average of column x in table foo:

SELECT average(
    stats_agg(x)
) FROM foo;

Calculate the average of the integers from 0 to 100:

SELECT average(stats_agg(data))
  FROM generate_series(0, 100) data;
average
-----------
50

kurtosis()

Stabilized in Toolkit v1.3.0

Hide content

`

kurtosis(

`

  summary StatsSummary1D,
  [ method TEXT ]

`

) DOUBLE PRECISION

`

Calculate the kurtosis from the values in a statistical aggregate. The kurtosis is the fourth statistical moment. It is a measure of “tailedness” of a data distribution compared to a normal distribution.

Required arguments

Optional arguments

Returns

Examples

Calculate the kurtosis of a sample containing the integers from 0 to 100:

SELECT kurtosis(stats_agg(data))
  FROM generate_series(0, 100) data;
kurtosis
----------
1.78195

num_vals()

Stabilized in Toolkit v1.3.0

Hide content

`

num_vals(

`

  summary StatsSummary1D

`

) RETURNS BIGINT

`

Calculate the number of values contained in a statistical aggregate.

Required arguments

Returns

Examples

Calculate the number of values from 0 to 100, inclusive:

SELECT num_vals(stats_agg(data))
  FROM generate_series(0, 100) data;
num_vals
--------
101

skewness()

Stabilized in Toolkit v1.3.0

Hide content

`

skewness(

`

  summary StatsSummary1D,
  [ method TEXT ]

`

) RETURNS DOUBLE PRECISION

`

Calculate the skewness from the values in a statistical aggregate. The skewness is the third statistical moment. It is a measure of asymmetry in a data distribution.

Required arguments

Optional arguments

Returns

Examples

Calculate the skewness of a sample containing the integers from 0 to 100:

SELECT skewness(stats_agg(data))
  FROM generate_series(0, 100) data;
skewness_x
----------
0

stddev()

Stabilized in Toolkit v1.3.0

Hide content

`

stddev(

`

  summary StatsSummary1D,
  [ method TEXT ]

`

) RETURNS DOUBLE PRECISION

`

Calculate the standard deviation from the values in a statistical aggregate.

Required arguments

Optional arguments

Returns

Examples

Calculate the standard deviation of a sample containing the integers from 0 to 100:

SELECT stddev(stats_agg(data))
  FROM generate_series(0, 100) data;
stddev_y
--------
29.3002

sum()

Stabilized in Toolkit v1.3.0

Hide content

`

sum(

`

  summary StatsSummary1D

`

) RETURNS DOUBLE PRECISION

`

Calculate the sum of the values contained in a statistical aggregate.

Required arguments

Returns

Examples

Calculate the sum of the integers from 0 to 100:

SELECT sum(stats_agg(data))
  FROM generate_series(0, 100) data;
sum
-----
5050

variance()

Stabilized in Toolkit v1.3.0

Hide content

`

variance(

`

  summary StatsSummary1D,
  [ method TEXT ]

`

) RETURNS DOUBLE PRECISION

`

Calculate the variance from the values in a statistical aggregate.

Required arguments

Optional arguments

Returns

Examples

Calculate the variance of a sample containing the integers from 0 to 100:

SELECT variance(stats_agg(data))
  FROM generate_series(0, 100) data;
variance
----------
858.5

rolling()

Stabilized in Toolkit v1.3.0

Hide content

`

rolling(

`

    ss StatsSummary1D

`

) RETURNS StatsSummary1D

`

Combine multiple intermediate statistical aggregate (StatsSummary1D) objects into a single StatsSummary1D object. It is optimized for use in a window function context for computing tumbling window statistical aggregates.

note

This is especially useful for computing tumbling window aggregates from a continuous aggregate. It can be orders of magnitude faster because it uses inverse transition and combine functions, with the possibility that bigger floating point errors can occur in unusual scenarios. For re-aggregation in a non-window function context, such as combining hourly buckets into daily buckets, see rollup().

Required arguments

Returns

Examples

Combine hourly continuous aggregates to create a tumbling window daily aggregate. Calculate the average and standard deviation using the appropriate accessors:

CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
  time_bucket('1h'::interval, ts) AS bucket,
  stats_agg(value) as stats
FROM foo
GROUP BY 1;
SELECT
  bucket,
  average(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING)),
  stddev(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING)),
FROM foo_hourly;

rollup()

Stabilized in Toolkit v1.3.0

Hide content

`

rollup(

`

    ss StatsSummary1D

`

) RETURNS StatsSummary1D

`

Combine multiple intermediate statistical aggregate (StatsSummary1D) objects produced by stats_agg (one variable) into a single intermediate StatsSummary1D object. For example, you can use rollup to combine statistical aggregates from 15-minute buckets into daily buckets. For use in window functions, see rolling().

Required arguments

Returns

Extended examples

Create a statistical aggregate to summarize daily statistical data about the variable val1. Use the statistical aggregate to calculate average, standard deviation, and skewness of the variable:

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        stats_agg(val1) AS stats1D
    FROM foo
    WHERE id = 'bar'
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    average(stats1D),
    stddev(stats1D),
    skewness(stats1D)
FROM t;