Skip to main content

Empirical distribution (aggregates)

Every function on this page is an aggregate: it collects a whole DOUBLE column as the sample and evaluates an empirical statistic over it. cdf, sf and quantile take a constant second argument; the moments (mean, variance, std_dev, min, max, …) summarise the column on their own.

sr_empirical_cdf(v, x)​

Signature: sr_empirical_cdf(v DOUBLE[], x DOUBLE) -> DOUBLE

Empirical cumulative distribution function of the collected column v, evaluated at the constant x. Equivalent to the fraction of rows with v <= x.

An empty group yields NULL.

SELECT sr_empirical_cdf(v, 2.0)
FROM (VALUES (1.0), (2.0), (3.0), (4.0)) t(v)
-- 0.5

sr_empirical_sf(v, x)​

Signature: sr_empirical_sf(v DOUBLE[], x DOUBLE) -> DOUBLE

Empirical survival function of v evaluated at x. Equivalent to 1 - cdf(x).

SELECT sr_empirical_sf(v, 2.0)
FROM (VALUES (1.0), (2.0), (3.0), (4.0)) t(v)
-- 0.5

sr_empirical_quantile(v, p)​

Signature: sr_empirical_quantile(v DOUBLE[], p DOUBLE) -> DOUBLE

Empirical quantile function of v at the constant probability p in [0, 1].

SELECT sr_empirical_quantile(v, 0.5)
FROM (VALUES (1.0), (2.0), (3.0), (4.0)) t(v)
-- 1.999969482421875

sr_empirical_entropy(values)​

Signature: sr_empirical_entropy(values DOUBLE[]) -> DOUBLE

Empirical entropy of a DOUBLE column; always NULL, since statrs implements no entropy for the empirical distribution.

SELECT sr_empirical_entropy(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- NULL

sr_empirical_max(values)​

Signature: sr_empirical_max(values DOUBLE[]) -> DOUBLE

Empirical maximum of a DOUBLE column.

SELECT sr_empirical_max(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- 3.0

sr_empirical_mean(values)​

Signature: sr_empirical_mean(values DOUBLE[]) -> DOUBLE

Empirical mean of a DOUBLE column.

SELECT sr_empirical_mean(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- 2.0

sr_empirical_min(values)​

Signature: sr_empirical_min(values DOUBLE[]) -> DOUBLE

Empirical minimum of a DOUBLE column.

SELECT sr_empirical_min(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- 1.0

sr_empirical_skewness(values)​

Signature: sr_empirical_skewness(values DOUBLE[]) -> DOUBLE

Empirical skewness of a DOUBLE column; always NULL, since statrs implements no skewness for the empirical distribution.

SELECT sr_empirical_skewness(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- NULL

sr_empirical_std_dev(values)​

Signature: sr_empirical_std_dev(values DOUBLE[]) -> DOUBLE

Empirical standard deviation of a DOUBLE column.

SELECT sr_empirical_std_dev(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- 1.0

sr_empirical_variance(values)​

Signature: sr_empirical_variance(values DOUBLE[]) -> DOUBLE

Empirical variance of a DOUBLE column.

SELECT sr_empirical_variance(v) FROM (VALUES (1.0), (2.0), (3.0)) t(v)
-- 1.0

NULL rows and empty groups​

NULL rows are skipped by the collector, matching the SQL aggregate convention. An empty group reports NULL:

SELECT sr_empirical_cdf(v, 2.0)
FROM (VALUES (1.0)) t(v) WHERE 1 = 0
-- NULL

With GROUP BY​

SELECT g, sr_empirical_cdf(v, 2.0) AS cdf_at_2
FROM (VALUES (1, 1.0), (1, 2.0), (1, 3.0), (2, 2.0), (2, 4.0), (2, 6.0)) t(g, v)
GROUP BY g ORDER BY g