Anofox Statistics Extension - API Reference

Version: 0.6.0 DuckDB Version: 1.5.1+ Backend: Rust (anofox-regression 0.5.2, anofox-statistics 0.4.0, faer)

Overview

The Anofox Statistics Extension provides comprehensive regression analysis capabilities for DuckDB. Built with Rust for performance and reliability, it supports multiple regression methods including linear models, generalized linear models (GLM), augmented linear models (ALM), and constrained optimization (BLS/NNLS).

Quick Reference

Regression Methods

Method	Scalar	Aggregate	Documentation
OLS	ols_fit	ols_fit_agg	OLS
Ridge	ridge_fit	ridge_fit_agg	Ridge
Elastic Net	elasticnet_fit	elasticnet_fit_agg	Elastic Net
WLS	wls_fit	wls_fit_agg	WLS
RLS	rls_fit	rls_fit_agg	RLS
BLS	-	bls_fit_agg	BLS/NNLS
NNLS	-	nnls_fit_agg	BLS/NNLS
PLS	pls_fit	pls_fit_agg	PLS
Isotonic	isotonic_fit	isotonic_fit_agg	Isotonic
Quantile	quantile_fit	quantile_fit_agg	Quantile

GLM Functions

Method	Aggregate	Documentation
Poisson	poisson_fit_agg	Poisson
ALM	alm_fit_agg	ALM

Statistical Hypothesis Tests

Category	Function	Documentation
Parametric	t_test_agg, one_way_anova_agg	Hypothesis Tests
Nonparametric	mann_whitney_u_agg, kruskal_wallis_agg	Hypothesis Tests
Normality	shapiro_wilk_agg, jarque_bera_agg	Hypothesis Tests
Equivalence	tost_t_test_agg, tost_paired_agg	Hypothesis Tests

Correlation Tests

Function	Description	Documentation
pearson_agg	Pearson correlation	Correlation
spearman_agg	Spearman rank correlation	Correlation
kendall_agg	Kendall tau correlation	Correlation
distance_cor_agg	Distance correlation	Correlation
icc_agg	Intraclass correlation	Correlation

Categorical Tests

Function	Description	Documentation
chisq_test_agg	Chi-square independence	Categorical
fisher_exact_agg	Fisher's exact test	Categorical
mcnemar_agg	McNemar's test	Categorical
cramers_v_agg	Cramér's V	Categorical
cohen_kappa_agg	Cohen's kappa	Categorical

Diagnostics & Utilities

Function	Description	Documentation
vif, vif_agg	Variance Inflation Factor	Diagnostics
aic, bic	Model selection criteria	Diagnostics
residuals_diagnostics_agg	Residual analysis	Diagnostics
aid_agg, aid_anomaly_agg	Demand classification	AID

Table Macros

Macro	Description	Documentation
ols_fit_predict_by	Per-group OLS predictions	Table Macros
ridge_fit_predict_by	Per-group Ridge predictions	Table Macros
elasticnet_fit_predict_by	Per-group Elastic Net	Table Macros
wls_fit_predict_by	Per-group WLS	Table Macros
rls_fit_predict_by	Per-group RLS	Table Macros
bls_fit_predict_by	Per-group BLS	Table Macros
alm_fit_predict_by	Per-group ALM	Table Macros
poisson_fit_predict_by	Per-group Poisson	Table Macros
aid_anomaly_by	Grouped anomaly detection	Table Macros

Deprecation Notice: The old *_predict_agg names (ols_predict_agg, etc.) are deprecated but still work for backwards compatibility. Use *_fit_predict_agg instead.

---

Function Types

Scalar Functions (Array-based)

Process complete arrays of data in a single call. Best for batch operations.

SELECT ols_fit(y_array, x_arrays);

Aggregate Functions (Streaming)

Accumulate data row-by-row. Support GROUP BY and window functions via OVER.

SELECT ols_fit_agg(y, [x1, x2]) FROM table GROUP BY category;

Table Macros

Convenience wrappers for per-group regression. All source columns are passed through to the output alongside predictions.

SELECT * FROM ols_fit_predict_by('sales', region, revenue, [ads, price]);

---

OLS Functions

anofox_stats_ols_fit

Ordinary Least Squares regression using SVD decomposition.

Signature:

anofox_stats_ols_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    [fit_intercept BOOLEAN DEFAULT true],
    [compute_inference BOOLEAN DEFAULT false],
    [confidence_level DOUBLE DEFAULT 0.95]
) -> STRUCT

Parameters:

Parameter	Type	Description
y	LIST(DOUBLE)	Response variable values
x	LIST(LIST(DOUBLE))	Feature arrays (each inner list is one feature)
fit_intercept	BOOLEAN	Include intercept term (default: true)
compute_inference	BOOLEAN	Compute t-tests, p-values, CIs (default: false)
confidence_level	DOUBLE	CI confidence level (default: 0.95)

Returns: FitResult STRUCT

Example:

-- Simple regression: y = 2x + 1
SELECT anofox_stats_ols_fit(
    [3.0, 5.0, 7.0, 9.0, 11.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]]
);

-- With inference
SELECT anofox_stats_ols_fit(
    [3.0, 5.0, 7.0, 9.0, 11.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]],
    true, true, 0.95
);

anofox_stats_ols_fit_agg

Streaming OLS regression aggregate function.

Signature:

anofox_stats_ols_fit_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [fit_intercept BOOLEAN DEFAULT true],
    [compute_inference BOOLEAN DEFAULT false],
    [confidence_level DOUBLE DEFAULT 0.95]
) -> STRUCT

Example:

-- Per-group regression
SELECT
    category,
    (anofox_stats_ols_fit_agg(sales, [price, ads])).r_squared
FROM data
GROUP BY category;

-- Rolling regression (window function)
SELECT
    date,
    (anofox_stats_ols_fit_agg(y, [x]) OVER (
        ORDER BY date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    )).coefficients[1] as rolling_beta
FROM time_series;

---

Ridge Functions

anofox_stats_ridge_fit

Ridge regression with L2 regularization.

Signature:

anofox_stats_ridge_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    alpha DOUBLE,
    [fit_intercept BOOLEAN DEFAULT true],
    [compute_inference BOOLEAN DEFAULT false],
    [confidence_level DOUBLE DEFAULT 0.95]
) -> STRUCT

Parameters:

Parameter	Type	Description
alpha	DOUBLE	L2 regularization strength (>= 0)

Example:

SELECT anofox_stats_ridge_fit(
    [2.1, 4.0, 5.9, 8.1, 10.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]],
    0.1  -- alpha
);

anofox_stats_ridge_fit_agg

Streaming Ridge regression aggregate function.

SELECT
    (anofox_stats_ridge_fit_agg(y, [x1, x2], 0.5)).coefficients
FROM data;

---

Elastic Net Functions

anofox_stats_elasticnet_fit

Elastic Net regression with combined L1/L2 regularization.

Signature:

anofox_stats_elasticnet_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    alpha DOUBLE,
    l1_ratio DOUBLE,
    [fit_intercept BOOLEAN DEFAULT true],
    [max_iterations INTEGER DEFAULT 1000],
    [tolerance DOUBLE DEFAULT 1e-6]
) -> STRUCT

Parameters:

Parameter	Type	Description
alpha	DOUBLE	Regularization strength (>= 0)
l1_ratio	DOUBLE	L1 ratio: 0=Ridge, 1=Lasso (range: 0-1)
max_iterations	INTEGER	Max coordinate descent iterations
tolerance	DOUBLE	Convergence tolerance

Example:

SELECT anofox_stats_elasticnet_fit(
    [2.1, 4.0, 5.9, 8.1, 10.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]],
    0.1,  -- alpha
    0.5   -- l1_ratio (50% L1, 50% L2)
);

anofox_stats_elasticnet_fit_agg

Streaming Elastic Net aggregate function.

---

WLS Functions

anofox_stats_wls_fit

Weighted Least Squares regression.

Signature:

anofox_stats_wls_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    weights LIST(DOUBLE),
    [fit_intercept BOOLEAN DEFAULT true],
    [compute_inference BOOLEAN DEFAULT false],
    [confidence_level DOUBLE DEFAULT 0.95]
) -> STRUCT

Parameters:

Parameter	Type	Description
weights	LIST(DOUBLE)	Observation weights (same length as y)

Example:

SELECT anofox_stats_wls_fit(
    [3.0, 5.0, 7.0, 9.0, 11.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]],
    [1.0, 2.0, 3.0, 2.0, 1.0]  -- higher weight for middle observations
);

anofox_stats_wls_fit_agg

Streaming WLS aggregate function.

SELECT anofox_stats_wls_fit_agg(y, [x], weight) FROM data;

---

RLS Functions

anofox_stats_rls_fit

Recursive Least Squares for online/adaptive regression.

Signature:

anofox_stats_rls_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    [forgetting_factor DOUBLE DEFAULT 1.0],
    [fit_intercept BOOLEAN DEFAULT true],
    [initial_p_diagonal DOUBLE DEFAULT 100.0]
) -> STRUCT

Parameters:

Parameter	Type	Description
forgetting_factor	DOUBLE	Exponential forgetting (0.95-1.0 typical)
initial_p_diagonal	DOUBLE	Initial covariance matrix diagonal

Example:

SELECT anofox_stats_rls_fit(
    [3.0, 5.0, 7.0, 9.0, 11.0],
    [[1.0, 2.0, 3.0, 4.0, 5.0]],
    0.99,  -- forgetting_factor
    true,  -- fit_intercept
    100.0  -- initial_p_diagonal
);

anofox_stats_rls_fit_agg

Streaming RLS aggregate function. Ideal for adaptive/online learning.

-- Adaptive regression with exponential forgetting
SELECT anofox_stats_rls_fit_agg(y, [x], 0.95) FROM streaming_data;

---

PLS Functions

Partial Least Squares regression for high-dimensional data and multicollinearity.

anofox_stats_pls_fit / pls_fit

PLS regression using the SIMPLS algorithm to find latent components that maximize covariance between X scores and y.

Signature:

