Skip to main content

Discrete, multivariate and empirical sampling

Bernoulli​

sr_sample_bernoulli(p, k)​

Signature: sr_sample_bernoulli(p DOUBLE, k BIGINT) -> LIST(DOUBLE)

Every sample is 0 or 1:

SELECT sr_sample_bernoulli(0.5, 5)

Binomial​

sr_sample_binomial(p, n, k)​

Signature: sr_sample_binomial(p DOUBLE, n UBIGINT, k BIGINT) -> LIST(UBIGINT)

SELECT sr_sample_binomial(0.5, 10, 12)

sr_sample_binomial_algorithm(p, n, algorithm, k)​

Signature: sr_sample_binomial_algorithm(p DOUBLE, n UBIGINT, algorithm DOUBLE, k BIGINT) -> LIST(UBIGINT)

Explicit algorithm selection: algorithm is 1.0 = automatic, 2.0 = inversion, 3.0 = rejection.

SELECT sr_sample_binomial_algorithm(0.5, 10, 2.0, 4)

Negative binomial​

sr_sample_negative_binomial(r, p, k)​

Signature: sr_sample_negative_binomial(r DOUBLE, p DOUBLE, k BIGINT) -> LIST(UBIGINT)

SELECT sr_sample_negative_binomial(2.0, 0.5, 10)

Poisson​

sr_sample_poisson(lambda, k)​

Signature: sr_sample_poisson(lambda DOUBLE, k BIGINT) -> LIST(UBIGINT)

SELECT sr_sample_poisson(3.0, 10)

Geometric​

sr_sample_geometric(p, k)​

Signature: sr_sample_geometric(p DOUBLE, k BIGINT) -> LIST(UBIGINT)

SELECT sr_sample_geometric(0.5, 10)

Hypergeometric​

sr_sample_hypergeometric(population, successes, draws, k)​

Signature: sr_sample_hypergeometric(population UBIGINT, successes UBIGINT, draws UBIGINT, k BIGINT) -> LIST(UBIGINT)

SELECT sr_sample_hypergeometric(10, 5, 4, 8)

Categorical​

sr_sample_categorical(probabilities, k)​

Signature: sr_sample_categorical(probabilities DOUBLE[], k BIGINT) -> LIST(UBIGINT)

Each sample is a category index in 0 .. len(probabilities) - 1.

SELECT sr_sample_categorical([1.0, 2.0, 1.0], 5)

Discrete uniform​

sr_sample_discrete_uniform(min, max, k)​

Signature: sr_sample_discrete_uniform(min BIGINT, max BIGINT, k BIGINT) -> LIST(BIGINT)

SELECT sr_sample_discrete_uniform(1, 6, 10)

Multivariate normal​

sr_sample_multivariate_normal(mean, covariance, k)​

Signature: sr_sample_multivariate_normal(mean DOUBLE[], covariance DOUBLE[], k BIGINT) -> LIST(LIST(DOUBLE))

Returns a list of k point vectors, each of the same length as mean.

SELECT sr_sample_multivariate_normal([0.0, 0.0], [1.0, 0.0, 0.0, 1.0], 5)

Multivariate Student's t​

sr_sample_multivariate_students_t(location, scale, freedom, k)​

Signature: sr_sample_multivariate_students_t(location DOUBLE[], scale DOUBLE[], freedom DOUBLE, k BIGINT) -> LIST(LIST(DOUBLE))

Returns a list of k point vectors, each of the same length as location. scale is the row-major flattened scale matrix (len(location)² entries); a mismatch is a query error.

SELECT sr_sample_multivariate_students_t([0.0, 0.0], [1.0, 0.0, 0.0, 1.0], 3.0, 6)

Dirichlet​

sr_sample_dirichlet(alpha, k)​

Signature: sr_sample_dirichlet(alpha DOUBLE[], k BIGINT) -> LIST(LIST(DOUBLE))

Returns k points on the simplex: each draw is a vector of the same length as alpha whose components sum to 1.

SELECT sr_sample_dirichlet([1.0, 2.0], 8)

Multinomial​

sr_sample_multinomial(probs, trials, k)​

Signature: sr_sample_multinomial(probs DOUBLE[], trials UBIGINT, k BIGINT) -> LIST(LIST(UBIGINT))

Returns k count vectors of the same length as probs; the components of every count vector sum to trials exactly.

SELECT sr_sample_multinomial([0.3, 0.7], 10, 8)

Empirical (aggregate)​

sr_sample_empirical(v, k)​

Signature: sr_sample_empirical(v DOUBLE[], k DOUBLE) -> LIST(DOUBLE)

The only aggregate sampler: it collects the whole column v and resamples k points from its empirical distribution.

SELECT sr_sample_empirical(v, 6)
FROM (VALUES (1.0), (2.0), (3.0)) t(v)

Errors​

Unknown algorithm code:

SELECT sr_sample_binomial_algorithm(0.5, 10, 9.0, 4)
-- error: sr_sample_binomial_algorithm: the algorithm must be 1 (automatic), 2 (inversion) or 3 (rejection), got 9