stats_agg() (two variables) functions

stats_agg(
    y DOUBLE PRECISION,
    x DOUBLE PRECISION
) RETURNS StatsSummary2D

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

average_y(
  summary StatsSummary 2D
) RETURNS DOUBLE PRECISION

average_x(
  summary StatsSummary 2D
) RETURNS DOUBLE PRECISION

Calculate the average from a two-dimensional aggregate for the given dimension. For example, average_y() calculates the average for all the values of the y variable, independent of the values of the x variable.

Examples

Calculate the average of the integers from 0 to 100:

SELECT average_x(stats_agg(y, x))
  FROM generate_series(1, 5) y,
       generate_series(0, 100) x;

Copy

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

corr(
  summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the correlation coefficient from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the correlation coefficient of independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
  id,
  time_bucket('15 min'::interval, ts) AS bucket,
  corr(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

covariance(
    summary StatsSummary2D,
    [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the covariance from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the covariance of independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
    id,
    time_bucket('15 min'::interval, ts) AS bucket,
    covariance(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

determination_coeff(
    summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the determination coefficient from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the determination coefficient of independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
    id,
    time_bucket('15 min'::interval, ts) AS bucket,
    determination_coeff(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

intercept(
    summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the y intercept from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the y intercept from independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
    id,
    time_bucket('15 min'::interval, ts) AS bucket,
    intercept(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

kurtosis_y(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

kurtosis_x(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the kurtosis from a two-dimensional statistical aggregate for the given dimension. For example, kurtosis_y() calculates the kurtosis for all the values of the y variable, independent of values of the x variable. 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_y(stats_agg(data, data))
  FROM generate_series(0, 100) data;

Copy

kurtosis_y
----------
1.78195

num_vals(
  summary StatsSummary2D
) RETURNS BIGINT

Calculate the number of values contained in a two-dimensional statistical aggregate.

Examples

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

SELECT num_vals(stats_agg(y, x))
  FROM generate_series(1, 5) y,
       generate_series(0, 100) x;

Copy

num_vals
--------
505

skewness_y(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

skewness_x(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the skewness from a two-dimensional statistical aggregate for the given dimension. For example, skewness_y() calculates the skewness for all the values of the y variable, independent of values of the x variable. 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_x(stats_agg(data, data))
  FROM generate_series(0, 100) data;

Copy

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

slope(
    summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the slope of the linear fitting line from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the slope from independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
    id,
    time_bucket('15 min'::interval, ts) AS bucket,
    slope(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

stddev_y(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

stddev_x(summary
    StatsSummary2D,
    [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the standard deviation from a two-dimensional statistical aggregate for the given dimension. For example, stddev_y() calculates the skewness for all the values of the y variable, independent of values of the x variable.

Examples

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

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

Copy

stddev_y
--------
29.3002

sum_y(
  summary StatsSummary2D
) RETURNS DOUBLE PRECISION

sum_x(
  summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the sum from a two-dimensional statistical aggregate for the given dimension. For example, sum_y() calculates the skewness for all the values of the y variable, independent of values of the x variable.

Examples

Calculate the sum of the numbers from 0 to 100:

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

Copy

sum_y
-----
5050

variance_y(
  summary StatsSummary2D,
  [ method TEXT ]
) RETURNS DOUBLE PRECISION

variance_x(summary
    StatsSummary2D,
    [ method TEXT ]
) RETURNS DOUBLE PRECISION

Calculate the variance from a two-dimensional statistical aggregate for the given dimension. For example, variance_y() calculates the skewness for all the values of the y variable, independent of values of the x variable.

Examples

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

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

Copy

variance_y
----------
858.5

x_intercept(
  summary StatsSummary2D
) RETURNS DOUBLE PRECISION

Calculate the x intercept from a two-dimensional statistical aggregate. The calculation uses the standard least-squares fitting for linear regression.

Examples

Calculate the x intercept from independent variable y and dependent variable x for each 15-minute time bucket:

SELECT
    id,
    time_bucket('15 min'::interval, ts) AS bucket,
    x_intercept(stats_agg(y, x)) AS summary
FROM foo
GROUP BY id, time_bucket('15 min'::interval, ts)

Copy

rolling(
    ss StatsSummary2D
) RETURNS StatsSummary2D

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

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

rolling(
    ss StatsSummary2D
) RETURNS StatsSummary2D

Combine multiple intermediate two-dimensional statistical aggregate (StatsSummary2D) objects into a single StatsSummary2D object. For example, you can use rollup to combine statistical aggregates from 15-minute buckets into daily buckets. For use in window function, see rolling().