anofox_stats_pls_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
n_components	INTEGER	2	Number of latent components to extract
fit_intercept	BOOLEAN	true	Include intercept term

Returns:

STRUCT(
    coefficients LIST(DOUBLE),  -- Regression coefficients
    intercept DOUBLE,           -- Intercept term (if fitted)
    r_squared DOUBLE,           -- Coefficient of determination
    n_components INTEGER,       -- Number of components used
    n_observations BIGINT,      -- Number of observations
    n_features INTEGER          -- Number of features
)

Example:

-- PLS with 3 components for high-dimensional data
SELECT pls_fit(
    [y1, y2, y3, y4, y5],
    [[x1_1, x1_2, x1_3, x1_4, x1_5],
     [x2_1, x2_2, x2_3, x2_4, x2_5],
     [x3_1, x3_2, x3_3, x3_4, x3_5]],
    {'n_components': 2}
);

-- Per-group PLS regression
SELECT
    category,
    (pls_fit_agg(y, [x1, x2, x3, x4, x5], {'n_components': 2})).r_squared
FROM high_dim_data
GROUP BY category;

Use Cases:

• High-dimensional data (more features than observations)
• Multicollinearity in predictors
• Chemometrics and spectroscopy
• Genomics and bioinformatics

anofox_stats_pls_fit_agg / pls_fit_agg

Streaming PLS regression aggregate function.

SELECT pls_fit_agg(y, [x1, x2, x3], {'n_components': 2}) FROM data;

---

Isotonic Functions

Isotonic regression for monotonic constraints.

anofox_stats_isotonic_fit / isotonic_fit

Fits a monotonic (non-decreasing or non-increasing) function to the data using pool adjacent violators algorithm (PAVA).

Signature:

