Skip to content

Fitting functions in the Stochastic DuckDB extension

Function category

Fitting

23 functions

dist_bernoulli_fit

Aggregate function Fitting Bernoulli
Signature
dist_bernoulli_fit(x: DOUBLE) → STRUCT(prob DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a bernoulli distribution by maximum likelihood
SELECT dist_bernoulli_fit(x) AS fit
FROM (VALUES (0), (1), (1), (0), (1), (1), (1), (0), (1), (0)) t(x);

Output

fit
{'prob': 0.6, 'log_likelihood': -6.730116670092564, 'aic': 15.460233340185129, 'bic': 15.762818433179174}

dist_beta_fit

Aggregate function Fitting Beta
Signature
dist_beta_fit(x: DOUBLE) → STRUCT(alpha DOUBLE, beta DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a beta distribution to proportions
SELECT dist_beta_fit(x) AS fit
FROM (VALUES (0.12), (0.31), (0.25), (0.08), (0.44), (0.19), (0.27)) t(x);

Output

fit
{'alpha': 3.1333610763000657, 'beta': 10.103705554724225, 'log_likelihood': 5.691911425810101, 'aic': -7.383822851620202, 'bic': -7.492002553509575}

dist_binomial_fit

Aggregate function Fitting Binomial
Signature
dist_binomial_fit(x: DOUBLE, trials: BIGINT) → STRUCT(prob DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Argument trials Type BIGINT Mode Positional Description
Description
1 Estimate the success probability from batches of 20 trials
SELECT round(dist_binomial_fit(defects, 20).prob, 6) AS prob
FROM (VALUES (3), (5), (2), (4), (1), (3)) t(defects);

Output

prob
0.15

dist_cauchy_fit

Aggregate function Fitting Cauchy
Signature
dist_cauchy_fit(x: DOUBLE) → STRUCT("location" DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a Cauchy distribution, robust to the outlier
SELECT dist_cauchy_fit(x) AS fit
FROM (VALUES (0.2), (-0.5), (1.1), (0.4), (-0.1), (45.0), (0.7), (0.0)) t(x);

Output

fit
{'location': 0.18918092986005372, 'scale': 0.3911158749331699, 'log_likelihood': -16.299967470868125, 'aic': 36.59993494173625, 'bic': 36.75881802509592}

dist_chi_squared_fit

Aggregate function Fitting Chi-squared
Signature
dist_chi_squared_fit(x: DOUBLE) → STRUCT(degrees_of_freedom DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a chi squared distribution by maximum likelihood
SELECT dist_chi_squared_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'degrees_of_freedom': 2.4395585639658903, 'log_likelihood': -16.65380346852325, 'aic': 35.3076069370465, 'bic': 35.610192030040544}

dist_exponential_fit

Aggregate function Fitting Exponential
Signature
dist_exponential_fit(x: DOUBLE) → STRUCT(rate DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit an exponential rate to waiting times
SELECT round(dist_exponential_fit(x).rate, 6) AS rate
FROM (VALUES (0.5), (1.2), (0.3), (2.0)) t(x);

Output

rate
1.0

dist_extreme_value_fit

Aggregate function Fitting Extreme Value
Signature
dist_extreme_value_fit(x: DOUBLE) → STRUCT("location" DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a extreme value distribution by maximum likelihood
SELECT dist_extreme_value_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'location': 1.3646057764916848, 'scale': 1.01710128234988, 'log_likelihood': -16.51499430573309, 'aic': 37.02998861146618, 'bic': 37.635158797454274}

dist_fits

Aggregate function Fitting
Signature
2 overloaded forms · click to inspect
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Rank chosen fits by AICc, best first
SELECT f.distribution,
       round(f.aicc, 2) AS aicc,
       round(f.delta_aicc, 2) AS delta_aicc,
       round(f.aicc_weight, 3) AS aicc_weight
FROM (
  SELECT unnest(dist_fits(x, ['gamma', 'lognormal', 'normal'])) AS f
  FROM (VALUES (2.1), (3.4), (2.9), (4.2), (3.3), (2.6), (5.8), (2.4)) t(x)
);

Output

distribution aicc delta_aicc aicc_weight
lognormal 28.61 0.0 0.482
gamma 29.18 0.57 0.362
normal 30.86 2.25 0.156
2 Try every continuous fit and keep the top three names
SELECT list_transform(dist_fits(x)[1:3], lambda f: f.distribution) AS best
FROM (VALUES (2.1), (3.4), (2.9), (4.2), (3.3), (2.6), (5.8), (2.4)) t(x);

Output

best
['inverse_gamma', 'extreme_value', 'inverse_gaussian']
3 A candidate that can't fit the sample comes last, with the reason in note
SELECT f.distribution, f.note
FROM (
  SELECT unnest(dist_fits(x, ['normal', 'gamma'])) AS f
  FROM (VALUES (-1.5), (0.5), (2.0), (0.7)) t(x)
);

Output

distribution note
normal NULL
gamma values must be > 0, got -1.5

dist_gamma_fit

Aggregate function Fitting Gamma
Signature
dist_gamma_fit(x: DOUBLE) → STRUCT(shape DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a gamma distribution to positive durations
SELECT dist_gamma_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2)) t(x);

Output

fit
{'shape': 2.5167864710333365, 'scale': 0.6903644866171825, 'log_likelihood': -10.910661400281635, 'aic': 25.82132280056327, 'bic': 25.98020588392294}

dist_geometric_fit

Aggregate function Fitting Geometric
Signature
dist_geometric_fit(x: DOUBLE) → STRUCT(prob DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a geometric distribution by maximum likelihood
SELECT dist_geometric_fit(x) AS fit
FROM (VALUES (2), (3), (1), (4), (2), (5), (3), (2), (6), (3)) t(x);

Output

fit
{'prob': 0.24390243902439027, 'log_likelihood': -22.777000465896627, 'aic': 47.554000931793254, 'bic': 47.8565860247873}

dist_inverse_gamma_fit

Aggregate function Fitting Inverse Gamma
Signature
dist_inverse_gamma_fit(x: DOUBLE) → STRUCT(shape DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a inverse gamma distribution by maximum likelihood
SELECT dist_inverse_gamma_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'shape': 1.9456573675692381, 'scale': 2.2614739187025505, 'log_likelihood': -15.993196506604518, 'aic': 35.98639301320904, 'bic': 36.591563199197125}

dist_inverse_gaussian_fit

Aggregate function Fitting Inverse Gaussian
Signature
dist_inverse_gaussian_fit(x: DOUBLE) → STRUCT(mean DOUBLE, shape DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a inverse gaussian distribution by maximum likelihood
SELECT dist_inverse_gaussian_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'mean': 2.01, 'shape': 2.756060007033965, 'log_likelihood': -15.553726161412696, 'aic': 35.107452322825395, 'bic': 35.71262250881348}

dist_laplace_fit

Aggregate function Fitting Laplace
Signature
dist_laplace_fit(x: DOUBLE) → STRUCT("location" DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a laplace distribution by maximum likelihood
SELECT dist_laplace_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'location': 1.5, 'scale': 1.15, 'log_likelihood': -18.32909122935104, 'aic': 40.65818245870208, 'bic': 41.26335264469017}

dist_logistic_fit

Aggregate function Fitting Logistic
Signature
dist_logistic_fit(x: DOUBLE) → STRUCT("location" DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Compare logistic and normal fits by AIC
SELECT round(dist_logistic_fit(x).aic, 4) AS logistic_aic, round(dist_normal_fit(x).aic, 4) AS normal_aic
FROM (VALUES (0.2), (-0.5), (1.1), (0.4), (-0.1), (2.9), (0.7), (0.0), (-1.8), (0.3)) t(x);

Output

logistic_aic normal_aic
34.0263 34.9066

dist_lognormal_fit

Aggregate function Fitting Log-normal
Signature
dist_lognormal_fit(x: DOUBLE) → STRUCT(mean DOUBLE, stddev DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a lognormal distribution by maximum likelihood
SELECT dist_lognormal_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'mean': 0.4288900973406523, 'stddev': 0.7552197869158146, 'log_likelihood': -15.670821669461231, 'aic': 35.34164333892247, 'bic': 35.94681352491055}

dist_negative_binomial_fit

Aggregate function Fitting Negative Binomial
Signature
dist_negative_binomial_fit(x: DOUBLE) → STRUCT(successes DOUBLE, prob DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit overdispersed counts; successes is a whole number
SELECT dist_negative_binomial_fit(x) AS fit
FROM (VALUES (0), (3), (1), (7), (2), (12), (0), (5), (1), (9)) t(x);

Output

fit
{'successes': 1.0, 'prob': 0.2, 'log_likelihood': -25.02012117690939, 'aic': 54.04024235381878, 'bic': 54.64541253980688}

dist_normal_fit

Aggregate function Fitting Normal
Signature
dist_normal_fit(x: DOUBLE) → STRUCT(mean DOUBLE, stddev DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a normal distribution by maximum likelihood
SELECT dist_normal_fit(x) AS fit
FROM (VALUES (2.1), (3.4), (2.9), (4.2), (3.3), (2.6)) t(x);

Output

fit
{'mean': 3.083333333333333, 'stddev': 0.6618576550554928, 'log_likelihood': -6.037402586784546, 'aic': 16.074805173569093, 'bic': 15.658324112025202}

dist_pareto_fit

Aggregate function Fitting Pareto
Signature
dist_pareto_fit(x: DOUBLE) → STRUCT(scale DOUBLE, shape DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a pareto distribution by maximum likelihood
SELECT dist_pareto_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'scale': 0.4, 'shape': 0.7433944777399986, 'log_likelihood': -17.254185468703348, 'aic': 38.508370937406696, 'bic': 39.11354112339479}

dist_poisson_fit

Aggregate function Fitting Poisson
Signature
dist_poisson_fit(x: DOUBLE) → STRUCT(rate DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a Poisson rate to daily counts
SELECT dist_poisson_fit(x).rate AS rate
FROM (VALUES (3), (5), (2), (4), (6), (4)) t(x);

Output

rate
4.0

dist_rayleigh_fit

Aggregate function Fitting Rayleigh
Signature
dist_rayleigh_fit(x: DOUBLE) → STRUCT(scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a rayleigh distribution by maximum likelihood
SELECT dist_rayleigh_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'scale': 1.7557049866079437, 'log_likelihood': -16.968508587157096, 'aic': 35.93701717431419, 'bic': 36.239602267308236}

dist_students_t_fit

Aggregate function Fitting Student's t
Signature
dist_students_t_fit(x: DOUBLE) → STRUCT(degrees_of_freedom DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a students t distribution by maximum likelihood
SELECT dist_students_t_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'degrees_of_freedom': 1.413024185041085, 'log_likelihood': -25.52061409183976, 'aic': 53.04122818367952, 'bic': 53.343813276673565}

dist_uniform_real_fit

Aggregate function Fitting Uniform (Real)
Signature
dist_uniform_real_fit(x: DOUBLE) → STRUCT(min DOUBLE, max DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a uniform real distribution by maximum likelihood
SELECT dist_uniform_real_fit(x) AS fit
FROM (VALUES (1.2), (0.7), (2.9), (1.8), (0.4), (3.6), (1.1), (2.2), (5.3), (0.9)) t(x);

Output

fit
{'min': 0.4, 'max': 5.3, 'log_likelihood': -15.892352051165808, 'aic': 35.78470410233162, 'bic': 36.38987428831971}

dist_weibull_fit

Aggregate function Fitting Weibull
Signature
dist_weibull_fit(x: DOUBLE) → STRUCT(shape DOUBLE, scale DOUBLE, log_likelihood DOUBLE, aic DOUBLE, bic DOUBLE)
Arguments (Positional)
Argument x Type DOUBLE Mode Positional Description
Description
1 Fit a Weibull distribution to component lifetimes
SELECT dist_weibull_fit(x) AS fit
FROM (VALUES (2.1), (3.4), (1.7), (4.0), (2.8), (3.1), (1.2), (2.5)) t(x);

Output

fit
{'shape': 3.4183090905293314, 'scale': 2.90120446838219, 'log_likelihood': -10.022056053941597, 'aic': 24.044112107883194, 'bic': 24.202995191242866}