stats_agg() (one variable) functions

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.

average(
  summary StatsSummary1D
) RETURNS DOUBLE PRECISION

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

Examples

Calculate the average of the integers from 0 to 100:

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

Copy

average
-----------
50

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.

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;

Copy

kurtosis
----------
1.78195

num_vals(
  summary StatsSummary1D
) RETURNS BIGINT

Calculate the number of values contained in a statistical aggregate.

Examples

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

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

Copy

num_vals
--------
101

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.

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;

Copy

skewness_x
----------
0

stddev(
  summary StatsSummary1D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

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

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;

Copy

stddev_y
--------
29.3002

sum(
  summary StatsSummary1D
) RETURNS DOUBLE PRECISION

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

Examples

Calculate the sum of the integers from 0 to 100:

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

Copy

sum
-----
5050

variance(
  summary StatsSummary1D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the variance from the values in a statistical aggregate.

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;

Copy

variance
----------
858.5

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().

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;

Copy

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().