anofox_stats_isotonic_fit(
    x LIST(DOUBLE),
    y LIST(DOUBLE),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
increasing	BOOLEAN	true	Fit increasing (true) or decreasing (false) function

Returns:

STRUCT(
    fitted_values LIST(DOUBLE),  -- Monotonic fitted values
    r_squared DOUBLE,            -- Coefficient of determination
    n_observations BIGINT,       -- Number of observations
    increasing BOOLEAN           -- Direction of monotonicity
)

Example:

-- Fit increasing monotonic function (e.g., dose-response curve)
SELECT isotonic_fit(
    [1.0, 2.0, 3.0, 4.0, 5.0],
    [1.5, 2.0, 1.8, 3.5, 4.0],  -- Noisy but generally increasing
    {'increasing': true}
);

-- Decreasing isotonic regression (e.g., decay curve)
SELECT isotonic_fit(
    dose_levels,
    response_values,
    {'increasing': false}
);

Use Cases:

• Dose-response modeling in pharmacology
• Calibration curves
• Monotonic trend estimation
• Quality control thresholds

anofox_stats_isotonic_fit_agg / isotonic_fit_agg

Streaming isotonic regression aggregate function.

SELECT isotonic_fit_agg(x, y, {'increasing': true}) FROM calibration_data;

---

Quantile Functions

Quantile regression for estimating conditional quantiles.

anofox_stats_quantile_fit / quantile_fit

Quantile regression estimates conditional quantiles of the response variable distribution, rather than the conditional mean. Robust to outliers.

Signature:

anofox_stats_quantile_fit(
    y LIST(DOUBLE),
    x LIST(LIST(DOUBLE)),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
tau	DOUBLE	0.5	Quantile to estimate (0 < tau < 1)
fit_intercept	BOOLEAN	true	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-6	Convergence tolerance

Returns:

STRUCT(
    coefficients LIST(DOUBLE),  -- Regression coefficients
    intercept DOUBLE,           -- Intercept term (if fitted)
    tau DOUBLE,                 -- Quantile estimated
    n_observations BIGINT,      -- Number of observations
    n_features INTEGER          -- Number of features
)

Example:

-- Median regression (tau = 0.5) - robust to outliers
SELECT quantile_fit(
    [y1, y2, y3, y4, y5],
    [[x1, x2, x3, x4, x5]],
    {'tau': 0.5}
);

-- 90th percentile regression (upper bound estimation)
SELECT quantile_fit(
    prices,
    [size, location_score],
    {'tau': 0.9}
);

-- Compare different quantiles
SELECT
    0.25 as quantile, (quantile_fit(y, [x], {'tau': 0.25})).coefficients[1] as coef
UNION ALL
SELECT
    0.50 as quantile, (quantile_fit(y, [x], {'tau': 0.50})).coefficients[1] as coef
UNION ALL
SELECT
    0.75 as quantile, (quantile_fit(y, [x], {'tau': 0.75})).coefficients[1] as coef;

Use Cases:

• Robust regression (outlier resistant)
• Understanding full response distribution
• Risk analysis (VaR, conditional tail expectations)
• Heteroscedastic data analysis

anofox_stats_quantile_fit_agg / quantile_fit_agg

Streaming quantile regression aggregate function.

-- Per-group median regression
SELECT
    region,
    (quantile_fit_agg(price, [sqft, bedrooms], {'tau': 0.5})).coefficients
FROM housing
GROUP BY region;

---

GLM Functions

Generalized Linear Models for count data and other non-normal response distributions.

anofox_stats_poisson_fit_agg / poisson_fit_agg

Poisson regression for count data using maximum likelihood estimation.

Signature:

anofox_stats_poisson_fit_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
link	VARCHAR	'log'	Link function: 'log', 'identity', 'sqrt'
max_iterations	INTEGER	100	Maximum IRLS iterations
tolerance	DOUBLE	1e-8	Convergence tolerance
compute_inference	BOOLEAN	false	Compute z-tests, p-values, CIs
confidence_level	DOUBLE	0.95	CI confidence level

Returns: GlmFitResult STRUCT

Example:

-- Basic Poisson regression for count data
SELECT poisson_fit_agg(count, [x1, x2])
FROM event_counts;

-- With inference and custom link
SELECT poisson_fit_agg(
    accidents,
    [traffic_volume, weather_score],
    {'compute_inference': true, 'link': 'log'}
)
FROM daily_accidents;

-- Per-group Poisson regression
SELECT
    region,
    (poisson_fit_agg(sales_count, [price, ads])).coefficients
FROM sales_data
GROUP BY region;

Use Cases:

• Modeling count data (events, occurrences, frequencies)
• Rate modeling with exposure offsets
• Insurance claims, website visits, defect counts

---

ALM Functions

Augmented Linear Models with 24 error distribution families for flexible regression.

anofox_stats_alm_fit_agg / alm_fit_agg

Fit an Augmented Linear Model with choice of distribution and loss function.

Signature:

anofox_stats_alm_fit_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
distribution	VARCHAR	'normal'	Error distribution (see below)
loss	VARCHAR	'likelihood'	Loss function: 'likelihood', 'mse', 'mae', 'ham', 'role'
max_iterations	INTEGER	100	Maximum iterations
tolerance	DOUBLE	1e-8	Convergence tolerance
quantile	DOUBLE	0.5	Quantile for asymmetric_laplace
role_trim	DOUBLE	0.05	Trim parameter for ROLE loss
compute_inference	BOOLEAN	false	Compute t-tests, p-values, CIs
confidence_level	DOUBLE	0.95	CI confidence level

Supported Distributions:

Category	Distributions
Continuous (unbounded)	normal, laplace, student_t, logistic, asymmetric_laplace, generalised_normal, s
Continuous (positive)	log_normal, log_laplace, log_s, log_generalised_normal, gamma, inverse_gaussian, exponential
Continuous (bounded)	folded_normal, rectified_normal, box_cox_normal, beta, logit_normal
Count	poisson, negative_binomial, binomial, geometric
Ordinal	cumulative_logistic, cumulative_normal

Returns: AlmFitResult STRUCT

Example:

-- Robust regression with Laplace distribution (median regression)
SELECT alm_fit_agg(y, [x1, x2], {'distribution': 'laplace'})
FROM data_with_outliers;

-- Quantile regression (75th percentile)
SELECT alm_fit_agg(
    price,
    [sqft, bedrooms],
    {'distribution': 'asymmetric_laplace', 'quantile': 0.75}
)
FROM housing;

-- Gamma regression for positive data
SELECT alm_fit_agg(
    claim_amount,
    [age, risk_score],
    {'distribution': 'gamma', 'compute_inference': true}
)
FROM insurance_claims;

-- Beta regression for proportions (0-1)
SELECT alm_fit_agg(
    conversion_rate,
    [ad_spend, page_views],
    {'distribution': 'beta'}
)
FROM marketing_data;

Use Cases:

• Robust regression (Laplace, Student-t)
• Quantile regression (asymmetric_laplace)
• Positive outcomes (gamma, log_normal)
• Proportions/rates (beta, logit_normal)
• Count data alternatives (negative_binomial)

---

BLS/NNLS Functions

Bounded Least Squares and Non-Negative Least Squares for constrained optimization.

anofox_stats_bls_fit_agg / bls_fit_agg

Bounded Least Squares with box constraints on coefficients.

Signature:

anofox_stats_bls_fit_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	false	Include intercept term
lower_bound	DOUBLE	-	Lower bound for all coefficients
upper_bound	DOUBLE	-	Upper bound for all coefficients
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-10	Convergence tolerance

Returns: BlsFitResult STRUCT

Example:

-- Coefficients bounded between 0 and 1
SELECT bls_fit_agg(
    y,
    [x1, x2, x3],
    {'lower_bound': 0.0, 'upper_bound': 1.0}
)
FROM portfolio_data;

-- Only lower bound (coefficients >= 0)
SELECT bls_fit_agg(
    y,
    [x1, x2],
    {'lower_bound': 0.0}
)
FROM data;

anofox_stats_nnls_fit_agg / nnls_fit_agg

Non-Negative Least Squares - all coefficients constrained to be >= 0.

Signature:

anofox_stats_nnls_fit_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	false	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-10	Convergence tolerance

Returns: BlsFitResult STRUCT

Example:

-- Non-negative coefficients (e.g., mixture models)
SELECT nnls_fit_agg(spectrum, [component1, component2, component3])
FROM spectral_data;

-- Portfolio weights (no short selling)
SELECT nnls_fit_agg(returns, [stock1, stock2, stock3])
FROM portfolio_data;

-- Per-group NNLS
SELECT
    category,
    (nnls_fit_agg(y, [x1, x2])).coefficients
FROM data
GROUP BY category;

Use Cases:

• Spectral unmixing / mixture models
• Portfolio optimization without short selling
• Physical constraints (concentrations, weights must be positive)
• Image processing (non-negative matrix factorization)

---

AID Functions

AID (Automatic Identification of Demand) provides demand pattern classification and anomaly detection for time series data. Useful for inventory management, supply chain analysis, and demand forecasting.

anofox_stats_aid_agg / aid_agg

Classifies demand patterns as regular or intermittent, identifies best-fit distribution, and detects various anomaly patterns.

Signature:

anofox_stats_aid_agg(
    y DOUBLE,
    [options MAP]
) -> STRUCT

Options MAP:

Key	Type	Default	Description
intermittent_threshold	DOUBLE	0.3	Zero proportion cutoff for intermittent classification
outlier_method	VARCHAR	'zscore'	Outlier detection: 'zscore' (mean±3σ) or 'iqr' (1.5×IQR)

Returns:

STRUCT(
    demand_type VARCHAR,           -- 'regular' or 'intermittent'
    is_intermittent BOOLEAN,       -- True if zero_proportion >= threshold
    distribution VARCHAR,          -- Best-fit distribution name
    mean DOUBLE,                   -- Mean of values
    variance DOUBLE,               -- Variance of values
    zero_proportion DOUBLE,        -- Proportion of zero values
    n_observations BIGINT,         -- Number of observations
    has_stockouts BOOLEAN,         -- True if stockouts detected
    is_new_product BOOLEAN,        -- True if new product pattern (leading zeros)
    is_obsolete_product BOOLEAN,   -- True if obsolete pattern (trailing zeros)
    stockout_count BIGINT,         -- Number of stockout observations
    new_product_count BIGINT,      -- Number of leading zero observations
    obsolete_product_count BIGINT, -- Number of trailing zero observations
    high_outlier_count BIGINT,     -- Number of unusually high values
    low_outlier_count BIGINT       -- Number of unusually low values
)

Distribution Selection:

• Count-like data: poisson, negative_binomial, geometric
• Continuous data: normal, gamma, lognormal, rectified_normal

Example:

-- Classify demand pattern for each SKU
SELECT
    sku,
    (aid_agg(demand)).*
FROM sales
GROUP BY sku;

-- With custom threshold
SELECT aid_agg(demand, {'intermittent_threshold': 0.4})
FROM sales
WHERE sku = 'WIDGET001';

-- Using IQR-based outlier detection
SELECT aid_agg(demand, {'outlier_method': 'iqr'})
FROM inventory_data;

anofox_stats_aid_anomaly_agg / aid_anomaly_agg

Returns per-observation anomaly flags for demand analysis. Maintains input order.

Signature:

anofox_stats_aid_anomaly_agg(
    y DOUBLE,
    [options MAP]
) -> LIST(STRUCT)

Options MAP:

Key	Type	Default	Description
intermittent_threshold	DOUBLE	0.3	Zero proportion cutoff
outlier_method	VARCHAR	'zscore'	Outlier detection: 'zscore' or 'iqr'

Returns:

LIST(STRUCT(
    stockout BOOLEAN,              -- Unexpected zero in positive demand
    new_product BOOLEAN,           -- Leading zeros pattern
    obsolete_product BOOLEAN,      -- Trailing zeros pattern
    high_outlier BOOLEAN,          -- Unusually high value
    low_outlier BOOLEAN            -- Unusually low value
))

Anomaly Definitions:

• Stockout: Zero value occurring between non-zero values (not at start or end)
• New Product: Leading sequence of zeros (before first non-zero)
• Obsolete Product: Trailing sequence of zeros (after last non-zero)
• High Outlier: Value > mean + 3std (zscore) or > Q3 + 1.5IQR (iqr)
• Low Outlier: Non-zero value < mean - 3std (zscore) or < Q1 - 1.5IQR (iqr)

Example:

-- Get anomaly flags for demand series
SELECT aid_anomaly_agg(demand)
FROM (VALUES (0), (0), (5), (0), (8), (0), (0)) AS t(demand);
-- Returns: [
--   {stockout: false, new_product: true, ...},   -- Leading zero
--   {stockout: false, new_product: true, ...},   -- Leading zero
--   {stockout: false, new_product: false, ...},  -- First non-zero
--   {stockout: true, new_product: false, ...},   -- Stockout (zero between)
--   {stockout: false, new_product: false, ...},  -- Normal
--   {stockout: false, obsolete_product: true,...}, -- Trailing zero
--   {stockout: false, obsolete_product: true,...}  -- Trailing zero
-- ]

-- Identify problematic SKUs with stockouts
WITH anomalies AS (
    SELECT sku, aid_agg(demand) as result
    FROM sales
    GROUP BY sku
)
SELECT sku, result.stockout_count
FROM anomalies
WHERE result.has_stockouts
ORDER BY result.stockout_count DESC;

Use Cases:

• Inventory management: Identify stockout patterns
• Product lifecycle: Detect new/obsolete products
• Demand forecasting: Choose appropriate models based on pattern type
• Data quality: Find outliers in demand data
• Supply chain: Monitor for demand anomalies

aid_by

Table macro that classifies demand patterns for each group, returning one row per group with flat columns.

Signature:

aid_by(
    source VARCHAR,           -- Table name (as string)
    group_col COLUMN,         -- Column to group by
    y_col COLUMN,             -- Demand/value column
    [options MAP]             -- Optional configuration (default: NULL)
) -> TABLE

Options:

Key	Type	Default	Description
intermittent_threshold	DOUBLE	0.3	Zero proportion cutoff for intermittent classification
outlier_method	VARCHAR	'zscore'	Outlier detection: 'zscore' (mean±3σ) or 'iqr' (1.5×IQR)

Returns:

Column	Type	Description
group_id	ANY	Group identifier (same type as group_col)
demand_type	VARCHAR	'regular' or 'intermittent'
is_intermittent	BOOLEAN	True if zero_proportion >= threshold
distribution	VARCHAR	Best-fit distribution name
mean	DOUBLE	Mean of values
variance	DOUBLE	Variance of values
zero_proportion	DOUBLE	Proportion of zero values (0.0 to 1.0)
n_observations	BIGINT	Number of observations
has_stockouts	BOOLEAN	True if stockouts detected
is_new_product	BOOLEAN	True if new product pattern (leading zeros)
is_obsolete_product	BOOLEAN	True if obsolete pattern (trailing zeros)
stockout_count	BIGINT	Number of stockout observations
new_product_count	BIGINT	Number of leading zero observations
obsolete_product_count	BIGINT	Number of trailing zero observations
high_outlier_count	BIGINT	Number of unusually high values
low_outlier_count	BIGINT	Number of unusually low values

Example:

-- Classify demand pattern for each SKU
SELECT * FROM aid_by('sales', sku, demand);

-- With custom intermittent threshold
SELECT * FROM aid_by('sales', sku, demand, {'intermittent_threshold': 0.4});

-- Find products with stockout issues
SELECT * FROM aid_by('sales', sku, demand)
WHERE has_stockouts
ORDER BY stockout_count DESC;

---

Statistical Hypothesis Testing Functions

Comprehensive statistical hypothesis testing powered by the anofox-statistics crate. All tests are implemented as aggregate functions that collect data and compute test results.

Distributional Tests

shapiro_wilk_agg / anofox_stats_shapiro_wilk_agg

Shapiro-Wilk test for normality. Tests whether a sample comes from a normal distribution.

Signature:

shapiro_wilk_agg(value DOUBLE) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- W statistic (closer to 1 = more normal)
    p_value DOUBLE,      -- p-value (low = reject normality)
    n BIGINT,            -- Sample size
    method VARCHAR       -- "Shapiro-Wilk"
)

Example:

-- Test normality of residuals
SELECT (shapiro_wilk_agg(residual)).p_value as normality_p
FROM model_diagnostics;

-- Per-group normality test
SELECT
    category,
    (shapiro_wilk_agg(value)).*
FROM data
GROUP BY category;

Parametric Tests

t_test_agg / anofox_stats_t_test_agg

Two-sample t-test comparing means of two groups. Supports both Student's t-test (equal variances) and Welch's t-test (unequal variances).

Signature:

t_test_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'
confidence_level	DOUBLE	0.95	Confidence level for CI
kind	VARCHAR	'welch'	'welch' (default) or 'student' (var_equal=true)
mu	DOUBLE	0.0	Hypothesized mean difference

Returns:

STRUCT(
    statistic DOUBLE,     -- t-statistic
    p_value DOUBLE,       -- p-value
    df DOUBLE,            -- Degrees of freedom
    effect_size DOUBLE,   -- Cohen's d
    ci_lower DOUBLE,      -- CI lower bound
    ci_upper DOUBLE,      -- CI upper bound
    n1 BIGINT,            -- Group 1 sample size
    n2 BIGINT,            -- Group 2 sample size
    method VARCHAR        -- "Welch's t-test" or "Student's t-test"
)

Example:

-- Compare treatment vs control (group_id: 0 = control, 1 = treatment)
SELECT (t_test_agg(outcome, treatment_group)).*
FROM experiment;

-- One-sided test (treatment > control)
SELECT t_test_agg(score, group, {'alternative': 'greater'})
FROM test_results;

-- Student's t-test (assuming equal variances)
SELECT t_test_agg(value, group, {'kind': 'student'})
FROM data;

one_way_anova_agg / anofox_stats_one_way_anova_agg

One-way Analysis of Variance for comparing means across multiple groups.

Signature:

one_way_anova_agg(value DOUBLE, group_id INTEGER) -> STRUCT

Returns:

STRUCT(
    f_statistic DOUBLE,   -- F-statistic
    p_value DOUBLE,       -- p-value
    df_between BIGINT,    -- Between-groups degrees of freedom
    df_within BIGINT,     -- Within-groups degrees of freedom
    ss_between DOUBLE,    -- Between-groups sum of squares
    ss_within DOUBLE,     -- Within-groups sum of squares
    n_groups BIGINT,      -- Number of groups
    n BIGINT,             -- Total sample size
    method VARCHAR        -- "One-Way ANOVA"
)

Example:

-- Compare means across multiple treatment groups
SELECT (one_way_anova_agg(response, treatment_group)).*
FROM clinical_trial;

-- Per-study ANOVA
SELECT
    study_id,
    (one_way_anova_agg(value, condition)).p_value as anova_p
FROM multi_study_data
GROUP BY study_id;

Nonparametric Tests

mann_whitney_u_agg / anofox_stats_mann_whitney_u_agg

Mann-Whitney U test (Wilcoxon rank-sum test). Non-parametric alternative to independent t-test.

Signature:

mann_whitney_u_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'
confidence_level	DOUBLE	0.95	Confidence level for CI
correction	BOOLEAN	true	Apply continuity correction

Returns:

STRUCT(
    statistic DOUBLE,     -- U statistic
    p_value DOUBLE,       -- p-value
    effect_size DOUBLE,   -- Rank-biserial correlation
    ci_lower DOUBLE,      -- CI lower bound
    ci_upper DOUBLE,      -- CI upper bound
    n1 BIGINT,            -- Group 1 sample size
    n2 BIGINT,            -- Group 2 sample size
    method VARCHAR        -- "Mann-Whitney U"
)

Example:

-- Non-parametric comparison of two groups
SELECT (mann_whitney_u_agg(score, group)).*
FROM non_normal_data;

-- One-sided test
SELECT mann_whitney_u_agg(rating, condition, {'alternative': 'greater'})
FROM survey_results;

kruskal_wallis_agg / anofox_stats_kruskal_wallis_agg

Kruskal-Wallis H test. Non-parametric alternative to one-way ANOVA.

Signature:

kruskal_wallis_agg(value DOUBLE, group_id INTEGER) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- H statistic
    p_value DOUBLE,      -- p-value
    df DOUBLE,           -- Degrees of freedom (k-1)
    n BIGINT,            -- Total sample size
    method VARCHAR       -- "Kruskal-Wallis"
)

Example:

-- Non-parametric comparison of multiple groups
SELECT (kruskal_wallis_agg(satisfaction, department)).*
FROM employee_survey;

Correlation Tests

pearson_agg / anofox_stats_pearson_agg

Pearson product-moment correlation with significance test.

Signature:

pearson_agg(x DOUBLE, y DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
confidence_level	DOUBLE	0.95	Confidence level for CI

Returns:

STRUCT(
    r DOUBLE,             -- Correlation coefficient (-1 to 1)
    statistic DOUBLE,     -- t-statistic
    p_value DOUBLE,       -- p-value (test r ≠ 0)
    ci_lower DOUBLE,      -- CI lower bound (Fisher z-transformed)
    ci_upper DOUBLE,      -- CI upper bound
    n BIGINT,             -- Sample size
    method VARCHAR        -- "Pearson"
)

Example:

-- Test correlation between two variables
SELECT (pearson_agg(height, weight)).*
FROM measurements;

-- Per-group correlation with 99% CI
SELECT
    region,
    (pearson_agg(income, spending, {'confidence_level': 0.99})).*
FROM economic_data
GROUP BY region;

spearman_agg / anofox_stats_spearman_agg

Spearman rank correlation with significance test. Robust to outliers and non-linear relationships.

Signature:

spearman_agg(x DOUBLE, y DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
confidence_level	DOUBLE	0.95	Confidence level for CI

Returns: Same structure as pearson_agg with method "Spearman"

Example:

-- Rank correlation for ordinal data
SELECT (spearman_agg(rank_x, rank_y)).*
FROM ranked_data;

Categorical Tests

chisq_test_agg / anofox_stats_chisq_test_agg

Chi-square test of independence for categorical variables.

Signature:

chisq_test_agg(row_var INTEGER, col_var INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
correction	BOOLEAN	false	Apply Yates' continuity correction

Returns:

STRUCT(
    statistic DOUBLE,    -- Chi-square statistic
    p_value DOUBLE,      -- p-value
    df BIGINT,           -- Degrees of freedom
    method VARCHAR       -- "Chi-Square"
)

Example:

-- Test independence of two categorical variables
SELECT (chisq_test_agg(gender, preference)).*
FROM survey;

-- With Yates correction for 2x2 tables
SELECT chisq_test_agg(group, outcome, {'correction': true})
FROM clinical_data;

chisq_gof_agg / anofox_stats_chisq_gof_agg

Chi-square goodness of fit test. Tests whether observed frequencies match expected frequencies.

Signature:

chisq_gof_agg(observed INTEGER, expected DOUBLE) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- Chi-square statistic
    p_value DOUBLE,      -- p-value
    df BIGINT,           -- Degrees of freedom
    method VARCHAR       -- "Chi-Square Goodness of Fit"
)

Example:

-- Test if observed frequencies match expected
SELECT (chisq_gof_agg(observed_count, expected_count)).*
FROM frequency_data;

g_test_agg / anofox_stats_g_test_agg

G-test (log-likelihood ratio test) for contingency tables.

Signature:

g_test_agg(row_var INTEGER, col_var INTEGER) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- G statistic
    p_value DOUBLE,      -- p-value
    df BIGINT,           -- Degrees of freedom
    method VARCHAR       -- "G-test"
)

Example:

-- G-test for independence
SELECT (g_test_agg(category_a, category_b)).*
FROM contingency_data;

fisher_exact_agg / anofox_stats_fisher_exact_agg

Fisher's exact test for 2x2 contingency tables.

Signature:

fisher_exact_agg(row_var INTEGER, col_var INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'

Returns:

STRUCT(
    odds_ratio DOUBLE,   -- Odds ratio
    p_value DOUBLE,      -- p-value
    ci_lower DOUBLE,     -- CI lower bound
    ci_upper DOUBLE,     -- CI upper bound
    method VARCHAR       -- "Fisher's Exact Test"
)

Example:

-- Fisher's exact test for small samples
SELECT (fisher_exact_agg(treatment, outcome)).*
FROM small_study;

mcnemar_agg / anofox_stats_mcnemar_agg

McNemar's test for paired nominal data.

Signature:

mcnemar_agg(var1 INTEGER, var2 INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
correction	BOOLEAN	true	Apply continuity correction

Returns:

STRUCT(
    statistic DOUBLE,    -- Chi-square statistic
    p_value DOUBLE,      -- p-value
    df BIGINT,           -- Degrees of freedom
    method VARCHAR       -- "McNemar's test"
)

Example:

-- Compare paired binary outcomes (before/after)
SELECT (mcnemar_agg(before_treatment, after_treatment)).*
FROM paired_study;

Effect Size Measures

cramers_v_agg / anofox_stats_cramers_v_agg

Cramér's V effect size for categorical association.

Signature:

cramers_v_agg(row_var INTEGER, col_var INTEGER) -> DOUBLE

Returns: Cramér's V coefficient (0 to 1)

Example:

-- Measure association strength
SELECT cramers_v_agg(category_a, category_b) as effect_size
FROM survey_data;

phi_coefficient_agg / anofox_stats_phi_coefficient_agg

Phi coefficient for 2x2 contingency tables.

Signature:

phi_coefficient_agg(row_var INTEGER, col_var INTEGER) -> DOUBLE

Returns: Phi coefficient (-1 to 1)

Example:

-- Phi coefficient for binary variables
SELECT phi_coefficient_agg(gender, preference) as phi
FROM binary_data;

contingency_coef_agg / anofox_stats_contingency_coef_agg

Pearson's contingency coefficient.

Signature:

contingency_coef_agg(row_var INTEGER, col_var INTEGER) -> DOUBLE

Returns: Contingency coefficient (0 to 1)

Example:

SELECT contingency_coef_agg(row_category, col_category) as c_coef
FROM categorical_data;

cohen_kappa_agg / anofox_stats_cohen_kappa_agg

Cohen's kappa for inter-rater agreement.

Signature:

cohen_kappa_agg(rater1 INTEGER, rater2 INTEGER) -> STRUCT

Returns:

STRUCT(
    kappa DOUBLE,        -- Kappa coefficient
    std_error DOUBLE,    -- Standard error
    z_value DOUBLE,      -- Z statistic
    p_value DOUBLE,      -- p-value
    ci_lower DOUBLE,     -- CI lower bound
    ci_upper DOUBLE,     -- CI upper bound
    method VARCHAR       -- "Cohen's Kappa"
)

Example:

-- Measure agreement between two raters
SELECT (cohen_kappa_agg(rater1_score, rater2_score)).*
FROM ratings;

Proportion Tests

prop_test_one_agg / anofox_stats_prop_test_one_agg

One-sample proportion test.

Signature:

prop_test_one_agg(successes INTEGER, trials INTEGER, p0 DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'
confidence_level	DOUBLE	0.95	Confidence level for CI

Returns:

STRUCT(
    statistic DOUBLE,    -- Z statistic
    p_value DOUBLE,      -- p-value
    estimate DOUBLE,     -- Sample proportion
    ci_lower DOUBLE,     -- CI lower bound
    ci_upper DOUBLE,     -- CI upper bound
    method VARCHAR       -- "One-sample proportion test"
)

Example:

-- Test if success rate differs from 50%
SELECT (prop_test_one_agg(successes, total, 0.5)).*
FROM experiment_results;

prop_test_two_agg / anofox_stats_prop_test_two_agg

Two-sample proportion test.

Signature:

prop_test_two_agg(successes INTEGER, trials INTEGER, group_id INTEGER, [options MAP]) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- Z statistic
    p_value DOUBLE,      -- p-value
    estimate1 DOUBLE,    -- Group 1 proportion
    estimate2 DOUBLE,    -- Group 2 proportion
    ci_lower DOUBLE,     -- CI lower bound for difference
    ci_upper DOUBLE,     -- CI upper bound for difference
    method VARCHAR       -- "Two-sample proportion test"
)

Example:

-- Compare conversion rates between groups
SELECT (prop_test_two_agg(conversions, visitors, ab_group)).*
FROM ab_test;

binom_test_agg / anofox_stats_binom_test_agg

Exact binomial test.

Signature:

binom_test_agg(successes INTEGER, trials INTEGER, p0 DOUBLE, [options MAP]) -> STRUCT

Returns:

STRUCT(
    p_value DOUBLE,      -- Exact p-value
    estimate DOUBLE,     -- Sample proportion
    ci_lower DOUBLE,     -- CI lower bound (Clopper-Pearson)
    ci_upper DOUBLE,     -- CI upper bound
    method VARCHAR       -- "Exact Binomial Test"
)

Example:

-- Exact test for small samples
SELECT (binom_test_agg(heads, flips, 0.5)).*
FROM coin_flip_data;

Additional Correlation Tests

kendall_agg / anofox_stats_kendall_agg

Kendall's tau rank correlation with significance test.

Signature:

kendall_agg(x DOUBLE, y DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
confidence_level	DOUBLE	0.95	Confidence level for CI

Returns:

STRUCT(
    tau DOUBLE,          -- Kendall's tau coefficient
    statistic DOUBLE,    -- Z statistic
    p_value DOUBLE,      -- p-value
    n BIGINT,            -- Sample size
    method VARCHAR       -- "Kendall"
)

Example:

-- Kendall correlation for ordinal data
SELECT (kendall_agg(rank_x, rank_y)).*
FROM ranked_data;

distance_cor_agg / anofox_stats_distance_cor_agg

Distance correlation for detecting nonlinear dependencies.

Signature:

distance_cor_agg(x DOUBLE, y DOUBLE) -> STRUCT

Returns:

STRUCT(
    dcor DOUBLE,         -- Distance correlation (0 to 1)
    dcov DOUBLE,         -- Distance covariance
    dvar_x DOUBLE,       -- Distance variance of x
    dvar_y DOUBLE,       -- Distance variance of y
    n BIGINT,            -- Sample size
    method VARCHAR       -- "Distance Correlation"
)

Example:

-- Detect nonlinear relationships
SELECT (distance_cor_agg(x, y)).*
FROM complex_relationships;

icc_agg / anofox_stats_icc_agg

Intraclass correlation coefficient.

Signature:

icc_agg(value DOUBLE, rater_id INTEGER, subject_id INTEGER, [options MAP]) -> STRUCT

Returns:

STRUCT(
    icc DOUBLE,          -- ICC value
    f_value DOUBLE,      -- F statistic
    df1 BIGINT,          -- Numerator df
    df2 BIGINT,          -- Denominator df
    p_value DOUBLE,      -- p-value
    ci_lower DOUBLE,     -- CI lower bound
    ci_upper DOUBLE,     -- CI upper bound
    method VARCHAR       -- "ICC"
)

Example:

-- Measure reliability across raters
SELECT (icc_agg(score, rater_id, subject_id)).*
FROM reliability_study;

Additional Parametric Tests

yuen_agg / anofox_stats_yuen_agg

Yuen's trimmed mean test (robust alternative to t-test).

Signature:

yuen_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
trim	DOUBLE	0.2	Proportion to trim from each tail
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'

Returns:

STRUCT(
    statistic DOUBLE,    -- Test statistic
    p_value DOUBLE,      -- p-value
    df DOUBLE,           -- Degrees of freedom
    trimmed_mean1 DOUBLE,-- Group 1 trimmed mean
    trimmed_mean2 DOUBLE,-- Group 2 trimmed mean
    method VARCHAR       -- "Yuen's Trimmed Mean Test"
)

Example:

-- Robust comparison with outliers
SELECT (yuen_agg(score, treatment_group, {'trim': 0.1})).*
FROM data_with_outliers;

brown_forsythe_agg / anofox_stats_brown_forsythe_agg

Brown-Forsythe test for equality of variances.

Signature:

brown_forsythe_agg(value DOUBLE, group_id INTEGER) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- F statistic
    p_value DOUBLE,      -- p-value
    df1 BIGINT,          -- Numerator df
    df2 BIGINT,          -- Denominator df
    method VARCHAR       -- "Brown-Forsythe Test"
)

Example:

-- Test homogeneity of variances
SELECT (brown_forsythe_agg(measurement, group)).*
FROM multi_group_data;

Additional Nonparametric Tests

wilcoxon_signed_rank_agg / anofox_stats_wilcoxon_signed_rank_agg

Wilcoxon signed-rank test for paired samples.

Signature:

wilcoxon_signed_rank_agg(value1 DOUBLE, value2 DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'
correction	BOOLEAN	true	Apply continuity correction

Returns:

STRUCT(
    statistic DOUBLE,    -- W statistic
    p_value DOUBLE,      -- p-value
    n BIGINT,            -- Number of pairs
    method VARCHAR       -- "Wilcoxon Signed-Rank"
)

Example:

-- Paired nonparametric test (before/after)
SELECT (wilcoxon_signed_rank_agg(before, after)).*
FROM paired_measurements;

brunner_munzel_agg / anofox_stats_brunner_munzel_agg

Brunner-Munzel test (generalized Wilcoxon test).

Signature:

brunner_munzel_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- Test statistic
    p_value DOUBLE,      -- p-value
    df DOUBLE,           -- Degrees of freedom
    estimate DOUBLE,     -- Probability estimate P(X < Y)
    method VARCHAR       -- "Brunner-Munzel"
)

Example:

-- Robust rank test
SELECT (brunner_munzel_agg(outcome, treatment_group)).*
FROM clinical_trial;

permutation_t_test_agg / anofox_stats_permutation_t_test_agg

Permutation t-test (resampling-based).

Signature:

permutation_t_test_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
n_permutations	INTEGER	10000	Number of permutations
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'

Returns:

STRUCT(
    statistic DOUBLE,    -- t statistic
    p_value DOUBLE,      -- Permutation p-value
    n_permutations BIGINT,
    method VARCHAR       -- "Permutation t-test"
)

Example:

-- Exact test via permutation
SELECT (permutation_t_test_agg(score, group, {'n_permutations': 5000})).*
FROM small_sample_data;

Normality Tests

dagostino_k2_agg / anofox_stats_dagostino_k2_agg

D'Agostino K² test for normality (based on skewness and kurtosis).

Signature:

dagostino_k2_agg(value DOUBLE) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- K² statistic
    p_value DOUBLE,      -- p-value
    skewness DOUBLE,     -- Sample skewness
    kurtosis DOUBLE,     -- Sample kurtosis
    n BIGINT,            -- Sample size
    method VARCHAR       -- "D'Agostino K²"
)

Example:

-- Test normality using skewness/kurtosis
SELECT (dagostino_k2_agg(residual)).*
FROM model_diagnostics;

Distribution Comparison

energy_distance_agg / anofox_stats_energy_distance_agg

Energy distance for comparing distributions.

Signature:

energy_distance_agg(value DOUBLE, group_id INTEGER) -> STRUCT

Returns:

STRUCT(
    distance DOUBLE,     -- Energy distance
    statistic DOUBLE,    -- Test statistic
    p_value DOUBLE,      -- p-value (permutation-based)
    method VARCHAR       -- "Energy Distance"
)

Example:

-- Compare two distributions
SELECT (energy_distance_agg(measurement, group)).*
FROM two_sample_data;

mmd_agg / anofox_stats_mmd_agg

Maximum Mean Discrepancy for distribution comparison.

Signature:

mmd_agg(value DOUBLE, group_id INTEGER, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
kernel	VARCHAR	'rbf'	Kernel type: 'rbf', 'linear'
bandwidth	DOUBLE	auto	RBF kernel bandwidth

Returns:

STRUCT(
    mmd DOUBLE,          -- MMD value
    mmd_squared DOUBLE,  -- MMD²
    p_value DOUBLE,      -- p-value
    method VARCHAR       -- "MMD"
)

Example:

-- Two-sample test using kernel methods
SELECT (mmd_agg(feature, sample_group)).*
FROM kernel_comparison;

Equivalence Tests (TOST)

tost_t_test_agg / anofox_stats_tost_t_test_agg

Two One-Sided Tests (TOST) for equivalence.

Signature:

tost_t_test_agg(value DOUBLE, group_id INTEGER, delta DOUBLE, [options MAP]) -> STRUCT

Parameters:

Parameter	Type	Description
delta	DOUBLE	Equivalence margin (symmetric bounds)

Returns:

STRUCT(
    p_value DOUBLE,      -- TOST p-value (max of two one-sided)
    ci_lower DOUBLE,     -- 90% CI lower bound
    ci_upper DOUBLE,     -- 90% CI upper bound
    equivalent BOOLEAN,  -- True if equivalence established
    method VARCHAR       -- "TOST t-test"
)

Example:

-- Test equivalence within ±0.5
SELECT (tost_t_test_agg(outcome, treatment_group, 0.5)).*
FROM bioequivalence_study;

tost_paired_agg / anofox_stats_tost_paired_agg

TOST for paired samples.

Signature:

tost_paired_agg(value1 DOUBLE, value2 DOUBLE, delta DOUBLE, [options MAP]) -> STRUCT

Returns: Same structure as tost_t_test_agg

Example:

-- Paired equivalence test
SELECT (tost_paired_agg(method_a, method_b, 0.1)).*
FROM method_comparison;

tost_correlation_agg / anofox_stats_tost_correlation_agg

TOST for testing correlation equivalence to a reference value.

Signature:

tost_correlation_agg(x DOUBLE, y DOUBLE, rho0 DOUBLE, delta DOUBLE) -> STRUCT

Parameters:

Parameter	Type	Description
rho0	DOUBLE	Reference correlation (typically 0)
delta	DOUBLE	Equivalence margin around rho0

Returns:

STRUCT(
    r DOUBLE,            -- Sample correlation
    p_value DOUBLE,      -- TOST p-value
    ci_lower DOUBLE,     -- CI lower bound
    ci_upper DOUBLE,     -- CI upper bound
    equivalent BOOLEAN,  -- True if equivalence established
    method VARCHAR       -- "TOST Correlation"
)

Example:

-- Test if correlation is equivalent to zero (negligible relationship)
SELECT (tost_correlation_agg(x, y, 0.0, 0.1)).*
FROM correlation_study;

Forecast Evaluation

diebold_mariano_agg / anofox_stats_diebold_mariano_agg

Diebold-Mariano test for comparing forecast accuracy.

Signature:

diebold_mariano_agg(actual DOUBLE, forecast1 DOUBLE, forecast2 DOUBLE, [options MAP]) -> STRUCT

Options MAP:

Key	Type	Default	Description
loss	VARCHAR	'mse'	Loss function: 'mse', 'mae', 'mape'
alternative	VARCHAR	'two_sided'	'two_sided', 'less', 'greater'

Returns:

STRUCT(
    statistic DOUBLE,    -- DM statistic
    p_value DOUBLE,      -- p-value
    loss1 DOUBLE,        -- Mean loss for forecast 1
    loss2 DOUBLE,        -- Mean loss for forecast 2
    method VARCHAR       -- "Diebold-Mariano"
)

Example:

-- Compare two forecasting models
SELECT (diebold_mariano_agg(actual, model1_pred, model2_pred)).*
FROM forecast_comparison;

clark_west_agg / anofox_stats_clark_west_agg

Clark-West test for nested model comparison.

Signature:

clark_west_agg(actual DOUBLE, forecast1 DOUBLE, forecast2 DOUBLE) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,    -- CW statistic
    p_value DOUBLE,      -- p-value
    mspe_adj DOUBLE,     -- Adjusted MSPE difference
    method VARCHAR       -- "Clark-West"
)

Example:

-- Compare nested forecasting models
SELECT (clark_west_agg(actual, restricted_model, unrestricted_model)).*
FROM nested_model_comparison;

Statistical Test Aliases

Full Name	Short Alias
anofox_stats_shapiro_wilk_agg	shapiro_wilk_agg
anofox_stats_jarque_bera_agg	jarque_bera_agg
anofox_stats_dagostino_k2_agg	dagostino_k2_agg
anofox_stats_t_test_agg	t_test_agg
anofox_stats_one_way_anova_agg	one_way_anova_agg
anofox_stats_yuen_agg	yuen_agg
anofox_stats_brown_forsythe_agg	brown_forsythe_agg
anofox_stats_mann_whitney_u_agg	mann_whitney_u_agg
anofox_stats_kruskal_wallis_agg	kruskal_wallis_agg
anofox_stats_wilcoxon_signed_rank_agg	wilcoxon_signed_rank_agg
anofox_stats_brunner_munzel_agg	brunner_munzel_agg
anofox_stats_permutation_t_test_agg	permutation_t_test_agg
anofox_stats_pearson_agg	pearson_agg
anofox_stats_spearman_agg	spearman_agg
anofox_stats_kendall_agg	kendall_agg
anofox_stats_distance_cor_agg	distance_cor_agg
anofox_stats_icc_agg	icc_agg
anofox_stats_chisq_test_agg	chisq_test_agg
anofox_stats_chisq_gof_agg	chisq_gof_agg
anofox_stats_g_test_agg	g_test_agg
anofox_stats_fisher_exact_agg	fisher_exact_agg
anofox_stats_mcnemar_agg	mcnemar_agg
anofox_stats_cramers_v_agg	cramers_v_agg
anofox_stats_phi_coefficient_agg	phi_coefficient_agg
anofox_stats_contingency_coef_agg	contingency_coef_agg
anofox_stats_cohen_kappa_agg	cohen_kappa_agg
anofox_stats_prop_test_one_agg	prop_test_one_agg
anofox_stats_prop_test_two_agg	prop_test_two_agg
anofox_stats_binom_test_agg	binom_test_agg
anofox_stats_tost_t_test_agg	tost_t_test_agg
anofox_stats_tost_paired_agg	tost_paired_agg
anofox_stats_tost_correlation_agg	tost_correlation_agg
anofox_stats_energy_distance_agg	energy_distance_agg
anofox_stats_mmd_agg	mmd_agg
anofox_stats_diebold_mariano_agg	diebold_mariano_agg
anofox_stats_clark_west_agg	clark_west_agg

---

Fit-Predict Window Functions

Window-based aggregate functions that fit a model incrementally and predict for each row. Use with OVER clause for rolling/expanding window regression.

anofox_stats_ols_fit_predict / ols_fit_predict

OLS regression with per-row predictions using window semantics.

Signature:

anofox_stats_ols_fit_predict(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) OVER (window_spec) -> STRUCT

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Returns:

STRUCT(
    yhat DOUBLE,        -- Predicted value
    yhat_lower DOUBLE,  -- Lower prediction interval bound
    yhat_upper DOUBLE   -- Upper prediction interval bound
)

Example:

-- Expanding window: train on all previous rows, predict current
SELECT
    date,
    y,
    pred.yhat,
    pred.yhat_lower,
    pred.yhat_upper
FROM (
    SELECT
        date, y,
        ols_fit_predict(y, [x1, x2]) OVER (
            ORDER BY date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) as pred
    FROM time_series
);

-- Rolling 30-day window
SELECT
    date,
    ols_fit_predict(y, [x]) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as pred
FROM daily_data;

-- Per-group expanding regression
SELECT
    category,
    date,
    ols_fit_predict(y, [x], {'confidence_level': 0.99}) OVER (
        PARTITION BY category
        ORDER BY date
    ) as pred
FROM grouped_data;

anofox_stats_ridge_fit_predict / ridge_fit_predict

Ridge regression with per-row predictions.

Additional Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	L2 regularization strength

SELECT ridge_fit_predict(y, [x], {'alpha': 0.5}) OVER (ORDER BY date) FROM data;

anofox_stats_wls_fit_predict / wls_fit_predict

Weighted Least Squares with per-row predictions.

Signature:

wls_fit_predict(y DOUBLE, x LIST(DOUBLE), weight DOUBLE, [options MAP]) OVER (...)

SELECT wls_fit_predict(y, [x], weight) OVER (ORDER BY date) FROM data;

anofox_stats_rls_fit_predict / rls_fit_predict

Recursive Least Squares with per-row predictions.

Additional Options:

Key	Type	Default	Description
forgetting_factor	DOUBLE	1.0	Exponential forgetting (0.95-1.0)
initial_p_diagonal	DOUBLE	100.0	Initial covariance diagonal

SELECT rls_fit_predict(y, [x], {'forgetting_factor': 0.99}) OVER (ORDER BY date) FROM data;

anofox_stats_elasticnet_fit_predict / elasticnet_fit_predict

Elastic Net with per-row predictions.

Additional Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	Regularization strength
l1_ratio	DOUBLE	0.5	L1 ratio (0=Ridge, 1=Lasso)
max_iterations	INTEGER	1000	Max iterations
tolerance	DOUBLE	1e-6	Convergence tolerance

SELECT elasticnet_fit_predict(y, [x], {'alpha': 0.1, 'l1_ratio': 0.7}) OVER (ORDER BY date) FROM data;

---

Fit-Predict Aggregate Functions

Non-rolling aggregate functions that fit a model once on training data (rows where y IS NOT NULL) and return predictions for ALL rows including out-of-sample predictions.

Deprecation Notice: The old *_predict_agg names (ols_predict_agg, ridge_predict_agg, etc.) are deprecated but still work for backwards compatibility. Use *_fit_predict_agg instead.

anofox_stats_ols_fit_predict_agg / ols_fit_predict_agg

Fit OLS on training rows, predict all rows.

Signature:

anofox_stats_ols_fit_predict_agg(
    y DOUBLE,
    x LIST(DOUBLE),
    [options MAP]
) -> LIST(STRUCT)

Options MAP:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Returns:

LIST(STRUCT(
    y DOUBLE,           -- Original y value (NULL for out-of-sample)
    x LIST(DOUBLE),     -- Original x values
    yhat DOUBLE,        -- Predicted value
    yhat_lower DOUBLE,  -- Lower prediction interval bound
    yhat_upper DOUBLE,  -- Upper prediction interval bound
    is_training BOOLEAN -- True if row was used for training
))

Example:

-- Basic usage: fit on rows where y IS NOT NULL, predict all
CREATE TABLE data AS
SELECT
    CASE WHEN i <= 80 THEN i * 2.0 ELSE NULL END as y,
    i::DOUBLE as x,
    i as id
FROM range(1, 101) t(i);

-- Get predictions with training indicator
SELECT
    (p).y as original_y,
    (p).x as features,
    (p).yhat as predicted,
    (p).is_training
FROM (
    SELECT UNNEST(ols_fit_predict_agg(y, [x])) AS p
    FROM data
);

-- Per-group predictions
SELECT
    segment,
    UNNEST(ols_fit_predict_agg(y, [x1, x2], {'confidence_level': 0.99})) AS pred
FROM sales_data
GROUP BY segment;

anofox_stats_ridge_fit_predict_agg / ridge_fit_predict_agg

Ridge regression fit-predict aggregate.

Additional Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	L2 regularization strength

SELECT UNNEST(ridge_fit_predict_agg(y, [x], {'alpha': 0.5})) FROM data;

anofox_stats_wls_fit_predict_agg / wls_fit_predict_agg

Weighted Least Squares fit-predict aggregate.

Signature:

wls_fit_predict_agg(y DOUBLE, x LIST(DOUBLE), weight DOUBLE, [options MAP]) -> LIST(STRUCT)

SELECT UNNEST(wls_fit_predict_agg(y, [x], weight)) FROM data;

anofox_stats_rls_fit_predict_agg / rls_fit_predict_agg

Recursive Least Squares fit-predict aggregate.

Additional Options:

Key	Type	Default	Description
forgetting_factor	DOUBLE	1.0	Exponential forgetting
initial_p_diagonal	DOUBLE	100.0	Initial covariance diagonal

SELECT UNNEST(rls_fit_predict_agg(y, [x], {'forgetting_factor': 0.99})) FROM data;

anofox_stats_elasticnet_fit_predict_agg / elasticnet_fit_predict_agg

Elastic Net fit-predict aggregate.

Additional Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	Regularization strength
l1_ratio	DOUBLE	0.5	L1 ratio (0=Ridge, 1=Lasso)
max_iterations	INTEGER	1000	Max iterations
tolerance	DOUBLE	1e-6	Convergence tolerance

SELECT UNNEST(elasticnet_fit_predict_agg(y, [x], {'alpha': 0.1, 'l1_ratio': 0.5})) FROM data;

anofox_stats_bls_fit_predict_agg / bls_fit_predict_agg

Bounded Least Squares (BLS/NNLS) fit-predict aggregate with coefficient constraints.

Signature:

bls_fit_predict_agg(y DOUBLE, x LIST(DOUBLE), [options MAP]) -> LIST(STRUCT)

Options MAP:

Key	Type	Default	Description
lower_bound	DOUBLE	0.0	Lower bound for coefficients
upper_bound	DOUBLE	+inf	Upper bound for coefficients
intercept	BOOLEAN	false	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-6	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Example:

-- NNLS (Non-Negative Least Squares) with out-of-sample predictions
CREATE TABLE bounded_data AS
SELECT
    group_id, week, x,
    CASE WHEN week <= 10 THEN 5.0 + 2.0*x + RANDOM() ELSE NULL END AS y
FROM (VALUES (1), (2)) AS g(group_id),
     generate_series(1, 14) AS w(week),
     LATERAL (SELECT week * 1.5 AS x);

SELECT
    group_id,
    (pred).y AS actual,
    ROUND((pred).yhat, 2) AS predicted,
    (pred).is_training
FROM (
    SELECT group_id, UNNEST(bls_fit_predict_agg(y, [x], {'lower_bound': 0})) AS pred
    FROM bounded_data GROUP BY group_id
) sub;

anofox_stats_alm_fit_predict_agg / alm_fit_predict_agg

Augmented Linear Model fit-predict aggregate with robust error distributions.

Signature:

alm_fit_predict_agg(y DOUBLE, x LIST(DOUBLE), [options MAP]) -> LIST(STRUCT)

Options MAP:

Key	Type	Default	Description
distribution	VARCHAR	'normal'	Error distribution (see below)
intercept	BOOLEAN	true	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-6	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Distributions: normal, laplace, studentt, cauchy, huber, tukey, quantile, expectile, trimmed, winsorized

Example:

-- Robust regression with Laplace distribution (robust to outliers)
CREATE TABLE robust_data AS
SELECT
    group_id, x,
    CASE WHEN id <= 10 THEN
        CASE WHEN id = 5 THEN 100.0  -- Outlier
             ELSE 10.0 + 3.0*x + RANDOM()
        END
    ELSE NULL END AS y
FROM (VALUES (1), (2)) AS g(group_id),
     generate_series(1, 14) AS t(id),
     LATERAL (SELECT id * 2.0 AS x);

SELECT
    group_id,
    ROUND((pred).yhat, 2) AS predicted,
    (pred).is_training
FROM (
    SELECT group_id, UNNEST(alm_fit_predict_agg(y, [x], {'distribution': 'laplace'})) AS pred
    FROM robust_data GROUP BY group_id
) sub;

anofox_stats_poisson_fit_predict_agg / poisson_fit_predict_agg

Poisson GLM fit-predict aggregate for count data.

Signature:

poisson_fit_predict_agg(y DOUBLE, x LIST(DOUBLE), [options MAP]) -> LIST(STRUCT)

Options MAP:

Key	Type	Default	Description
link	VARCHAR	'log'	Link function: 'log', 'identity', 'sqrt'
intercept	BOOLEAN	true	Include intercept term
max_iterations	INTEGER	100	Maximum IRLS iterations
tolerance	DOUBLE	1e-8	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Example:

-- Poisson regression for visitor count prediction
CREATE TABLE visitor_data AS
SELECT
    store_id, week, marketing_spend,
    CASE WHEN week <= 10 THEN
        ROUND(EXP(2.0 + 0.05*marketing_spend) + RANDOM()*5)::INTEGER
    ELSE NULL END AS visitors
FROM (VALUES (1), (2), (3)) AS s(store_id),
     generate_series(1, 14) AS w(week),
     LATERAL (SELECT 20.0 + week*5.0 AS marketing_spend);

SELECT
    store_id,
    (pred).y AS actual_visitors,
    ROUND((pred).yhat) AS predicted_visitors,
    (pred).is_training
FROM (
    SELECT store_id, UNNEST(poisson_fit_predict_agg(visitors, [marketing_spend], {'link': 'log'})) AS pred
    FROM visitor_data GROUP BY store_id
) sub
WHERE store_id = 1;

---

Fit-Predict Table Macros

Table macros that wrap *_fit_predict_agg functions for easy per-group regression with long-format output. All source columns are passed through to the output, so you retain the original data alongside predictions.

All table macros accept an optional options MAP parameter to configure method-specific settings. When not provided, defaults are used.

ols_fit_predict_by

OLS regression per group with predictions in long format.

Signature:

ols_fit_predict_by(
    source VARCHAR,           -- Table name (as string)
    group_col COLUMN,         -- Column to group by
    y_col COLUMN,             -- Response variable column
    x_cols LIST(COLUMN),      -- Feature columns as list
    [options STRUCT],         -- Optional configuration (default: NULL)
    [split COLUMN]            -- Optional train/test split column (default: NULL)
) -> TABLE

Options:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling: 'drop' or 'drop_y_zero_x'

Returns:

All columns from the source table are preserved in the output (including the group column, y column, and all feature columns with their original names). The following prediction columns are appended:

Column	Type	Description
yhat	DOUBLE	Predicted value
yhat_lower	DOUBLE	Lower prediction interval bound
yhat_upper	DOUBLE	Upper prediction interval bound
is_training	BOOLEAN	True if row was used for training

Note: Column names in the output preserve the original names from the source table.

Example:

-- Per-group OLS regression
SELECT * FROM ols_fit_predict_by('sales_data', region, revenue, [advertising, price]);

-- With 99% prediction intervals
SELECT * FROM ols_fit_predict_by('sales_data', region, revenue, [advertising, price],
    {'confidence_level': 0.99});

-- Filter to out-of-sample predictions only
SELECT * FROM ols_fit_predict_by('forecast_data', store_id, sales, [inventory, promotions])
WHERE NOT is_training;

ridge_fit_predict_by

Ridge regression per group with predictions in long format.

Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	L2 regularization strength
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- Ridge with default alpha
SELECT * FROM ridge_fit_predict_by('data', category, y, [x1, x2]);

-- Ridge with custom regularization
SELECT * FROM ridge_fit_predict_by('data', category, y, [x1, x2],
    {'alpha': 0.5});

-- Strong regularization
SELECT * FROM ridge_fit_predict_by('data', category, y, [x1, x2],
    {'alpha': 10.0, 'confidence_level': 0.99});

elasticnet_fit_predict_by

Elastic Net regression per group with predictions in long format.

Options:

Key	Type	Default	Description
alpha	DOUBLE	1.0	Regularization strength
l1_ratio	DOUBLE	0.5	L1 ratio: 0=Ridge, 1=Lasso
max_iterations	INTEGER	1000	Max coordinate descent iterations
tolerance	DOUBLE	1e-6	Convergence tolerance
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- ElasticNet with default settings
SELECT * FROM elasticnet_fit_predict_by('data', category, y, [x1, x2]);

-- More Lasso-like (70% L1)
SELECT * FROM elasticnet_fit_predict_by('data', category, y, [x1, x2],
    {'alpha': 0.1, 'l1_ratio': 0.7});

wls_fit_predict_by

Weighted Least Squares per group with predictions in long format.

Signature:

wls_fit_predict_by(
    source VARCHAR,
    group_col COLUMN,
    y_col COLUMN,
    x_cols LIST(COLUMN),
    weight_col COLUMN,        -- Weight column (required)
    [options STRUCT]          -- Optional configuration (default: NULL)
) -> TABLE

Options:

Key	Type	Default	Description
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- WLS with weight column
SELECT * FROM wls_fit_predict_by('weighted_data', segment, y, [x1, x2], weight);

-- WLS with custom confidence level
SELECT * FROM wls_fit_predict_by('weighted_data', segment, y, [x1, x2], weight,
    {'confidence_level': 0.99});

rls_fit_predict_by

Recursive Least Squares per group with predictions in long format.

Options:

Key	Type	Default	Description
forgetting_factor	DOUBLE	1.0	Exponential forgetting (0.95-1.0 typical)
initial_p_diagonal	DOUBLE	100.0	Initial covariance diagonal
fit_intercept	BOOLEAN	true	Include intercept term
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- RLS with default settings
SELECT * FROM rls_fit_predict_by('streaming_data', sensor_id, reading, [temp, pressure]);

-- RLS with forgetting (adapts to recent data)
SELECT * FROM rls_fit_predict_by('streaming_data', sensor_id, reading, [temp, pressure],
    {'forgetting_factor': 0.95});

bls_fit_predict_by

Bounded Least Squares per group with predictions in long format.

Options:

Key	Type	Default	Description
lower_bound	DOUBLE	0.0	Lower bound for coefficients
upper_bound	DOUBLE	+inf	Upper bound for coefficients
intercept	BOOLEAN	false	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-6	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- BLS with default (non-negative coefficients)
SELECT * FROM bls_fit_predict_by('constrained_data', portfolio_id, returns, [factor1, factor2]);

-- Box constraints (coefficients between 0 and 1)
SELECT * FROM bls_fit_predict_by('portfolio_data', asset_class, returns, [factors],
    {'lower_bound': 0.0, 'upper_bound': 1.0});

alm_fit_predict_by

Augmented Linear Model per group with predictions in long format.

Options:

Key	Type	Default	Description
distribution	VARCHAR	'normal'	Error distribution (see below)
intercept	BOOLEAN	true	Include intercept term
max_iterations	INTEGER	1000	Maximum iterations
tolerance	DOUBLE	1e-6	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Distributions: normal, laplace, studentt, cauchy, huber, tukey, quantile, expectile, trimmed, winsorized

Example:

-- ALM with default (normal distribution)
SELECT * FROM alm_fit_predict_by('robust_data', group_id, y, [x1, x2]);

-- Robust regression with Laplace (median regression)
SELECT * FROM alm_fit_predict_by('data_with_outliers', group_id, y, [x1, x2],
    {'distribution': 'laplace'});

-- Student-t for heavy tails
SELECT * FROM alm_fit_predict_by('heavy_tailed_data', group_id, y, [x1, x2],
    {'distribution': 'studentt'});

poisson_fit_predict_by

Poisson GLM per group with predictions in long format.

Options:

Key	Type	Default	Description
link	VARCHAR	'log'	Link function: 'log', 'identity', 'sqrt'
intercept	BOOLEAN	true	Include intercept term
max_iterations	INTEGER	100	Maximum IRLS iterations
tolerance	DOUBLE	1e-8	Convergence tolerance
confidence_level	DOUBLE	0.95	Prediction interval confidence
null_policy	VARCHAR	'drop'	NULL handling

Example:

-- Poisson with default log link
SELECT * FROM poisson_fit_predict_by('count_data', store_id, visitor_count, [marketing_spend]);

-- Poisson with identity link
SELECT * FROM poisson_fit_predict_by('count_data', store_id, visitor_count, [marketing_spend],
    {'link': 'identity'});

-- Poisson with custom iterations
SELECT * FROM poisson_fit_predict_by('count_data', store_id, visitor_count, [marketing_spend],
    {'link': 'log', 'max_iterations': 200});

Table Macro Aliases

Macro	Underlying Aggregate	Method-Specific Options
ols_fit_predict_by	ols_fit_predict_agg	(common only)
ridge_fit_predict_by	ridge_fit_predict_agg	alpha
elasticnet_fit_predict_by	elasticnet_fit_predict_agg	alpha, l1_ratio, max_iterations, tolerance
wls_fit_predict_by	wls_fit_predict_agg	(common only)
rls_fit_predict_by	rls_fit_predict_agg	forgetting_factor, initial_p_diagonal
bls_fit_predict_by	bls_fit_predict_agg	lower_bound, upper_bound, intercept, max_iterations, tolerance
alm_fit_predict_by	alm_fit_predict_agg	distribution, intercept, max_iterations, tolerance
poisson_fit_predict_by	poisson_fit_predict_agg	link, intercept, max_iterations, tolerance

---

Predict Function

anofox_stats_predict

Generate predictions using fitted coefficients.

Signature:

anofox_stats_predict(
    x LIST(LIST(DOUBLE)),
    coefficients LIST(DOUBLE),
    intercept DOUBLE
) -> LIST(DOUBLE)

Example:

-- First fit a model
WITH model AS (
    SELECT anofox_stats_ols_fit(y_values, x_values) as fit FROM training_data
)
-- Then predict
SELECT anofox_stats_predict(
    [[6.0, 7.0, 8.0]],  -- new x values
    model.fit.coefficients,
    model.fit.intercept
) as predictions
FROM model;

---

Diagnostic Functions

anofox_stats_vif / vif

Compute Variance Inflation Factor for multicollinearity detection.

Signature:

anofox_stats_vif(x LIST(LIST(DOUBLE))) -> LIST(DOUBLE)

Interpretation:

• VIF = 1: No correlation
• VIF > 5: Moderate correlation (warning)
• VIF > 10: High correlation (problematic)

Example:

SELECT vif([[x1_vals], [x2_vals], [x3_vals]]) as vif_values;

anofox_stats_vif_agg / vif_agg

Streaming VIF aggregate function.

SELECT vif_agg([x1, x2, x3]) FROM data;

anofox_stats_aic / aic

Compute Akaike Information Criterion.

Signature:

anofox_stats_aic(rss DOUBLE, n BIGINT, k BIGINT) -> DOUBLE

Parameters:

Parameter	Type	Description
rss	DOUBLE	Residual Sum of Squares
n	BIGINT	Number of observations
k	BIGINT	Number of parameters (including intercept)

Example:

SELECT aic(100.0, 50, 3) as aic_value;

anofox_stats_bic / bic

Compute Bayesian Information Criterion.

Signature:

anofox_stats_bic(rss DOUBLE, n BIGINT, k BIGINT) -> DOUBLE

Example:

SELECT bic(100.0, 50, 3) as bic_value;

anofox_stats_jarque_bera / jarque_bera

Jarque-Bera test for normality of residuals.

Signature:

anofox_stats_jarque_bera(data LIST(DOUBLE)) -> STRUCT

Returns:

STRUCT(
    statistic DOUBLE,
    p_value DOUBLE,
    skewness DOUBLE,
    kurtosis DOUBLE,
    n BIGINT
)

Example:

SELECT jarque_bera(residuals).p_value as normality_pvalue;

anofox_stats_jarque_bera_agg / jarque_bera_agg

Streaming Jarque-Bera aggregate function.

SELECT jarque_bera_agg(residual) FROM fitted_data;

anofox_stats_residuals_diagnostics / residuals_diagnostics

Compute comprehensive residual diagnostics.

Signature:

anofox_stats_residuals_diagnostics(
    y LIST(DOUBLE),
    y_hat LIST(DOUBLE),
    [x LIST(LIST(DOUBLE))],
    [residual_std_error DOUBLE],
    [include_studentized BOOLEAN]
) -> STRUCT

Returns:

STRUCT(
    raw LIST(DOUBLE),
    standardized LIST(DOUBLE),
    studentized LIST(DOUBLE),
    leverage LIST(DOUBLE)
)

Example:

SELECT residuals_diagnostics(
    actual_values,
    predicted_values
) as diagnostics;

anofox_stats_residuals_diagnostics_agg / residuals_diagnostics_agg

Streaming residuals diagnostics aggregate function.

---

Common Options

null_policy Parameter

The null_policy option controls how NULL values are handled during model training.

Value	Training Set	Predictions
'drop' (default)	Rows where y IS NOT NULL	All rows get predictions
'drop_y_zero_x'	Rows where y IS NOT NULL AND all x != 0	All rows get predictions

solver Parameter

Controls the matrix decomposition method for OLS, Ridge, and WLS.

Value	Description	Best for
'svd' (default)	Singular Value Decomposition	Most robust, handles rank-deficient matrices
'qr'	QR Decomposition	Faster for well-conditioned problems
'cholesky'	Cholesky Decomposition	Fastest for positive-definite X'X

hc_type Parameter

Heteroscedasticity-consistent standard errors for OLS and WLS. Requires compute_inference: true.

Value	Description
'none' (default)	Classical (homoscedastic) standard errors
'hc0'	White's estimator
'hc1'	HC0 with degrees-of-freedom correction
'hc2'	HC0 with leverage adjustment
'hc3'	HC0 with squared leverage adjustment (most conservative)

lambda_scaling Parameter

Controls the lambda scaling convention for Ridge and Elastic Net.

Value	Description
'raw' (default)	Lambda is used as-is in the penalty term
'glmnet'	Lambda is scaled by 1/(2n) to match glmnet convention

glm_lambda Parameter

L2 regularization for Poisson GLM. Set to 0.0 (default) for no regularization.

---

Return Types

FitResult Structure

Standard return type for linear regression functions.

STRUCT(
    coefficients LIST(DOUBLE),
    intercept DOUBLE,
    r_squared DOUBLE,
    adj_r_squared DOUBLE,
    mse DOUBLE,
    rmse DOUBLE,
    mae DOUBLE,
    rss DOUBLE,
    tss DOUBLE,
    n_observations BIGINT,
    n_features INTEGER,
    -- When compute_inference=true:
    t_statistics LIST(DOUBLE),
    p_values LIST(DOUBLE),
    std_errors LIST(DOUBLE),
    conf_int_lower LIST(DOUBLE),
    conf_int_upper LIST(DOUBLE)
)

Accessing Results

-- Extract specific fields
SELECT
    (result).r_squared,
    (result).coefficients[1] as beta1,
    (result).coefficients[2] as beta2
FROM (SELECT ols_fit_agg(y, [x1, x2]) as result FROM data);

-- Expand all fields
SELECT (ols_fit_agg(y, [x1, x2])).* FROM data;

---

Short Aliases

Most functions have short aliases without the anofox_stats_ prefix:

Full Name	Alias
anofox_stats_ols_fit	ols_fit
anofox_stats_ridge_fit	ridge_fit
anofox_stats_t_test_agg	t_test_agg
anofox_stats_pearson_agg	pearson_agg
...	...

---

Detailed Documentation

For comprehensive documentation on each function category:

• Regression: OLS | Ridge | Elastic Net | WLS | RLS | BLS/NNLS | PLS | Isotonic | Quantile
• GLM: Poisson | ALM
• Statistics: Hypothesis Tests | Correlation | Categorical
• AID: Demand Classification
• Diagnostics: Model Diagnostics
• Table Macros: Table Macros

---

Error Handling

All functions return NULL on error conditions:

• Invalid input types
• Empty arrays
• Singular matrices (insufficient data variation)

Check for NULL results when using these functions:

SELECT COALESCE((ols_fit_agg(y, [x])).r_squared, 0.0) as r_squared
FROM data;

---

Performance Notes

• Aggregate functions are generally preferred for large datasets as they process data in a streaming fashion
• Scalar functions may be faster for small, pre-aggregated arrays
• Use table macros for the simplest syntax when doing per-group predictions
• VIF computation is O(k³) where k is the number of features

---

Version History

• 0.6.0: Added aid_anomaly_by table macro, reorganized documentation
• 0.5.0: Added PLS, Isotonic, Quantile regression
• 0.4.0: Added ALM with 24 distributions
• 0.3.0: Added comprehensive hypothesis testing
• 0.2.0: Added BLS/NNLS, RLS
• 0.1.0: Initial release with OLS, Ridge, Elastic Net, WLS