Skip to main content

Summary statistics (aggregates)

All functions on this page are aggregates: pass a DOUBLE column (optionally a constant), get one value per group. NULL rows are skipped automatically.

Central tendency​

sr_mean(x)​

Signature: sr_mean(x DOUBLE[]) -> DOUBLE

Arithmetic mean, NULL when no row is non-NULL.

SELECT sr_mean(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 0.3333333333333333

sr_geometric_mean(x)​

Signature: sr_geometric_mean(x DOUBLE[]) -> DOUBLE

Geometric mean. NULL when any value is negative.

SELECT sr_geometric_mean(x) FROM (VALUES (1.0), (2.0), (3.0)) t(x)
-- 1.8171205928321397

sr_harmonic_mean(x)​

Signature: sr_harmonic_mean(x DOUBLE[]) -> DOUBLE

Harmonic mean. NULL when any value is negative.

SELECT sr_harmonic_mean(x) FROM (VALUES (1.0), (2.0), (3.0)) t(x)
-- 1.6363636363636365

sr_quadratic_mean(x)​

Signature: sr_quadratic_mean(x DOUBLE[]) -> DOUBLE

Quadratic mean (root mean square).

SELECT sr_quadratic_mean(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 2.0816659994661326

Order statistics​

sr_median(x)​

Signature: sr_median(x DOUBLE[]) -> DOUBLE

Median. Even-length inputs average the two middle values.

SELECT sr_median(x) FROM (VALUES (1.0), (2.0), (3.0), (4.0)) t(x)
-- 2.5

sr_quantile(x, tau)​

Signature: sr_quantile(x DOUBLE[], tau DOUBLE) -> DOUBLE

Tau quantile. tau must be in [0, 1]; otherwise NULL.

SELECT sr_quantile(x, 0.5) FROM (VALUES (-1.0), (5.0), (0.0), (-3.0), (10.0), (-0.5), (4.0), (0.2), (1.0), (6.0)) t(x)
-- 0.6

sr_order_statistic(x, k)​

Signature: sr_order_statistic(x DOUBLE[], k UBIGINT) -> DOUBLE

k-th smallest value (1-based). NULL when k is outside the data range. An aggregate's constant argument needs an explicit ::UBIGINT cast.

SELECT sr_order_statistic(x, 2::UBIGINT) FROM (VALUES (3.0), (1.0), (2.0)) t(x)
-- 2.0

sr_percentile(x, p)​

Signature: sr_percentile(x DOUBLE[], p UBIGINT) -> DOUBLE

p-th percentile. p is a UBIGINT in [0, 100]; an aggregate's constant argument needs an explicit ::UBIGINT cast.

SELECT sr_percentile(x, 50::UBIGINT) FROM (VALUES (1.0), (2.0), (3.0), (4.0)) t(x)
-- 2.5

sr_lower_quartile(x)​

Signature: sr_lower_quartile(x DOUBLE[]) -> DOUBLE

First quartile (lower hinge).

SELECT sr_lower_quartile(x) FROM (VALUES (2.0), (1.0), (3.0), (4.0)) t(x)
-- 1.4166666666666665

sr_upper_quartile(x)​

Signature: sr_upper_quartile(x DOUBLE[]) -> DOUBLE

Third quartile (upper hinge).

SELECT sr_upper_quartile(x) FROM (VALUES (2.0), (1.0), (3.0), (4.0)) t(x)
-- 3.5833333333333335

sr_interquartile_range(x)​

Signature: sr_interquartile_range(x DOUBLE[]) -> DOUBLE

IQR = upper_quartile - lower_quartile.

SELECT sr_interquartile_range(x) FROM (VALUES (2.0), (1.0), (3.0), (4.0)) t(x)
-- 2.166666666666667

sr_ranks(x, method)​

Signature: sr_ranks(x DOUBLE[], method DOUBLE) -> LIST(DOUBLE)

Ranks of each value. Method: 1=average, 2=min, 3=max, 4=first.

SELECT sr_ranks(x, 1.0) FROM (VALUES (1.0), (3.0), (2.0), (2.0)) t(x)
-- [1.0, 4.0, 2.5, 2.5]

Dispersion​

sr_variance(x)​

Signature: sr_variance(x DOUBLE[]) -> DOUBLE

Sample variance (Bessel-corrected). NULL when fewer than two rows.

SELECT sr_variance(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 6.333333333333333

sr_std_dev(x)​

Signature: sr_std_dev(x DOUBLE[]) -> DOUBLE

Sample standard deviation. NULL when fewer than two rows.

SELECT sr_std_dev(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 2.5166114784690383

sr_population_variance(x)​

Signature: sr_population_variance(x DOUBLE[]) -> DOUBLE

Population variance (divides by N, not N-1).

SELECT sr_population_variance(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 4.222222222222222

sr_population_std_dev(x)​

Signature: sr_population_std_dev(x DOUBLE[]) -> DOUBLE

Population standard deviation.

SELECT sr_population_std_dev(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 2.0548046670007003

sr_skewness(x)​

Signature: sr_skewness(x DOUBLE[]) -> DOUBLE

Sample skewness (statrs' OnlineMoments<3>: the third central moment divided by the 3/2 power of the population second moment). NULL when fewer than two rows are non-NULL; a constant column (zero variance) yields 0, following statrs.

SELECT sr_skewness(x)
FROM (VALUES (2.0), (4.0), (4.0), (4.0), (5.0), (5.0), (7.0), (9.0)) t(x)
-- 0.65625

Extremes​

sr_min(x)​

Signature: sr_min(x DOUBLE[]) -> DOUBLE

Minimum value.

SELECT sr_min(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- -2.0

sr_max(x)​

Signature: sr_max(x DOUBLE[]) -> DOUBLE

Maximum value.

SELECT sr_max(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 3.0

sr_abs_min(x)​

Signature: sr_abs_min(x DOUBLE[]) -> DOUBLE

Smallest absolute value.

SELECT sr_abs_min(x) FROM (VALUES (0.0), (3.0), (-2.0)) t(x)
-- 0.0

sr_abs_max(x)​

Signature: sr_abs_max(x DOUBLE[]) -> DOUBLE

Largest absolute value.

SELECT sr_abs_max(x) FROM (VALUES (0.0), (3.0), (-8.0)) t(x)
-- 8.0

Covariance (two-column)​

sr_covariance(x, y)​

Signature: sr_covariance(x DOUBLE[], y DOUBLE[]) -> DOUBLE

Sample covariance (Bessel-corrected). A row with NULL in either column is skipped entirely. NULL when fewer than two fully non-NULL rows.

SELECT sr_covariance(x, y) FROM (VALUES (0.0, -5.0), (3.0, 4.0), (-2.0, 10.0)) t(x, y)
-- -11.5

sr_population_covariance(x, y)​

Signature: sr_population_covariance(x DOUBLE[], y DOUBLE[]) -> DOUBLE

Population covariance (divides by N). NULL when no fully non-NULL row exists.

SELECT sr_population_covariance(x, y) FROM (VALUES (0.0, -5.0), (3.0, 4.0), (-2.0, 10.0)) t(x, y)
-- -7.666666666666667

Using with GROUP BY​

SELECT g,
sr_mean(x) AS mean,
sr_median(x) AS median,
sr_std_dev(x) AS std_dev,
sr_min(x) AS min,
sr_max(x) AS max
FROM (VALUES (1, 2.5), (1, 3.1), (1, 1.8), (2, 7.2), (2, 8.1), (2, 6.9)) t(g, x)
GROUP BY g ORDER BY g

NULL handling​

-- NULL rows are skipped; the mean of [1, 3] is 2.0, not affected by the NULL
SELECT sr_mean(x) AS mean, sr_variance(x) AS var, sr_std_dev(x) AS sd
FROM (VALUES (1.0), (NULL), (3.0)) t(x)
-- Single value has no sample variance: result is NULL
SELECT sr_variance(x) FROM (VALUES (42.0)) t(x)
-- NULL