stats_duck

13.1k downloads this week 62 stars Extension descriptor

A statistical computing toolkit for DuckDB, with descriptive statistics, hypothesis tests, R-style distribution functions (d/p/q/r), linear and generalized linear models with robust and cluster-robust standard errors, linear hypotheses and estimated marginal means, grammar-of-graphics charts (VISUALIZE to Vega-Lite), and readers and writers for SAS, SPSS and Stata files, all in SQL.

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL stats_duck FROM community;
LOAD stats_duck;

Example

-- R-style distribution functions (d/p/q/r for 15+ families):
D SELECT pnorm(1.96) AS p, qt(0.975, 30) AS t_crit, dpois(3, 2.5) AS pmf;
┌────────────────────┬────────────────────┬─────────────────────┐
│         p          │       t_crit       │         pmf         │
│       double       │       double       │       double        │
├────────────────────┼────────────────────┼─────────────────────┤
│ 0.9750021048517796 │ 2.0422724563012373 │ 0.21376301724973648 │
└────────────────────┴────────────────────┴─────────────────────┘

-- OLS regression as an aggregate (one model per GROUP BY), with robust SEs.
-- vcov: 'const' | 'HC0'..'HC3' | 'CR0'/'CR1' (cluster-robust, + a cluster key).
D SELECT (c).term, (c).estimate, (c).std_error
  FROM (
    SELECT unnest((lm_fit(y, [x1, x2], 'HC1')).coefficients) AS c
    FROM my_data
  );

-- A generalized linear model as an aggregate: poisson with a log link.
-- Families: gaussian, binomial, poisson, gamma. Options (link, vcov,
-- intercept, weights, offset, cluster) go in a trailing STRUCT.
D SELECT (c).term, (c).estimate, (c).std_error
  FROM (
    SELECT unnest((glm_fit(events, [x1, x2], 'poisson', {'vcov': 'HC1'})).coefficients) AS c
    FROM my_data
  );

-- A clinical "Table 1" over mixed variable types, with between-group tests:
D SELECT * FROM table_one('patients', variables := ['age', 'sex', 'bmi'], by := ['arm']);

-- Grammar-of-graphics: turn a query into a Vega-Lite spec (+ the SQL to feed it):
D VISUALIZE wt AS x, mpg AS y FROM cars DRAW point;

About stats_duck

The Stats Duck

A statistical computing toolkit that brings common statistical work into DuckDB SQL. Every function is a streaming aggregate or a scalar function, so it runs inside GROUP BY and window frames over large tables, and the same build runs in DuckDB-WASM in the browser.

What is included

• Descriptive statistics: summary_stats, table_one (the clinical "Table 1"), corr_matrix, meta.
• Hypothesis tests: one-sample, two-sample and paired t-tests, one-way ANOVA, chi-square, Mann-Whitney, Wilcoxon signed-rank, Kolmogorov-Smirnov, Shapiro-Wilk, Anderson-Darling, Jarque-Bera, and correlation tests (Pearson, Spearman, Kendall).
• Distribution functions: R-style d/p/q/r quartets for 15+ families (normal, t, chi-square, F, gamma, beta, exponential, Weibull, log-normal, Poisson, negative binomial, hypergeometric, and more), plus ptukey and qtukey for the studentized range.
• Regression: the lm and lm_summary table functions and the lm_fit aggregate (one model per GROUP BY) with classical, HC0 to HC3 heteroskedasticity-consistent, and CR0/CR1 cluster-robust standard errors. A rank-deficient design is fitted with the aliased columns reported NULL, as R does. glm_fit fits generalized linear models by iteratively reweighted least squares for the gaussian, binomial, poisson and gamma families and their links, with the same covariance options.
• Inference after a fit: lin_hyp tests a general linear hypothesis L·β = 0 on any fit's coefficients and covariance (contrasts, joint tests, Type III sums of squares). emmeans computes estimated marginal means and their pairwise differences, with Tukey, Bonferroni, Holm, Hochberg, BH or BY adjustment.
• Grammar of graphics: VISUALIZE … DRAW <mark> returns a Vega-Lite v5 spec plus the SQL needed to feed it.
• Statistical file formats: readers and writers for SAS (.sas7bdat, .xpt), SPSS (.sav, .por) and Stata files, via ReadStat.
• Resampling and correction: bootstrap, and adjust_p with the Bonferroni, Holm, Hochberg, BH, BY or no correction.

Full function reference: https://github.com/KoliStat/the-stats-duck.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings