You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This library is a DuckDB loadable extension (C++ extension API) that makes statistical functions directly available from SQL.
This reference covers a total of 259 functions: the 258 functions of
sqlite3-stats, with the scalar form of stat_bonferroni renamed to
stat_bonferroni_scalar (DuckDB cannot give a scalar function the name of an
aggregate / window function).
DB-independent: additional distribution functions, effect size conversions, power analysis, etc.
Basic Aggregate Functions through Two-Column Aggregate Functions (71 functions) are all aggregate functions and can be used in any context where DuckDB's built-in aggregate functions are available, including GROUP BY, HAVING, and subqueries.
Window Functions (26 functions) are implemented as full-scan type and return one value per row. Call them with OVER (ORDER BY ...); see How to Call.
Complex Aggregate Functions (41 functions) are aggregate functions (including those returning JSON results).
Scalar Functions (121 functions) compute results solely from their parameters.
Loading the Extension
The extension is unsigned, so DuckDB must be started with unsigned extensions allowed.
Loading from the duckdb CLI
-- start the CLI with: duckdb -unsigned
LOAD './build/ext_funcs.duckdb_extension';
Note: The extension is built with DuckDB's C++ extension API and loads only into the exact DuckDB version it was compiled against (v1.5.6).
Common Specifications
NULL Handling
Rows with NULL input values are ignored (following SQL aggregate function conventions)
Returns NULL when all rows are NULL or the result set is empty
Returns NULL when the computation results in NaN or Inf
NaN is a value in DuckDB, not NULL: it is passed to statcpp, which handles
it as R handles NA (a NaN result, rows dropped, or an error)
Window functions return one value per row instead: a NULL row returns
NULL, and the rolling functions return NULL for a window that contains a
NULL row (see Window Functions)
A NULL parameter or scalar-function argument gives NULL, as in SQL;
omit an optional parameter to use its default. See
NULL, NaN and Inf Notes for this and other cases
-- Data with NULL valuesCREATETABLEsample (val DOUBLE);
INSERT INTO sample VALUES (1), (NULL), (3), (NULL), (5);
-- NULLs are ignored; computed over 3 values: 1, 3, 5SELECT stat_mean(val) FROM sample;
-- → 3.0-- All rows are NULLSELECT stat_mean(NULL);
-- → NULL-- Empty result setSELECT stat_mean(val) FROM sample WHERE val >100;
-- → NULL
Minimum Data Count Requirements
Some functions require a minimum number of data points. When insufficient data is provided, NULL is returned.
Arguments outside the domain a function is defined on -- a probability above 1, a confidence
level outside (0, 1), a trim proportion of 0.5 or more -- are reported as ordinary SQL errors:
D SELECT stat_poisson_quantile(1.5, 2.5);
Invalid Input Error: statcpp::poisson_quantile: p must be in [0, 1]
The statement fails and the connection stays usable; the process is never terminated. This holds
for scalar, aggregate and window functions alike. Insufficient or empty input is not an error --
those cases return NULL as described above.
Using with GROUP BY
All aggregate functions can be used with GROUP BY.
SELECT category,
stat_mean(score) AS mean_score,
stat_median(score) AS median_score,
stat_sample_stddev(score) AS stddev_score
FROM exam_results
GROUP BY category;
SELECTCOUNT(val) AS n,
stat_mean(val) AS mean,
stat_median(val) AS median,
stat_mode(val) AS mode,
stat_sample_stddev(val) AS stddev,
stat_sample_variance(val) AS variance,
MIN(val) AS min,
MAX(val) AS max,
stat_range(val) AS range,
stat_iqr(val) AS iqr,
stat_se(val) AS se,
stat_skewness(val) AS skewness,
stat_kurtosis(val) AS kurtosis
FROM measurements;
Comparing Robust and Classical Statistics
-- Assess the impact of outliersSELECT'classical'AS type,
stat_mean(val) AS location,
stat_sample_stddev(val) AS spread
FROM data
UNION ALLSELECT'robust'AS type,
stat_hodges_lehmann(val) AS location,
stat_mad_scaled(val) AS spread
FROM data;
Detailed Analysis by Group
SELECT
category,
COUNT(score) AS n,
stat_mean(score) AS mean,
stat_median(score) AS median,
stat_sample_stddev(score) AS stddev,
stat_cv(score) AS cv,
stat_iqr(score) AS iqr,
stat_skewness(score) AS skewness,
stat_kurtosis(score) AS kurtosis,
stat_mad_scaled(score) AS robust_spread,
stat_hodges_lehmann(score) AS robust_location
FROM exam_results
GROUP BY category
ORDER BY mean DESC;
Quick Normality Assessment
-- Check normality of multiple columns at onceSELECT'height'AS variable,
stat_skewness(height) AS skewness,
stat_kurtosis(height) AS kurtosis,
CASE
WHEN ABS(stat_skewness(height)) <2AND ABS(stat_kurtosis(height)) <7
THEN 'approximately normal'
ELSE 'non-normal'
END AS normality
FROM people
UNION ALLSELECT'weight',
stat_skewness(weight),
stat_kurtosis(weight),
CASE
WHEN ABS(stat_skewness(weight)) <2AND ABS(stat_kurtosis(weight)) <7
THEN 'approximately normal'
ELSE 'non-normal'
END
FROM people;
Regression Analysis and Prediction Accuracy
-- Run regression analysis and evaluate accuracy at onceSELECT
stat_simple_regression(x, y) AS regression,
stat_pearson_r(x, y) AS correlation,
stat_r_squared(x, y) AS r_squared,
stat_mae(x, y) AS mae,
stat_rmse(x, y) AS rmse
FROM experiment_data;
Time Series Analysis Pipeline
-- Comprehensive time series analysis of stock price dataSELECTdate, close_price,
stat_moving_avg(close_price, 20) OVER (ORDER BYdate) AS sma_20,
stat_ema(close_price, 12) OVER (ORDER BYdate) AS ema_12,
stat_rolling_std(close_price, 20) OVER (ORDER BYdate) AS volatility,
stat_diff(close_price, 1) OVER (ORDER BYdate) AS daily_change,
stat_lag(close_price, 1) OVER (ORDER BYdate) AS prev_close
FROM stock_prices
ORDER BYdate;
Missing Value Handling and Outlier Removal
-- Impute missing values -> detect outliers -> analyze clean dataSELECT id, raw_value,
stat_fillna_interp(raw_value) OVER (ORDER BY id) AS filled,
stat_outliers_iqr(raw_value) OVER (ORDER BY id) AS is_outlier,
stat_winsorize(raw_value, 5) OVER (ORDER BY id) AS winsorized
FROM sensor_data
ORDER BY id;