# AnoFox | Enterprise Forecasting

AnoFox is an enterprise forecasting platform for Sales & Operations Planning, delivered as three specialized DuckDB extensions.

# Conformal Prediction

AnoFox provides 11 conformal prediction functions -- 7 scalar functions and 4 table macros -- for generating distribution-free prediction intervals with guaranteed coverage. Unlike parametric intervals that assume normal residuals, conformal prediction provides finite-sample valid coverage for any underlying distribution. The system supports both symmetric and asymmetric intervals (for skewed residuals common in demand forecasting), and includes evaluation via coverage rate, violation rate, mean interval width, and Winkler score.

Conformal prediction is a statistical framework that provides **distribution-free prediction intervals** with guaranteed coverage probability. Unlike parametric methods that assume residuals follow a specific distribution (e.g., normal), conformal prediction makes minimal assumptions about the underlying distribution and provides valid coverage even for finite samples.

All TypeScalarTable Macro

| Function | Description | Type |
| --- | --- | --- |
| ([`ts_conformal_quantile`](#ts_conformal_quantile)) | Compute conformity score from residuals | Scalar |
| ([`ts_conformal_intervals`](#ts_conformal_intervals)) | Apply conformity score to create intervals | Scalar |
| ([`ts_conformal_predict`](#ts_conformal_predict)) | Full split conformal prediction | Scalar |
| ([`ts_conformal_predict_asymmetric`](#ts_conformal_predict_asymmetric)) | Asymmetric conformal for skewed residuals | Scalar |
| ([`ts_mean_interval_width`](#ts_mean_interval_width)) | Compute mean width of prediction intervals | Scalar |
| ([`ts_conformal_coverage`](#ts_conformal_coverage)) | Empirical coverage evaluation | Scalar |
| ([`ts_conformal_evaluate`](#ts_conformal_evaluate)) | Full evaluation (coverage, width, Winkler) | Scalar |
| ([`ts_conformal_by`](#ts_conformal_by)) | High-level grouped conformal prediction | Table Macro |
| ([`ts_conformal_calibrate`](#ts_conformal_calibrate)) | Calibrate conformity score from backtest table | Table Macro |
| ([`ts_conformal_apply_by`](#ts_conformal_apply_by)) | Apply pre-computed score to forecast table | Table Macro |
| ([`ts_interval_width_by`](#ts_interval_width_by)) | Compute interval widths for grouped results | Table Macro |

Showing 11 of 11

---

## How It Works

The conformal prediction system operates in two phases:

1.  **Calibration**: Compute a conformity score from calibration residuals (actual - predicted)
2.  **Prediction**: Apply the conformity score to new forecasts to create prediction intervals

The resulting intervals will cover the true value with probability at least `1 - alpha`, where `alpha` is the miscoverage rate.

---

## Scalar Functions

### ts\_conformal\_quantile

Computes the empirical quantile of absolute residuals for split conformal prediction.

```
ts_conformal_quantile(residuals DOUBLE[], alpha DOUBLE) → DOUBLE
```

**Parameters:**

*   `residuals`: Array of residuals (actual - predicted) from calibration set
*   `alpha`: Miscoverage rate (0 < alpha < 1). Use 0.1 for 90% coverage, 0.05 for 95% coverage.

**Returns:** DOUBLE - The conformity score (quantile of absolute residuals)

**Example:**

```
SELECT ts_conformal_quantile(    [1.0, -0.5, 2.0, -1.5, 0.8],    0.1) AS conformity_score;
```

---

### ts\_conformal\_intervals

Applies a pre-computed conformity score to create symmetric prediction intervals.

```
ts_conformal_intervals(forecasts DOUBLE[], conformity_score DOUBLE)→ STRUCT(lower DOUBLE[], upper DOUBLE[])
```

**Parameters:**

*   `forecasts`: Array of point forecasts
*   `conformity_score`: Pre-computed quantile from `ts_conformal_quantile`

**Returns:** STRUCT containing:

*   `lower`: Array of lower interval bounds
*   `upper`: Array of upper interval bounds

**Example:**

```
SELECT    (ts_conformal_intervals([100.0, 110.0, 120.0], 5.0)).lower AS lower,    (ts_conformal_intervals([100.0, 110.0, 120.0], 5.0)).upper AS upper;-- Returns: lower = [95.0, 105.0, 115.0], upper = [105.0, 115.0, 125.0]
```

---

### ts\_conformal\_predict

Full split conformal prediction: computes conformity score from residuals and applies to forecasts.

```
ts_conformal_predict(residuals DOUBLE[], forecasts DOUBLE[], alpha DOUBLE)→ STRUCT(    point DOUBLE[],    lower DOUBLE[],    upper DOUBLE[],    coverage DOUBLE,    conformity_score DOUBLE,    method VARCHAR)
```

**Parameters:**

*   `residuals`: Calibration residuals (actual - predicted)
*   `forecasts`: Point forecasts to generate intervals for
*   `alpha`: Miscoverage rate

**Example:**

```
WITH backtest_residuals AS (    SELECT [1.2, -0.8, 1.5, -1.0, 0.5]::DOUBLE[] AS residuals),future_forecasts AS (    SELECT [100.0, 102.0, 104.0]::DOUBLE[] AS forecasts)SELECT    (ts_conformal_predict(residuals, forecasts, 0.1)).*FROM backtest_residuals, future_forecasts;
```

---

### ts\_conformal\_predict\_asymmetric

Asymmetric conformal prediction that computes separate upper and lower quantiles. Useful when residuals are not symmetric (e.g., skewed demand forecasts).

```
ts_conformal_predict_asymmetric(residuals DOUBLE[], forecasts DOUBLE[], alpha DOUBLE)→ STRUCT(    point DOUBLE[],    lower DOUBLE[],    upper DOUBLE[],    coverage DOUBLE,    conformity_score DOUBLE,    method VARCHAR)
```

**Example:**

```
-- Asymmetric residuals (over-predictions more common)SELECT    (ts_conformal_predict_asymmetric(        [-0.5, -0.3, 0.2, 1.0, 2.5],        [100.0, 110.0],        0.1    )).*;
```

---

### ts\_mean\_interval\_width

Computes the mean width of prediction intervals. Useful for comparing interval sharpness across models.

```
ts_mean_interval_width(lower DOUBLE[], upper DOUBLE[]) → DOUBLE
```

**Example:**

```
SELECT    ts_mean_interval_width([95.0, 105.0], [105.0, 115.0]) AS model_a_width,    ts_mean_interval_width([90.0, 100.0], [110.0, 120.0]) AS model_b_width;-- Model A: 10.0 (sharper), Model B: 20.0 (wider)
```

---

### ts\_conformal\_coverage

Computes empirical coverage: the fraction of actual values that fall within the prediction intervals.

```
ts_conformal_coverage(actuals DOUBLE[], lower DOUBLE[], upper DOUBLE[]) → DOUBLE
```

**Example:**

```
SELECT ts_conformal_coverage(    [100.0, 105.0, 110.0],    [95.0, 100.0, 105.0],    [105.0, 110.0, 115.0]) AS empirical_coverage;-- Returns: 1.0 (all actuals within intervals)
```

---

### ts\_conformal\_evaluate

Full evaluation of prediction intervals: coverage, violation rate, mean width, and Winkler score.

```
ts_conformal_evaluate(actuals DOUBLE[], lower DOUBLE[], upper DOUBLE[], alpha DOUBLE)→ STRUCT(    coverage DOUBLE,    violation_rate DOUBLE,    mean_width DOUBLE,    winkler_score DOUBLE,    n_observations INTEGER)
```

**Example:**

```
SELECT (ts_conformal_evaluate(    [100.0, 105.0, 110.0, 95.0],    [97.0, 102.0, 107.0, 92.0],    [103.0, 108.0, 113.0, 98.0],    0.1)).*;
```

**Return fields:**

*   `coverage`: Fraction of actuals within intervals (target: `1 - alpha`)
*   `violation_rate`: Fraction of actuals outside intervals
*   `mean_width`: Average interval width (narrower = sharper)
*   `winkler_score`: Combined width + penalty for violations (lower = better)
*   `n_observations`: Number of observations evaluated

---

## Table Macros

### ts\_conformal\_by

High-level macro that performs conformal prediction on grouped backtest results.

```
ts_conformal_by(    backtest_results VARCHAR,    group_col COLUMN,    actual_col COLUMN,    forecast_col COLUMN,    point_forecast_col COLUMN,    params STRUCT) → TABLE
```

**Params Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `alpha` | DOUBLE | `0.1` | Miscoverage rate |
| `method` | VARCHAR | `'split'` | `'split'` or `'asymmetric'` |

**Example:**

```
SELECT * FROM ts_conformal_by(    'backtest_results',    product_id,    actual,    forecast,    point_forecast,    {'alpha': 0.1, 'method': 'split'});
```

---

### ts\_conformal\_calibrate

Calibrates a conformity score from backtest residuals.

```
ts_conformal_calibrate(    backtest_results VARCHAR,    actual_col COLUMN,    forecast_col COLUMN,    params STRUCT) → TABLE(conformity_score DOUBLE, coverage DOUBLE, n_residuals INTEGER)
```

**Returns:** Single row table with:

*   `conformity_score`: Computed quantile for intervals
*   `coverage`: Theoretical coverage probability
*   `n_residuals`: Number of residuals used

**Example:**

```
SELECT * FROM ts_conformal_calibrate(    'backtest_results',    actual,    forecast,    {'alpha': 0.05});
```

---

### ts\_conformal\_apply\_by

Applies a pre-computed conformity score to grouped forecast results.

```
ts_conformal_apply_by(    forecast_results VARCHAR,    group_col COLUMN,    forecast_col COLUMN,    conformity_score DOUBLE) → TABLE
```

**Example:**

```
-- Two-step workflow: calibrate then applyCREATE TABLE calibration ASSELECT * FROM ts_conformal_calibrate('backtest', actual, forecast, {'alpha': 0.1});SELECT * FROM ts_conformal_apply_by(    'future_forecasts',    product_id,    yhat,    (SELECT conformity_score FROM calibration));
```

---

### ts\_interval\_width\_by

Computes mean interval width for grouped forecast results.

```
ts_interval_width_by(    results VARCHAR,    group_col COLUMN,    lower_col COLUMN,    upper_col COLUMN) → TABLE(group_col, mean_width DOUBLE, n_intervals BIGINT)
```

**Example:**

```
SELECT * FROM ts_interval_width_by(    'forecast_results',    product_id,    lower,    upper);
```

---

## Complete Workflow

```
-- Step 1: Generate backtest forecastsCREATE TABLE cv_folds ASSELECT * FROM ts_cv_folds_by('sales_data', store_id, date, revenue, 5, 7, MAP{});CREATE TABLE backtest_results ASSELECT * FROM ts_cv_forecast_by('cv_folds', store_id, date, revenue, 'AutoETS',    MAP{'seasonal_period': '7'});-- Step 2: Calibrate conformity score (90% coverage)CREATE TABLE calibration ASSELECT * FROM ts_conformal_calibrate(    'backtest_results',    y,    yhat,    {'alpha': 0.1});-- Step 3: Generate future forecastsCREATE TABLE future_forecasts ASSELECT * FROM ts_forecast_by(    'sales_data', store_id, date, revenue,    'AutoETS', 7, '1d', MAP{'seasonal_period': '7'});-- Step 4: Apply conformal intervalsSELECT * FROM ts_conformal_apply_by(    'future_forecasts',    store_id,    yhat,    (SELECT conformity_score FROM calibration));-- Step 5: Evaluate interval qualitySELECT (ts_conformal_evaluate(    LIST(y ORDER BY ds),    LIST(yhat_lower ORDER BY ds),    LIST(yhat_upper ORDER BY ds),    0.1)).*FROM backtest_results;
```

---

## When to Use Conformal Prediction

| Scenario | Recommendation |
| --- | --- |
| Need guaranteed coverage | Use conformal prediction |
| Non-normal residuals | Use asymmetric conformal |
| Comparing model uncertainty | Use `ts_mean_interval_width` |
| Production intervals | Calibrate on backtest, apply to future |
| Small calibration set | Use conformal (valid for any n) |
| Evaluating interval quality | Use `ts_conformal_evaluate` for Winkler scores |

---

## Comparison with Parametric Intervals

| Aspect | Parametric | Conformal |
| --- | --- | --- |
| Distribution assumption | Requires normality | Distribution-free |
| Coverage guarantee | Asymptotic | Finite-sample valid |
| Interval shape | Always symmetric | Can be asymmetric |
| Calibration data | Not required | Required |
| Computation | Analytical | Empirical quantiles |

The practical advantage of conformal prediction is significant for S&OP planning: parametric intervals assume normal residuals, which rarely holds for real-world demand data that exhibits skewness, heavy tails, and heteroscedasticity. Conformal prediction sidesteps these assumptions entirely. The 4-step production workflow -- backtest with cross-validation, calibrate a conformity score, generate future forecasts, apply conformal intervals -- runs as 4 SQL statements and produces intervals with mathematically guaranteed coverage for any forecast model in the AnoFox library.

---

## Frequently Asked Questions

What does "distribution-free" mean in practice?

Distribution-free means conformal prediction does not assume your forecast errors follow a normal (Gaussian) distribution. It uses the empirical distribution of calibration residuals to construct intervals. This makes it valid for skewed, heavy-tailed, or otherwise non-standard error distributions that are common in real-world demand forecasting.

How many calibration residuals do I need?

Conformal prediction is theoretically valid for any sample size, but practical accuracy improves with more calibration data. For 90% coverage (alpha=0.1), at least 20-30 residuals give reasonable results. For 95% coverage (alpha=0.05), aim for 50+ residuals. Use `ts_conformal_evaluate` to verify that empirical coverage matches the target.

When should I use asymmetric vs. symmetric conformal prediction?

Use symmetric conformal (`ts_conformal_predict`) when your forecast errors are roughly symmetric around zero. Use asymmetric conformal (`ts_conformal_predict_asymmetric`) when errors are skewed, which is common in demand forecasting where over-predictions and under-predictions have different magnitudes. Asymmetric intervals produce tighter, more realistic bounds for skewed data.

What is the Winkler score and how do I interpret it?

The Winkler score is a combined metric that penalizes both interval width and coverage violations. A narrow interval that covers all actuals gets a low (good) score. Violations incur a penalty proportional to how far the actual falls outside the interval. Lower Winkler scores indicate better interval quality. Use it to compare different models' prediction intervals.

([🍪 Cookie Settings](#cookie-settings))

# Cross-Validation & Backtesting

AnoFox provides 8 cross-validation functions covering fold creation (4 functions), forecasting on folds (1 function), feature hydration with automatic masking (1 function), and data leakage prevention (2 functions). The system supports 3 window types -- expanding, fixed, and sliding -- with configurable gap, embargo, and skip length parameters. The `ts_cv_hydrate_by` function automatically masks future values in test sets, preventing the most common source of data leakage in time series backtesting.

Time series cross-validation (also called backtesting) is the process of evaluating a forecasting model by simulating how it would have performed on historical data. Unlike standard k-fold cross-validation, time series CV must respect temporal ordering -- training data must always precede test data to avoid data leakage, where future information contaminates the model. These functions help you create proper train/test splits, forecast on folds, and prevent data leakage.

All CategoryFold CreationForecastingHydrationLeakage Prevention

| Function | Description | Category |
| --- | --- | --- |
| ([`ts_cv_folds_by`](#ts_cv_folds_by)) | Create CV folds by number of folds (primary) | Fold Creation |
| ([`ts_cv_split_by`](#ts_cv_split_by)) | Create splits with explicit cutoff dates | Fold Creation |
| ([`ts_cv_split_folds_by`](#ts_cv_split_folds_by)) | View fold date ranges (train/test boundaries) | Fold Creation |
| ([`ts_cv_split_index_by`](#ts_cv_split_index_by)) | Memory-efficient split (index columns only) | Fold Creation |
| ([`ts_cv_forecast_by`](#ts_cv_forecast_by)) | Generate forecasts for all CV folds | Forecasting |
| ([`ts_cv_hydrate_by`](#ts_cv_hydrate_by)) | Add features to folds with automatic masking | Hydration |
| ([`ts_fill_unknown_by`](#ts_fill_unknown_by)) | Fill unknown future values safely | Leakage Prevention |
| ([`ts_mark_unknown_by`](#ts_mark_unknown_by)) | Mark rows as known/unknown | Leakage Prevention |

Showing 8 of 8

---

## Fold Creation

### ts\_cv\_folds\_by

Create train/test splits for backtesting. This is the primary fold creation function.

```
ts_cv_folds_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    target_col COLUMN,    n_folds BIGINT,    horizon BIGINT,    params MAP) → TABLE
```

**Source must be a table name string, NOT a CTE.** Data must be pre-cleaned (no gaps).

**Parameters:**

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Table name (quoted string) |
| `group_col` | COLUMN | Yes | Series identifier (unquoted) |
| `date_col` | COLUMN | Yes | Date/timestamp column (unquoted) |
| `target_col` | COLUMN | Yes | Target value column (unquoted) |
| `n_folds` | BIGINT | Yes | Number of CV folds |
| `horizon` | BIGINT | Yes | Number of test periods per fold |
| `params` | MAP | No  | Configuration (use `MAP{}` for defaults) |

**Params MAP Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `gap` | BIGINT | `0` | Periods between train end and test start |
| `embargo` | BIGINT | `0` | Periods excluded from training after previous test |
| `window_type` | VARCHAR | `'expanding'` | `'expanding'`, `'fixed'`, or `'sliding'` |
| `min_train_size` | BIGINT | `1` | Min training size (fixed/sliding only) |
| `initial_train_size` | BIGINT | auto | Periods before first fold |
| `skip_length` | BIGINT | `horizon` | Periods between folds (1=dense) |
| `clip_horizon` | BOOLEAN | `false` | Allow partial test windows |

**Returns:** Exactly 5 columns:

| Column | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier |
| `date_col` | (input) | Timestamp |
| `target_col` | DOUBLE | Target value |
| `fold_id` | BIGINT | Fold number |
| `split` | VARCHAR | `'train'` or `'test'` |

**Caution:**

Output has exactly 5 columns. Features are NOT passed through — use `ts_cv_hydrate_by` to add features to folds.

**Example:**

```
-- Create 3 folds with 6-day horizonCREATE TABLE cv_folds ASSELECT * FROM ts_cv_folds_by('sales', store_id, date, revenue, 3, 6, MAP{});-- Fixed window with gapCREATE TABLE cv_folds ASSELECT * FROM ts_cv_folds_by('sales', store_id, date, revenue, 5, 14,    MAP{'window_type': 'fixed', 'gap': '2'});
```

---

### ts\_cv\_split\_by

Split using explicit cutoff dates instead of automatic fold generation.

```
ts_cv_split_by(    source VARCHAR,    group_col IDENTIFIER,    date_col IDENTIFIER,    target_col IDENTIFIER,    training_end_times DATE[],    horizon BIGINT,    params MAP) → TABLE
```

**Returns:** `group_col`, `date_col`, `target_col`, `fold_id`, `split`

**Example:**

```
-- Custom quarterly cutoffsCREATE TABLE cv_splits ASSELECT * FROM ts_cv_split_by('sales', store_id, date, revenue,    ['2024-03-31'::DATE, '2024-06-30'::DATE, '2024-09-30'::DATE], 30, MAP{});
```

---

### ts\_cv\_split\_folds\_by

View fold date ranges before running a full split. Useful for verifying fold boundaries.

```
ts_cv_split_folds_by(    source VARCHAR,    group_col VARCHAR,    date_col VARCHAR,    training_end_times DATE[],    horizon BIGINT,    frequency VARCHAR) → TABLE(fold_id, train_start, train_end, test_start, test_end, horizon)
```

**Example:**

```
SELECT * FROM ts_cv_split_folds_by(    'sales_data', 'store_id', 'date',    ['2024-01-10'::DATE, '2024-01-15'::DATE, '2024-01-20'::DATE],    5, '1d');
```

---

### ts\_cv\_split\_index\_by

Memory-efficient alternative returning only index columns without duplicating data.

```
ts_cv_split_index_by(    source VARCHAR,    group_col IDENTIFIER,    date_col IDENTIFIER,    training_end_times DATE[],    horizon BIGINT,    frequency VARCHAR,    params MAP) → TABLE(group_col, date_col, fold_id, split)
```

**Example:**

```
CREATE TABLE cv_index ASSELECT * FROM ts_cv_split_index_by(    'sales_data', store_id, date,    ['2024-01-10'::DATE, '2024-01-15'::DATE], 5, '1d', MAP{});
```

---

## Forecasting on Folds

### ts\_cv\_forecast\_by

Generate univariate forecasts for all CV folds. **Univariate only** — no exogenous support.

```
ts_cv_forecast_by(    cv_folds VARCHAR,    group_col COLUMN,    date_col COLUMN,    target_col COLUMN,    method VARCHAR,    params MAP) → TABLE
```

**Caution:**

Input table **must** have `fold_id` and `split` columns (from `ts_cv_folds_by` or `ts_cv_split_by`). Passing raw data throws a clear error. Horizon is inferred from test rows — no frequency parameter needed.

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `fold_id` | BIGINT | Fold number |
| `group_col` | (input) | Series identifier |
| `date_col` | (input) | Timestamp |
| `target_col` | DOUBLE | Actual value (from test set) |
| `split` | VARCHAR | Always `'test'` |
| `yhat` | DOUBLE | Point forecast |
| `yhat_lower` | DOUBLE | Lower prediction interval |
| `yhat_upper` | DOUBLE | Upper prediction interval |
| `model_name` | VARCHAR | Model used |

**Example:**

```
-- Create folds firstCREATE TABLE cv_folds ASSELECT * FROM ts_cv_folds_by('sales', store_id, date, revenue, 3, 7, MAP{});-- Forecast on foldsCREATE TABLE cv_results ASSELECT * FROM ts_cv_forecast_by('cv_folds', store_id, date, revenue, 'AutoETS',    MAP{'seasonal_period': '7'});
```

---

## Hydration

### ts\_cv\_hydrate\_by

Add feature columns to CV folds with automatic masking: train rows get actual values, test rows get filled values (preventing data leakage).

```
ts_cv_hydrate_by(    cv_folds VARCHAR,    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    unknown_features VARCHAR[],    params MAP) → TABLE
```

**Parameters:**

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `cv_folds` | VARCHAR | Yes | Table with fold assignments |
| `source` | VARCHAR | Yes | Table containing features |
| `group_col` | COLUMN | Yes | Group column (unquoted) |
| `date_col` | COLUMN | Yes | Date column (unquoted) |
| `unknown_features` | VARCHAR\[\] | Yes | Feature columns to mask in test sets |
| `params` | MAP | No  | Fill strategy configuration |

**Params MAP Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `strategy` | VARCHAR | `'last_value'` | `'last_value'`, `'null'`, or `'default'` |
| `fill_value` | VARCHAR | `''` | Value when `strategy='default'` |

Features are output as direct columns — no MAP extraction needed.

**Example:**

```
SELECT store_id, date, revenue, fold_id, split, competitor_sales, actual_tempFROM ts_cv_hydrate_by('cv_folds', 'sales', store_id, date,    ['competitor_sales', 'actual_temp'], MAP{'strategy': 'last_value'});
```

---

## Leakage Prevention

### ts\_fill\_unknown\_by

Fill unknown future values in test sets to prevent data leakage.

```
ts_fill_unknown_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    value_col COLUMN,    cutoff_date DATE,    params MAP) → TABLE
```

**Params MAP Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `strategy` | VARCHAR | `'last_value'` | `'last_value'`, `'null'`, or `'default'` |
| `fill_value` | DOUBLE | `0.0` | Value when `strategy='default'` |

**Example:**

```
-- Forward fill temperature beyond cutoffSELECT * FROM ts_fill_unknown_by(    'backtest_data', category, date, temperature,    '2023-06-01'::DATE, MAP{});-- Set unknown to NULLSELECT * FROM ts_fill_unknown_by(    'backtest_data', category, date, temperature,    '2023-06-01'::DATE, MAP{'strategy': 'null'});
```

---

### ts\_mark\_unknown\_by

Mark rows as known/unknown based on cutoff date without modifying values.

```
ts_mark_unknown_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    cutoff_date DATE) → TABLE(*, is_unknown BOOLEAN, last_known_date TIMESTAMP)
```

**Returns:** All source columns plus:

*   `is_unknown`: TRUE for future rows
*   `last_known_date`: Per-group last known timestamp

**Example:**

```
SELECT    category, date, temperature,    is_unknown, last_known_date,    CASE        WHEN is_unknown THEN AVG(temperature) OVER (PARTITION BY category)        ELSE temperature    END AS temperature_filledFROM ts_mark_unknown_by(    'backtest_data', category, date,    '2023-06-01'::DATE)ORDER BY category, date;
```

---

## Complete Workflow Example

```
-- 1. Create foldsCREATE TABLE cv_folds ASSELECT * FROM ts_cv_folds_by('sales', store_id, date, revenue, 3, 7, MAP{});-- 2. Forecast per fold (for each model)CREATE TABLE cv_naive ASSELECT * FROM ts_cv_forecast_by('cv_folds', store_id, date, revenue, 'Naive', MAP{});CREATE TABLE cv_autoets ASSELECT * FROM ts_cv_forecast_by('cv_folds', store_id, date, revenue, 'AutoETS',    MAP{'seasonal_period': '7'});-- 3. Compare metrics (use scalar functions with GROUP BY)SELECT 'Naive' AS model,    ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae,    ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS rmseFROM cv_naive GROUP BY ALLUNION ALLSELECT 'AutoETS',    ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)),    ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds))FROM cv_autoets GROUP BY ALL;
```

([🍪 Cookie Settings](#cookie-settings))

# Data Quality

Assess time series data quality across multiple dimensions before forecasting.

| Function | Description |
| --- | --- |
| ([`ts_data_quality_by`](#ts_data_quality_by)) | Multi-dimensional quality scores per series |
| ([`ts_data_quality_summary`](#ts_data_quality_summary)) | Aggregate quality metrics across all series |

Showing 2 of 2

---

## ts\_data\_quality\_by

Evaluates time series data across four quality dimensions, returning per-series quality scores. Also aliased as `ts_data_quality`.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Source table name |
| `group_col` | COLUMN | Yes | Series identifier column (unquoted) |
| `date_col` | COLUMN | Yes | Temporal column (unquoted) |
| `value_col` | COLUMN | Yes | Value column (unquoted) |
| `n_short` | INTEGER | No  | Short series threshold (default: 30) |
| `frequency` | VARCHAR | Yes | Time frequency (`'1d'`, `'1w'`, etc.) |

### Output

Returns one row per series with quality scores:

| Column | Type | Description |
| --- | --- | --- |
| `unique_id` | (input) | Series identifier |
| `structural_score` | DOUBLE | Data completeness (0-1, higher=better) |
| `temporal_score` | DOUBLE | Timestamp regularity (0-1, higher=better) |
| `magnitude_score` | DOUBLE | Value distribution health (0-1, higher=better) |
| `behavioral_score` | DOUBLE | Predictability (0-1, higher=better) |
| `overall_score` | DOUBLE | Overall quality (0-1, higher=better) |
| `n_gaps` | UBIGINT | Gap count |
| `n_missing` | UBIGINT | Missing values |
| `is_constant` | BOOLEAN | Constant series? |

### Quality Dimensions

| Dimension | Score | What It Checks |
| --- | --- | --- |
| **Structural** | `structural_score` | Data completeness, duplicate keys |
| **Temporal** | `temporal_score` | Timestamp regularity, gaps, alignment |
| **Magnitude** | `magnitude_score` | NULLs, constants, value distribution |
| **Behavioural** | `behavioral_score` | Intermittency, seasonality, trend |
| **Overall** | `overall_score` | Weighted combination of all dimensions |

### Examples

```
-- Assess quality with daily frequencySELECT * FROM ts_data_quality_by(    'sales',    product_id,    date,    amount,    30,    '1d');-- Find high-quality series onlySELECT unique_idFROM ts_data_quality_by('sales', product_id, date, amount, 10, '1d')WHERE overall_score > 0.8;-- Find series with temporal issuesSELECT unique_id, temporal_score, n_gapsFROM ts_data_quality_by('sales', product_id, date, amount, 30, '1d')WHERE temporal_score < 0.7;-- Find series needing attentionSELECT unique_id, overall_score, n_gaps, n_missing, is_constantFROM ts_data_quality_by('sales', product_id, date, amount, 30, '1d')WHERE overall_score < 0.5   OR is_constant   OR n_gaps > 10;
```

**Use when:**

*   Auditing data before forecasting
*   Identifying series requiring data preparation
*   Building data quality dashboards

---

## ts\_data\_quality\_summary

Aggregates quality metrics across all series for dataset-level insights.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Source table name |
| `group_col` | COLUMN | Yes | Series identifier column (unquoted) |
| `date_col` | COLUMN | Yes | Temporal column (unquoted) |
| `value_col` | COLUMN | Yes | Value column (unquoted) |
| `n_short` | INTEGER | No  | Short series threshold (default: 30) |

### Example

```
-- Get dataset-level quality summarySELECT * FROM ts_data_quality_summary(    'sales',    product_id,    date,    amount,    30);
```

**Use when:**

*   Identifying systemic data quality issues
*   Reporting on overall dataset health
*   Comparing quality across different datasets

---

## Quality Assessment Workflow

```
-- 1. Run quality assessmentCREATE TABLE quality ASSELECT * FROM ts_data_quality_by(    'sales_raw',    product_id,    date,    amount,    60,    '1d');-- 2. Summarize by score dimensionSELECT    AVG(structural_score) AS avg_structural,    AVG(temporal_score) AS avg_temporal,    AVG(magnitude_score) AS avg_magnitude,    AVG(behavioral_score) AS avg_behavioral,    AVG(overall_score) AS avg_overallFROM quality;-- 3. Identify series needing attentionSELECT unique_id, overall_score, n_gaps, n_missingFROM qualityWHERE overall_score < 0.5ORDER BY overall_score;-- 4. Get dataset-level summarySELECT * FROM ts_data_quality_summary(    'sales_raw', product_id, date, amount, 60);
```

---

## Quality Dimension Guide

| Dimension | What It Checks | Action If Issues Found |
| --- | --- | --- |
| Structural | Duplicate keys, completeness | Deduplicate data |
| Temporal | Missing timestamps, gaps | Use gap filling functions |
| Magnitude | NULLs, constants | Impute or filter |
| Behavioural | Intermittency, patterns | Consider intermittent models |

---

([🍪 Cookie Settings](#cookie-settings))

# Exploratory Data Analysis

Profile your time series data before forecasting with comprehensive statistical metrics.

| Function | Description |
| --- | --- |
| ([`ts_stats`](#anofox_fcst_ts_stats)) | Compute per-series statistical metrics |
| ([`ts_quality_report`](#anofox_fcst_ts_quality_report)) | Generate quality assessment from stats |
| ([`ts_stats_summary`](#anofox_fcst_ts_stats_summary)) | Aggregate statistics across all series |

Showing 3 of 3

---

## anofox\_fcst\_ts\_stats

Computes per-series statistical metrics including length, date ranges, central tendencies, dispersion, value distributions, and quality indicators for exploratory analysis and data profiling.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | VARCHAR | Yes | Grouping column name |
| `date_col` | VARCHAR | Yes | Date/timestamp column |
| `value_col` | VARCHAR | Yes | Value column |
| `frequency` | VARCHAR/INTEGER | Yes | Time frequency (`'1d'`, `'1w'`, `'1mo'`) or integer step |

### Output

| Column | Type | Description |
| --- | --- | --- |
| `series_id` | BIGINT | Series identifier (matches group\_col) |
| `length` | BIGINT | Number of observations |
| `start_date` | DATE/TIMESTAMP | First date in series |
| `end_date` | DATE/TIMESTAMP | Last date in series |
| `expected_length` | BIGINT | Expected length based on frequency |
| `mean` | DOUBLE | Average value |
| `std` | DOUBLE | Standard deviation |
| `min` | DOUBLE | Minimum value |
| `max` | DOUBLE | Maximum value |
| `median` | DOUBLE | Median value |
| `n_null` | BIGINT | Count of NULL values |
| `n_zeros` | BIGINT | Count of zero values |
| `n_unique_values` | BIGINT | Count of distinct values |
| `is_constant` | BOOLEAN | True if series has no variation |
| `plateau_size` | BIGINT | Longest consecutive constant segment |
| `plateau_size_non_zero` | BIGINT | Longest non-zero constant segment |
| `n_zeros_start` | BIGINT | Leading zeros count |
| `n_zeros_end` | BIGINT | Trailing zeros count |
| `n_duplicate_timestamps` | HUGEINT | Duplicate date count |

### Example

```
-- Compute statistics for each productCREATE TABLE sales_stats ASSELECT * FROM anofox_fcst_ts_stats(    'sales_raw',    'product_id',    'date',    'amount',    '1d');-- View series with potential issuesSELECT series_id, length, n_null, is_constantFROM sales_statsWHERE n_null > 0 OR is_constant = true;
```

**Use when:**

*   Profiling a new dataset before forecasting
*   Identifying series with data quality issues
*   Understanding the characteristics of your time series

---

## anofox\_fcst\_ts\_quality\_report

Generates quality assessment reports by evaluating series against configurable thresholds for gaps, missing values, constant series, short series, and temporal alignment.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `stats_table` | VARCHAR | Yes | Table produced by `ts_stats` |
| `min_length` | INTEGER | Yes | Minimum acceptable series length |

### Quality Checks Performed

*   **Gap analysis** - Missing timestamps within series
*   **Missing values** - NULL counts
*   **Constant series** - No variation in values
*   **Short series** - Below minimum length threshold
*   **End date alignment** - Series ending on different dates

### Example

```
-- First generate statisticsCREATE TABLE stats ASSELECT * FROM anofox_fcst_ts_stats(    'sales',    'product_id',    'date',    'amount',    '1d');-- Then generate quality report with 30-day minimumCREATE TABLE quality ASSELECT * FROM anofox_fcst_ts_quality_report('stats', 30);
```

**Use when:**

*   Identifying series that need data preparation
*   Creating data quality dashboards
*   Filtering out problematic series before forecasting

---

## anofox\_fcst\_ts\_stats\_summary

Aggregates statistical metrics across all series from `ts_stats` output, computing dataset-level summaries for high-level dataset characterization.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `stats_table` | VARCHAR | Yes | Table produced by `ts_stats` |

### Output

| Column | Type | Description |
| --- | --- | --- |
| `total_series` | INTEGER | Count of distinct series |
| `total_observations` | BIGINT | Sum of all observations |
| `avg_series_length` | DOUBLE | Mean series length |
| `date_span` | INTEGER | Number of distinct date intervals |

### Example

```
-- Generate statistics firstCREATE TABLE stats ASSELECT * FROM anofox_fcst_ts_stats(    'sales',    'product_id',    'date',    'amount',    '1d');-- Get dataset-level summarySELECT * FROM anofox_fcst_ts_stats_summary('stats');-- Example output:-- total_series | total_observations | avg_series_length | date_span-- 150          | 45000              | 300.0             | 365
```

**Use when:**

*   Getting a quick overview of your dataset
*   Reporting on data characteristics
*   Comparing datasets

---

## Complete EDA Workflow

```
-- 1. Compute per-series statisticsCREATE TABLE stats ASSELECT * FROM anofox_fcst_ts_stats(    'sales_raw',    'product_id',    'date',    'amount',    '1d');-- 2. Get dataset overviewSELECT * FROM anofox_fcst_ts_stats_summary('stats');-- 3. Generate quality report (minimum 60 days)CREATE TABLE quality ASSELECT * FROM anofox_fcst_ts_quality_report('stats', 60);-- 4. Identify problematic seriesSELECT series_idFROM statsWHERE is_constant = true   OR length < 60   OR n_null > length * 0.1;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Exogenous Variables

Incorporate external predictors like temperature, promotions, or competitor prices into your forecasts using exogenous variable support.

| Function | Description |
| --- | --- |
| ([`ts_forecast_exog_by`](#ts_forecast_exog_by)) | Multi-series forecast with exogenous variables |

Showing 1 of 1

---

## Supported Models

Not all forecasting models support exogenous variables. The following models have exogenous variants:

| Base Model | Exogenous Variant | Description |
| --- | --- | --- |
| `ARIMA` / `AutoARIMA` | `ARIMAX` | ARIMA with exogenous regressors |
| `OptimizedTheta` | `ThetaX` | Theta method with exogenous |
| `MFLES` | `MFLESX` | MFLES with exogenous regressors |

---

## ts\_forecast\_exog\_by

Multi-series forecasting with external variables.

```
ts_forecast_exog_by(    table_name VARCHAR,    group_col COLUMN,    date_col COLUMN,    target_col COLUMN,    x_cols VARCHAR[],    future_table VARCHAR,    future_date_col COLUMN,    future_x_cols VARCHAR[],    model VARCHAR,    horizon INTEGER,    params MAP,    frequency VARCHAR) → TABLE
```

**Parameters:**

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table name (quoted string) |
| `group_col` | COLUMN | Yes | Column for grouping series (unquoted) |
| `date_col` | COLUMN | Yes | Date/timestamp column (unquoted) |
| `target_col` | COLUMN | Yes | Target value column (unquoted) |
| `x_cols` | VARCHAR\[\] | Yes | Exogenous column names (array) |
| `future_table` | VARCHAR | Yes | Table containing future exogenous values |
| `future_date_col` | COLUMN | Yes | Date column in future table (unquoted) |
| `future_x_cols` | VARCHAR\[\] | Yes | Exogenous column names in future table |
| `model` | VARCHAR | Yes | Forecasting method (`'ARIMAX'`, `'ThetaX'`, `'MFLESX'`) |
| `horizon` | INTEGER | Yes | Number of periods to forecast |
| `params` | MAP | No  | Model parameters (use `MAP{}` for defaults) |
| `frequency` | VARCHAR | Yes | Data frequency (`'1d'`, `'1w'`, etc.) |

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier |
| `date_col` | TIMESTAMP | Forecast timestamp |
| `yhat` | DOUBLE | Point forecast |
| `yhat_lower` | DOUBLE | Lower prediction interval |
| `yhat_upper` | DOUBLE | Upper prediction interval |

**Example:**

```
-- Create historical data with exogenous variablesCREATE TABLE sales_by_product ASSELECT * FROM (VALUES    ('A', '2024-01-01'::DATE, 100.0, 20.0),    ('A', '2024-01-02'::DATE, 120.0, 22.0),    ('A', '2024-01-03'::DATE, 110.0, 19.0),    ('A', '2024-01-04'::DATE, 130.0, 23.0),    ('B', '2024-01-01'::DATE, 200.0, 20.0),    ('B', '2024-01-02'::DATE, 210.0, 22.0),    ('B', '2024-01-03'::DATE, 205.0, 19.0),    ('B', '2024-01-04'::DATE, 220.0, 23.0)) AS t(product_id, date, amount, temperature);-- Create future exogenous values per productCREATE TABLE future_weather ASSELECT * FROM (VALUES    ('A', '2024-01-05'::DATE, 21.0),    ('A', '2024-01-06'::DATE, 23.0),    ('B', '2024-01-05'::DATE, 21.0),    ('B', '2024-01-06'::DATE, 23.0)) AS t(product_id, date, temperature);-- Forecast each product with temperature as exogenousSELECT * FROM ts_forecast_exog_by(    'sales_by_product', product_id, date, amount,    ['temperature'],    'future_weather', date, ['temperature'],    'ARIMAX', 2, MAP{}, '1d');
```

---

## Requirements

### Future Exogenous Table

The `future_table` must contain:

1.  **Date column** matching `future_date_col`
2.  **All exogenous columns** listed in `future_x_cols`
3.  **Group column** with matching values (for grouped forecasts)
4.  **Exactly `horizon` rows** per group for the forecast period

### Column Alignment

Exogenous column names in the future table must match the historical table:

```
-- Historical: x_cols = ['temperature']-- Future: future_x_cols = ['temperature']-- Column names must matchSELECT * FROM ts_forecast_exog_by(    'sales', product_id, date, amount,    ['temperature', 'promotion'],    'future_data', date, ['temperature', 'promotion'],    'ARIMAX', 7, MAP{}, '1d');
```

---

## Use Cases

### Weather-Adjusted Demand

```
SELECT * FROM ts_forecast_exog_by(    'store_sales', store_id, date, units_sold,    ['temperature', 'humidity'],    'weather_forecast', date, ['temperature', 'humidity'],    'ARIMAX', 14, MAP{}, '1d');
```

### Promotion Planning

```
SELECT * FROM ts_forecast_exog_by(    'store_sales', store_id, date, revenue,    ['is_promotion', 'discount_pct'],    'promotion_calendar', date, ['is_promotion', 'discount_pct'],    'ARIMAX', 28, MAP{}, '1d');
```

### Economic Indicators

```
SELECT * FROM ts_forecast_exog_by(    'monthly_revenue', region, month, revenue,    ['gdp_growth', 'unemployment_rate'],    'economic_forecast', month, ['gdp_growth', 'unemployment_rate'],    'MFLESX', 12, MAP{}, '1mo');
```

---

## Model Selection Guide

| Model | Best For |
| --- | --- |
| `ARIMAX` | Complex patterns with external drivers, automatic order selection |
| `ThetaX` | Trending data with external influences |
| `MFLESX` | Multiple seasonal patterns plus exogenous factors |

---

## Comparison with Base Models

| Aspect | Base Model | Exogenous Variant |
| --- | --- | --- |
| Data requirement | Historical only | Historical + future exogenous |
| Captures external effects | No  | Yes |
| Forecast accuracy | Good | Better (if variables are predictive) |
| Complexity | Simple | Requires future value preparation |

([🍪 Cookie Settings](#cookie-settings))

# Feature Engineering

Feature engineering is the process of extracting meaningful numerical summaries (features) from raw time series data to serve as inputs for machine learning models or downstream analysis. AnoFox computes 117 tsfresh-compatible statistical features -- spanning basic statistics, autocorrelation, trend, entropy, complexity, frequency domain, and pattern detection -- directly from your time series data in SQL.

**Calculation Modes:**

Each feature can be calculated in three ways:

*   **Whole Series** - Calculate the feature for the entire time series (default)
*   **Rolling** - Calculate the feature over a sliding window for temporal patterns
*   **Lag** - Calculate the feature with a time lag for predictive modeling

---

## Extract Time Series Features

Use `anofox_fcst_ts_features` to extract 117 statistical features from time series data.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `values` | DOUBLE\[\] | Yes | \-  | Time series values |
| `dates` | DATE\[\]/TIMESTAMP\[\]/INTEGER\[\] | Yes | \-  | Corresponding dates |

### Output

Returns MAP containing 117 statistical features across multiple categories.

### Example

```
SELECT anofox_fcst_ts_features(    LIST(value),    LIST(date)) as featuresFROM sales_data;
```

**Use for:**

*   Machine learning models
*   Feature engineering at scale
*   Automatic feature extraction

---

## List Available Features

```
SELECT * FROM anofox_fcst_ts_features_list();
```

Returns a table listing all available features that can be extracted.

---

## All Available Features (117)

All CategoryAutocorrelationBasic StatisticsChangeComplexityDistributionDuplicatesEntropyFrequencyLocationPatternPeaksRunsTrend

| Feature | Parameters | Category |
| --- | --- | --- |
| `abs_energy` | \-  | Basic Statistics |
| `absolute_maximum` | \-  | Basic Statistics |
| `absolute_sum_of_changes` | \-  | Basic Statistics |
| `mean` | \-  | Basic Statistics |
| `median` | \-  | Basic Statistics |
| `minimum` | \-  | Basic Statistics |
| `maximum` | \-  | Basic Statistics |
| `standard_deviation` | \-  | Basic Statistics |
| `variance` | \-  | Basic Statistics |
| `skewness` | \-  | Basic Statistics |
| `kurtosis` | \-  | Basic Statistics |
| `root_mean_square` | \-  | Basic Statistics |
| `sum_values` | \-  | Basic Statistics |
| `length` | \-  | Basic Statistics |
| `mean_abs_change` | \-  | Basic Statistics |
| `mean_change` | \-  | Basic Statistics |
| `mean_second_derivative_central` | \-  | Basic Statistics |
| `variation_coefficient` | \-  | Basic Statistics |
| `variance_larger_than_standard_deviation` | \-  | Basic Statistics |
| `quantile` | q: 0.1 | Distribution |
| `count_above` | t: 0.0 | Distribution |
| `count_above_mean` | \-  | Distribution |
| `count_below` | t: 0.0 | Distribution |
| `count_below_mean` | \-  | Distribution |
| `range_count` | min: -1.0, max: 1.0 | Distribution |
| `ratio_beyond_r_sigma` | r: 0.5 | Distribution |
| `ratio_value_number_to_time_series_length` | \-  | Distribution |
| `large_standard_deviation` | r: 0.05 | Distribution |
| `symmetry_looking` | r: 0.0 | Distribution |
| `value_count` | value: 0 | Distribution |
| `index_mass_quantile` | q: 0.1 | Distribution |
| `autocorrelation` | lag: 1 | Autocorrelation |
| `partial_autocorrelation` | lag: 1 | Autocorrelation |
| `agg_autocorrelation` | f\_agg: mean, maxlag: 40 | Autocorrelation |
| `c3` | lag: 1 | Autocorrelation |
| `time_reversal_asymmetry_statistic` | lag: 1 | Autocorrelation |
| `linear_trend` | attr: pvalue | Trend |
| `linear_trend_timewise` | attr: pvalue | Trend |
| `agg_linear_trend` | attr: rvalue, chunk\_len: 5, f\_agg: max | Trend |
| `ar_coefficient` | coeff: 0, k: 10 | Trend |
| `augmented_dickey_fuller` | attr: teststat | Trend |
| `friedrich_coefficients` | coeff: 0, m: 3, r: 30 | Trend |
| `max_langevin_fixed_point` | m: 3, r: 30 | Trend |
| `approximate_entropy` | m: 2, r: 0.1 | Entropy |
| `sample_entropy` | \-  | Entropy |
| `permutation_entropy` | dimension: 3, tau: 1 | Entropy |
| `binned_entropy` | max\_bins: 10 | Entropy |
| `fourier_entropy` | bins: 2 | Entropy |
| `lempel_ziv_complexity` | bins: 2 | Complexity |
| `cid_ce` | \-  | Complexity |
| `benford_correlation` | \-  | Complexity |
| `fft_coefficient` | attr: real, coeff: 0 | Frequency |
| `fft_aggregated` | aggtype: centroid | Frequency |
| `spkt_welch_density` | coeff: 2 | Frequency |
| `cwt_coefficients` | coeff: 0, w: 2, widths: \[2,5,10,20\] | Frequency |
| `energy_ratio_by_chunks` | num\_segments: 10, segment\_focus: 0 | Frequency |
| `first_location_of_maximum` | \-  | Location |
| `first_location_of_minimum` | \-  | Location |
| `last_location_of_maximum` | \-  | Location |
| `last_location_of_minimum` | \-  | Location |
| `longest_strike_above_mean` | \-  | Runs |
| `longest_strike_below_mean` | \-  | Runs |
| `number_crossing_m` | m: 0 | Runs |
| `has_duplicate` | \-  | Duplicates |
| `has_duplicate_max` | \-  | Duplicates |
| `has_duplicate_min` | \-  | Duplicates |
| `percentage_of_reoccurring_datapoints_to_all_datapoints` | \-  | Duplicates |
| `percentage_of_reoccurring_values_to_all_values` | \-  | Duplicates |
| `sum_of_reoccurring_data_points` | \-  | Duplicates |
| `sum_of_reoccurring_values` | \-  | Duplicates |
| `number_peaks` | n: 1 | Peaks |
| `number_cwt_peaks` | n: 1 | Peaks |
| `mean_n_absolute_max` | number\_of\_maxima: 3 | Peaks |
| `change_quantiles` | f\_agg: mean, isabs: false, qh: 0.2, ql: 0.0 | Change |
| `matrix_profile` | feature: min, threshold: 0.98 | Pattern |
| `query_similarity_count` | threshold: 0.0 | Pattern |

Showing 76 of 76

---

## Load Feature Configuration

### From JSON

```
SELECT anofox_fcst_ts_features_config_from_json('/path/to/config.json') as config;
```

### From CSV

```
SELECT anofox_fcst_ts_features_config_from_csv('/path/to/config.csv') as config;
```

---

## Feature Engineering Example

Extract features for machine learning models:

```
SELECT    product_id,    anofox_fcst_ts_features(        LIST(sales ORDER BY date),        LIST(date ORDER BY date)    ) as featuresFROM sales_by_productGROUP BY product_id;
```

---

## Feature Engineering Workflow

```
-- 1. Extract time series featuresCREATE TABLE step1_features ASSELECT    product_id,    anofox_fcst_ts_features(        LIST(sales ORDER BY date),        LIST(date ORDER BY date)    ) as featuresFROM sales_by_productGROUP BY product_id;-- 2. Use standard SQL for temporal featuresCREATE TABLE step2_temporal ASSELECT    date,    sales,    DAYOFWEEK(date) as day_of_week,    MONTH(date) as month,    LAG(sales, 7) OVER (ORDER BY date) as lag_7,    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7FROM sales_data;-- 3. Use for forecastingSELECT * FROM ts_forecast_by(    'step2_temporal', NULL, date, sales,    'AutoETS', 30, '1d',    MAP{});
```

---

## Standard SQL for Lag and Rolling

Use DuckDB window functions for lag and rolling statistics:

```
SELECT    date,    sales,    -- Lag features    LAG(sales, 1) OVER (ORDER BY date) as lag_1,    LAG(sales, 7) OVER (ORDER BY date) as lag_7,    LAG(sales, 28) OVER (ORDER BY date) as lag_28,    -- Rolling features    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma_7,    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as ma_28,    -- Other features    DAYOFWEEK(date) as day_of_week,    MONTH(date) as month,    QUARTER(date) as quarterFROM sales_data;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Hierarchy Management

When dealing with hierarchical time series (e.g., region/store/item), you often need to combine multiple ID columns into a single `unique_id` for forecasting. These functions provide a complete workflow.

| Function | Description |
| --- | --- |
| ([`ts_validate_separator`](#ts_validate_separator)) | Check if separator conflicts with data values |
| ([`ts_combine_keys`](#ts_combine_keys)) | Merge multiple ID columns into unique\_id |
| ([`ts_aggregate_hierarchy`](#ts_aggregate_hierarchy)) | Create aggregated series at all hierarchy levels |
| ([`ts_split_keys`](#ts_split_keys)) | Split unique\_id back into component columns |

Showing 4 of 4

---

## Workflow Overview

```
┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐│  1. Validate    │ -> │  2. Combine     │ -> │  3. Forecast    │ -> │  4. Split       ││  Separator      │    │  Keys           │    │  (any model)    │    │  Keys           │└─────────────────┘    └─────────────────┘    └─────────────────┘    └─────────────────┘
```

---

## ts\_validate\_separator

Checks if a separator character exists in any ID column values before combining keys.

```
ts_validate_separator(    source,    id_col1, [id_col2], [id_col3], [id_col4], [id_col5],    separator := '|')
```

**Parameters:**

*   `source` - Source table or subquery name
*   `id_col1` through `id_col5` - ID columns to check (up to 5, at least 1 required)
*   `separator` - The separator character to validate (default: `'|'`)

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `separator` | VARCHAR | The separator being validated |
| `is_valid` | BOOLEAN | True if separator is safe to use |
| `n_conflicts` | INTEGER | Number of values containing separator |
| `conflicting_values` | VARCHAR\[\] | List of problematic values |
| `message` | VARCHAR | Helpful message with alternatives if invalid |

**Example:**

```
-- Check if pipe separator is safeSELECT * FROM ts_validate_separator('sales', region_id, store_id, item_id);-- Check with custom separatorSELECT * FROM ts_validate_separator('sales', region_id, store_id, separator := '::');
```

---

## ts\_combine\_keys

Merges multiple ID columns into a single `unique_id` column without creating aggregated series.

```
ts_combine_keys(    source,    date_col,    value_col,    id_col1, [id_col2], [id_col3], [id_col4], [id_col5],    params := MAP{})
```

**Parameters:**

*   `source` - Source table or subquery name
*   `date_col` - The date/timestamp column
*   `value_col` - The value column to preserve
*   `id_col1` through `id_col5` - ID columns to combine (up to 5, at least 1 required)
*   `params` - Optional MAP of parameters

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `unique_id` | VARCHAR | Combined ID (e.g., \`'EU |
| `date_col` | (input type) | Date column |
| `value_col` | (input type) | Value column |

**Example:**

```
-- Combine region, store, and item into single IDSELECT * FROM ts_combine_keys(    'sales',    sale_date,    quantity,    region_id, store_id, item_id);-- Result:-- unique_id        | sale_date  | quantity-- EU|STORE001|SKU42| 2024-01-01 | 100-- EU|STORE001|SKU42| 2024-01-02 | 105-- EU|STORE002|SKU42| 2024-01-01 | 50
```

---

## ts\_aggregate\_hierarchy

Combines ID columns AND creates aggregated series at all hierarchy levels. Useful for hierarchical reconciliation.

```
ts_aggregate_hierarchy(    source,    date_col,    value_col,    id_col1, id_col2, id_col3,    params := MAP{})
```

**Parameters:**

*   `source` - Source table or subquery name
*   `date_col` - The date/timestamp column
*   `value_col` - The value column to aggregate
*   `id_col1`, `id_col2`, `id_col3` - ID columns defining hierarchy levels
*   `params` - Optional MAP of parameters

**Returns:**

Table with aggregated series at each level, identified by `unique_id` containing aggregation markers.

**Example:**

```
-- Create aggregated series at all levelsSELECT * FROM ts_aggregate_hierarchy(    'sales',    sale_date,    quantity,    region_id, store_id, item_id);-- Result includes:-- EU|STORE001|SKU42    (leaf level)-- EU|STORE001|_ALL_    (store total)-- EU|_ALL_|_ALL_       (region total)-- _ALL_|_ALL_|_ALL_    (grand total)
```

---

## ts\_split\_keys

Reverses the combination process, splitting `unique_id` back into component columns after forecasting.

```
ts_split_keys(    source,    id_col,    date_col,    value_col,    separator := '|')
```

**Parameters:**

*   `source` - Source table or subquery name (typically forecast results)
*   `id_col` - The combined unique\_id column to split
*   `date_col` - The date column name
*   `value_col` - The value column name (e.g., forecast values)
*   `separator` - The separator used when combining (default: `'|'`)

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `id_part_1` | VARCHAR | First component |
| `id_part_2` | VARCHAR | Second component |
| `id_part_3` | VARCHAR | Third component |
| `date_col` | (input type) | Date column |
| `value_col` | (input type) | Value column |

**Example:**

```
-- Split forecast results back into componentsSELECT * FROM ts_split_keys(    'forecast_results',    unique_id,    forecast_date,    point_forecast);-- Result:-- id_part_1 | id_part_2 | id_part_3 | forecast_date | point_forecast-- EU        | STORE001  | SKU42     | 2024-02-01    | 110.5-- EU        | STORE001  | SKU42     | 2024-02-02    | 112.3
```

---

## Complete Workflow Example

```
-- Step 1: Validate separator won't conflict with dataSELECT * FROM ts_validate_separator('sales', region, store, product);-- Ensure is_valid = TRUE before proceeding-- Step 2: Combine keys for forecastingCREATE TABLE sales_combined ASSELECT * FROM ts_combine_keys(    'sales',    date,    revenue,    region, store, product);-- Step 3: Generate forecastsCREATE TABLE forecasts ASSELECT * FROM anofox_fcst_ts_forecast_by(    'sales_combined',    'unique_id',    'date',    'revenue',    'AutoETS',    28,    MAP{});-- Step 4: Split keys back for analysisCREATE TABLE forecasts_detailed ASSELECT    id_part_1 AS region,    id_part_2 AS store,    id_part_3 AS product,    forecast_date,    point_forecastFROM ts_split_keys(    'forecasts',    unique_id,    forecast_date,    point_forecast);-- Now analyze forecasts by region, store, or productSELECT region, SUM(point_forecast) AS total_forecastFROM forecasts_detailedGROUP BY region;
```

---

## Use Cases

### Retail Hierarchy

```
-- Region > Store > Category > SKUSELECT * FROM ts_combine_keys(    'retail_sales',    sale_date,    units,    region, store_id, category, sku);
```

### Geographic Hierarchy

```
-- Country > State > CitySELECT * FROM ts_combine_keys(    'location_data',    date,    value,    country, state, city);
```

### Product Hierarchy

```
-- Division > Department > Class > SKUSELECT * FROM ts_combine_keys(    'inventory',    week_date,    quantity,    division, department, class_id);
```

---

## Best Practices

1.  **Always validate first** - Run `ts_validate_separator` before combining to avoid data corruption
    
2.  **Choose unique separator** - Use `|` (pipe) or `::` that won't appear in your data
    
3.  **Preserve original columns** - Keep the original table for reference
    
4.  **Document hierarchy** - Note the column order used for combining (important for splitting)
    
5.  **Consider aggregation** - Use `ts_aggregate_hierarchy` when you need forecasts at multiple levels
    

([🍪 Cookie Settings](#cookie-settings))

# Installation & Setup

Get AnoFox Forecast running in minutes.

## System Requirements

| Requirement | Version |
| --- | --- |
| **DuckDB** | 1.4.3 or later |
| **OS** | Linux, macOS, Windows |
| **Architecture** | x86\_64, ARM64, WASM |

## Installation

Install from the DuckDB Community Registry:

```
INSTALL anofox_forecast FROM community;LOAD anofox_forecast;
```

For local builds or development versions, see the [GitHub repository](https://github.com/DataZooDE/anofox-forecast).

## Verify Installation

```
-- Load extensionLOAD anofox_forecast;-- Test with a simple forecastCREATE TABLE test_data ASSELECT DATE '2023-01-01' + INTERVAL (i) DAY AS date,       'series_1' AS id,       100 + i AS valueFROM generate_series(0, 99) t(i);SELECT * FROM ts_forecast_by(    'test_data', id, date, value,    'Naive', 7, '1d', MAP{})LIMIT 3;
```

## Persistent Loading

To load the extension automatically, create a `.duckdbrc` file:

```
# ~/.duckdbrcLOAD anofox_forecast;
```

---

## Frequency Format

Many functions require a `frequency` parameter. Three formats are supported:

| Format | Example | Description |
| --- | --- | --- |
| Integer | `7` | Period in observations |
| Polars-style | `'1d'`, `'7d'`, `'1w'`, `'1mo'`, `'1q'`, `'1y'` | String duration |
| DuckDB INTERVAL | `INTERVAL 7 DAY` | Native DuckDB interval |

**Examples:**

```
-- All equivalent for weekly dataSELECT * FROM ts_fill_gaps_by('data', 'id', 'date', 'value', 7);SELECT * FROM ts_fill_gaps_by('data', 'id', 'date', 'value', '1w');SELECT * FROM ts_fill_gaps_by('data', 'id', 'date', 'value', '7d');
```

**Common Frequencies:**

| Data | Integer | String |
| --- | --- | --- |
| Daily → Weekly | 7   | `'1w'` or `'7d'` |
| Hourly → Daily | 24  | `'1d'` or `'24h'` |
| Daily → Monthly | 30  | `'1mo'` |
| Daily → Yearly | 365 | `'1y'` |
| Weekly → Yearly | 52  | `'52w'` |
| Monthly → Yearly | 12  | `'12mo'` |

---

## Key Design Principles

### NULL Handling

*   Most functions ignore NULL values in calculations
*   NULLs in arrays are skipped, not replaced
*   Use `ts_fill_nulls_*` functions to handle NULLs before forecasting

### Minimum Requirements

| Function Type | Minimum Observations |
| --- | --- |
| Basic statistics | 3   |
| Period detection | 4-32 (varies by method) |
| Forecasting | `2 × seasonal_period` recommended |
| Feature extraction | 10+ for reliable results |

### Function Naming

All functions follow the pattern:

```
anofox_fcst_ts_<operation>[_variant]
```

Short aliases are available:

| Full Name | Short Alias |
| --- | --- |
| `anofox_fcst_ts_forecast_by` | `ts_forecast_by` |
| `anofox_fcst_ts_detect_periods_by` | `ts_detect_periods_by` |
| `anofox_fcst_ts_fill_gaps_by` | `ts_fill_gaps_by` |

### Case Sensitivity

*   Function names are case-insensitive
*   Column names follow DuckDB's standard rules
*   Model names (e.g., `'AutoETS'`) are case-sensitive

([🍪 Cookie Settings](#cookie-settings))

# Evaluation Metrics

AnoFox Forecast includes 11 built-in accuracy metrics across 3 categories: 8 error metrics (MAE, MSE, RMSE, MAPE, SMAPE, MASE, Bias, RMAE), 1 goodness-of-fit metric (R-squared), and 2 interval validation metrics (coverage and quantile loss). MASE is the recommended primary metric for comparing forecasts across different scales because it normalizes error against a naive baseline -- a MASE below 1.0 means the model outperforms naive forecasting. For prediction interval validation, target 93-97% empirical coverage for a 95% confidence interval.

Forecast evaluation metrics are numerical measures that quantify how close a model's predictions are to the actual observed values. Choosing the right metric depends on your business context: error metrics (MAE, RMSE) measure absolute accuracy, percentage metrics (MAPE, SMAPE) enable cross-series comparison, scaled metrics (MASE) benchmark against baselines, and interval metrics (coverage) validate prediction uncertainty.

All CategoryErrorGoodness of FitInterval

| Function | Description | Category |
| --- | --- | --- |
| ([`ts_mae`](#mae-mean-absolute-error)) | Mean Absolute Error | Error |
| ([`ts_mse`](#mse-mean-squared-error)) | Mean Squared Error | Error |
| ([`ts_rmse`](#rmse-root-mean-squared-error)) | Root Mean Squared Error | Error |
| ([`ts_mape`](#mape-mean-absolute-percentage-error)) | Mean Absolute Percentage Error | Error |
| ([`ts_smape`](#smape-symmetric-mape)) | Symmetric MAPE | Error |
| ([`ts_mase`](#mase-mean-absolute-scaled-error)) | Mean Absolute Scaled Error | Error |
| ([`ts_bias`](#bias-mean-error)) | Forecast bias (mean error) | Error |
| ([`ts_rmae`](#rmae-relative-mae)) | Relative Mean Absolute Error | Error |
| ([`ts_r2`](#r²-coefficient-of-determination)) | Coefficient of Determination | Goodness of Fit |
| ([`ts_coverage`](#coverage)) | Interval coverage percentage | Interval |
| ([`ts_quantile_loss`](#quantile-loss)) | Pinball loss for quantile forecasts | Interval |

Showing 11 of 11

---

## Error Metrics

### MAE (Mean Absolute Error)

Average absolute difference.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `predicted` | DOUBLE\[\] | Yes | \-  | Predicted values |

**Formula:** MAE = Σ|actual - predicted| / n

**Range:** 0 to infinity. Lower is better.

#### Example

```
SELECT anofox_fcst_ts_mae(LIST(actual), LIST(predicted)) as MAEFROM comparison;
```

---

### MSE (Mean Squared Error)

Average squared difference between actual and predicted values.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `predicted` | DOUBLE\[\] | Yes | \-  | Predicted values |

**Formula:** MSE = Σ(actual - predicted)² / n

**Range:** 0 to infinity. Lower is better.

#### Example

```
SELECT anofox_fcst_ts_mse(LIST(actual), LIST(predicted)) as MSEFROM comparison;
```

---

### RMSE (Root Mean Squared Error)

Square root of mean squared error.

**Formula:** RMSE = sqrt(Σ(actual - predicted)² / n)

**Range:** 0 to infinity. Lower is better.

#### Example

```
SELECT anofox_fcst_ts_rmse(LIST(actual), LIST(predicted)) as RMSEFROM comparison;
```

---

### MAPE (Mean Absolute Percentage Error)

Average percentage error.

**Formula:** MAPE = Σ|actual - predicted| / |actual| × 100 / n

**Range:** 0 to infinity (usually 0-100%).

#### Example

```
SELECT anofox_fcst_ts_mape(LIST(actual), LIST(predicted)) as MAPE_pctFROM comparison;
```

---

### SMAPE (Symmetric MAPE)

Symmetric percentage error.

**Formula:** SMAPE = 2 × Σ|actual - predicted| / (|actual| + |predicted|) × 100 / n

**Range:** 0 to 200%.

#### Example

```
SELECT anofox_fcst_ts_smape(LIST(actual), LIST(predicted)) as SMAPE_pctFROM comparison;
```

---

### MASE (Mean Absolute Scaled Error)

Error scaled relative to naive baseline.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `predicted` | DOUBLE\[\] | Yes | \-  | Predicted values |
| `seasonal_period` | INTEGER | Yes | \-  | Seasonal period |

**Formula:** MASE = MAE / MAE(naive\_forecast)

**Interpretation:**

*   MASE < 1: Better than naive
*   MASE = 1: Same as naive
*   MASE > 1: Worse than naive

#### Example

```
SELECT anofox_fcst_ts_mase(LIST(actual), LIST(predicted), 7) as MASEFROM comparison;
```

---

### Bias (Mean Error)

Average signed error (with direction).

**Formula:** Bias = Σ(actual - predicted) / n

**Interpretation:**

*   Positive: Forecast too low (pessimistic)
*   Negative: Forecast too high (optimistic)
*   Zero: Unbiased

#### Example

```
SELECT anofox_fcst_ts_bias(LIST(actual), LIST(predicted)) as biasFROM comparison;
```

---

### RMAE (Relative MAE)

MAE of the model relative to a baseline model's MAE.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `predicted` | DOUBLE\[\] | Yes | \-  | Model predictions |
| `baseline` | DOUBLE\[\] | Yes | \-  | Baseline model predictions |

**Formula:** RMAE = MAE(model) / MAE(baseline)

**Interpretation:**

*   RMAE < 1: Model is better than baseline
*   RMAE = 1: Same as baseline
*   RMAE > 1: Model is worse than baseline

#### Example

```
SELECT anofox_fcst_ts_rmae(    LIST(actual),    LIST(predicted),    LIST(baseline_predicted)) as RMAEFROM comparison;
```

---

## Goodness of Fit

### R² (Coefficient of Determination)

Proportion of variance explained.

**Formula:** R² = 1 - (Σ(actual - predicted)²) / (Σ(actual - mean(actual))²)

**Range:** -infinity to 1. Higher is better.

**Interpretation:**

*   R² = 1.0: Perfect prediction
*   R² = 0.8: Explains 80% of variance
*   R² = 0: No better than predicting mean
*   R² < 0: Worse than mean

#### Example

```
SELECT anofox_fcst_ts_r2(LIST(actual), LIST(predicted)) as R2FROM comparison;
```

---

## Interval Validation

### Coverage

Percentage of actuals within prediction intervals.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `lower` | DOUBLE\[\] | Yes | \-  | Lower bounds |
| `upper` | DOUBLE\[\] | Yes | \-  | Upper bounds |

**Formula:** Coverage = % of actuals where: lower ≤ actual ≤ upper

**Range:** 0 to 1 (0% to 100%)

**Interpretation:**

*   Expected: 95% for 95% confidence
*   Too low: Intervals too narrow (risky)
*   Too high: Intervals too wide (wasteful)

#### Example

```
SELECT anofox_fcst_ts_coverage(LIST(actual), LIST(lower), LIST(upper)) as coverageFROM comparison;
```

**Ideal range:** 92-98% for 95% CI

---

### Quantile Loss

Pinball loss for evaluating quantile forecasts.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE\[\] | Yes | \-  | Actual values |
| `predicted` | DOUBLE\[\] | Yes | \-  | Quantile predictions |
| `quantile` | DOUBLE | Yes | \-  | Target quantile (0-1) |

**Formula:** QL = (q - 1{actual < predicted}) × (actual - predicted)

**Range:** 0 to infinity. Lower is better.

#### Example

```
-- Evaluate 90th percentile forecastSELECT anofox_fcst_ts_quantile_loss(LIST(actual), LIST(q90), 0.9) as QL_90FROM comparison;
```

---

## Complete Metrics Example

```
WITH comparison AS (    SELECT        actual,        forecast,        lower_95,        upper_95    FROM forecast_results)SELECT    ROUND(anofox_fcst_ts_mae(LIST(actual), LIST(forecast)), 2) as MAE,    ROUND(anofox_fcst_ts_rmse(LIST(actual), LIST(forecast)), 2) as RMSE,    ROUND(anofox_fcst_ts_mape(LIST(actual), LIST(forecast)), 2) as MAPE_pct,    ROUND(anofox_fcst_ts_smape(LIST(actual), LIST(forecast)), 2) as SMAPE_pct,    ROUND(anofox_fcst_ts_mase(LIST(actual), LIST(forecast), 7), 2) as MASE,    ROUND(anofox_fcst_ts_bias(LIST(actual), LIST(forecast)), 2) as Bias,    ROUND(anofox_fcst_ts_r2(LIST(actual), LIST(forecast)), 4) as R2,    ROUND(anofox_fcst_ts_coverage(LIST(actual), LIST(lower_95), LIST(upper_95)), 3) as coverage_95FROM comparison;
```

---

## Metric Selection Guide

| Goal | Primary | Secondary |
| --- | --- | --- |
| Minimize errors | MAE or RMSE | MAPE |
| Executive report | MAPE | MAE |
| Avoid big mistakes | RMSE | MAE |
| Compare products | MAPE or MASE | SMAPE |
| Confidence needed | Coverage | R²  |
| Inventory planning | Bias | MASE |

---

## Typical Metric Ranges

| Metric | Excellent | Good | Fair | Poor |
| --- | --- | --- | --- | --- |
| MAPE | < 5% | 5-10% | 10-20% | \> 20% |
| RMSE | < 10% of mean | 10-20% | 20-30% | \> 30% |
| R²  | \> 0.95 | 0.80-0.95 | 0.60-0.80 | < 0.60 |
| MASE | < 0.8 | 0.8-1.0 | 1.0-1.2 | \> 1.2 |
| Coverage | 93-97% | 90-98% | 85-99% | Outside |

For inventory and supply chain planning, the Bias metric deserves special attention: a positive bias means the forecast is systematically too low (risking stockouts), while a negative bias means it is systematically too high (causing excess inventory). The complete metrics example above computes all 8 key metrics in a single SQL query, giving a comprehensive view of point forecast accuracy, directional bias, baseline comparison, and prediction interval quality.

---

([🍪 Cookie Settings](#cookie-settings))

# Function Finder

The AnoFox Forecast extension exposes 50+ SQL functions organized into 10 categories: exploratory data analysis (3 functions), data quality assessment (2 functions), data preparation (14 functions), diagnostics with 12 period detection algorithms (8 functions), 117 tsfresh-compatible feature extraction functions (4 functions), 32 forecasting models, 11 accuracy metrics, 8 cross-validation functions, 11 conformal prediction functions, and hierarchy management (3 functions). Every function follows the `ts_*_by` naming convention for grouped operations, processing all series in a single SQL call.

Quick reference to all Forecast extension functions. Find the right function for your task.

**Function Naming:** All functions use the `anofox_fcst_ts_*` prefix. Short aliases (e.g., `ts_forecast_by`) are also available.

## Categories

[### Exploratory Data Analysis

Profile your time series data

`stats``quality_report``stats_summary`](/docs/forecast/eda.md)[### Data Quality

Multi-dimensional quality assessment

`data_quality``data_quality_summary`](/docs/forecast/data-quality.md)[### Data Preparation

12 functions for cleaning and transforming data

`fill_gaps``drop_short``fill_nulls`+9 more](/docs/forecast/data-preparation.md)[### Diagnostics

Period detection, decomposition, peaks, changepoints

`detect_periods_by``classify_seasonality``detrend_by`+4 more](/docs/forecast/diagnostics.md)[### Cross-Validation

8 functions for backtesting and CV splits

`cv_folds_by``cv_forecast_by``cv_hydrate_by`+5 more](/docs/forecast/cross-validation.md)[### Conformal Prediction

11 functions for distribution-free intervals

`conformal_predict``conformal_calibrate``conformal_evaluate`](/docs/forecast/conformal-prediction.md)[### Exogenous Variables

Incorporate external predictors

`forecast_exog_by`](/docs/forecast/exogenous.md)[### Hierarchy Management

Multi-level key handling for forecasting

`combine_keys``split_keys``aggregate_hierarchy`](/docs/forecast/hierarchy.md)[### Features

Extract 117 time series characteristics

`features``features_list``config_from_json`](/docs/forecast/features.md)[### Models

32 forecasting algorithms for any data type

`AutoETS``AutoARIMA``TBATS``Theta`+27 more](/docs/forecast/models.md)[### Metrics

11 forecast accuracy and error metrics

`MAE``RMSE``MAPE``MASE`+7 more](/docs/forecast/metrics.md)

## All Functions

All CategoryConformalCross-ValidationData PreparationData QualityDiagnosticsEDAFeaturesForecastingMetrics

| Function | Description | SQL Signature | Category |
| --- | --- | --- | --- |
| `anofox_fcst_ts_forecast_by` | Multiple series forecast | `(table, group, date, value, model, horizon, freq, params) -> TABLE` | Forecasting |
| `anofox_fcst_ts_forecast_exog_by` | Multi-series forecast with exogenous | `(table, group, date, value, x_cols, future, ...) -> TABLE` | Forecasting |
| `anofox_fcst_ts_stats_by` | Per-series statistics (36 columns) | `(table, group, date, value, freq) -> TABLE` | EDA |
| `anofox_fcst_ts_quality_report` | Quality assessment from stats | `(stats_table, min_length) -> TABLE` | EDA |
| `anofox_fcst_ts_stats_summary` | Dataset-level summary | `(stats_table) -> TABLE` | EDA |
| `anofox_fcst_ts_data_quality_by` | Multi-dimensional quality scores | `(table, group, date, value, n_short, freq) -> TABLE` | Data Quality |
| `anofox_fcst_ts_data_quality_summary` | Quality summary across series | `(table, group, date, value, n_short) -> TABLE` | Data Quality |
| `anofox_fcst_ts_detect_periods_by` | Multi-method period detection | `(table, group, date, value, params) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_classify_seasonality_by` | Classify seasonality type | `(table, group, date, value, period) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_classify_seasonality` | Classify seasonality (single) | `(table, date, value, period) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_mstl_decomposition_by` | MSTL decomposition | `(table, group, date, value, periods[], params) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_detrend_by` | Remove trend | `(table, group, date, value, method) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_detect_peaks_by` | Detect local maxima | `(table, group, date, value, params) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_analyze_peak_timing_by` | Analyze peak timing | `(table, group, date, value, period, params) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_detect_changepoints_by` | Multi-series changepoints | `(table, group, date, value, params) -> TABLE` | Diagnostics |
| `anofox_fcst_ts_features_by` | Extract 117 features | `(table, group, date, value) -> TABLE` | Features |
| `anofox_fcst_ts_features_list` | List available features | `() -> TABLE` | Features |
| `anofox_fcst_ts_features_config_from_json` | Load feature config (JSON) | `(path) -> MAP` | Features |
| `anofox_fcst_ts_features_config_from_csv` | Load feature config (CSV) | `(path) -> MAP` | Features |
| `anofox_fcst_ts_mae` | Mean Absolute Error | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_mse` | Mean Squared Error | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_rmse` | Root Mean Squared Error | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_mape` | Mean Abs Percentage Error | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_smape` | Symmetric MAPE | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_mase` | Mean Abs Scaled Error | `(actual[], predicted[], baseline[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_r2` | R-squared | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_bias` | Forecast bias (mean error) | `(actual[], predicted[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_rmae` | Relative MAE | `(actual[], predicted[], baseline[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_quantile_loss` | Quantile loss | `(actual[], predicted[], quantile) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_coverage` | Interval coverage | `(actual[], lower[], upper[]) -> DOUBLE` | Metrics |
| `anofox_fcst_ts_fill_gaps_by` | Fill missing timestamps | `(table, group, date, value, freq) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_fill_forward_by` | Extend series to target date | `(table, group, date, value, target, freq) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_constant_by` | Remove constant series | `(table, group, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_short_by` | Remove short series | `(table, group, min_length) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_gappy_by` | Remove gappy series | `(table, group, value, max_gap_ratio) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_zeros_by` | Remove all-zero series | `(table, group, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_leading_zeros_by` | Remove leading zeros | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_trailing_zeros_by` | Remove trailing zeros | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_drop_edge_zeros_by` | Remove edge zeros | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_fill_nulls_const_by` | Fill NULLs with constant | `(table, group, date, value, fill_value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_fill_nulls_forward_by` | Forward fill (LOCF) | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_fill_nulls_backward_by` | Backward fill (NOCB) | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_fill_nulls_mean_by` | Fill with series mean | `(table, group, date, value) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_diff_by` | Differencing transformation | `(table, group, date, value, order) -> TABLE` | Data Preparation |
| `anofox_fcst_ts_cv_folds_by` | Create CV folds | `(table, group, date, value, n_folds, horizon, params) -> TABLE` | Cross-Validation |
| `anofox_fcst_ts_cv_forecast_by` | Forecast on CV folds | `(folds, group, date, value, method, params) -> TABLE` | Cross-Validation |
| `anofox_fcst_ts_cv_split_by` | Custom fold boundaries | `(table, group, date, value, cutoffs, horizon, params) -> TABLE` | Cross-Validation |
| `anofox_fcst_ts_cv_hydrate_by` | Add features to folds | `(folds, source, group, date, features[], params) -> TABLE` | Cross-Validation |
| `anofox_fcst_ts_conformal_by` | Grouped conformal prediction | `(backtest, group, actual, forecast, point, params) -> TABLE` | Conformal |
| `anofox_fcst_ts_conformal_calibrate` | Calibrate conformity score | `(backtest, actual, forecast, params) -> TABLE` | Conformal |
| `anofox_fcst_ts_conformal_apply_by` | Apply score to forecasts | `(forecasts, group, forecast_col, score) -> TABLE` | Conformal |
| `anofox_fcst_ts_conformal_coverage` | Empirical coverage | `(actuals[], lower[], upper[]) -> DOUBLE` | Conformal |
| `anofox_fcst_ts_conformal_evaluate` | Full interval evaluation | `(actuals[], lower[], upper[], alpha) -> STRUCT` | Conformal |

Showing 53 of 53

([🍪 Cookie Settings](#cookie-settings))

# Edge Cleaning

Edge cleaning is the process of removing leading zeros (before a product's first sale) and trailing zeros (after a product's discontinuation) from time series data. These zero-filled edges distort forecasting models by artificially lowering average demand levels and introducing false seasonality signals.

| Function | Description |
| --- | --- |
| ([`drop_leading_zeros`](#anofox_fcst_ts_drop_leading_zeros)) | Remove initial zero values |
| ([`drop_trailing_zeros`](#anofox_fcst_ts_drop_trailing_zeros)) | Remove trailing zero values |
| ([`drop_edge_zeros`](#anofox_fcst_ts_drop_edge_zeros)) | Remove both leading and trailing zeros |

Showing 3 of 3

---

## anofox\_fcst\_ts\_drop\_leading\_zeros

Removes initial zero values from the start of each series. Useful for products that had no sales before launch.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with leading zeros removed from each series.

### Example

```
-- Remove pre-launch zerosSELECT * FROM anofox_fcst_ts_drop_leading_zeros(    'sales_by_product',    product_id,    date,    sales);
```

**Warning:**

Results differ if gap-filling functions were previously applied. Gap-filled NULLs converted to zeros will also be removed.

---

## anofox\_fcst\_ts\_drop\_trailing\_zeros

Removes trailing zero values from the end of each series. Useful for discontinued products.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with trailing zeros removed from each series.

### Example

```
-- Remove post-discontinuation zerosSELECT * FROM anofox_fcst_ts_drop_trailing_zeros(    'sales_by_product',    product_id,    date,    sales);
```

**Warning:**

Results differ if gap-filling functions were previously applied.

---

## anofox\_fcst\_ts\_drop\_edge\_zeros

Removes both leading and trailing zero values in one operation.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with edge zeros removed from each series.

### Example

```
-- Clean both edges at onceSELECT * FROM anofox_fcst_ts_drop_edge_zeros(    'sales_by_product',    product_id,    date,    sales);
```

**Warning:**

Results differ if gap-filling functions were previously applied.

---

## When to Use Edge Cleaning

| Scenario | Function | Reason |
| --- | --- | --- |
| New product launch | `drop_leading_zeros` | Remove pre-launch history |
| Discontinued product | `drop_trailing_zeros` | Remove post-discontinuation |
| Product lifecycle | `drop_edge_zeros` | Clean both ends |
| Intermittent demand | **Don't use** | Zeros are valid data points |

---

## Edge Cleaning Workflow

```
-- 1. Analyze edge zeros before cleaningSELECT    product_id,    MIN(CASE WHEN sales > 0 THEN date END) as first_sale,    MAX(CASE WHEN sales > 0 THEN date END) as last_sale,    MIN(date) as data_start,    MAX(date) as data_endFROM sales_by_productGROUP BY product_id;-- 2. Clean edgesCREATE TABLE sales_cleaned ASSELECT * FROM anofox_fcst_ts_drop_edge_zeros(    'sales_by_product',    product_id,    date,    sales);-- 3. Verify cleaningSELECT    product_id,    MIN(date) as new_start,    MAX(date) as new_end,    COUNT(*) as remaining_rowsFROM sales_cleanedGROUP BY product_id;
```

---

## Order of Operations

The recommended order for data preparation:

```
-- 1. Edge cleaning FIRST (before gap filling)CREATE TABLE step1 ASSELECT * FROM anofox_fcst_ts_drop_edge_zeros(    'raw_data', product_id, date, sales);-- 2. Gap filling SECONDCREATE TABLE step2 ASSELECT * FROM anofox_fcst_ts_fill_gaps(    'step1', product_id, date, sales, '1d');-- 3. Imputation THIRDCREATE TABLE step3 ASSELECT * FROM anofox_fcst_ts_fill_nulls_forward(    'step2', product_id, date, sales);-- 4. Filtering LASTCREATE TABLE ready ASSELECT * FROM anofox_fcst_ts_drop_short(    'step3', product_id, 30);
```

---

## Frequently Asked Questions

Should I clean edges before or after gap filling?

Always clean edges **before** gap filling. If you gap-fill first, the gap-filling process may convert NULLs to zeros at the edges, which then get removed by edge cleaning -- producing unexpected results. The recommended order is: (1) edge cleaning, (2) gap filling, (3) imputation, (4) filtering. See the ([Order of Operations](#order-of-operations)) section above.

Will edge cleaning remove legitimate zero-demand periods in the middle of my series?

No. Edge cleaning only removes zeros at the **start** (leading) and **end** (trailing) of each series. Zeros in the middle of the series are never touched. For data with genuinely intermittent demand throughout, use [intermittent demand models](/docs/forecast/models/intermittent.md) instead of removing internal zeros.

How do I know if my data has edge zero problems?

Compare each series' first non-zero observation date with the data start date and the last non-zero observation date with the data end date. If there is a large gap at either end, edge cleaning will help. The ([Edge Cleaning Workflow](#edge-cleaning-workflow)) section above shows a diagnostic query that identifies these gaps per product.

([🍪 Cookie Settings](#cookie-settings))

# Series Filtering

Remove series that don't meet quality criteria for forecasting.

| Function | Description |
| --- | --- |
| ([`drop_constant`](#anofox_fcst_ts_drop_constant)) | Remove series with no variation |
| ([`drop_short`](#anofox_fcst_ts_drop_short)) | Remove series below minimum length |
| ([`drop_gappy`](#anofox_fcst_ts_drop_gappy)) | Remove series exceeding max gap ratio |
| ([`drop_zeros`](#anofox_fcst_ts_drop_zeros)) | Remove all-zero series |

Showing 4 of 4

---

## anofox\_fcst\_ts\_drop\_constant

Removes series exhibiting no variation (constant values). Constant series cannot be forecasted meaningfully.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | Yes | Group column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns filtered table excluding constant series.

### Example

```
-- Remove products with no sales variationSELECT * FROM anofox_fcst_ts_drop_constant(    'sales_by_product',    product_id,    sales);
```

**Warning:**

May drop intermittent demand series with many zeros if gaps haven't been filled first. Fill gaps before filtering to preserve sparse series.

---

## anofox\_fcst\_ts\_drop\_short

Filters out series shorter than a minimum length threshold. Short series don't have enough history for reliable forecasting.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | Yes | Group column |
| `min_length` | INTEGER | Yes | Minimum number of observations |

### Output

Returns filtered table excluding short series.

### Example

```
-- Keep only products with at least 30 observationsSELECT * FROM anofox_fcst_ts_drop_short(    'sales_by_product',    product_id,    30);
```

### Minimum Length Guidelines

| Model Type | Recommended Minimum |
| --- | --- |
| Naive / SMA | 10 observations |
| Exponential Smoothing | 20 observations |
| ARIMA | 30 observations |
| Seasonal models | 2+ full seasons |
| TBATS / MSTL | 3+ full seasons |

**Warning:**

May drop intermittent demand series if gaps haven't been pre-filled. Fill gaps before filtering.

---

## anofox\_fcst\_ts\_drop\_gappy

Removes series where the proportion of missing values exceeds a threshold. Useful for filtering out series with too many gaps for reliable forecasting.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | Yes | Group column |
| `value_col` | DOUBLE | Yes | Value column |
| `max_gap_ratio` | DOUBLE | Yes | Maximum gap ratio (0-1). Series with more gaps are removed |

### Output

Returns filtered table excluding gappy series.

### Example

```
-- Remove series with more than 20% missing valuesSELECT * FROM anofox_fcst_ts_drop_gappy(    'sales_by_product',    product_id,    sales,    0.2);
```

---

## anofox\_fcst\_ts\_drop\_zeros

Removes series that contain only zero values. All-zero series provide no signal for forecasting.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | Yes | Group column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns filtered table excluding all-zero series.

### Example

```
-- Remove products with zero sales across all periodsSELECT * FROM anofox_fcst_ts_drop_zeros(    'sales_by_product',    product_id,    sales);
```

---

## Filtering Workflow

```
-- 1. Fill gaps first (important!)CREATE TABLE sales_filled ASSELECT * FROM anofox_fcst_ts_fill_gaps(    'sales_by_product',    product_id,    date,    sales,    '1d');-- 2. Remove constant seriesCREATE TABLE sales_variable ASSELECT * FROM anofox_fcst_ts_drop_constant(    'sales_filled',    product_id,    sales);-- 3. Remove short seriesCREATE TABLE sales_ready ASSELECT * FROM anofox_fcst_ts_drop_short(    'sales_variable',    product_id,    30);-- 4. Check how many series remainSELECT COUNT(DISTINCT product_id) as remaining_productsFROM sales_ready;
```

---

## Pre-Filtering Analysis

Before filtering, understand what you'll be removing:

```
-- Check series lengthsSELECT    product_id,    COUNT(*) as length,    CASE        WHEN COUNT(*) < 30 THEN 'Too short'        ELSE 'OK'    END as statusFROM sales_by_productGROUP BY product_idORDER BY length;-- Check for constant seriesSELECT    product_id,    STDDEV(sales) as std_dev,    CASE        WHEN STDDEV(sales) = 0 THEN 'Constant'        ELSE 'Variable'    END as statusFROM sales_by_productGROUP BY product_id;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Gap Filling

Fill missing timestamps in your time series data.

| Function | Description |
| --- | --- |
| ([`fill_gaps_by`](#anofox_fcst_ts_fill_gaps_by)) | Fill missing timestamps with NULL values |
| ([`fill_forward_by`](#anofox_fcst_ts_fill_forward_by)) | Extend series to target date |

Showing 2 of 2

---

## anofox\_fcst\_ts\_fill\_gaps\_by

Populates missing timestamps in time series using a specified frequency interval.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column (NULL for single series) |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |
| `frequency` | VARCHAR/INTEGER | Yes | Time frequency |

**Frequency Values:**

| Type | Examples | Description |
| --- | --- | --- |
| Minutes | `"30m"` | 30-minute intervals |
| Hours | `"1h"` | Hourly intervals |
| Days | `"1d"` | Daily intervals |
| Weeks | `"1w"` | Weekly intervals |
| Months | `"1mo"` | Monthly intervals |
| Quarters | `"1q"` | Quarterly intervals |
| Years | `"1y"` | Yearly intervals |
| Integer | `1`, `2`, `3` | For integer-indexed series |

### Output

Returns table with filled timestamps. Missing values are set to NULL.

### Example

```
-- Fill daily gaps in sales dataSELECT * FROM ts_fill_gaps_by(    'sales_data',    NULL,      -- no grouping    date,    sales,    '1d'       -- daily frequency);-- Fill gaps for multiple productsSELECT * FROM ts_fill_gaps_by(    'sales_by_product',    product_id,    date,    sales,    '1d');
```

---

## anofox\_fcst\_ts\_fill\_forward\_by

Extends series forward to a target date, populating gaps with NULL values.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |
| `target_date` | DATE/TIMESTAMP/INTEGER | Yes | Target date to extend to |
| `frequency` | VARCHAR/INTEGER | Yes | Time frequency |

### Output

Returns extended table with NULL-filled gaps up to target date.

### Example

```
-- Extend series to end of yearSELECT * FROM ts_fill_forward_by(    'sales_data',    NULL,    date,    sales,    '2024-12-31'::DATE,    '1d');-- Extend multiple products to same targetSELECT * FROM ts_fill_forward_by(    'sales_by_product',    product_id,    date,    sales,    '2024-12-31'::DATE,    '1d');
```

---

## Gap Filling Workflow

```
-- 1. Check for gaps in your dataSELECT    date,    LEAD(date) OVER (ORDER BY date) as next_date,    LEAD(date) OVER (ORDER BY date) - date as gap_daysFROM sales_dataWHERE LEAD(date) OVER (ORDER BY date) - date > 1;-- 2. Fill gapsCREATE TABLE sales_filled ASSELECT * FROM ts_fill_gaps_by(    'sales_data',    NULL,    date,    sales,    '1d');-- 3. Impute NULL values (see Imputation section)CREATE TABLE sales_imputed ASSELECT * FROM ts_fill_nulls_forward_by(    'sales_filled',    NULL,    date,    sales);
```

---

([🍪 Cookie Settings](#cookie-settings))

# Imputation

Fill missing values and transform data for analysis. AnoFox Forecast provides five imputation functions -- constant fill, forward fill (LOCF), backward fill (NOCB), mean fill, and differencing -- to prepare time series for accurate forecasting.

| Function | Description |
| --- | --- |
| ([`fill_nulls_const`](#anofox_fcst_ts_fill_nulls_const)) | Replace NULLs with constant value |
| ([`fill_nulls_forward`](#anofox_fcst_ts_fill_nulls_forward)) | Forward fill (LOCF) |
| ([`fill_nulls_backward`](#anofox_fcst_ts_fill_nulls_backward)) | Backward fill (NOCB) |
| ([`fill_nulls_mean`](#anofox_fcst_ts_fill_nulls_mean)) | Fill with series mean |
| ([`diff`](#anofox_fcst_ts_diff)) | Differencing transformation |

Showing 5 of 5

---

## anofox\_fcst\_ts\_fill\_nulls\_const

Replaces NULL values with a constant value.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |
| `fill_value` | DOUBLE | Yes | Constant value to fill |

### Output

Returns table with NULLs replaced by specified constant.

### Example

```
-- Fill missing sales with zeroSELECT * FROM anofox_fcst_ts_fill_nulls_const(    'sales_filled',    product_id,    date,    sales,    0.0);
```

**Use when:** Missing values represent no activity (e.g., zero sales).

---

## anofox\_fcst\_ts\_fill\_nulls\_forward

Implements forward fill using Last Observation Carried Forward (LOCF).

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with NULLs filled forward from prior values.

### Example

```
-- Forward fill missing valuesSELECT * FROM anofox_fcst_ts_fill_nulls_forward(    'sales_filled',    product_id,    date,    sales);
```

**Use when:** Missing values should inherit from previous observation (e.g., inventory levels, prices).

---

## anofox\_fcst\_ts\_fill\_nulls\_backward

Implements backward fill using Next Observation Carried Backward (NOCB).

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with NULLs filled backward from subsequent values.

### Example

```
-- Backward fill missing valuesSELECT * FROM anofox_fcst_ts_fill_nulls_backward(    'sales_filled',    product_id,    date,    sales);
```

**Use when:** Missing values at the start of series need filling, or when future values are more representative.

---

## anofox\_fcst\_ts\_fill\_nulls\_mean

Fills NULL values with the mean computed per series.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |

### Output

Returns table with NULLs replaced by series mean.

### Example

```
-- Fill with series meanSELECT * FROM anofox_fcst_ts_fill_nulls_mean(    'sales_filled',    product_id,    date,    sales);
```

**Use when:** Missing values should reflect typical behavior without trend bias.

---

## anofox\_fcst\_ts\_diff

Computes differenced values for detrending and stationarity transformation.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | Source table |
| `group_col` | ANY | No  | Group column |
| `date_col` | DATE/TIMESTAMP/INTEGER | Yes | Date column |
| `value_col` | DOUBLE | Yes | Value column |
| `order` | INTEGER | Yes | Differencing order (must be > 0) |

### Output

Returns table with original and differenced value columns.

**Formula:** `diff[t] = value[t] - value[t-order]`

### Example

```
-- First difference (remove trend)SELECT * FROM anofox_fcst_ts_diff(    'sales_data',    product_id,    date,    sales,    1);-- Second difference (remove acceleration)SELECT * FROM anofox_fcst_ts_diff(    'sales_data',    product_id,    date,    sales,    2);
```

**Use when:**

*   `order=1`: Remove linear trend, make series stationary
*   `order=2`: Remove quadratic trend, address acceleration
*   Preparing data for ARIMA models

---

## Imputation Method Selection

| Scenario | Recommended Method | Reason |
| --- | --- | --- |
| Sales/demand data | `fill_nulls_const(0)` | No sale = zero |
| Prices/rates | `fill_nulls_forward` | Price persists until changed |
| Sensor data | `fill_nulls_mean` | Neutral assumption |
| Start of series | `fill_nulls_backward` | Initialize with nearest value |
| Trending data | `diff` | Make stationary first |

---

## Complete Imputation Workflow

```
-- 1. Fill gaps (creates NULLs)CREATE TABLE step1 ASSELECT * FROM anofox_fcst_ts_fill_gaps(    'sales_by_product',    product_id,    date,    sales,    '1d');-- 2. Forward fill for most NULLsCREATE TABLE step2 ASSELECT * FROM anofox_fcst_ts_fill_nulls_forward(    'step1',    product_id,    date,    sales);-- 3. Backward fill for remaining (start of series)CREATE TABLE step3 ASSELECT * FROM anofox_fcst_ts_fill_nulls_backward(    'step2',    product_id,    date,    sales);-- 4. Verify no NULLs remainSELECT COUNT(*) as null_countFROM step3WHERE sales IS NULL;
```

---

## Combining Forward and Backward Fill

For complete coverage, combine both methods:

```
-- Forward fill, then backward fill remainderCREATE TABLE fully_imputed ASSELECT * FROM anofox_fcst_ts_fill_nulls_backward(    (SELECT * FROM anofox_fcst_ts_fill_nulls_forward(        'gap_filled_data',        product_id,        date,        sales    )),    product_id,    date,    sales);
```

---

([🍪 Cookie Settings](#cookie-settings))

# Changepoint Detection

A changepoint (also called a structural break or regime shift) is a point in time where the statistical properties of a time series -- such as its mean, variance, or trend -- change abruptly. Detecting changepoints is essential for forecasting because models trained on pre-changepoint data may not represent the current regime. AnoFox uses Bayesian Online Changepoint Detection to find these structural breaks in your time series data.

---

## ts\_detect\_changepoints\_by

Detect structural breaks in grouped time series. Also aliased as `ts_detect_changepoints`.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `source` | VARCHAR | Yes | \-  | Source table name |
| `group_col` | COLUMN | Yes | \-  | Group column (unquoted) |
| `date_col` | COLUMN | Yes | \-  | Date column (unquoted) |
| `value_col` | COLUMN | Yes | \-  | Value column (unquoted) |
| `params` | MAP | No  | MAP | Configuration |

**Params MAP Options:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `hazard_lambda` | DOUBLE | 250.0 | Hazard rate. Lower = more changepoints detected |

### Output

Returns one row per data point:

| Column | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier (name preserved) |
| `date_col` | TIMESTAMP | Date (name preserved) |
| `is_changepoint` | BOOLEAN | Detected changepoint? |
| `changepoint_probability` | DOUBLE | Probability (0-1) |

Row preservation: output rows = input rows. NULL dates, groups with < 2 points, or errors get `is_changepoint=false`, `changepoint_probability=NULL`.

### Examples

```
-- Detect changepoints across productsSELECT    product_id,    date,    is_changepoint,    changepoint_probabilityFROM ts_detect_changepoints_by(    'sales_by_product',    product_id,    date,    sales,    MAP{'hazard_lambda': '250'})WHERE is_changepoint = true;-- Count changepoints per seriesSELECT product_id, COUNT(*) FILTER (WHERE is_changepoint) AS n_changepointsFROM ts_detect_changepoints_by('sales', product_id, date, quantity, MAP{'hazard_lambda': '100'})GROUP BY product_id;
```

### Sensitivity Tuning

| `hazard_lambda` | Sensitivity | Use Case |
| --- | --- | --- |
| 50-100 | High | Find subtle changes |
| 250 (default) | Medium | General purpose |
| 500-1000 | Low | Only major regime changes |

---

## Handling Changepoints in Forecasting

When changepoints are detected, you can use them to improve forecast accuracy.

### Option 1: Filter to Recent Regime

```
-- Find last changepointWITH last_change AS (    SELECT MAX(date) as change_date    FROM ts_detect_changepoints_by('sales_data', product_id, date, sales, MAP{})    WHERE is_changepoint = true)-- Forecast using only data after last changepointCREATE TABLE recent_data ASSELECT * FROM sales_data WHERE date > (SELECT change_date FROM last_change);SELECT * FROM ts_forecast_by(    'recent_data', product_id, date, sales,    'AutoETS', 30, '1d', MAP{});
```

### Option 2: Segment and Forecast by Regime

```
-- Identify regimesCREATE TABLE regimes ASWITH changes AS (    SELECT product_id, date    FROM ts_detect_changepoints_by('sales_data', product_id, date, sales, MAP{})    WHERE is_changepoint = true)SELECT    s.*,    SUM(CASE WHEN s.date IN (SELECT date FROM changes WHERE changes.product_id = s.product_id) THEN 1 ELSE 0 END)        OVER (PARTITION BY s.product_id ORDER BY s.date) as regimeFROM sales_data s;-- Forecast each regime separatelySELECT * FROM ts_forecast_by(    'regimes', regime, date, sales,    'AutoETS', 30, '1d', MAP{});
```

---

## Complete Changepoint Analysis

```
-- Comprehensive changepoint analysisWITH changepoints AS (    SELECT        product_id,        date,        is_changepoint,        changepoint_probability    FROM ts_detect_changepoints_by(        'sales_data',        product_id,        date,        sales,        MAP{'hazard_lambda': '250'}    ))SELECT    product_id,    COUNT(*) FILTER (WHERE is_changepoint) AS n_changepoints,    MIN(date) FILTER (WHERE is_changepoint) AS first_changepoint,    MAX(date) FILTER (WHERE is_changepoint) AS last_changepoint,    ROUND(AVG(changepoint_probability) FILTER (WHERE is_changepoint), 3) AS avg_probabilityFROM changepointsGROUP BY product_id;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Time Series Decomposition

Time series decomposition is the process of breaking a time series into its constituent components: **trend** (long-term direction), **seasonal** (repeating cyclical patterns), and **residual** (random fluctuations remaining after trend and seasonality are removed). Understanding these components helps you choose the right forecasting model and diagnose data quality issues.

| Function | Description |
| --- | --- |
| ([`ts_mstl_decomposition_by`](#ts_mstl_decomposition_by)) | Multiple Seasonal-Trend decomposition (MSTL) |
| ([`ts_detrend_by`](#ts_detrend_by)) | Remove trend using multiple methods |
| ([`ts_detect_peaks_by`](#ts_detect_peaks_by)) | Detect local maxima with prominence |
| ([`ts_analyze_peak_timing_by`](#ts_analyze_peak_timing_by)) | Analyze peak timing variability |

Showing 4 of 4

---

## ts\_mstl\_decomposition\_by

Multiple Seasonal-Trend decomposition using Loess (MSTL). Decomposes a time series into trend, one or more seasonal components, and residual.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Source table name |
| `group_col` | IDENTIFIER | Yes | Group column for multi-series (unquoted) |
| `date_col` | IDENTIFIER | Yes | Date column (unquoted) |
| `value_col` | IDENTIFIER | Yes | Value column (unquoted) |
| `seasonal_periods` | INTEGER\[\] | Yes | Seasonal periods to decompose |
| `params` | MAP | No  | Configuration (use `MAP{}` for defaults) |

### Output

| Field | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier |
| `trend` | DOUBLE\[\] | Trend component |
| `seasonal` | DOUBLE\[\]\[\] | Seasonal components (one per period) |
| `remainder` | DOUBLE\[\] | Remainder after removing trend and seasonality |
| `periods` | INTEGER\[\] | Seasonal periods used |

### Examples

```
-- Single seasonality (weekly)SELECT * FROM ts_mstl_decomposition_by(    'sales_data',    product_id,    date,    sales,    [7],    MAP{});-- Multiple seasonalities (daily + weekly for hourly data)SELECT * FROM ts_mstl_decomposition_by(    'hourly_demand',    sensor_id,    timestamp,    demand,    [24, 168],    MAP{});-- Weekly + yearly for daily dataSELECT * FROM ts_mstl_decomposition_by(    'daily_sales',    product_id,    date,    revenue,    [7, 365],    MAP{});
```

---

## Interpretation Guide

| Component | What It Shows | Business Use |
| --- | --- | --- |
| **Trend** | Long-term direction | Growth/decline analysis |
| **Seasonal** | Repeating patterns | Capacity planning, promotions |
| **Residual** | Random fluctuations | Anomaly detection, forecast uncertainty |

**Residual Analysis:**

*   Large residuals indicate unusual events or data quality issues
*   Patterns in residuals suggest missing seasonality
*   Residual variance indicates forecast uncertainty

---

## Common Seasonal Periods

| Data Frequency | Common Periods |
| --- | --- |
| Daily | 7 (weekly), 365 (yearly) |
| Hourly | 24 (daily), 168 (weekly) |
| Weekly | 52 (yearly) |
| Monthly | 12 (yearly) |
| Quarterly | 4 (yearly) |

---

## Decomposition Workflow

```
-- 1. Detect seasonality firstSELECT id, (periods).primary_periodFROM ts_detect_periods_by('sales_data', product_id, date, sales, MAP{});-- 2. Use detected periods for decompositionSELECT * FROM ts_mstl_decomposition_by(    'sales_data',    product_id,    date,    sales,    [7, 365],    MAP{});
```

---

## ts\_detrend\_by

Remove trend from grouped time series.

```
ts_detrend_by(    source VARCHAR,    group_col IDENTIFIER,    date_col IDENTIFIER,    value_col IDENTIFIER,    method VARCHAR) → TABLE
```

**Methods:**

| Method | Description | Best For |
| --- | --- | --- |
| `'linear'` | Linear trend via least squares | Simple linear trends |
| `'quadratic'` | Quadratic polynomial | Curved trends |
| `'cubic'` | Cubic polynomial | Complex curves |
| `'auto'` | Automatic selection | Unknown trend type |

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier |
| `trend` | DOUBLE\[\] | Extracted trend component |
| `detrended` | DOUBLE\[\] | Detrended values |
| `method` | VARCHAR | Method used |
| `coefficients` | DOUBLE\[\] | Polynomial coefficients |
| `rss` | DOUBLE | Residual sum of squares |
| `n_params` | BIGINT | Number of parameters |

**Example:**

```
-- Linear detrendingSELECT * FROM ts_detrend_by('sales', product_id, date, revenue, 'linear');-- Automatic method selectionSELECT * FROM ts_detrend_by('sales', product_id, date, revenue, 'auto');
```

**Use Cases:**

*   Preparing data for stationarity tests
*   Isolating seasonal patterns
*   Removing growth effects for comparison

---

## ts\_detect\_peaks\_by

Detect peaks (local maxima) in grouped time series data with prominence calculation.

```
ts_detect_peaks_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    value_col COLUMN,    params MAP) → TABLE(id, peaks)
```

**Params MAP Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `min_distance` | VARCHAR | `'1.0'` | Minimum distance between peaks |
| `min_prominence` | VARCHAR | `'0.0'` | Minimum peak prominence threshold |
| `smooth_first` | VARCHAR | `'false'` | Smooth data before peak detection |

**Returns `peaks` STRUCT:**

| Field | Type | Description |
| --- | --- | --- |
| `peaks[]` | STRUCT\[\] | Array of `{index, time, value, prominence}` |
| `n_peaks` | INTEGER | Number of peaks detected |
| `inter_peak_distances[]` | DOUBLE\[\] | Distances between consecutive peaks |
| `mean_period` | DOUBLE | Average inter-peak distance |

**Example:**

```
-- Detect all peaksSELECT id, (peaks).n_peaks, (peaks).mean_periodFROM ts_detect_peaks_by('daily_data', series_id, date, value, MAP{});-- Detect significant peaks only (prominence > 2.0)SELECT id, (peaks).n_peaksFROM ts_detect_peaks_by('sales', product_id, date, revenue,    MAP{'min_prominence': '2.0'});
```

**Use Cases:**

*   Finding demand spikes
*   Identifying seasonal peaks
*   Anomaly detection

---

## ts\_analyze\_peak\_timing\_by

Analyze peak timing regularity across seasonal cycles.

```
ts_analyze_peak_timing_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    value_col COLUMN,    period DOUBLE,    params MAP) → TABLE(id, timing)
```

**Returns `timing` STRUCT:**

| Field | Type | Description |
| --- | --- | --- |
| `n_peaks` | INTEGER | Number of peaks analyzed |
| `peak_times[]` | DOUBLE\[\] | Peak positions |
| `variability_score` | DOUBLE | Lower = more stable |
| `is_stable` | BOOLEAN | Consistent peak timing? |

**Example:**

```
-- Analyze weekly peak timingSELECT id, (timing).is_stable, (timing).variability_scoreFROM ts_analyze_peak_timing_by('sales', product_id, date, value, 7.0, MAP{});
```

**Interpretation:**

*   `is_stable = TRUE`: Peaks occur at predictable times (good for planning)
*   `is_stable = FALSE`: Peak timing varies (need flexible capacity)

---

([🍪 Cookie Settings](#cookie-settings))

# Seasonality Detection

Seasonality refers to regular, predictable fluctuations that recur at fixed intervals -- such as weekly sales spikes on Fridays, monthly billing cycles, or annual holiday demand surges. Detecting these patterns and their exact period length is a critical prerequisite for accurate forecasting, because passing the wrong seasonal period (or none at all) to a forecasting model degrades prediction quality. AnoFox provides 12 detection methods and pattern classification tools to identify these cycles automatically.

All TypeTable Macro

| Function | Description | Type |
| --- | --- | --- |
| ([`ts_detect_periods_by`](#ts_detect_periods_by)) | Multi-method period detection (12 algorithms) | Table Macro |
| ([`ts_classify_seasonality_by`](#ts_classify_seasonality_by)) | Classify seasonality type per group | Table Macro |
| ([`ts_classify_seasonality`](#ts_classify_seasonality)) | Classify seasonality (single series) | Table Macro |

Showing 3 of 3

---

## ts\_detect\_periods\_by

Detect seasonal periods for grouped series using one of 12 algorithms.

```
ts_detect_periods_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    value_col COLUMN,    params MAP) → TABLE(id, periods)
```

**Parameters:**

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Source table name |
| `group_col` | COLUMN | Yes | Series identifier (unquoted) |
| `date_col` | COLUMN | Yes | Date/timestamp column (unquoted) |
| `value_col` | COLUMN | Yes | Value column (unquoted) |
| `params` | MAP | No  | Configuration (use `MAP{}` for defaults) |

**Params MAP Options:**

| Key | Type | Default | Description |
| --- | --- | --- | --- |
| `method` | VARCHAR | `'fft'` | Detection algorithm (see table below) |
| `max_period` | VARCHAR | `'365'` | Maximum period to search |
| `min_confidence` | VARCHAR | method-specific | Minimum confidence threshold; `'0'` to see all |

### Detection Methods

The `method` parameter selects one of 12 algorithms, each optimized for different data characteristics:

| Method | Aliases | Speed | Best For |
| --- | --- | --- | --- |
| `'fft'` | `'periodogram'` | Very Fast | Clean signals (default) |
| `'acf'` | `'autocorrelation'` | Fast | Cyclical patterns, noise-robust |
| `'autoperiod'` | `'ap'` | Fast | General purpose, robust |
| `'cfd'` | `'cfdautoperiod'` | Fast | Trending data |
| `'lombscargle'` | `'lomb_scargle'` | Medium | Irregular sampling |
| `'aic'` | `'aic_comparison'` | Slow | Model comparison |
| `'ssa'` | `'singular_spectrum'` | Medium | Complex patterns |
| `'stl'` | `'stl_period'` | Slow | Decomposition-based |
| `'matrix_profile'` | `'matrixprofile'` | Slow | Pattern repetition |
| `'sazed'` | `'zero_padded'` | Medium | High frequency resolution |
| `'auto'` | —   | Medium | Unknown characteristics |
| `'multi'` | `'multiple'` | Medium | Multiple seasonalities |

### Returns

Returns a `periods` STRUCT per group:

| Field | Type | Description |
| --- | --- | --- |
| `periods[]` | STRUCT\[\] | Array of `{period, confidence, strength, amplitude, phase, iteration}` |
| `n_periods` | BIGINT | Number of detected periods |
| `primary_period` | DOUBLE | Dominant period |
| `method` | VARCHAR | Method used |

### Confidence Interpretation

| Method | Confidence Meaning | Good Threshold |
| --- | --- | --- |
| FFT | Peak-to-mean power ratio | \> 5.0 |
| ACF | Autocorrelation at lag | \> 0.3 |

Default thresholds filter low-confidence periods automatically. Set `min_confidence='0'` to see all candidates.

### Examples

```
-- Detect periods using default FFT methodSELECT id, (periods).primary_period, (periods).n_periodsFROM ts_detect_periods_by('sales', product_id, date, value, MAP{});-- Use ACF method with limited search rangeSELECT * FROM ts_detect_periods_by('sales', product_id, date, value,    MAP{'method': 'acf', 'max_period': '28'});-- Detect multiple seasonalitiesSELECT * FROM ts_detect_periods_by('hourly_data', sensor_id, timestamp, reading,    MAP{'method': 'multi'});-- Use auto method for unknown dataSELECT id, (periods).primary_periodFROM ts_detect_periods_by('new_data', series_id, date, value,    MAP{'method': 'auto'});
```

### Workflow: Detect → Forecast

```
-- Step 1: Detect seasonal periodSELECT id, (periods).primary_periodFROM ts_detect_periods_by('sales', product_id, date, value, MAP{});-- Returns e.g. primary_period = 7 (weekly)-- Step 2: Use detected period in forecastSELECT * FROM ts_forecast_by(    'sales', product_id, date, value,    'AutoETS', 14, '1d', MAP{'seasonal_period': '7'});
```

---

## ts\_classify\_seasonality\_by

Classify the type of seasonal pattern per group, including timing stability and amplitude modulation.

```
ts_classify_seasonality_by(    source VARCHAR,    group_col COLUMN,    date_col COLUMN,    value_col COLUMN,    period DOUBLE) → TABLE
```

**Parameters:**

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `source` | VARCHAR | Yes | Source table name |
| `group_col` | COLUMN | Yes | Series identifier (unquoted) |
| `date_col` | COLUMN | Yes | Date/timestamp column (unquoted) |
| `value_col` | COLUMN | Yes | Value column (unquoted) |
| `period` | DOUBLE | Yes | Expected seasonal period |

**Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `group_col` | (input) | Series identifier |
| `timing_classification` | VARCHAR | `'early'`, `'on_time'`, `'late'`, `'variable'` |
| `modulation_type` | VARCHAR | `'stable'`, `'growing'`, `'shrinking'`, `'variable'` |
| `has_stable_timing` | BOOLEAN | Consistent peak timing? |
| `timing_variability` | DOUBLE | Lower = more stable |
| `seasonal_strength` | DOUBLE | 0-1 scale |
| `is_seasonal` | BOOLEAN | Significant seasonality? |
| `cycle_strengths` | DOUBLE\[\] | Strength per cycle |
| `weak_seasons` | INTEGER\[\] | Indices of weak cycles |

**Example:**

```
-- Classify weekly seasonality per productSELECT id, seasonal_strength, is_seasonalFROM ts_classify_seasonality_by('sales', product_id, date, quantity, 7.0)WHERE is_seasonal AND has_stable_timing;
```

---

## ts\_classify\_seasonality

Single-series variant (no grouping).

```
ts_classify_seasonality(    source VARCHAR,    date_col COLUMN,    value_col COLUMN,    period DOUBLE) → TABLE
```

Same return columns as `ts_classify_seasonality_by` but without the group column.

**Example:**

```
SELECT seasonal_strength, timing_classification, modulation_typeFROM ts_classify_seasonality('monthly_sales', date, revenue, 12.0);
```

---

## Interpretation Guide

| Metric | Value | Interpretation |
| --- | --- | --- |
| `seasonal_strength` | \> 0.6 | Strong seasonality, use seasonal models |
| `seasonal_strength` | 0.3 - 0.6 | Moderate seasonality |
| `seasonal_strength` | < 0.3 | Weak or no seasonality |
| `timing_classification` | on\_time | Consistent peak timing |
| `timing_classification` | variable | Peak timing varies |
| `modulation_type` | stable | Seasonal amplitude is consistent |
| `modulation_type` | growing | Seasonal effect is increasing |

---

## Model Selection Based on Detection

| Detection Result | Recommended Models |
| --- | --- |
| Strong stable seasonality | `AutoETS`, `HoltWinters`, `SeasonalES` |
| Multiple periods detected | `AutoTBATS`, `AutoMSTL`, `MFLES` |
| Variable seasonality | `DynamicTheta`, `DynamicOptimizedTheta` |
| No seasonality detected | `Naive`, `SES`, `Holt` |
| Trending + seasonal | `HoltWinters`, `AutoARIMA` |

([🍪 Cookie Settings](#cookie-settings))

# ARIMA Models

AnoFox implements 3 ARIMA variants: classic ARIMA with manual (p,d,q) specification, AutoARIMA with automatic order selection up to p=5 and q=5, and ARIMAX for incorporating exogenous variables like temperature, promotions, or economic indicators. Seasonal ARIMA extends these with (P,D,Q) seasonal components. AutoARIMA is the recommended choice for production pipelines -- it tests candidate models using information criteria and handles both seasonal and non-seasonal patterns.

ARIMA (AutoRegressive Integrated Moving Average) is a time-series forecasting model that combines three components: autoregression (AR), which models the relationship between an observation and a number of lagged observations; differencing (I), which makes the series stationary by subtracting consecutive values; and moving average (MA), which models the dependency between an observation and residual errors from past predictions. ARIMA models dependencies between observations.

| Model | Description |
| --- | --- |
| ([`ARIMA`](#arima)) | Classic ARIMA with manual parameter specification |
| ([`AutoARIMA`](#autoarima)) | Automatic ARIMA with (p,d,q) selection |
| ([`ARIMAX`](#arimax)) | ARIMA with exogenous variables |

Showing 3 of 3

---

## ARIMA

Classic ARIMA model with manual parameter specification.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `p` | INTEGER | Yes | AR (autoregressive) order |
| `d` | INTEGER | Yes | Differencing order |
| `q` | INTEGER | Yes | MA (moving average) order |
| `seasonal_period` | INTEGER | No  | For seasonal ARIMA |
| `P` | INTEGER | No  | Seasonal AR order |
| `D` | INTEGER | No  | Seasonal differencing |
| `Q` | INTEGER | No  | Seasonal MA order |

### Example

```
-- Non-seasonal ARIMA(1,1,1)SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'ARIMA', 28, '1d',    MAP{'p': '1', 'd': '1', 'q': '1'});-- Seasonal ARIMA(1,1,1)(1,1,1)[7]SELECT * FROM ts_forecast_by(    'weekly_data', NULL, date, value,    'ARIMA', 28, '1d',    MAP{'p': '1', 'd': '1', 'q': '1', 'P': '1', 'D': '1', 'Q': '1', 'seasonal_period': '7'});
```

**Best for:** Complex patterns when you know the appropriate order.

---

## AutoARIMA

Automatic ARIMA with (p,d,q) parameter selection using information criteria.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | \-  | Seasonal period (detect with `ts_detect_periods_by` first) |
| `max_p` | INTEGER | 5   | Maximum AR order |
| `max_d` | INTEGER | 2   | Maximum differencing order |
| `max_q` | INTEGER | 5   | Maximum MA order |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'AutoARIMA', 28, '1d',    MAP{'seasonal_period': '7'});
```

**Best for:** Complex patterns, long-term forecasts, automatic tuning.

**Seasonality Not Auto-Detected:**

Seasonality is NOT auto-detected. You must detect the period first with `ts_detect_periods_by` and pass it explicitly via `seasonal_period`.

---

## Parameter Selection Guide

| Scenario | p   | d   | q   |
| --- | --- | --- | --- |
| Stationary data | 1-2 | 0   | 1-2 |
| Trending data | 1-2 | 1   | 1-2 |
| Strong trend | 1-2 | 2   | 1-2 |
| Random walk | 0   | 1   | 0   |

## When to Use ARIMA vs AutoARIMA

| Scenario | Recommended |
| --- | --- |
| Known order from domain expertise | ARIMA |
| Exploratory analysis | AutoARIMA |
| Production pipeline | AutoARIMA |
| Performance-critical | ARIMA (faster) |
| Many time series | AutoARIMA |
| External predictors | ARIMAX |

---

## ARIMAX

ARIMA with exogenous (external) variables. Incorporates external predictors like temperature, promotions, or economic indicators.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | \-  | Seasonal period (detect with `ts_detect_periods_by` first) |
| `max_p` | INTEGER | 5   | Maximum AR order |
| `max_d` | INTEGER | 2   | Maximum differencing order |
| `max_q` | INTEGER | 5   | Maximum MA order |

### Example

```
-- Create future exogenous valuesCREATE TABLE future_exog ASSELECT * FROM (VALUES    ('2024-01-08'::DATE, 22.0, 1),    ('2024-01-09'::DATE, 20.0, 0),    ('2024-01-10'::DATE, 21.0, 1)) AS t(date, temperature, promotion);-- Forecast with exogenous variablesSELECT * FROM ts_forecast_exog_by(    'sales_data', NULL, date, sales,    ['temperature', 'promotion'],    'future_exog', date, ['temperature', 'promotion'],    'ARIMAX', 3,    MAP{}, '1d');
```

**Best for:** Complex patterns with external drivers, weather-adjusted demand, promotion planning.

ARIMAX is one of 3 exogenous-capable models in AnoFox Forecast (alongside ThetaX and MFLESX). All 3 use the `ts_forecast_exog_by` function, which requires both historical and future values for each external predictor. This makes ARIMAX particularly effective for demand planning where promotional calendars, weather forecasts, or pricing schedules are known in advance.

See [Exogenous Variables](/docs/forecast/exogenous.md) for detailed usage

---

## Frequently Asked Questions

What do the (p, d, q) parameters mean in practice?

**p** (AR order) controls how many past values influence the current prediction. **d** (differencing order) controls how many times the series is differenced to achieve stationarity -- use d=1 for trending data, d=2 for strong trends. **q** (MA order) controls how many past forecast errors influence the current prediction. For seasonal ARIMA, (P, D, Q) are the same concepts applied at the seasonal frequency.

Why does AutoARIMA not auto-detect seasonality?

Seasonal period detection and model fitting are separate concerns. AutoARIMA optimizes the (p,d,q) orders given a seasonal period, but it does not search for the period itself. Run `ts_detect_periods_by` first to find the seasonal period, then pass it to AutoARIMA via `MAP{'seasonal_period': '7'}`. This separation gives you explicit control and avoids silent misdetection.

When should I use ARIMAX instead of AutoARIMA?

Use ARIMAX when you have external predictors (temperature, promotions, pricing) that you believe influence the target variable and for which you can provide future values. ARIMAX models the relationship between these external variables and your target while also capturing the autoregressive and moving average dynamics. If you have no external data, stick with AutoARIMA.

ARIMA is slow on my large dataset. How can I speed it up?

AutoARIMA's search over candidate models is inherently slower than simpler methods. To speed things up: (1) reduce `max_p` and `max_q` from the default of 5 to 2-3 if you do not expect high-order dependencies, (2) use the Theta method for comparable accuracy with faster execution, or (3) use AutoETS which typically runs faster than AutoARIMA for seasonal data.

---

([🍪 Cookie Settings](#cookie-settings))

# Baseline Models

AnoFox includes 4 baseline models -- Naive, SeasonalNaive, SMA, and RandomWalkDrift -- that serve as essential performance benchmarks. A sophisticated forecasting model that cannot outperform a simple baseline (MASE > 1.0) is adding complexity without value. These models execute near-instantly because they require no parameter estimation, making them the fastest forecasters in the AnoFox library.

Baseline models are simple, assumption-free forecasting methods that serve as performance benchmarks. No forecasting model should be deployed without first outperforming simple baselines.

| Model | Description |
| --- | --- |
| ([`Naive`](#naive)) | Repeats the last observed value |
| ([`SeasonalNaive`](#seasonalnaive)) | Repeats values from previous seasonal cycle |
| ([`SMA`](#sma-simple-moving-average)) | Simple Moving Average |
| ([`RandomWalkDrift`](#randomwalkdrift)) | Random walk with drift (trend) |

Showing 4 of 4

---

## Naive

Repeats the last observed value for all forecast horizons.

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'Naive', 14, '1d',    MAP{});
```

**Best for:** Establishing baseline, random walk data.

---

## SeasonalNaive

Repeats values from the same period in the previous seasonal cycle.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | Yes | Seasonal period |

### Example

```
-- Weekly seasonality: forecast = same day last weekSELECT * FROM ts_forecast_by(    'weekly_sales', NULL, date, sales,    'SeasonalNaive', 28, '1d',    MAP{'seasonal_period': '7'});
```

**Best for:** Strong seasonal patterns, limited data.

---

## SMA (Simple Moving Average)

Average of the last N observations.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `window` | INTEGER | 5   | Number of observations to average |

### Example

```
SELECT * FROM ts_forecast_by(    'noisy_data', NULL, date, value,    'SMA', 14, '1d',    MAP{'window': '7'});
```

**Best for:** Smoothing noise, simple baseline.

---

## RandomWalkDrift

Random walk with drift (trend). Adds average historical change to the last value.

### Example

```
SELECT * FROM ts_forecast_by(    'trending_data', NULL, date, value,    'RandomWalkDrift', 28, '1d',    MAP{});
```

**Best for:** Trending data baseline, financial data.

---

## Comparison

| Model | Handles Trend | Handles Seasonality | Speed |
| --- | --- | --- | --- |
| Naive | No  | No  | Fastest |
| SeasonalNaive | No  | Yes | Fast |
| SMA | Smoothed | No  | Fast |
| RandomWalkDrift | Yes | No  | Fast |

## When to Use Baseline Models

| Scenario | Recommended |
| --- | --- |
| Establish performance baseline | Naive |
| Strong weekly/yearly patterns | SeasonalNaive |
| Noisy stationary data | SMA |
| Trending random walk | RandomWalkDrift |
| Very limited data | SeasonalNaive |
| Real-time quick forecast | Naive |

## Using Baselines for Comparison

Always compare your sophisticated model against baselines:

```
-- Compare AutoETS against SeasonalNaiveCREATE TABLE baseline ASSELECT * FROM ts_forecast_by(    'sales', NULL, date, value,    'SeasonalNaive', 28, '1d',    MAP{'seasonal_period': '7'});CREATE TABLE model_forecast ASSELECT * FROM ts_forecast_by(    'sales', NULL, date, value,    'AutoETS', 28, '1d',    MAP{});-- Calculate MASE (< 1 means model beats baseline)SELECT ts_mase(    LIST(actual ORDER BY date),    LIST(model_pred ORDER BY date),    LIST(baseline_pred ORDER BY date)) AS maseFROM comparison_data;
```

If MASE < 1, your model beats the baseline.

---

## Frequently Asked Questions

Why should I always compare against a baseline model?

A baseline establishes the minimum accuracy bar. If a sophisticated model like AutoARIMA or TBATS cannot beat SeasonalNaive, it means the model is adding complexity without improving predictions. MASE (Mean Absolute Scaled Error) directly measures this: MASE < 1 means the model outperforms the baseline, MASE > 1 means it does not.

Which baseline should I use: Naive or SeasonalNaive?

Use **Naive** for data without clear seasonal patterns (random walk, financial data). Use **SeasonalNaive** for data with repeating patterns (weekly sales cycles, yearly seasonality). SeasonalNaive is the stronger baseline for most business time series because it captures the most obvious pattern -- last week's same day.

Can baseline models produce prediction intervals?

Yes. All AnoFox forecast models, including baselines, return `yhat_lower` and `yhat_upper` columns with prediction intervals. For baselines, these intervals are derived from historical residual variance. For tighter, distribution-free intervals, apply [conformal prediction](/docs/forecast/conformal-prediction.md) on top of baseline forecasts.

---

([🍪 Cookie Settings](#cookie-settings))

# Exponential Smoothing

AnoFox provides 8 exponential smoothing models ranging from Simple Exponential Smoothing (SES) with a single parameter to AutoETS with automatic component selection across all error, trend, and seasonal combinations. AutoETS is the recommended default for most forecasting tasks -- it selects the optimal ETS configuration using information criteria and produces prediction intervals without manual tuning.

Exponential smoothing is a family of forecasting methods that assign exponentially decreasing weights to older observations, so recent data points have more influence on the forecast than distant ones. The state space formulation classifies models by their Error (A/M), Trend (N/A/M), and Seasonal (N/A/M) components.

| Model | Description |
| --- | --- |
| ([`ETS`](#ets-error-trend-seasonality)) | Error-Trend-Seasonality state space model |
| ([`AutoETS`](#autoets)) | Automatic ETS with component selection |
| ([`SES`](#ses-simple-exponential-smoothing)) | Simple Exponential Smoothing |
| ([`SESOptimized`](#sesoptimized)) | SES with optimized smoothing parameter |
| ([`Holt`](#holt-linear-trend)) | Linear trend method |
| ([`HoltWinters`](#holtwinters)) | Trend + seasonality |
| ([`SeasonalES`](#seasonales)) | Seasonal exponential smoothing |
| ([`SeasonalESOptimized`](#seasonalesoptimized)) | SeasonalES with optimized parameters |

Showing 8 of 8

---

## ETS (Error-Trend-Seasonality)

State space model with explicit error, trend, and seasonal components.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `error` | VARCHAR | 'A' | Error type: A (Additive), M (Multiplicative) |
| `trend` | VARCHAR | 'A' | Trend type: A, M, N (None) |
| `seasonal` | VARCHAR | 'A' | Seasonal type: A, M, N |
| `seasonal_period` | INTEGER | \-  | Seasonal period (required if seasonal != 'N') |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'ETS', 28, '1d',    MAP{'trend': 'A', 'seasonal': 'A', 'seasonal_period': '7'});
```

**Best for:** Trending + seasonal data, smooth patterns.

---

## AutoETS

Automatic ETS with component selection using information criteria.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | \-  | Seasonal period (detect with `ts_detect_periods_by` first) |
| `confidence_level` | DOUBLE | 0.90 | Prediction interval width |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'AutoETS', 28, '1d',    MAP{'seasonal_period': '7', 'confidence_level': '0.95'});
```

**Best for:** Most common use case, default choice for trend + seasonality.

---

## SES (Simple Exponential Smoothing)

Weighted average of past observations with exponentially decreasing weights.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `alpha` | DOUBLE | auto | Smoothing parameter (0-1) |

### Example

```
SELECT * FROM ts_forecast_by(    'stable_data', NULL, date, value,    'SES', 14, '1d',    MAP{});
```

**Best for:** No trend, no seasonality, stationary data.

---

## SESOptimized

SES with automatically optimized smoothing parameter.

### Example

```
SELECT * FROM ts_forecast_by(    'stable_data', NULL, date, value,    'SESOptimized', 14, '1d',    MAP{});
```

**Best for:** Stationary data with automatic parameter tuning.

---

## Holt (Linear Trend)

Extends SES to capture linear trends.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `alpha` | DOUBLE | auto | Level smoothing |
| `beta` | DOUBLE | auto | Trend smoothing |

### Example

```
SELECT * FROM ts_forecast_by(    'trending_data', NULL, date, value,    'Holt', 28, '1d',    MAP{});
```

**Best for:** Linear trending data without seasonality.

---

## HoltWinters

Extends Holt to include seasonality (additive or multiplicative).

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal` | VARCHAR | 'additive' | 'additive' or 'multiplicative' |
| `seasonal_period` | INTEGER | \-  | Seasonal period (required) |

### Example

```
SELECT * FROM ts_forecast_by(    'retail_sales', NULL, date, sales,    'HoltWinters', 52, '1d',    MAP{'seasonal': 'multiplicative', 'seasonal_period': '7'});
```

**Best for:** Clear trend + seasonality patterns.

---

## SeasonalES

Seasonal exponential smoothing without trend component.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | \-  | Seasonal period (required) |

### Example

```
SELECT * FROM ts_forecast_by(    'seasonal_data', NULL, date, value,    'SeasonalES', 28, '1d',    MAP{'seasonal_period': '7'});
```

**Best for:** Seasonal patterns without trend.

---

## SeasonalESOptimized

SeasonalES with optimized parameters.

### Example

```
SELECT * FROM ts_forecast_by(    'seasonal_data', NULL, date, value,    'SeasonalESOptimized', 28, '1d',    MAP{'seasonal_period': '7'});
```

**Best for:** Seasonal patterns with automatic parameter tuning.

---

## Comparison

| Model | Trend | Seasonality | Parameters |
| --- | --- | --- | --- |
| SES | No  | No  | alpha |
| SESOptimized | No  | No  | auto |
| Holt | Yes | No  | alpha, beta |
| HoltWinters | Yes | Yes | alpha, beta, gamma |
| SeasonalES | No  | Yes | alpha, gamma |
| SeasonalESOptimized | No  | Yes | auto |
| ETS | Configurable | Configurable | Full state space |
| AutoETS | Auto | Auto | Automatic selection |

## When to Use AutoETS

AutoETS is recommended as the **default choice** for most forecasting tasks:

*   Automatically selects error, trend, and seasonal components
*   Handles both additive and multiplicative patterns
*   Produces prediction intervals
*   Fast and reliable

The 8 exponential smoothing models form a hierarchy of increasing complexity: SES handles stationary data with 1 parameter, Holt adds trend with 2 parameters, HoltWinters adds seasonality with 3 parameters, and ETS provides the full configurable state space. AutoETS automates the selection process, testing all valid component combinations and choosing the best fit via information criteria. For production pipelines processing thousands of series, AutoETS eliminates manual model selection entirely.

---

## Frequently Asked Questions

What is the difference between additive and multiplicative seasonality?

**Additive** seasonality means the seasonal effect is a constant amount added to the trend (e.g., sales always increase by 500 units in December). **Multiplicative** seasonality means the seasonal effect scales proportionally with the level (e.g., sales increase by 20% in December). Use multiplicative when the seasonal amplitude grows as the series level increases. AutoETS selects the appropriate type automatically.

Why would I use ETS with manual components instead of AutoETS?

Use manual ETS when you have domain knowledge about your data's structure. For example, if you know your demand has multiplicative seasonality but no trend, specifying `ETS(A,N,M)` directly avoids searching over unnecessary configurations. This can also be faster for large-scale batch forecasting where the model structure is already known.

How does AutoETS select the best model?

AutoETS fits all valid combinations of Error (A/M), Trend (N/A/M), and Seasonal (N/A/M) components and selects the model with the lowest AIC (Akaike Information Criterion). This balances goodness-of-fit against model complexity, automatically guarding against overfitting. The search typically evaluates 15-30 candidate models depending on the data characteristics.

Can SES or Holt handle seasonal data?

No. SES and Holt do not model seasonality. SES handles only stationary (level-only) data, and Holt handles level + trend. For seasonal data, use HoltWinters (trend + seasonality), SeasonalES (seasonality without trend), or AutoETS (automatic selection). The ([comparison table](#comparison)) above shows which component each model supports.

---

([🍪 Cookie Settings](#cookie-settings))

# Intermittent Demand Models

AnoFox provides 6 intermittent demand models: 3 Croston variants (Classic, Optimized, SBA with bias correction), ADIDA for very sparse demand, IMAPA for complex multi-temporal intermittency, and TSB for trending sparse patterns. These models are designed for data with high zero percentages -- spare parts inventory, low-volume SKUs, and irregular service demand -- where standard ETS or ARIMA models produce unreliable forecasts. CrostonSBA is the recommended default for most intermittent demand scenarios due to its bias correction.

Intermittent demand refers to demand patterns characterized by frequent zero-demand periods interspersed with irregular, often variable-sized orders. These specialized models handle sparse, lumpy demand patterns that standard forecasting methods like ETS or ARIMA struggle with.

| Model | Description |
| --- | --- |
| ([`CrostonClassic`](#crostonclassic)) | Classic Croston method for sparse demand |
| ([`CrostonOptimized`](#crostonoptimized)) | Croston with optimized smoothing parameters |
| ([`CrostonSBA`](#crostonsba)) | Syntetos-Boylan Approximation (bias-corrected) |
| ([`ADIDA`](#adida)) | Aggregate-Disaggregate Intermittent Demand |
| ([`IMAPA`](#imapa)) | Intermittent Multiple Aggregation Prediction |
| ([`TSB`](#tsb)) | Teunter-Syntetos-Babai (trending intermittent) |

Showing 6 of 6

---

## CrostonClassic

The Croston method is a forecasting approach designed for intermittent demand that decomposes the problem into two components: the size of non-zero demands and the interval between consecutive non-zero demands. Each component is forecast separately using exponential smoothing, and the final demand rate is their ratio. This classic variant separately forecasts demand size and inter-arrival times.

### Example

```
SELECT * FROM ts_forecast_by(    'spare_parts', NULL, date, demand,    'CrostonClassic', 30, '1d',    MAP{});
```

**Best for:** Sparse demand, many zeros, spare parts inventory.

---

## CrostonOptimized

Croston method with optimized smoothing parameters.

### Example

```
SELECT * FROM ts_forecast_by(    'spare_parts', NULL, date, demand,    'CrostonOptimized', 30, '1d',    MAP{});
```

**Best for:** Better variance estimation than classic Croston.

---

## CrostonSBA

Syntetos-Boylan Approximation (SBA) - a bias-corrected variant of Croston's method.

### Example

```
SELECT * FROM ts_forecast_by(    'low_frequency', NULL, date, demand,    'CrostonSBA', 30, '1d',    MAP{});
```

**Best for:** Reduced bias, low-frequency demand.

---

## ADIDA

Aggregate-Disaggregate Intermittent Demand Approach.

### Example

```
SELECT * FROM ts_forecast_by(    'very_sparse', NULL, date, demand,    'ADIDA', 30, '1d',    MAP{});
```

**Best for:** Very sparse demand, complex intermittent patterns.

---

## IMAPA

Intermittent Multiple Aggregation Prediction Algorithm.

### Example

```
SELECT * FROM ts_forecast_by(    'complex_intermittent', NULL, date, demand,    'IMAPA', 30, '1d',    MAP{});
```

**Best for:** Very complex intermittency, multiple temporal patterns.

---

## TSB

Teunter-Syntetos-Babai (TSB) method - handles trending intermittent demand.

### Example

```
SELECT * FROM ts_forecast_by(    'trending_sparse', NULL, date, demand,    'TSB', 30, '1d',    MAP{});
```

**Best for:** Intermittent + trending, growing/declining sparse demand.

---

## Comparison

| Model | Trend Support | Bias Correction | Complexity |
| --- | --- | --- | --- |
| CrostonClassic | No  | No  | Simple |
| CrostonOptimized | No  | No  | Simple |
| CrostonSBA | No  | Yes | Simple |
| ADIDA | No  | Moderate | Medium |
| IMAPA | No  | Yes | Complex |
| TSB | Yes | Yes | Medium |

## Demand Classification

Before choosing a model, classify your demand pattern:

| Pattern | Zero % | Variation | Recommended |
| --- | --- | --- | --- |
| Smooth | Low | Low | AutoETS |
| Erratic | Low | High | MFLES |
| Intermittent | High | Low | CrostonSBA |
| Lumpy | High | High | TSB or ADIDA |

## When to Use Intermittent Models

| Scenario | Recommended Model |
| --- | --- |
| Spare parts inventory | CrostonClassic |
| Low-frequency demand | CrostonSBA |
| Very sparse (>80% zeros) | ADIDA |
| Complex intermittency | IMAPA |
| Growing/declining sparse | TSB |
| Quick baseline | CrostonClassic |

The demand classification approach is critical for intermittent series: use the zero percentage as a primary selector. Below 30% zeros, standard models like AutoETS or MFLES perform well. Between 30-70% zeros, CrostonClassic or CrostonSBA are appropriate. Above 70% zeros, ADIDA or IMAPA handle the extreme sparsity. TSB is the only intermittent model that handles trending demand, making it essential for products with growing or declining order patterns.

## Example: Spare Parts Forecasting

```
-- Classify demand firstWITH classified AS (    SELECT        sku_id,        COUNT(*) as n_obs,        SUM(CASE WHEN demand = 0 THEN 1 ELSE 0 END)::DOUBLE / COUNT(*) as zero_pct    FROM spare_parts_demand    GROUP BY sku_id)-- Use appropriate model based on zero percentageSELECT    sku_id,    CASE        WHEN zero_pct > 0.7 THEN 'CrostonSBA'        WHEN zero_pct > 0.3 THEN 'CrostonClassic'        ELSE 'AutoETS'    END as recommended_modelFROM classified;
```

---

## Frequently Asked Questions

How do I know if my demand is "intermittent" enough for these models?

If more than 30% of your observations are zero, consider intermittent demand models. Classify your demand using the ADI (Average Demand Interval) and CV (Coefficient of Variation) metrics. High ADI with low CV indicates intermittent demand (use CrostonSBA). High ADI with high CV indicates lumpy demand (use TSB or ADIDA). The ([Demand Classification table](#demand-classification)) above provides thresholds.

Why does Croston tend to overestimate demand?

Classic Croston has a known positive bias because it estimates demand size and interval independently, then divides them. The ratio of two separately smoothed estimates introduces systematic upward bias. CrostonSBA (Syntetos-Boylan Approximation) applies a correction factor to remove this bias, making it the preferred default for most intermittent demand scenarios.

Can I use these models with grouped data (e.g., per SKU)?

Yes. All intermittent models work with `ts_forecast_by` which supports the `group_col` parameter. Pass your SKU or product column as the group column and the function fits a separate model for each group automatically. Use `NULL` for the group column if you have a single series.

When should I use TSB instead of Croston variants?

Use TSB (Teunter-Syntetos-Babai) when your intermittent demand has a trend -- either growing or declining over time. Croston and its variants assume a stationary demand rate, so they cannot capture systematic changes. TSB explicitly models the demand probability as time-varying, making it the only intermittent model in this set that handles trending sparse demand.

([🍪 Cookie Settings](#cookie-settings))

# Multi-Seasonal Models

AnoFox provides 7 multi-seasonal models across 3 algorithm families: TBATS (trigonometric seasonality with Box-Cox and ARMA errors), MSTL (Loess-based decomposition), and MFLES (median-based, outlier-robust). These models handle data with overlapping seasonal cycles -- for example, hourly data with daily (24), weekly (168), and yearly (8,760) periods simultaneously. MFLES is particularly robust to noisy real-world data, while MFLESX adds exogenous variable support for external predictors.

Multi-seasonal models are forecasting methods that can capture two or more overlapping seasonal cycles simultaneously, such as hourly data with both daily and weekly patterns.

| Model | Description |
| --- | --- |
| ([`TBATS`](#tbats)) | Trigonometric seasonality, Box-Cox, ARMA errors |
| ([`AutoTBATS`](#autotbats)) | Automatic TBATS with parameter selection |
| ([`MSTL`](#mstl)) | Multiple Seasonal-Trend decomposition using Loess |
| ([`AutoMSTL`](#automstl)) | Automatic MSTL with period detection |
| ([`MFLES`](#mfles)) | Median-based Feature-Logic Expert System |
| ([`AutoMFLES`](#automfles)) | Automatic MFLES configuration |
| ([`MFLESX`](#mflesx)) | MFLES with exogenous variables |

Showing 7 of 7

---

## TBATS

Trigonometric seasonality, Box-Cox transformation, ARMA errors, Trend, and Seasonal components. TBATS is designed specifically for handling complex multiple seasonalities.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `seasonal_periods` | INTEGER\[\] | Yes | Multiple seasonal periods (e.g., \[24, 168\] for hourly data) |

### Example

```
-- Hourly data with daily (24) and weekly (168) seasonalitySELECT * FROM ts_forecast_by(    'hourly_energy', NULL, timestamp, consumption,    'TBATS', 336, '1h',    MAP{'seasonal_periods': '[24, 168]'});
```

**Best for:** Multiple seasonal patterns, high-frequency data.

---

## AutoTBATS

Automatic TBATS with parameter selection.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_periods` | INTEGER\[\] | auto | Multiple seasonal periods |

### Example

```
SELECT * FROM ts_forecast_by(    'complex_data', NULL, timestamp, value,    'AutoTBATS', 336, '1h',    MAP{});
```

**Best for:** Multiple seasonality with automatic configuration.

---

## MSTL

Multiple Seasonal-Trend decomposition using Loess, extending the STL method to handle multiple seasonal periods. Decomposes series into trend + multiple seasonal components + remainder.

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `seasonal_periods` | INTEGER\[\] | Yes | Multiple seasonal periods |

### Example

```
-- Daily data with weekly (7) and yearly (365) seasonalitySELECT * FROM ts_forecast_by(    'daily_sales', NULL, date, sales,    'MSTL', 90, '1d',    MAP{'seasonal_periods': '[7, 365]'});
```

**Best for:** Very complex seasonality, decomposition-based forecasting.

---

## AutoMSTL

Automatic MSTL with period detection.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_periods` | INTEGER\[\] | auto | Multiple seasonal periods |

### Example

```
SELECT * FROM ts_forecast_by(    'hourly_data', NULL, timestamp, value,    'AutoMSTL', 168, '1h',    MAP{});
```

**Best for:** Complex seasonality with automatic period detection.

---

## MFLES

Median-based Feature-Logic Expert System. Robust to outliers and noise.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Primary seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'noisy_retail', NULL, date, sales,    'MFLES', 28, '1d',    MAP{'seasonal_period': '7'});
```

**Best for:** Noisy data, outliers, real-world messy time series.

---

## AutoMFLES

Automatic MFLES configuration.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'noisy_data', NULL, date, value,    'AutoMFLES', 28, '1d',    MAP{});
```

**Best for:** Noisy data with automatic robust forecasting.

---

## Comparison

| Model | Outlier Robust | Speed | Seasonality Handling |
| --- | --- | --- | --- |
| TBATS | No  | Slow | Trigonometric |
| AutoTBATS | No  | Slow | Auto trigonometric |
| MSTL | Moderate | Slow | Loess decomposition |
| AutoMSTL | Moderate | Slow | Auto decomposition |
| MFLES | Yes | Medium | Median-based |
| AutoMFLES | Yes | Medium | Auto median-based |

## When to Use Multi-Seasonal Models

| Data Pattern | Recommended Model |
| --- | --- |
| Hourly + daily + weekly | AutoTBATS or AutoMSTL |
| Daily + weekly + yearly | AutoMSTL |
| Noisy with multiple patterns | AutoMFLES |
| High-frequency (sub-hourly) | AutoTBATS |
| Need decomposition output | MSTL |

## Common Seasonal Periods

| Frequency | Common Periods |
| --- | --- |
| Hourly | 24 (daily), 168 (weekly), 8760 (yearly) |
| Daily | 7 (weekly), 30/31 (monthly), 365 (yearly) |
| Weekly | 52 (yearly) |
| Monthly | 12 (yearly) |

---

## MFLESX

MFLES with exogenous (external) variables. Combines MFLES's robustness to outliers with external predictor support.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Primary seasonal period |

### Example

```
-- Create future exogenous valuesCREATE TABLE future_exog ASSELECT * FROM (VALUES    ('2024-01-08'::DATE, 22.0, 1),    ('2024-01-09'::DATE, 20.0, 0),    ('2024-01-10'::DATE, 21.0, 1)) AS t(date, temperature, promotion);-- Forecast noisy retail data with exogenous variablesSELECT * FROM ts_forecast_exog_by(    'noisy_retail', NULL, date, sales,    ['temperature', 'promotion'],    'future_exog', date, ['temperature', 'promotion'],    'MFLESX', 3,    MAP{}, '1d');
```

**Best for:** Noisy data with external drivers, real-world messy time series with predictors.

For multi-seasonal data, the choice between TBATS, MSTL, and MFLES depends on data characteristics. TBATS uses trigonometric representations that handle high-frequency periods (e.g., 8,760 for yearly cycles in hourly data) efficiently. MSTL decomposes the series into separate seasonal components via Loess and is effective when you need the decomposition output itself. MFLES uses median-based estimation that resists outlier contamination -- making it the best default for real-world operational data where anomalous observations are common.

See [Exogenous Variables](/docs/forecast/exogenous.md) for detailed usage

---

## Frequently Asked Questions

How do I determine which seasonal periods to use?

Run `ts_detect_periods_by` with `method='multi'` to detect multiple seasonal periods automatically. Common combinations are daily+weekly for hourly data (`[24, 168]`), weekly+yearly for daily data (`[7, 365]`), and quarterly+yearly for monthly data (`[3, 12]`). See the ([Common Seasonal Periods](#common-seasonal-periods)) table for reference values by data frequency.

TBATS is slow on my data. What are faster alternatives?

TBATS can be slow because it searches over multiple model configurations including Box-Cox transformations and ARMA error orders. Try **AutoMFLES** as a faster alternative that is also more robust to outliers. If you need decomposition output, **AutoMSTL** is another option. Both run significantly faster than TBATS while handling multiple seasonal periods.

What makes MFLES "robust to outliers"?

MFLES uses median-based estimation instead of mean-based estimation. Medians are inherently resistant to extreme values because a single outlier cannot drag the estimate as far as it would with a mean. This makes MFLES particularly well-suited for real-world operational data where anomalous spikes and drops are common.

Can I use exogenous variables with multi-seasonal models?

Yes, via **MFLESX**. This variant combines MFLES's outlier robustness and multi-seasonal handling with external predictor support. Use `ts_forecast_exog_by` and pass your external variable columns. You must provide future values for all exogenous variables covering the full forecast horizon. See the ([MFLESX section](#mflesx)) above for a complete example.

---

([🍪 Cookie Settings](#cookie-settings))

# Theta Methods

AnoFox implements 6 Theta variants -- from the classic Theta method to DynamicOptimizedTheta and ThetaX with exogenous variable support. The Theta method gained prominence by winning the M3 forecasting competition, demonstrating that a simple decomposition approach can outperform complex statistical models on diverse datasets. In AnoFox, AutoTheta is the recommended production choice, while ThetaX extends the family with external predictor support via `ts_forecast_exog_by`.

The Theta method is a decomposition-based forecasting approach that separates a time series into two "theta lines" -- one capturing long-term trend and the other capturing short-term behavior -- then extrapolates and recombines them. It is known for winning the M3 forecasting competition.

| Model | Description |
| --- | --- |
| ([`Theta`](#theta)) | Classic Theta method |
| ([`AutoTheta`](#autotheta)) | Automatic Theta with optimized parameters |
| ([`OptimizedTheta`](#optimizedtheta)) | Theta with optimized decomposition parameters |
| ([`DynamicTheta`](#dynamictheta)) | Theta with time-varying parameters |
| ([`DynamicOptimizedTheta`](#dynamicoptimizedtheta)) | Dynamic + optimized Theta |
| ([`ThetaX`](#thetax)) | Theta with exogenous variables |

Showing 6 of 6

---

## Theta

Classic Theta method that decomposes series into two theta lines and extrapolates.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'Theta', 14, '1d',    MAP{});
```

**Best for:** Short-term forecasts, simple + fast, competition-winning accuracy.

---

## AutoTheta

Automatic Theta method with optimized parameters.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'AutoTheta', 14, '1d',    MAP{});
```

**Best for:** Short-term forecasts with automatic optimization.

---

## OptimizedTheta

Theta method with optimized decomposition parameters.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'sales_data', NULL, date, sales,    'OptimizedTheta', 14, '1d',    MAP{});
```

**Best for:** Better accuracy than basic Theta through parameter optimization.

---

## DynamicTheta

Theta method with time-varying parameters.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'changing_data', NULL, date, value,    'DynamicTheta', 28, '1d',    MAP{});
```

**Best for:** Series with changing patterns over time.

---

## DynamicOptimizedTheta

Combines dynamic and optimized Theta approaches.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
SELECT * FROM ts_forecast_by(    'complex_data', NULL, date, value,    'DynamicOptimizedTheta', 28, '1d',    MAP{});
```

**Best for:** Maximum Theta accuracy with dynamic adaptation.

---

## Comparison

| Model | Speed | Optimization | Best Use Case |
| --- | --- | --- | --- |
| Theta | Fast | None | Quick baseline |
| AutoTheta | Fast | Automatic | General purpose |
| OptimizedTheta | Fast | Parameters | Better accuracy |
| DynamicTheta | Medium | Time-varying | Changing patterns |
| DynamicOptimizedTheta | Medium | Both | Maximum accuracy |

## When to Use Theta Methods

| Scenario | Recommended |
| --- | --- |
| Quick forecast needed | Theta |
| Production system | AutoTheta |
| Maximum accuracy | DynamicOptimizedTheta |
| Changing patterns | DynamicTheta |
| Limited data | Theta |
| External predictors | ThetaX |

---

## ThetaX

Theta method with exogenous (external) variables. Combines Theta's simplicity with external predictor support.

### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `seasonal_period` | INTEGER | auto | Seasonal period |

### Example

```
-- Create future exogenous valuesCREATE TABLE future_exog ASSELECT * FROM (VALUES    ('2024-01-08'::DATE, 22.0),    ('2024-01-09'::DATE, 20.0),    ('2024-01-10'::DATE, 21.0)) AS t(date, temperature);-- Forecast with exogenous variablesSELECT * FROM ts_forecast_exog_by(    'sales_data', NULL, date, sales,    ['temperature'],    'future_exog', date, ['temperature'],    'ThetaX', 3,    MAP{}, '1d');
```

**Best for:** Short-term forecasts with external drivers, simple models with predictor support.

The 5 Theta variants without exogenous support (Theta, AutoTheta, OptimizedTheta, DynamicTheta, DynamicOptimizedTheta) offer a speed-accuracy tradeoff: the basic Theta model is one of the fastest forecasters in the AnoFox library, while DynamicOptimizedTheta provides maximum accuracy at moderate computational cost. For series with limited historical data, the classic Theta method is particularly effective because it requires fewer observations to produce stable forecasts compared to ARIMA or TBATS.

See [Exogenous Variables](/docs/forecast/exogenous.md) for detailed usage

---

## Frequently Asked Questions

Why did Theta win the M3 competition if it is so simple?

The Theta method succeeds because it effectively decomposes a series into a dampened trend component and a short-term component, then recombines them. The classic Theta method is equivalent to simple exponential smoothing with drift. Its strength lies in this simplicity -- it avoids overfitting that plagues more complex models on short or noisy series.

What is the difference between OptimizedTheta and DynamicTheta?

**OptimizedTheta** optimizes the decomposition parameters (theta coefficient) to minimize forecast error but keeps parameters fixed over time. **DynamicTheta** allows parameters to vary over time to adapt to changing patterns. **DynamicOptimizedTheta** combines both approaches for maximum accuracy at moderate computational cost.

How much data does Theta need compared to ARIMA?

Theta requires less historical data than ARIMA. The basic Theta method can produce stable forecasts with as few as 10-15 observations, whereas ARIMA typically needs at least 30-50 (more for seasonal variants). This makes Theta an excellent choice for new products or short time series.

---

([🍪 Cookie Settings](#cookie-settings))

# Diagnostics

AnoFox provides 6 diagnostic functions for model validation: VIF in both scalar and aggregate forms for multicollinearity detection (VIF > 10 indicates severe collinearity), comprehensive residual diagnostics returning 7 output fields (raw residuals, standardized, studentized, leverage, Cook's distance, DFFITS, and outlier flags), AIC and BIC for model comparison, and a prediction function for generating forecasts from fitted coefficients. A complete diagnostic workflow -- fit, check VIF, analyze residuals, test normality -- runs as a single SQL query.

Model diagnostics refers to the set of statistical tests and measures used to validate that a regression model's assumptions hold and that its results are trustworthy. This page covers VIF for multicollinearity detection, residual diagnostics for outlier and influence analysis, information criteria (AIC/BIC) for model selection, and prediction from fitted coefficients.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([VIF](#vif-variance-inflation-factor)) | Multicollinearity detection | `anofox_stats_vif(x) -> LIST(DOUBLE)` |
| ([VIF (agg)](#vif-variance-inflation-factor)) | VIF from row-wise data | `anofox_stats_vif_agg(x) -> LIST(DOUBLE)` |
| ([Residuals](#residual-diagnostics)) | Comprehensive residual analysis | `anofox_stats_residuals_diagnostics_agg(y, y_hat, [x]) -> STRUCT` |
| ([AIC](#aic-akaike-information-criterion)) | Akaike Information Criterion | `anofox_stats_aic(rss, n, k) -> DOUBLE` |
| ([BIC](#bic-bayesian-information-criterion)) | Bayesian Information Criterion | `anofox_stats_bic(rss, n, k) -> DOUBLE` |

---

## VIF (Variance Inflation Factor)

The Variance Inflation Factor (VIF) is a measure of how much the variance of a regression coefficient is inflated due to multicollinearity with other predictors. A VIF of 1 means no correlation with other predictors, while a VIF above 10 indicates severe multicollinearity that can make coefficient estimates unreliable.

### Scalar Version

For matrix input.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | LIST(LIST(DOUBLE)) | Yes | \-  | Predictor matrix |

#### Example

```
SELECT anofox_stats_vif(    [[1.0, 2.0], [1.5, 2.5], [2.0, 3.0]]) as vif_values;
```

### Aggregate Version

For row-wise input.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | LIST(DOUBLE) | Yes | \-  | Predictors per row |

#### Output

Returns LIST(DOUBLE) - VIF for each predictor.

#### Interpretation

| VIF Range | Multicollinearity |
| --- | --- |
| < 5 | Acceptable |
| 5-10 | Moderate |
| \> 10 | Severe (consider removing/combining) |

#### Example

```
WITH vif_result AS (    SELECT anofox_stats_vif_agg([price, promotion, competitor_price]) as vif    FROM sales_data)SELECT    vif[1] as price_vif,    vif[2] as promotion_vif,    vif[3] as competitor_vif,    CASE WHEN max(v) > 10 THEN 'SEVERE'         WHEN max(v) > 5 THEN 'MODERATE'         ELSE 'OK' END as statusFROM vif_result, (SELECT unnest(vif) as v FROM vif_result) tGROUP BY vif[1], vif[2], vif[3];
```

---

## Residual Diagnostics

Comprehensive residual analysis including leverage and influence. Cook's distance measures the influence of each observation on the fitted model.

### Scalar Version

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) | Yes | \-  | Actual values |
| `y_hat` | LIST(DOUBLE) | Yes | \-  | Predicted values |
| `x` | LIST(LIST(DOUBLE)) | No  | \-  | Predictor matrix |
| `options` | MAP | No  | \-  | Configuration options |

### Aggregate Version

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Actual value |
| `y_hat` | DOUBLE | Yes | \-  | Predicted value |
| `x` | LIST(DOUBLE) | No  | \-  | Predictors |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `residuals` | LIST(DOUBLE) | Raw residuals (y - y\_hat) |
| `standardized_residuals` | LIST(DOUBLE) | Standardized residuals |
| `studentized_residuals` | LIST(DOUBLE) | Studentized residuals |
| `leverage` | LIST(DOUBLE) | Hat values (diagonal of H matrix) |
| `cooks_distance` | LIST(DOUBLE) | Cook's D (influence measure) |
| `dffits` | LIST(DOUBLE) | DFFITS (influence on fitted values) |
| `outliers` | LIST(BOOLEAN) | Flagged outliers |

#### Interpretation

| Metric | Threshold | Meaning |
| --- | --- | --- |
| Leverage | \> 2(k+1)/n | High leverage point |
| Cook's D | \> 4/n | Influential observation |
| \|Studentized residual\| | \> 3 | Potential outlier |

#### Example

```
WITH model AS (    SELECT        y,        anofox_stats_predict([x1, x2], coefficients, intercept) as y_hat,        [x1, x2] as x    FROM sales_data, fitted_model),diag AS (    SELECT anofox_stats_residuals_diagnostics_agg(y, y_hat, x) as d    FROM model)SELECT    row_number() OVER () as obs,    d.residuals[obs] as residual,    d.leverage[obs] as leverage,    d.cooks_distance[obs] as cooks_d,    d.outliers[obs] as is_outlierFROM diagWHERE d.cooks_distance[obs] > 4.0 / count(*) OVER ();
```

---

## Information Criteria

Model selection metrics.

### AIC (Akaike Information Criterion)

AIC estimates the relative quality of statistical models for a given dataset.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `rss` | DOUBLE | Yes | \-  | Residual sum of squares |
| `n` | BIGINT | Yes | \-  | Number of observations |
| `k` | BIGINT | Yes | \-  | Number of parameters |

**Formula:** AIC = n × log(RSS/n) + 2k

#### Example

```
SELECT anofox_stats_aic(rss, n, k) as aic;
```

### BIC (Bayesian Information Criterion)

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `rss` | DOUBLE | Yes | \-  | Residual sum of squares |
| `n` | BIGINT | Yes | \-  | Number of observations |
| `k` | BIGINT | Yes | \-  | Number of parameters |

**Formula:** BIC = n × log(RSS/n) + k × log(n)

#### Example

```
SELECT anofox_stats_bic(rss, n, k) as bic;
```

### Usage Guidelines

*   Lower values = better model
*   BIC penalizes complexity more than AIC
*   Use AIC for prediction, BIC for explanation

### Model Comparison Example

```
WITH models AS (    SELECT        'Model 1' as name,        anofox_stats_ols_fit_agg(y, [x1]) as m    FROM data    UNION ALL    SELECT        'Model 2',        anofox_stats_ols_fit_agg(y, [x1, x2])    FROM data    UNION ALL    SELECT        'Model 3',        anofox_stats_ols_fit_agg(y, [x1, x2, x3])    FROM data)SELECT    name,    m.r_squared,    m.aic,    m.bic,    ROW_NUMBER() OVER (ORDER BY m.bic) as bic_rankFROM modelsORDER BY m.bic;
```

---

## Prediction

Generate predictions from fitted coefficients.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | LIST(LIST(DOUBLE)) | Yes | \-  | New predictor values |
| `coefficients` | LIST(DOUBLE) | Yes | \-  | Fitted coefficients |
| `intercept` | DOUBLE | Yes | \-  | Intercept term |

**Returns:** LIST(DOUBLE) - predicted values

### Example

```
WITH fitted AS (    SELECT        (anofox_stats_ols_fit_agg(y, [x1, x2])).coefficients as coef,        (anofox_stats_ols_fit_agg(y, [x1, x2])).intercept as intercept    FROM training_data)SELECT    anofox_stats_predict([[10, 5], [15, 8], [20, 10]], coef, intercept) as forecastsFROM fitted;
```

---

## Normality Tests

For testing residual normality, see [Parametric Tests](/docs/statistics/hypothesis-tests/parametric.md#normality-tests):

*   `anofox_stats_shapiro_wilk_agg` - Shapiro-Wilk test
*   `anofox_stats_jarque_bera_agg` - Jarque-Bera test
*   `anofox_stats_dagostino_k2_agg` - D'Agostino K² test

---

## Complete Diagnostic Workflow

Full model validation workflow:

```
-- 1. Fit modelWITH fitted AS (    SELECT anofox_stats_ols_fit_agg(        revenue,        [marketing_spend, seasonality, competitor_activity],        true, true, 0.95    ) as model    FROM sales_data),-- 2. Check multicollinearityvif_check AS (    SELECT anofox_stats_vif_agg([marketing_spend, seasonality, competitor_activity]) as vif    FROM sales_data),-- 3. Get predictionspredictions AS (    SELECT        s.revenue as actual,        anofox_stats_predict(            [[s.marketing_spend, s.seasonality, s.competitor_activity]],            f.model.coefficients,            f.model.intercept        )[1] as predicted    FROM sales_data s, fitted f),-- 4. Test residual normalitynormality AS (    SELECT anofox_stats_jarque_bera_agg(actual - predicted) as jb    FROM predictions)SELECT    'R-squared' as metric, round(model.r_squared, 3)::VARCHAR as value FROM fittedUNION ALLSELECT 'Max VIF', round(max(v), 2)::VARCHAR FROM vif_check, unnest(vif) as vUNION ALLSELECT 'Normality p-value', round(jb.p_value, 4)::VARCHAR FROM normalityUNION ALLSELECT 'Model Valid',    CASE WHEN model.r_squared > 0.5         AND (SELECT max(v) FROM vif_check, unnest(vif) as v) < 10         AND jb.p_value > 0.05    THEN 'YES' ELSE 'NO' ENDFROM fitted, normality;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Installation & Setup

Get AnoFox Statistics running in minutes.

## System Requirements

| Requirement | Version |
| --- | --- |
| **DuckDB** | 1.4.3 or later |
| **OS** | Linux, macOS, Windows |
| **Architecture** | x86\_64, ARM64, WASM |

## Installation

Install from the DuckDB Community Registry:

```
INSTALL anofox_statistics FROM community;LOAD anofox_statistics;
```

For local builds or development versions, see the [GitHub repository](https://github.com/DataZooDE/anofox-statistics).

## Verify Installation

```
-- Load extensionLOAD anofox_statistics;-- Test with a simple regressionSELECT anofox_stats_ols_fit_agg(    y,    [x]) as modelFROM (VALUES (1.0, 2.0), (2.0, 4.0), (3.0, 6.0)) AS t(x, y);
```

## Persistent Loading

To load the extension automatically, create a `.duckdbrc` file:

```
# ~/.duckdbrcLOAD anofox_statistics;
```

([🍪 Cookie Settings](#cookie-settings))

# Function Finder

The AnoFox Statistics extension provides 50+ SQL functions across 6 categories: 9 regression model types (OLS, Ridge, WLS, RLS, Elastic Net, Poisson GLM, ALM, BLS, NNLS), demand analysis (AID classification and anomaly detection), 30+ hypothesis tests spanning parametric, nonparametric, correlation, and categorical methods, model diagnostics (VIF, residuals, AIC, BIC), and forecast model selection (Diebold-Mariano, Clark-West). All functions run natively in DuckDB with no external dependencies.

Regression models, hypothesis tests, and diagnostics for in-database statistical analysis. All functions run natively in DuckDB with no external dependencies. Use aggregate functions with `GROUP BY` for per-group models or window functions with `OVER` for rolling analysis — see [DuckDB Patterns](/docs/statistics/utilities.md) for details.

## Categories

[### DuckDB Patterns

Aggregate patterns, window functions, and predictions

`_agg``OVER``predict`](./utilities)[### Regression

Linear, regularized, and generalized regression models

`OLS``Ridge``GLM``ALM`+5 more](./regression)[### Demand Analysis

Intermittent demand classification and anomaly detection

`AID``AID Anomaly`](./demand)[### Hypothesis Tests

Parametric, nonparametric, correlation, and categorical tests

`t-Test``ANOVA``Chi-Square``Pearson`+20 more](./hypothesis-tests)[### Diagnostics

Model validation, residuals, and information criteria

`VIF``AIC``BIC``Residuals`](./diagnostics)

## All Functions

All CategoryCategoricalCorrelationDemand AnalysisDiagnosticsForecast Model SelectionNonparametricNormalityParametricRegression

| Function | Description | SQL Signature | Category |
| --- | --- | --- | --- |
| `anofox_stats_ols_*` | Ordinary Least Squares | `anofox_stats_ols_fit(y, x, [options]) -> STRUCT` | Regression |
| `anofox_stats_ridge_*` | Ridge Regression (L2) | `anofox_stats_ridge_fit(y, x, alpha, [options]) -> STRUCT` | Regression |
| `anofox_stats_wls_*` | Weighted Least Squares | `anofox_stats_wls_fit(y, x, weights, [options]) -> STRUCT` | Regression |
| `anofox_stats_rls_*` | Recursive Least Squares | `anofox_stats_rls_fit(y, x, [forgetting_factor]) -> STRUCT` | Regression |
| `anofox_stats_elasticnet_*` | Elastic Net (L1+L2) | `anofox_stats_elasticnet_fit(y, x, alpha, l1_ratio) -> STRUCT` | Regression |
| `anofox_stats_poisson_fit_agg` | Poisson GLM | `anofox_stats_poisson_fit_agg(y, x, [options]) -> STRUCT` | Regression |
| `anofox_stats_alm_fit_agg` | Augmented Linear Model | `anofox_stats_alm_fit_agg(y, x, options) -> STRUCT` | Regression |
| `anofox_stats_bls_fit_agg` | Bounded Least Squares | `anofox_stats_bls_fit_agg(y, x, options) -> STRUCT` | Regression |
| `anofox_stats_nnls_fit_agg` | Non-Negative Least Squares | `anofox_stats_nnls_fit_agg(y, x, [options]) -> STRUCT` | Regression |
| `anofox_stats_aid_agg` | AID Classification | `anofox_stats_aid_agg(y, [options]) -> STRUCT` | Demand Analysis |
| `anofox_stats_aid_anomaly_agg` | AID Anomaly Detection | `anofox_stats_aid_anomaly_agg(y, [options]) -> LIST(STRUCT)` | Demand Analysis |
| `anofox_stats_shapiro_wilk_agg` | Shapiro-Wilk test | `anofox_stats_shapiro_wilk_agg(value) -> STRUCT` | Normality |
| `anofox_stats_jarque_bera_agg` | Jarque-Bera test | `anofox_stats_jarque_bera_agg(value) -> STRUCT` | Normality |
| `anofox_stats_dagostino_k2_agg` | D'Agostino K² test | `anofox_stats_dagostino_k2_agg(value) -> STRUCT` | Normality |
| `anofox_stats_t_test_agg` | Student's t-test | `anofox_stats_t_test_agg(value, group_id, [options]) -> STRUCT` | Parametric |
| `anofox_stats_one_way_anova_agg` | One-way ANOVA | `anofox_stats_one_way_anova_agg(value, group_id) -> STRUCT` | Parametric |
| `anofox_stats_yuen_agg` | Yuen's trimmed mean | `anofox_stats_yuen_agg(value, group_id, [options]) -> STRUCT` | Parametric |
| `anofox_stats_brown_forsythe_agg` | Brown-Forsythe test | `anofox_stats_brown_forsythe_agg(value, group_id) -> STRUCT` | Parametric |
| `anofox_stats_prop_test_one_agg` | One-sample proportion | `anofox_stats_prop_test_one_agg(successes, trials, p0, [options]) -> STRUCT` | Parametric |
| `anofox_stats_prop_test_two_agg` | Two-sample proportion | `anofox_stats_prop_test_two_agg(successes, trials, group_id, [options]) -> STRUCT` | Parametric |
| `anofox_stats_binom_test_agg` | Binomial test | `anofox_stats_binom_test_agg(successes, trials, p0, [options]) -> STRUCT` | Parametric |
| `anofox_stats_tost_t_test_agg` | TOST t-test | `anofox_stats_tost_t_test_agg(value, group_id, delta, [options]) -> STRUCT` | Parametric |
| `anofox_stats_tost_paired_agg` | TOST paired | `anofox_stats_tost_paired_agg(v1, v2, delta, [options]) -> STRUCT` | Parametric |
| `anofox_stats_tost_correlation_agg` | TOST correlation | `anofox_stats_tost_correlation_agg(x, y, rho0, delta) -> STRUCT` | Parametric |
| `anofox_stats_mann_whitney_u_agg` | Mann-Whitney U | `anofox_stats_mann_whitney_u_agg(value, group_id, [options]) -> STRUCT` | Nonparametric |
| `anofox_stats_kruskal_wallis_agg` | Kruskal-Wallis | `anofox_stats_kruskal_wallis_agg(value, group_id) -> STRUCT` | Nonparametric |
| `anofox_stats_wilcoxon_signed_rank_agg` | Wilcoxon signed-rank | `anofox_stats_wilcoxon_signed_rank_agg(v1, v2, [options]) -> STRUCT` | Nonparametric |
| `anofox_stats_brunner_munzel_agg` | Brunner-Munzel | `anofox_stats_brunner_munzel_agg(value, group_id, [options]) -> STRUCT` | Nonparametric |
| `anofox_stats_permutation_t_test_agg` | Permutation t-test | `anofox_stats_permutation_t_test_agg(value, group_id, [options]) -> STRUCT` | Nonparametric |
| `anofox_stats_energy_distance_agg` | Energy distance | `anofox_stats_energy_distance_agg(value, group_id) -> STRUCT` | Nonparametric |
| `anofox_stats_mmd_agg` | Maximum Mean Discrepancy | `anofox_stats_mmd_agg(value, group_id, [options]) -> STRUCT` | Nonparametric |
| `anofox_stats_pearson_agg` | Pearson correlation | `anofox_stats_pearson_agg(x, y, [options]) -> STRUCT` | Correlation |
| `anofox_stats_spearman_agg` | Spearman rank | `anofox_stats_spearman_agg(x, y, [options]) -> STRUCT` | Correlation |
| `anofox_stats_kendall_agg` | Kendall's tau | `anofox_stats_kendall_agg(x, y, [options]) -> STRUCT` | Correlation |
| `anofox_stats_distance_cor_agg` | Distance correlation | `anofox_stats_distance_cor_agg(x, y) -> STRUCT` | Correlation |
| `anofox_stats_icc_agg` | Intraclass correlation | `anofox_stats_icc_agg(value, rater_id, subject_id, [options]) -> STRUCT` | Correlation |
| `anofox_stats_chisq_test_agg` | Chi-square test | `anofox_stats_chisq_test_agg(row_var, col_var, [options]) -> STRUCT` | Categorical |
| `anofox_stats_chisq_gof_agg` | Chi-square GOF | `anofox_stats_chisq_gof_agg(observed, expected) -> STRUCT` | Categorical |
| `anofox_stats_g_test_agg` | G-test | `anofox_stats_g_test_agg(row_var, col_var) -> STRUCT` | Categorical |
| `anofox_stats_fisher_exact_agg` | Fisher's exact | `anofox_stats_fisher_exact_agg(row_var, col_var, [options]) -> STRUCT` | Categorical |
| `anofox_stats_mcnemar_agg` | McNemar's test | `anofox_stats_mcnemar_agg(var1, var2, [options]) -> STRUCT` | Categorical |
| `anofox_stats_cramers_v_agg` | Cramér's V | `anofox_stats_cramers_v_agg(row_var, col_var) -> DOUBLE` | Categorical |
| `anofox_stats_phi_coefficient_agg` | Phi coefficient | `anofox_stats_phi_coefficient_agg(row_var, col_var) -> DOUBLE` | Categorical |
| `anofox_stats_contingency_coef_agg` | Contingency coef | `anofox_stats_contingency_coef_agg(row_var, col_var) -> DOUBLE` | Categorical |
| `anofox_stats_cohen_kappa_agg` | Cohen's Kappa | `anofox_stats_cohen_kappa_agg(rater1, rater2) -> STRUCT` | Categorical |
| `anofox_stats_diebold_mariano_agg` | Diebold-Mariano | `anofox_stats_diebold_mariano_agg(actual, f1, f2, [options]) -> STRUCT` | Forecast Model Selection |
| `anofox_stats_clark_west_agg` | Clark-West | `anofox_stats_clark_west_agg(actual, f1, f2) -> STRUCT` | Forecast Model Selection |
| `anofox_stats_vif` | Variance Inflation Factor | `anofox_stats_vif(x) -> LIST(DOUBLE)` | Diagnostics |
| `anofox_stats_vif_agg` | VIF (aggregate) | `anofox_stats_vif_agg(x) -> LIST(DOUBLE)` | Diagnostics |
| `anofox_stats_aic` | Akaike IC | `anofox_stats_aic(rss, n, k) -> DOUBLE` | Diagnostics |
| `anofox_stats_bic` | Bayesian IC | `anofox_stats_bic(rss, n, k) -> DOUBLE` | Diagnostics |
| `anofox_stats_predict` | Prediction | `anofox_stats_predict(x, coefficients, intercept) -> LIST(DOUBLE)` | Diagnostics |
| `anofox_stats_residuals_diagnostics_agg` | Residual analysis | `anofox_stats_residuals_diagnostics_agg(y, y_hat, [x]) -> STRUCT` | Diagnostics |

Showing 53 of 53

---

([🍪 Cookie Settings](#cookie-settings))

# DuckDB Patterns

DuckDB Patterns refers to the set of SQL integration conventions that determine how AnoFox Statistics functions compose with standard SQL clauses like `GROUP BY`, `OVER (...)`, and scalar expressions.

## How the API Works

Every statistics function follows a consistent naming pattern:

```
anofox_stats_<method>_<variant>
```

**Variants determine how the function integrates with SQL:**

| Suffix | SQL Pattern | Use Case |
| --- | --- | --- |
| `_fit` | Scalar function | Fit a single model on your entire dataset |
| `_fit_agg` | `GROUP BY` | Fit separate models per group (e.g., per region, per product) |
| `_fit_predict` | `OVER (...)` | Rolling or expanding window models (e.g., 60-day rolling regression) |
| `_predict_agg` | `GROUP BY` | Batch predictions per group |

**All model functions return a `STRUCT`** containing coefficients, p-values, R², and diagnostics. Extract fields using `(result).field_name` syntax:

```
SELECT    (model).r_squared,    (model).coefficients[2] as slope,    (model).p_values[2] as p_valueFROM (    SELECT anofox_stats_ols_fit_agg(sales, [advertising]) as model    FROM data);
```

---

## Quick Reference

| Category | Pattern | Description |
| --- | --- | --- |
| ([Aggregates](#aggregate-function-pattern)) | `_agg` suffix | Per-group models with GROUP BY |
| ([Windows](#window-functions)) | `_fit_predict` suffix | Rolling/expanding with OVER |
| ([Prediction](#prediction)) | `anofox_stats_predict` | Generate predictions from coefficients |
| ([Info Criteria](#information-criteria)) | `anofox_stats_aic`, `anofox_stats_bic` | Model selection metrics |

---

## Aggregate Function Pattern

All `_agg` functions work with GROUP BY for per-group models.

### Basic GROUP BY

```
SELECT    group_column,    anofox_stats_ols_fit_agg(y, [x1, x2]) as modelFROM dataGROUP BY group_column;
```

### Extracting Results

```
SELECT    region,    (model).r_squared as fit,    (model).coefficients[1] as intercept,    (model).coefficients[2] as slope,    (model).p_values[2] as p_valueFROM (    SELECT        region,        anofox_stats_ols_fit_agg(sales, [marketing_spend]) as model    FROM regional_data    GROUP BY region);
```

---

## Available Aggregate Functions

### Regression Aggregates

| Function | Description |
| --- | --- |
| `anofox_stats_ols_fit_agg` | OLS regression |
| `anofox_stats_ridge_fit_agg` | Ridge regression |
| `anofox_stats_wls_fit_agg` | Weighted least squares |
| `anofox_stats_rls_fit_agg` | Recursive least squares |
| `anofox_stats_elasticnet_fit_agg` | Elastic Net |
| `anofox_stats_poisson_fit_agg` | Poisson GLM |
| `anofox_stats_alm_fit_agg` | Augmented linear model |
| `anofox_stats_bls_fit_agg` | Bounded least squares |
| `anofox_stats_nnls_fit_agg` | Non-negative least squares |

### Prediction Aggregates

| Function | Description |
| --- | --- |
| `anofox_stats_ols_predict_agg` | Batch OLS predictions |
| `anofox_stats_ridge_predict_agg` | Batch Ridge predictions |
| `anofox_stats_wls_predict_agg` | Batch WLS predictions |
| `anofox_stats_rls_predict_agg` | Batch RLS predictions |
| `anofox_stats_elasticnet_predict_agg` | Batch Elastic Net predictions |

### Statistical Test Aggregates

| Function | Description |
| --- | --- |
| `anofox_stats_t_test_agg` | t-test |
| `anofox_stats_one_way_anova_agg` | ANOVA |
| `anofox_stats_mann_whitney_u_agg` | Mann-Whitney U |
| `anofox_stats_kruskal_wallis_agg` | Kruskal-Wallis |
| `anofox_stats_pearson_agg` | Pearson correlation |
| `anofox_stats_spearman_agg` | Spearman correlation |
| `anofox_stats_chisq_test_agg` | Chi-square test |

### Diagnostic Aggregates

| Function | Description |
| --- | --- |
| `anofox_stats_vif_agg` | Variance Inflation Factor |
| `anofox_stats_shapiro_wilk_agg` | Shapiro-Wilk normality |
| `anofox_stats_jarque_bera_agg` | Jarque-Bera normality |
| `anofox_stats_residuals_diagnostics_agg` | Residual analysis |

### Demand Analysis Aggregates

| Function | Description |
| --- | --- |
| `anofox_stats_aid_agg` | AID classification |
| `anofox_stats_aid_anomaly_agg` | AID anomaly detection |

---

## Window Functions

Use `_fit_predict` functions with OVER for rolling/expanding analyses.

### Rolling Regression

```
SELECT    date,    value,    anofox_stats_ols_fit_predict(y, [x1, x2]) OVER (        ORDER BY date        ROWS BETWEEN 60 PRECEDING AND CURRENT ROW    ) as rolling_modelFROM time_series_data;
```

### Expanding Window

```
SELECT    date,    anofox_stats_ols_fit_predict(y, [x1]) OVER (        ORDER BY date        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    ) as expanding_modelFROM data;
```

### Partitioned Rolling

```
SELECT    region,    date,    anofox_stats_ridge_fit_predict(y, [x1, x2], 0.5) OVER (        PARTITION BY region        ORDER BY date        ROWS BETWEEN 30 PRECEDING AND CURRENT ROW    ) as regional_rollingFROM multi_region_data;
```

---

## Window Function Variants

| Function | Description |
| --- | --- |
| `anofox_stats_ols_fit_predict` | Rolling/expanding OLS |
| `anofox_stats_ridge_fit_predict` | Rolling/expanding Ridge |
| `anofox_stats_wls_fit_predict` | Rolling/expanding WLS |
| `anofox_stats_rls_fit_predict` | Rolling/expanding RLS |
| `anofox_stats_elasticnet_fit_predict` | Rolling/expanding Elastic Net |

---

## Window Specifications

### Rolling Window

Fixed-size moving window:

```
ROWS BETWEEN 60 PRECEDING AND CURRENT ROW-- Uses last 61 observations (including current)
```

### Expanding Window

Growing window from start:

```
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW-- Uses all observations up to current
```

### Range-Based Window

Time-based window:

```
RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW-- Uses observations within last 30 days
```

---

## Prediction

Generate predictions from fitted coefficients.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | LIST(LIST(DOUBLE)) | Yes | \-  | New predictor values |
| `coefficients` | LIST(DOUBLE) | Yes | \-  | Fitted coefficients |
| `intercept` | DOUBLE | Yes | \-  | Intercept term |

**Returns:** LIST(DOUBLE) - predicted values

### Example

```
WITH fitted AS (    SELECT anofox_stats_ols_fit_agg(revenue, [marketing, seasonality]) as model    FROM historical_data)SELECT    scenario_name,    anofox_stats_predict(        [[marketing_plan, seasonal_factor]],        model.coefficients,        model.intercept    )[1] as predicted_revenueFROM scenarios, fitted;
```

---

## Information Criteria

### AIC (Akaike Information Criterion)

```
SELECT anofox_stats_aic(    rss,   -- DOUBLE: residual sum of squares    n,     -- BIGINT: number of observations    k      -- BIGINT: number of parameters (including intercept)) as aic;
```

### BIC (Bayesian Information Criterion)

```
SELECT anofox_stats_bic(    rss,   -- DOUBLE: residual sum of squares    n,     -- BIGINT: number of observations    k      -- BIGINT: number of parameters) as bic;
```

### Example

```
WITH residuals AS (    SELECT sum(power(actual - predicted, 2)) as rss,           count(*) as n    FROM predictions)SELECT    anofox_stats_aic(rss, n, 3) as aic,  -- 3 = intercept + 2 predictors    anofox_stats_bic(rss, n, 3) as bicFROM residuals;
```

---

## Working with STRUCT Results

All model functions return STRUCT types. Here's how to extract values.

### Direct Field Access

```
SELECT    (model).r_squared,    (model).coefficients[2],    (model).p_values[2]FROM (    SELECT anofox_stats_ols_fit_agg(y, [x1, x2]) as model    FROM data);
```

### Named Extraction

```
SELECT    result.coefficients[1] as intercept,    result.coefficients[2] as x1_effect,    result.coefficients[3] as x2_effect,    result.p_values[2] as x1_pvalue,    result.r_squared as fitFROM (    SELECT anofox_stats_ols_fit_agg(y, [x1, x2]) as result    FROM data);
```

### Unnesting Arrays

```
WITH model AS (    SELECT anofox_stats_ols_fit_agg(y, [x1, x2, x3]) as m    FROM data)SELECT    idx,    m.coefficients[idx] as coefficient,    m.std_errors[idx] as std_error,    m.p_values[idx] as p_valueFROM model, generate_series(1, array_length(m.coefficients)) as idx;
```

---

## Common Patterns

### Filtering by Significance

```
SELECT    predictor_name,    coefficient,    p_valueFROM model_resultsWHERE p_value < 0.05;
```

### Model Comparison Table

```
WITH models AS (    SELECT 'Simple' as name, anofox_stats_ols_fit_agg(y, [x1]) as m FROM data    UNION ALL    SELECT 'Full', anofox_stats_ols_fit_agg(y, [x1, x2, x3]) FROM data)SELECT    name,    round(m.r_squared, 4) as r2,    round(m.adj_r_squared, 4) as adj_r2,    round(m.aic, 2) as aic,    round(m.bic, 2) as bic,    round(m.rmse, 4) as rmseFROM modelsORDER BY m.bic;
```

### Coefficient Summary Table

```
WITH model AS (    SELECT anofox_stats_ols_fit_agg(        revenue,        [marketing, seasonality, competitor],        true, true, 0.95    ) as m    FROM sales_data),predictors AS (    SELECT * FROM (VALUES        (1, 'Intercept'),        (2, 'Marketing'),        (3, 'Seasonality'),        (4, 'Competitor')    ) AS t(idx, name))SELECT    p.name,    round(m.coefficients[p.idx], 4) as estimate,    round(m.std_errors[p.idx], 4) as std_error,    round(m.t_statistics[p.idx], 3) as t_stat,    round(m.p_values[p.idx], 4) as p_value,    CASE WHEN m.p_values[p.idx] < 0.001 THEN '***'         WHEN m.p_values[p.idx] < 0.01 THEN '**'         WHEN m.p_values[p.idx] < 0.05 THEN '*'         WHEN m.p_values[p.idx] < 0.1 THEN '.'         ELSE '' END as sigFROM model, predictors pORDER BY p.idx;
```

---

## Examples

### Per-Product Price Elasticity

```
SELECT    product_id,    product_name,    (model).coefficients[2] as price_elasticity,    (model).r_squared as model_fit,    CASE        WHEN (model).p_values[2] < 0.05 THEN 'Significant'        ELSE 'Not Significant'    END as significanceFROM (    SELECT        product_id,        product_name,        anofox_stats_ols_fit_agg(            log(quantity_sold),            [log(price), log(competitor_price)]        ) as model    FROM sales_data    GROUP BY product_id, product_name)ORDER BY abs((model).coefficients[2]) DESC;
```

### Rolling Beta Estimation

```
SELECT    date,    stock_ticker,    (rolling_model).coefficients[2] as beta,    (rolling_model).r_squared as r_squaredFROM (    SELECT        date,        stock_ticker,        anofox_stats_ols_fit_predict(            stock_return,            [market_return]        ) OVER (            PARTITION BY stock_ticker            ORDER BY date            ROWS BETWEEN 252 PRECEDING AND CURRENT ROW        ) as rolling_model    FROM stock_returns)WHERE date >= '2024-01-01';
```

### Regional A/B Test Analysis

```
SELECT    region,    (result).mean_diff as lift,    (result).p_value,    (result).cohens_d as effect_size,    CASE        WHEN (result).p_value < 0.05 AND (result).mean_diff > 0 THEN 'Winner'        WHEN (result).p_value < 0.05 AND (result).mean_diff < 0 THEN 'Loser'        ELSE 'Inconclusive'    END as decisionFROM (    SELECT        region,        anofox_stats_t_test_agg(conversion_rate, treatment_group) as result    FROM ab_test_data    GROUP BY region);
```

### Time-Varying Correlation

```
SELECT    date,    (corr).r as correlation,    (corr).p_value,    CASE        WHEN abs((corr).r) > 0.7 THEN 'Strong'        WHEN abs((corr).r) > 0.4 THEN 'Moderate'        ELSE 'Weak'    END as strengthFROM (    SELECT        date,        anofox_stats_pearson_agg(x, y) OVER (            ORDER BY date            ROWS BETWEEN 60 PRECEDING AND CURRENT ROW        ) as corr    FROM paired_series);
```

---

## Type Reference

### Common Output Types

| Field | Type | Description |
| --- | --- | --- |
| `coefficients` | LIST(DOUBLE) | Regression coefficients |
| `std_errors` | LIST(DOUBLE) | Standard errors |
| `t_statistics` | LIST(DOUBLE) | t-statistics |
| `p_values` | LIST(DOUBLE) | p-values |
| `confidence_intervals` | LIST(STRUCT) | CI bounds per coefficient |
| `r_squared` | DOUBLE | R² (0-1) |
| `adj_r_squared` | DOUBLE | Adjusted R² |
| `rmse` | DOUBLE | Root mean squared error |
| `aic` | DOUBLE | Akaike Information Criterion |
| `bic` | DOUBLE | Bayesian Information Criterion |
| `n` | BIGINT | Number of observations |
| `k` | BIGINT | Number of predictors |

### Hypothesis Test Output Types

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | Test statistic |
| `p_value` | DOUBLE | p-value |
| `df` | DOUBLE/BIGINT | Degrees of freedom |
| `effect_size` | DOUBLE | Effect size measure |
| `ci_lower` | DOUBLE | CI lower bound |
| `ci_upper` | DOUBLE | CI upper bound |

---

## Frequently Asked Questions

What is the difference between \_fit\_agg and \_fit\_predict variants?

The `_fit_agg` variant works with `GROUP BY` to fit one model per group and returns a single STRUCT result per group. The `_fit_predict` variant works with `OVER (...)` window functions to perform rolling or expanding computations, returning a result for every row in the window. Use `_fit_agg` for batch analysis and `_fit_predict` for time-varying analysis.

How do I extract individual fields from the STRUCT result?

Use parenthesized dot notation: `(model).r_squared`, `(model).coefficients[2]`. Array indices start at 1, where index 1 is the intercept (if included) and index 2 is the first predictor's coefficient. You can also alias the result and use `result.field_name` syntax.

Can I use window functions with a time-based range instead of row count?

Yes. DuckDB supports range-based windows like `RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW`. This uses all observations within the last 30 calendar days rather than a fixed row count. This is useful when your data has irregular time spacing.

How do I compare multiple models using information criteria?

Fit each model and extract the AIC and BIC values from the result STRUCT. Lower AIC favors prediction accuracy while lower BIC favors simpler models. Use `UNION ALL` to combine results and `ORDER BY m.bic` to rank them. See the ([Model Comparison Table](#common-patterns)) example above.

([🍪 Cookie Settings](#cookie-settings))

# Demand Classification

Classify demand patterns and detect anomalies for inventory management.

---

## AID - Demand Classification

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

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Demand values over time |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | 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) |

### Output Fields

| Field | Type | Description |
| --- | --- | --- |
| `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 |

### Example

```
SELECT    sku_id,    (result).demand_type,    (result).is_intermittent,    (result).distribution,    (result).zero_proportion,    (result).has_stockouts,    (result).high_outlier_countFROM (    SELECT        sku_id,        anofox_stats_aid_agg(            daily_demand,            MAP {'intermittent_threshold': '0.3', 'outlier_method': 'zscore'}        ) as result    FROM inventory_data    GROUP BY sku_id);
```

---

## AID Anomaly Detection

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

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Demand values |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `intermittent_threshold` | DOUBLE | 0.3 | Zero proportion cutoff |
| `outlier_method` | VARCHAR | `zscore` | Outlier detection: `zscore` or `iqr` |

### Output Fields (per observation)

Returns `LIST(STRUCT)` with one entry per input observation:

| Field | Type | Description |
| --- | --- | --- |
| `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 |

### Example

```
SELECT anofox_stats_aid_anomaly_agg(    daily_demand,    MAP {'outlier_method': 'iqr'}) as anomaliesFROM demand_series;
```

### Anomaly Definitions

| Anomaly | Definition |
| --- | --- |
| **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 + 3×std (zscore) or > Q3 + 1.5×IQR (iqr) |
| **Low Outlier** | Non-zero value < mean - 3×std (zscore) or < Q1 - 1.5×IQR (iqr) |

([🍪 Cookie Settings](#cookie-settings))

# Categorical Tests

AnoFox provides 9 functions for categorical data analysis: 5 independence and goodness-of-fit tests (chi-square independence, chi-square GOF, G-test, Fisher's exact for 2x2 tables, McNemar's for paired before/after data) and 4 effect size measures (Cramer's V, Phi coefficient, contingency coefficient, Cohen's Kappa for inter-rater agreement). Fisher's exact test is the correct choice for small sample 2x2 tables where chi-square approximations break down. Cohen's Kappa returns values on a -1 to 1 scale where 0.8+ indicates almost perfect agreement between raters.

Categorical tests are statistical methods for analyzing data where variables take on discrete categories rather than continuous values. These tests determine whether observed frequencies differ significantly from expected frequencies (goodness-of-fit), whether two categorical variables are related (independence), or whether raters agree on their classifications (agreement).

---

## Independence Tests

### Chi-Square Test of Independence

Tests whether two categorical variables are associated.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `row_var` | INTEGER | Yes | \-  | Row category |
| `col_var` | INTEGER | Yes | \-  | Column category |
| `options` | MAP | No  | \-  | `yates_correction` (default: false) |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `chi2` | DOUBLE | Chi-square statistic |
| `p_value` | DOUBLE | p-value |
| `df` | BIGINT | Degrees of freedom |
| `cramers_v` | DOUBLE | Effect size |

#### Example

```
SELECT anofox_stats_chisq_test_agg(    row_var,    col_var) as resultFROM contingency_data;
```

### Chi-Square Goodness of Fit

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `observed` | INTEGER | Yes | \-  | Observed counts |
| `expected` | DOUBLE | Yes | \-  | Expected counts |

#### Example

```
SELECT anofox_stats_chisq_gof_agg(    observed,    expected) as resultFROM frequency_data;
```

### G-Test (Log-Likelihood Ratio)

Alternative to chi-square, better for small samples.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `row_var` | INTEGER | Yes | \-  | Row category |
| `col_var` | INTEGER | Yes | \-  | Column category |

#### Example

```
SELECT anofox_stats_g_test_agg(    row_var,    col_var) as resultFROM data;
```

### Fisher's Exact Test

Exact test for 2x2 tables.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `row_var` | INTEGER | Yes | \-  | Row category |
| `col_var` | INTEGER | Yes | \-  | Column category |
| `options` | MAP | No  | \-  | `alternative` setting |

#### Example

```
SELECT anofox_stats_fisher_exact_agg(    row_var,    col_var) as resultFROM data;
```

### McNemar's Test

Paired categorical data (before/after).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `var1` | INTEGER | Yes | \-  | Before measurement |
| `var2` | INTEGER | Yes | \-  | After measurement |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_mcnemar_agg(    var1,    var2) as resultFROM paired_categorical;
```

---

## Effect Size Measures

Quantify the magnitude of associations.

### Cramér's V

Effect size for chi-square test.

```
SELECT anofox_stats_cramers_v_agg(row_var, col_var) as vFROM data;
```

**Interpretation:**

| V value | Effect Size |
| --- | --- |
| 0.0 - 0.1 | Negligible |
| 0.1 - 0.3 | Small |
| 0.3 - 0.5 | Medium |
| \> 0.5 | Large |

### Phi Coefficient

Effect size for 2x2 tables.

```
SELECT anofox_stats_phi_coefficient_agg(row_var, col_var) as phiFROM data;
```

### Contingency Coefficient

Normalized association measure.

```
SELECT anofox_stats_contingency_coef_agg(row_var, col_var) as cFROM data;
```

### Cohen's Kappa

Inter-rater agreement measure.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `rater1` | INTEGER | Yes | \-  | First rater's classification |
| `rater2` | INTEGER | Yes | \-  | Second rater's classification |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `kappa` | DOUBLE | Cohen's kappa (-1 to 1) |
| `p_value` | DOUBLE | p-value |
| `se` | DOUBLE | Standard error |
| `ci_lower` | DOUBLE | CI lower |
| `ci_upper` | DOUBLE | CI upper |

#### Example

```
SELECT anofox_stats_cohen_kappa_agg(    rater1,    rater2) as resultFROM rating_data;
```

**Interpretation:**

| Kappa | Agreement |
| --- | --- |
| < 0 | Less than chance |
| 0.0 - 0.2 | Slight |
| 0.2 - 0.4 | Fair |
| 0.4 - 0.6 | Moderate |
| 0.6 - 0.8 | Substantial |
| 0.8 - 1.0 | Almost perfect |

---

## Choosing a Test

| Scenario | Recommended |
| --- | --- |
| 2+ categories, large samples | Chi-Square |
| 2x2 table, small samples | Fisher's Exact |
| Before/after paired data | McNemar's |
| Prefer likelihood-based | G-Test |
| Quantify association | Cramér's V |
| Rater agreement | Cohen's Kappa |

---

([🍪 Cookie Settings](#cookie-settings))

# Correlation Tests

AnoFox provides 5 correlation methods spanning linear (Pearson), rank-based (Spearman, Kendall), nonlinear (distance correlation), and reliability (Intraclass Correlation Coefficient with 3 model types). Distance correlation is uniquely powerful: it equals zero if and only if the two variables are statistically independent -- unlike Pearson, which only detects linear relationships. ICC supports one-way, two-way random, and two-way mixed models for inter-rater reliability assessment. All correlation functions return p-values, confidence intervals, and sample sizes alongside the correlation coefficient.

Correlation is a statistical measure that quantifies the strength and direction of the relationship between two variables. A correlation of +1 indicates a perfect positive relationship, -1 a perfect negative relationship, and 0 no relationship. Different correlation methods capture different types of relationships: linear (Pearson), monotonic (Spearman, Kendall), or general dependence (distance correlation).

---

## Linear Correlation

### Pearson Correlation

Linear correlation with significance test.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | DOUBLE | Yes | \-  | First variable |
| `y` | DOUBLE | Yes | \-  | Second variable |
| `options` | MAP | No  | \-  | Configuration options |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `r` | DOUBLE | Pearson correlation (-1 to 1) |
| `p_value` | DOUBLE | p-value |
| `t_statistic` | DOUBLE | t-statistic |
| `ci_lower` | DOUBLE | CI lower bound |
| `ci_upper` | DOUBLE | CI upper bound |
| `n` | BIGINT | Sample size |

#### Example

```
SELECT anofox_stats_pearson_agg(x, y) as resultFROM data;
```

**Interpretation:**

| r value | Strength |
| --- | --- |
| 0.0 - 0.3 | Weak |
| 0.3 - 0.7 | Moderate |
| 0.7 - 1.0 | Strong |

---

## Rank Correlations

### Spearman Rank Correlation

Monotonic relationship (robust to outliers).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | DOUBLE | Yes | \-  | First variable |
| `y` | DOUBLE | Yes | \-  | Second variable |
| `options` | MAP | No  | \-  | Configuration options |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `rho` | DOUBLE | Spearman's rho |
| `p_value` | DOUBLE | p-value |
| `n` | BIGINT | Sample size |

#### Example

```
SELECT anofox_stats_spearman_agg(x, y) as resultFROM data;
```

### Kendall's Tau

Rank correlation (handles ties well).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | DOUBLE | Yes | \-  | First variable |
| `y` | DOUBLE | Yes | \-  | Second variable |
| `options` | MAP | No  | \-  | Configuration options |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `tau` | DOUBLE | Kendall's tau |
| `p_value` | DOUBLE | p-value |
| `n` | BIGINT | Sample size |

#### Example

```
SELECT anofox_stats_kendall_agg(x, y) as resultFROM data;
```

---

## Nonlinear Correlation

### Distance Correlation

Detects nonlinear relationships using the distance correlation measure.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | DOUBLE | Yes | \-  | First variable |
| `y` | DOUBLE | Yes | \-  | Second variable |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `dcor` | DOUBLE | Distance correlation (0 to 1) |
| `dcov` | DOUBLE | Distance covariance |
| `n` | BIGINT | Sample size |

#### Example

```
SELECT anofox_stats_distance_cor_agg(x, y) as resultFROM data;
```

**Key property:** Distance correlation = 0 if and only if X and Y are independent (unlike Pearson which only detects linear relationships).

---

## Reliability Measures

### Intraclass Correlation (ICC)

Reliability/agreement between raters.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Rating value |
| `rater_id` | INTEGER | Yes | \-  | Rater identifier |
| `subject_id` | INTEGER | Yes | \-  | Subject identifier |
| `options` | MAP | No  | \-  | Model configuration |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `model` | VARCHAR | `two_way_random` | `one_way`, `two_way_random`, `two_way_mixed` |

#### Example

```
SELECT anofox_stats_icc_agg(    value,    rater_id,    subject_id,    MAP {'model': 'two_way_random'}) as resultFROM rating_data;
```

**Interpretation:**

| ICC value | Reliability |
| --- | --- |
| < 0.5 | Poor |
| 0.5 - 0.75 | Moderate |
| 0.75 - 0.9 | Good |
| \> 0.9 | Excellent |

---

## Choosing a Correlation Method

| Scenario | Recommended |
| --- | --- |
| Linear relationship, normal data | Pearson |
| Outliers present | Spearman |
| Ordinal data | Spearman or Kendall |
| Many ties | Kendall |
| Nonlinear relationship | Distance Correlation |
| Rater agreement | ICC |

---

([🍪 Cookie Settings](#cookie-settings))

# Forecast Model Selection

Forecast model selection tests are statistical methods that determine whether the accuracy difference between two competing forecasting models is statistically significant or merely due to random chance. Without these tests, you might deploy a more complex model that appears better on a single test set but offers no real improvement.

---

## Diebold-Mariano Test

The Diebold-Mariano test is a statistical test that evaluates whether two forecasting models have significantly different predictive accuracy. It compares the loss differential between two sets of forecasts and tests whether the mean difference is statistically distinguishable from zero.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE | Yes | \-  | Actual values |
| `forecast1` | DOUBLE | Yes | \-  | First forecast |
| `forecast2` | DOUBLE | Yes | \-  | Second forecast |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `loss` | VARCHAR | `mse` | `mse`, `mae`, or `mape` |
| `alternative` | VARCHAR | `two_sided` | `two_sided`, `less`, `greater` |

### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | DM test statistic |
| `p_value` | DOUBLE | p-value |
| `better_model` | INTEGER | 1 or 2 (which forecast is better) |

### Example

```
SELECT anofox_stats_diebold_mariano_agg(    actual,    forecast1,    forecast2,    MAP {'loss': 'mse'}) as resultFROM forecast_data;
```

---

## Clark-West Test

The Clark-West test is designed for comparing nested forecasting models, where one model (restricted) is a special case of the other (unrestricted). It adjusts for the parameter estimation noise that makes the Diebold-Mariano test conservative in nested comparisons. More powerful than Diebold-Mariano when comparing nested models.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `actual` | DOUBLE | Yes | \-  | Actual values |
| `forecast1` | DOUBLE | Yes | \-  | First forecast (restricted model) |
| `forecast2` | DOUBLE | Yes | \-  | Second forecast (unrestricted model) |

### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | CW test statistic |
| `p_value` | DOUBLE | p-value |

### Example

```
SELECT anofox_stats_clark_west_agg(    actual,    forecast1,    forecast2) as resultFROM forecast_data;
```

---

## Choosing Between Tests

| Scenario | Recommended Test |
| --- | --- |
| Non-nested models | Diebold-Mariano |
| Nested models | Clark-West |
| Large errors matter more | DM with `loss: 'mse'` |
| Robust to outliers | DM with `loss: 'mae'` |
| Percentage errors | DM with `loss: 'mape'` |

([🍪 Cookie Settings](#cookie-settings))

# Nonparametric Tests

AnoFox provides 7 nonparametric test functions that make no assumptions about the underlying data distribution. The suite includes rank-based group comparisons (Mann-Whitney U for 2 groups, Kruskal-Wallis for 3+ groups), paired comparisons (Wilcoxon signed-rank), robust alternatives (Brunner-Munzel without equal variance assumption), exact testing (permutation t-test with configurable iterations up to 10,000), and full distribution comparison (energy distance, Maximum Mean Discrepancy). These tests are the correct choice when normality tests reject or when working with ordinal data, heavy-tailed distributions, or small sample sizes.

Nonparametric tests are distribution-free statistical methods that make no assumptions about the shape of the underlying data distribution. They work with ranks or permutations instead of raw values, making them robust to outliers, skewness, and non-normal data. Use these tests when normality tests reject or when working with ordinal data.

---

## Group Comparison Tests

### Mann-Whitney U Test

Test for stochastic superiority between two groups.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier (0 or 1) |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `alternative` | VARCHAR | `two_sided` | `two_sided`, `less`, `greater` |
| `correction` | BOOLEAN | true | Apply continuity correction |
| `confidence_level` | DOUBLE | 0.95 | Confidence level |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `u_statistic` | DOUBLE | U statistic |
| `p_value` | DOUBLE | p-value |
| `z_score` | DOUBLE | z approximation |
| `effect_size` | DOUBLE | Rank-biserial correlation |

#### Example

```
SELECT anofox_stats_mann_whitney_u_agg(    value,    group_id) as resultFROM data;
```

### Kruskal-Wallis Test

Test whether samples originate from the same distribution across 3+ groups.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `h_statistic` | DOUBLE | H statistic |
| `p_value` | DOUBLE | p-value |
| `df` | BIGINT | Degrees of freedom |
| `epsilon_squared` | DOUBLE | Effect size |

#### Example

```
SELECT anofox_stats_kruskal_wallis_agg(    value,    group_id) as resultFROM data;
```

### Wilcoxon Signed-Rank Test

Paired samples comparison.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value1` | DOUBLE | Yes | \-  | First measurement |
| `value2` | DOUBLE | Yes | \-  | Paired measurement |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_wilcoxon_signed_rank_agg(    value1,    value2) as resultFROM paired_data;
```

### Brunner-Munzel Test

Robust comparison without equal variance assumption.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_brunner_munzel_agg(    value,    group_id) as resultFROM data;
```

### Permutation t-Test

Exact test using permutation distribution.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |
| `options` | MAP | No  | \-  | `n_permutations` (default: 10000) |

#### Example

```
SELECT anofox_stats_permutation_t_test_agg(    value,    group_id,    MAP {'n_permutations': '10000'}) as resultFROM data;
```

---

## Distribution Comparison

Compare entire distributions.

### Energy Distance

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |

#### Example

```
SELECT anofox_stats_energy_distance_agg(    value,    group_id) as resultFROM data;
```

### Maximum Mean Discrepancy (MMD)

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |
| `options` | MAP | No  | \-  | Kernel settings |

#### Example

```
SELECT anofox_stats_mmd_agg(    value,    group_id) as resultFROM data;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Parametric Tests

AnoFox provides 12 parametric test functions covering normality testing (Shapiro-Wilk, Jarque-Bera, D'Agostino K-squared), group comparison (Student's t-test with Welch correction, one-way ANOVA, Yuen's trimmed mean, Brown-Forsythe), proportion testing (one-sample, two-sample, binomial exact), and equivalence testing (TOST t-test, TOST paired, TOST correlation). Each test returns structured output with test statistics, p-values, confidence intervals, and effect sizes like Cohen's d and eta-squared -- enabling automated decision logic directly in SQL.

Parametric tests are statistical hypothesis tests that assume the data follows a specific probability distribution (typically normal/Gaussian) and estimate population parameters like means and variances. They are generally more powerful than nonparametric alternatives when the distributional assumptions hold.

---

## Normality Tests

Test whether data follows a normal distribution.

### Shapiro-Wilk Test

Most powerful test for small to medium samples (n < 5000).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Values to test |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | W statistic (closer to 1 = more normal) |
| `p_value` | DOUBLE | p-value |
| `is_normal` | BOOLEAN | p > 0.05 |

#### Example

```
SELECT anofox_stats_shapiro_wilk_agg(value) as resultFROM data;
```

### Jarque-Bera Test

Based on skewness and kurtosis. Good for large samples.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Values to test |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | JB statistic |
| `p_value` | DOUBLE | p-value |
| `skewness` | DOUBLE | Sample skewness |
| `kurtosis` | DOUBLE | Sample excess kurtosis |

#### Example

```
SELECT anofox_stats_jarque_bera_agg(value) as resultFROM data;
```

### D'Agostino K² Test

Omnibus test combining skewness and kurtosis tests.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Values to test |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | K² statistic |
| `p_value` | DOUBLE | p-value |
| `z_skewness` | DOUBLE | z-score for skewness |
| `z_kurtosis` | DOUBLE | z-score for kurtosis |

#### Example

```
SELECT anofox_stats_dagostino_k2_agg(value) as resultFROM data;
```

---

## Group Comparison Tests

### Student's t-Test

Compare means of two groups.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier (0 or 1) |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `alternative` | VARCHAR | `two_sided` | `two_sided`, `less`, `greater` |
| `kind` | VARCHAR | `welch` | `welch` or `student` (equal variances) |
| `confidence_level` | DOUBLE | 0.95 | Confidence level for CI |
| `mu` | DOUBLE | 0.0 | Hypothesized difference |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `statistic` | DOUBLE | t-statistic |
| `p_value` | DOUBLE | p-value |
| `df` | DOUBLE | Degrees of freedom |
| `mean_diff` | DOUBLE | Difference in means |
| `ci_lower` | DOUBLE | Confidence interval lower |
| `ci_upper` | DOUBLE | Confidence interval upper |
| `cohens_d` | DOUBLE | Effect size |

#### Example

```
SELECT    (result).p_value,    (result).mean_diff,    (result).cohens_d as effect_sizeFROM (    SELECT anofox_stats_t_test_agg(        conversion_rate,        treatment_group    ) as result    FROM ab_test_data);
```

### One-Way ANOVA

Compare means across 3+ groups.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |

#### Output

| Field | Type | Description |
| --- | --- | --- |
| `f_statistic` | DOUBLE | F-statistic |
| `p_value` | DOUBLE | p-value |
| `df_between` | BIGINT | Between-groups df |
| `df_within` | BIGINT | Within-groups df |
| `ss_between` | DOUBLE | Sum of squares between |
| `ss_within` | DOUBLE | Sum of squares within |
| `eta_squared` | DOUBLE | Effect size |

#### Example

```
SELECT anofox_stats_one_way_anova_agg(    value,    group_id) as resultFROM data;
```

### Yuen's Trimmed Mean Test

Robust alternative to t-test with trimmed means.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `trim` | DOUBLE | 0.2 | Proportion to trim from each tail |
| `alternative` | VARCHAR | `two_sided` | `two_sided`, `less`, `greater` |

#### Example

```
SELECT anofox_stats_yuen_agg(    value,    group_id,    MAP {'trim': '0.2'}) as resultFROM data;
```

### Brown-Forsythe Test

Test equality of variances (more robust than Levene's).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |

#### Example

```
SELECT anofox_stats_brown_forsythe_agg(    value,    group_id) as resultFROM data;
```

---

## Proportion Tests

Tests for proportions and binomial data.

### One-Sample Proportion Test

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `successes` | INTEGER | Yes | \-  | Number of successes |
| `trials` | INTEGER | Yes | \-  | Number of trials |
| `p0` | DOUBLE | Yes | \-  | Null hypothesis proportion |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_prop_test_one_agg(    successes,    trials,    0.5) as resultFROM data;
```

### Two-Sample Proportion Test

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `successes` | INTEGER | Yes | \-  | Number of successes |
| `trials` | INTEGER | Yes | \-  | Number of trials |
| `group_id` | INTEGER | Yes | \-  | Group identifier (0 or 1) |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_prop_test_two_agg(    successes,    trials,    group_id) as resultFROM data;
```

### Binomial Test

Exact test for proportions.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `successes` | INTEGER | Yes | \-  | Number of successes |
| `trials` | INTEGER | Yes | \-  | Number of trials |
| `p0` | DOUBLE | Yes | \-  | Null hypothesis proportion |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_binom_test_agg(    successes,    trials,    0.5) as resultFROM data;
```

---

## Equivalence Tests (TOST)

Two One-Sided Tests (TOST) for equivalence.

### TOST t-Test

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value` | DOUBLE | Yes | \-  | Measurement values |
| `group_id` | INTEGER | Yes | \-  | Group identifier |
| `delta` | DOUBLE | Yes | \-  | Equivalence margin |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_tost_t_test_agg(    value,    group_id,    0.5) as resultFROM data;
```

### TOST Paired

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `value1` | DOUBLE | Yes | \-  | First measurement |
| `value2` | DOUBLE | Yes | \-  | Second measurement |
| `delta` | DOUBLE | Yes | \-  | Equivalence margin |
| `options` | MAP | No  | \-  | Configuration options |

#### Example

```
SELECT anofox_stats_tost_paired_agg(    value1,    value2,    0.5) as resultFROM paired_data;
```

### TOST Correlation

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `x` | DOUBLE | Yes | \-  | First variable |
| `y` | DOUBLE | Yes | \-  | Second variable |
| `rho0` | DOUBLE | Yes | \-  | Null correlation |
| `delta` | DOUBLE | Yes | \-  | Equivalence margin |

#### Example

```
SELECT anofox_stats_tost_correlation_agg(    x,    y,    0.0,    0.1) as resultFROM data;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Constrained Models

AnoFox provides 2 constrained regression methods: BLS (Bounded Least Squares) with user-defined upper and lower coefficient bounds, and NNLS (Non-Negative Least Squares) that enforces all coefficients to be zero or positive. BLS is essential for economic models where domain knowledge dictates constraints -- for example, price elasticity must be negative (law of demand). NNLS is the standard approach for marketing mix modeling, spectral decomposition, and portfolio allocation where negative contributions are physically impossible.

## Constrained regression refers to least squares estimation where the coefficients are restricted to lie within specified bounds or satisfy certain constraints, such as non-negativity. These constraints encode domain knowledge that standard unconstrained regression ignores.

## BLS - Bounded Least Squares

Bounded Least Squares is a constrained regression method that restricts each coefficient to lie within user-specified lower and upper bounds. This allows you to enforce domain knowledge -- for example, that price elasticity must be negative or that marketing effects cannot exceed a physical maximum.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Target values |
| `x` | LIST(DOUBLE) | Yes | \-  | Predictors |
| `options` | MAP | Yes | \-  | lower\_bounds, upper\_bounds, fit\_intercept |

### Example

```
-- Elasticity must be negative (law of demand)SELECT anofox_stats_bls_fit_agg(    log(quantity),    [log(price), log(income)],    MAP {        'lower_bounds': '[-5.0, 0.0]',        'upper_bounds': '[0.0, 3.0]'    }) as modelFROM demand_data;
```

**When to use BLS:**

*   Economic constraints (negative elasticities)
*   Physical constraints (positive effects)
*   Domain knowledge requiring bounded coefficients

---

## NNLS - Non-Negative Least Squares

Non-Negative Least Squares (NNLS) is a special case of constrained regression where all coefficients are restricted to be zero or positive. This constraint is appropriate when negative coefficients are physically meaningless -- for example, marketing channel contributions to sales cannot be negative, and spectral components cannot have negative weights.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Target values |
| `x` | LIST(DOUBLE) | Yes | \-  | Predictors |
| `options` | MAP | No  | \-  | fit\_intercept (default: false), max\_iterations, tolerance |

### Example

```
-- Channel contributions must be non-negativeSELECT    (model).coefficients[1] as search_contribution,    (model).coefficients[2] as social_contribution,    (model).coefficients[3] as email_contributionFROM (    SELECT anofox_stats_nnls_fit_agg(        conversions,        [search_spend, social_spend, email_spend]    ) as model    FROM marketing_data);
```

**When to use NNLS:**

*   Marketing mix modeling (positive contributions only)
*   Spectral decomposition
*   Portfolio weights (no short selling)

---

([🍪 Cookie Settings](#cookie-settings))

# Generalized Linear Models

AnoFox provides 2 generalized model types: Poisson GLM for count data (defects, arrivals, event counts) and the Augmented Linear Model (ALM) supporting 24 error distributions including Student-t, Cauchy, Laplace, Huber, Weibull, Gamma, and Log-Normal. Poisson GLM coefficients are on the log scale -- `exp(coefficient)` gives the rate ratio for a one-unit change in the predictor. ALM is the go-to choice when residuals violate the normality assumption, offering robust estimation that resists outlier contamination.

A Generalized Linear Model (GLM) extends ordinary linear regression to handle response variables that follow non-Gaussian distributions, such as counts, binary outcomes, or positive continuous values. It uses a link function to connect the linear predictor to the expected value of the response. The GLM framework unifies regression for exponential family distributions.

---

## Poisson GLM

The Poisson GLM is a generalized linear model that uses a log link function and Poisson distribution for modeling count data -- non-negative integers such as event counts, defects per unit, or arrivals per hour.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Count target (non-negative integers) |
| `x` | LIST(DOUBLE) | Yes | \-  | Predictors |
| `options` | MAP | No  | \-  | fit\_intercept, max\_iterations, tolerance |

### Example

```
SELECT    production_line,    (model).coefficients[2] as temperature_effect,    exp((model).coefficients[2]) as rate_ratio,    (model).p_values[2] as pvalueFROM (    SELECT        production_line,        anofox_stats_poisson_fit_agg(            defect_count,            [temperature, humidity, shift_hours]        ) as model    FROM quality_data    GROUP BY production_line);
```

**Interpretation:** Coefficients are on log scale. `exp(coefficient)` = rate ratio (multiplicative effect).

---

## ALM - Augmented Linear Model

The Augmented Linear Model (ALM) is a robust regression framework that replaces the Gaussian error assumption with any of 24 alternative distributions. By fitting the model under the correct error distribution (e.g., Student-t for heavy tails, Huber for outlier resistance, Weibull for survival data), ALM produces more efficient and reliable estimates than OLS when the normality assumption is violated.

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | DOUBLE | Yes | \-  | Target values |
| `x` | LIST(DOUBLE) | Yes | \-  | Predictors |
| `options` | MAP | Yes | \-  | distribution, fit\_intercept, max\_iterations, tolerance |

### Supported Distributions

| Distribution | Use Case |
| --- | --- |
| `normal` | Standard regression |
| `student_t` | Heavy tails, outliers |
| `cauchy` | Extreme outliers |
| `laplace` | LAD (median) regression |
| `huber` | Robust with breakdown |
| `weibull` | Survival, reliability |
| `gamma` | Positive, right-skewed |
| `log_normal` | Multiplicative errors |

### Example

```
SELECT anofox_stats_alm_fit_agg(    revenue,    [marketing_spend, competitor_activity],    MAP {        'distribution': 'student_t',        'fit_intercept': 'true'    }) as modelFROM sales_with_outliers;
```

**When to use ALM:**

*   Data has heavy tails or outliers
*   Non-normal error distributions
*   Robust estimation needed

---

([🍪 Cookie Settings](#cookie-settings))

# Linear Models

AnoFox implements 3 classical least squares variants -- OLS, WLS (Weighted), and RLS (Recursive) -- each available in 4 SQL integration patterns: scalar fit, aggregate fit with GROUP BY, window predict with OVER, and batch predict. OLS provides full inference output including coefficients, t-statistics, p-values, confidence intervals, R-squared, and information criteria (AIC/BIC). WLS handles heteroscedastic data where variance differs across observations. RLS enables real-time streaming analysis with a configurable forgetting factor between 0.9 and 1.0.

Linear regression models estimate the relationship between a dependent variable and one or more independent variables by fitting a linear equation to observed data. The three variants here differ in how they weight observations: OLS (Ordinary Least Squares) weights all observations equally, WLS (Weighted Least Squares) assigns different weights to handle heteroscedasticity, and RLS (Recursive Least Squares) adapts weights over time for streaming data.

---

## OLS - Ordinary Least Squares

The statistical workhorse for linear regression with full inference support.

### Variants

*   **Scalar Fit:** `anofox_stats_ols_fit(y, x, [options]) -> STRUCT`
*   **Aggregate Fit:** `anofox_stats_ols_fit_agg(y, x, [options]) -> STRUCT`
*   **Window Predict:** `anofox_stats_ols_fit_predict(y, x, [options]) OVER (...) -> STRUCT`
*   **Batch Predict:** `anofox_stats_ols_predict_agg(y, x, [options]) -> LIST(STRUCT)`

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Target values |
| `x` | LIST(LIST(DOUBLE)) / LIST(DOUBLE) | Yes | \-  | Predictor matrix |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `fit_intercept` | BOOLEAN | true | Include intercept term |
| `compute_inference` | BOOLEAN | false | Compute t-stats, p-values, CIs |
| `confidence_level` | DOUBLE | 0.95 | Confidence level for intervals |

### Example

```
SELECT    (model).coefficients[2] as marketing_effect,    (model).p_values[2] as p_value,    (model).r_squared as fitFROM (    SELECT anofox_stats_ols_fit_agg(        revenue,        [marketing_spend, seasonality_index],        MAP {            'fit_intercept': 'true',            'compute_inference': 'true',            'confidence_level': '0.95'        }    ) as model    FROM sales_data);
```

---

## WLS - Weighted Least Squares

Weighted Least Squares is a regression method that assigns different weights to observations, giving more influence to observations with lower variance. Heteroscedasticity refers to the condition where the variance of residuals is not constant across observations, which violates an OLS assumption and can lead to inefficient estimates.

### Variants

*   **Scalar Fit:** `anofox_stats_wls_fit(y, x, weights, [options]) -> STRUCT`
*   **Aggregate Fit:** `anofox_stats_wls_fit_agg(y, x, weight, [options]) -> STRUCT`
*   **Window Predict:** `anofox_stats_wls_fit_predict(y, x, weight, [options]) OVER (...) -> STRUCT`
*   **Batch Predict:** `anofox_stats_wls_predict_agg(y, x, weight, [options]) -> LIST(STRUCT)`

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Target values |
| `x` | LIST(LIST(DOUBLE)) / LIST(DOUBLE) | Yes | \-  | Predictor matrix |
| `weights` / `weight` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Observation weights |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `fit_intercept` | BOOLEAN | true | Include intercept term |
| `compute_inference` | BOOLEAN | false | Compute inference statistics |
| `confidence_level` | DOUBLE | 0.95 | Confidence level |

### Example

```
SELECT anofox_stats_wls_fit_agg(    y,    [x1, x2],    sample_size,    MAP {'compute_inference': 'true'}) as modelFROM aggregated_data;
```

**When to use WLS:**

*   Residual variance changes with predictor values
*   Aggregated data with different sample sizes
*   Known measurement precision differences

---

## RLS - Recursive Least Squares

Recursive Least Squares is an adaptive regression method that updates coefficient estimates incrementally as new observations arrive, without needing to re-fit the entire model. The forgetting factor controls how quickly old observations lose influence, making RLS suitable for streaming data and non-stationary relationships where the true coefficients change over time (concept drift).

### Variants

*   **Scalar Fit:** `anofox_stats_rls_fit(y, x, [options]) -> STRUCT`
*   **Aggregate Fit:** `anofox_stats_rls_fit_agg(y, x, [options]) -> STRUCT`
*   **Window Predict:** `anofox_stats_rls_fit_predict(y, x, [options]) OVER (...) -> STRUCT`
*   **Batch Predict:** `anofox_stats_rls_predict_agg(y, x, [options]) -> LIST(STRUCT)`

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Target values |
| `x` | LIST(LIST(DOUBLE)) / LIST(DOUBLE) | Yes | \-  | Predictor matrix |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `forgetting_factor` | DOUBLE | 1.0 | Weight decay (0.9-1.0) |
| `fit_intercept` | BOOLEAN | true | Include intercept term |
| `initial_p_diagonal` | DOUBLE | 100.0 | Initial covariance diagonal |

### Example

```
SELECT    timestamp,    (adaptive_model).coefficients[2] as current_betaFROM (    SELECT        timestamp,        anofox_stats_rls_fit_predict(            y,            [x],            MAP {'forgetting_factor': '0.95'}        ) OVER (            ORDER BY timestamp            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW        ) as adaptive_model    FROM sensor_data);
```

**When to use RLS:**

*   Real-time model updates
*   Non-stationary relationships
*   Concept drift detection

The 3 linear model types cover distinct use cases in the S&OP pipeline: OLS is the standard choice for batch regression with full inference output. WLS is essential when aggregating data from sources with different sample sizes or measurement precision -- common when combining regional sales data where some regions have 10x more observations. RLS with a forgetting factor of 0.95 gives a 20-observation effective memory window, enabling real-time coefficient tracking for sensor data, pricing models, or any relationship that shifts over time.

---

([🍪 Cookie Settings](#cookie-settings))

# Regularized Models

AnoFox provides 2 regularized regression methods: Ridge (L2 penalty) and Elastic Net (combined L1+L2 penalty). Ridge shrinks coefficients toward zero without eliminating them, making it the right choice when VIF exceeds 5 for any predictor. Elastic Net adds L1 regularization for automatic feature selection -- controlled by the `l1_ratio` parameter (0 = pure Ridge, 1 = pure Lasso) -- with convergence tunable via `max_iterations` (default: 1,000) and `tolerance` (default: 1e-4). Both methods support all 4 SQL integration patterns including GROUP BY for per-segment models.

## Regularized regression refers to a family of techniques that add penalty terms to the loss function to prevent overfitting and handle multicollinearity. The penalty shrinks coefficient estimates toward zero, trading a small increase in bias for a larger reduction in variance.

## Ridge - L2 Regularization

Ridge regression adds an L2 penalty (sum of squared coefficients) to the OLS loss function, shrinking all coefficients toward zero without eliminating any. The regularization strength is controlled by the `alpha` parameter: higher values produce more shrinkage.

### Variants

*   **Scalar Fit:** `anofox_stats_ridge_fit(y, x, alpha, [options]) -> STRUCT`
*   **Aggregate Fit:** `anofox_stats_ridge_fit_agg(y, x, alpha, [options]) -> STRUCT`
*   **Window Predict:** `anofox_stats_ridge_fit_predict(y, x, [options]) OVER (...) -> STRUCT`
*   **Batch Predict:** `anofox_stats_ridge_predict_agg(y, x, [options]) -> LIST(STRUCT)`

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Target values |
| `x` | LIST(LIST(DOUBLE)) / LIST(DOUBLE) | Yes | \-  | Predictor matrix |
| `alpha` | DOUBLE | Yes | \-  | Regularization strength (0.01-10.0) |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `fit_intercept` | BOOLEAN | true | Include intercept term |
| `compute_inference` | BOOLEAN | false | Compute inference statistics |
| `confidence_level` | DOUBLE | 0.95 | Confidence level |

### Example

```
SELECT    region,    (model).r_squared as fit,    (model).coefficients[2] as price_elasticityFROM (    SELECT        region,        anofox_stats_ridge_fit_agg(            sales,            [price, promotion],            0.5,            MAP {'compute_inference': 'true'}        ) as model    FROM regional_sales    GROUP BY region);
```

**When to use Ridge:**

*   VIF > 5 for any predictor
*   More predictors than observations
*   Coefficients unstable across samples

---

## Elastic Net - Combined L1+L2

Elastic Net combines L1 (Lasso) and L2 (Ridge) penalties, enabling both coefficient shrinkage and automatic feature selection. The `l1_ratio` parameter controls the balance: 0 is pure Ridge, 1 is pure Lasso, and values in between provide a mixture.

### Variants

*   **Scalar Fit:** `anofox_stats_elasticnet_fit(y, x, alpha, l1_ratio, [options]) -> STRUCT`
*   **Aggregate Fit:** `anofox_stats_elasticnet_fit_agg(y, x, alpha, l1_ratio, [options]) -> STRUCT`
*   **Window Predict:** `anofox_stats_elasticnet_fit_predict(y, x, [options]) OVER (...) -> STRUCT`
*   **Batch Predict:** `anofox_stats_elasticnet_predict_agg(y, x, [options]) -> LIST(STRUCT)`

### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `y` | LIST(DOUBLE) / DOUBLE | Yes | \-  | Target values |
| `x` | LIST(LIST(DOUBLE)) / LIST(DOUBLE) | Yes | \-  | Predictor matrix |
| `alpha` | DOUBLE | Yes | \-  | Overall regularization (0.01-10.0) |
| `l1_ratio` | DOUBLE | Yes | \-  | L1/L2 balance (0=Ridge, 1=Lasso) |
| `options` | MAP | No  | \-  | Configuration options |

**Options MAP:**

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `fit_intercept` | BOOLEAN | true | Include intercept term |
| `max_iterations` | INTEGER | 1000 | Convergence limit |
| `tolerance` | DOUBLE | 1e-4 | Convergence threshold |

### Example

```
SELECT anofox_stats_elasticnet_fit_agg(    y,    [x1, x2, x3, x4, x5],    0.5,   -- alpha: regularization strength    0.7    -- l1_ratio: 70% L1, 30% L2) as modelFROM high_dim_data;
```

**When to use Elastic Net:**

*   High-dimensional data (many predictors)
*   Feature selection needed (sparse solutions)
*   Correlated predictors with variable selection

---

([🍪 Cookie Settings](#cookie-settings))

# Anomaly Detection

AnoFox Tabular provides 6 anomaly detection functions spanning 2 statistical methods (Z-Score with O(n) single-pass computation, IQR with O(n log n) sorting) and 4 ML methods (Isolation Forest with Extended IF and SCiForest variants supporting up to 500 trees and 10,000 sample size, multivariate Isolation Forest, DBSCAN density-based clustering, and OutlierTree for explainable anomaly detection with human-readable explanations). OutlierTree is uniquely valuable because it provides context-aware outlier explanations -- for example, identifying that a salary is anomalous specifically for a Junior Developer role, not just anomalous in absolute terms.

Statistical and machine learning methods for outlier detection.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_metric_zscore`](#anofox_tab_metric_zscore)) | Z-score outlier | `(table, column, threshold) -> TABLE` |
| ([`anofox_tab_metric_iqr`](#anofox_tab_metric_iqr)) | IQR outlier | `(table, column, multiplier) -> TABLE` |
| ([`anofox_tab_metric_isolation_forest`](#anofox_tab_metric_isolation_forest)) | Univariate ML | `(table, column, ...) -> TABLE` |
| ([`anofox_tab_metric_isolation_forest_multivariate`](#anofox_tab_metric_isolation_forest_multivariate)) | Multivariate ML | `(table, columns, ...) -> TABLE` |
| ([`anofox_tab_metric_dbscan`](#anofox_tab_metric_dbscan)) | Density-based | `(table, column, eps, min_pts, mode) -> TABLE` |
| ([`anofox_tab_outlier_tree`](#anofox_tab_outlier_tree)) | Explainable outliers | `(table, columns, mode) -> TABLE` |

---

## Statistical Methods (2)

### anofox\_tab\_metric\_zscore

Detect outliers using Z-score method.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | \-  | Table to analyze |
| `column_name` | VARCHAR | Yes | \-  | Column to analyze |
| `threshold` | DOUBLE | No  | 3.0 | Z-score threshold |

**Method:** z = (value - mean) / std\_dev, anomaly = |z| > threshold

**Thresholds:**

| Threshold | % in Distribution | Outliers |
| --- | --- | --- |
| 2.0 | 95% | 5% of data |
| 2.5 | 98.8% | 1.2% of data |
| 3.0 | 99.7% | 0.3% of data |

#### Example

```
SELECT * FROM anofox_tab_metric_zscore('transactions', 'amount', 3.0)WHERE is_outlier = true;
```

### anofox\_tab\_metric\_iqr

Detect outliers using Interquartile Range.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | \-  | Table to analyze |
| `column_name` | VARCHAR | Yes | \-  | Column to analyze |
| `multiplier` | DOUBLE | No  | 1.5 | IQR multiplier |

**Method:**

*   IQR = Q3 - Q1
*   lower = Q1 - multiplier \* IQR
*   upper = Q3 + multiplier \* IQR
*   anomaly = value < lower OR value > upper

#### Example

```
SELECT * FROM anofox_tab_metric_iqr('transactions', 'amount', 1.5);
```

**Pros over Z-score:**

*   Robust to extreme outliers
*   Works with any distribution
*   Doesn't require normality assumption

---

## Isolation Forest (Enhanced)

Industry-grade anomaly detection with advanced capabilities.

### Core Capabilities

*   **Categorical Support** - Auto-detect VARCHAR columns with random subset splitting
*   **Extended IF (ndim)** - Hyperplane splits for diagonal/curved anomaly patterns
*   **Density Scoring** - Alternative metric based on points-to-volume ratio
*   **Sample Weights** - Weighted sampling for imbalanced datasets
*   **SCiForest** - Information-gain guided splitting

### anofox\_tab\_metric\_isolation\_forest

Univariate Isolation Forest for single column anomaly detection.

#### Full Signature

```
anofox_tab_metric_isolation_forest(    table_name VARCHAR,    column_name VARCHAR,    n_trees BIGINT,           -- 1-500, default 100    sample_size BIGINT,       -- 1-10000, default 256    contamination DOUBLE,     -- 0.0-0.5, default 0.1    output_mode VARCHAR,      -- 'summary' or 'scores'    ndim BIGINT,              -- 1-N, default 1 (Extended IF)    coef_type VARCHAR,        -- 'uniform' or 'normal'    scoring_metric VARCHAR,   -- 'depth', 'density', or 'adj_depth'    weight_column VARCHAR,    -- Column for sample weights (NULL = uniform)    ntry BIGINT,              -- 1-100, default 1 (SCiForest)    prob_pick_avg_gain DOUBLE -- 0.0-1.0, default 0.0) -> TABLE
```

#### Parameters

| Parameter | Range | Default | Description |
| --- | --- | --- | --- |
| `n_trees` | 1-500 | 100 | Number of isolation trees |
| `sample_size` | 1-10000 | 256 | Subsample size per tree |
| `contamination` | 0.0-0.5 | 0.1 | Expected anomaly fraction |
| `output_mode` | \-  | 'scores' | `'summary'` or `'scores'` |
| `ndim` | 1-N | 1   | Hyperplane dimensions (Extended IF) |
| `coef_type` | \-  | 'uniform' | `'uniform'` or `'normal'` |
| `scoring_metric` | \-  | 'depth' | `'depth'`, `'density'`, `'adj_depth'` |
| `weight_column` | \-  | NULL | Sample weight column name |
| `ntry` | 1-100 | 1   | Split candidates (SCiForest) |
| `prob_pick_avg_gain` | 0.0-1.0 | 0.0 | Gain-based selection probability |

**Contamination guidance:**

| Value | Expectation | Use Case |
| --- | --- | --- |
| 0.01 | 1% anomalies | High quality data |
| 0.05 | 5% anomalies | Normal data |
| 0.10 | 10% anomalies | Messy data |

#### Examples

```
-- Basic usage (backward compatible)SELECT * FROM anofox_tab_metric_isolation_forest(    'sales_data', 'amount', 100, 256, 0.1, 'scores') WHERE is_anomaly = true;-- Extended IF with hyperplane splitsSELECT * FROM anofox_tab_metric_isolation_forest(    'transactions', 'amount', 100, 256, 0.1, 'scores',    3,          -- ndim: 3-dimensional hyperplanes    'normal',   -- coef_type: normal distribution    'depth'     -- scoring_metric);-- SCiForest with gain-based selectionSELECT * FROM anofox_tab_metric_isolation_forest(    'events', 'value', 100, 256, 0.05, 'scores',    1, 'uniform', 'depth',    NULL,       -- weight_column    10,         -- ntry: evaluate 10 candidates    0.5         -- prob_pick_avg_gain: 50% gain-based);
```

### anofox\_tab\_metric\_isolation\_forest\_multivariate

Multivariate Isolation Forest for multiple column anomaly detection.

#### Parameters

Same as univariate, but `column_name` becomes `columns` (comma-separated).

#### Example

```
-- Basic multivariateSELECT * FROM anofox_tab_metric_isolation_forest_multivariate(    'customer_events', 'purchase_amount, session_duration, page_views',    100, 256, 0.1, 'scores') ORDER BY anomaly_score DESC LIMIT 10;-- Extended IF with density scoringSELECT * FROM anofox_tab_metric_isolation_forest_multivariate(    'transactions', 'amount, quantity, duration',    100, 256, 0.05, 'scores',    3,          -- ndim    'normal',   -- coef_type    'density'   -- scoring_metric: density-based);
```

---

## DBSCAN Clustering

### anofox\_tab\_metric\_dbscan

Detect outliers using Density-Based Clustering.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | \-  | Table to analyze |
| `column_name` | VARCHAR | Yes | \-  | Column to analyze |
| `eps` | DOUBLE | No  | 0.5 | Neighborhood radius |
| `min_pts` | BIGINT | No  | 5   | Min points for dense region |
| `output_mode` | VARCHAR | No  | 'clusters' | `'summary'` or `'clusters'` |

**Point Classifications:**

*   **CORE** - Dense region centers (low anomaly score)
*   **BORDER** - Cluster edges (moderate anomaly score)
*   **NOISE** - Isolated outliers (high anomaly score: 1.0)

#### Example

```
SELECT * FROM anofox_tab_metric_dbscan(    'transactions', 'amount', 10.0, 5, 'clusters') WHERE point_type = 'NOISE';
```

### anofox\_tab\_metric\_dbscan\_multivariate

Multivariate DBSCAN for multiple columns.

```
SELECT * FROM anofox_tab_metric_dbscan_multivariate(    'customer_events', 'amount, frequency',    0.5, 10, 'clusters') WHERE point_type = 'NOISE';
```

---

## OutlierTree (Explainable Anomaly Detection)

Unlike Isolation Forest which provides anomaly scores, OutlierTree generates **human-readable explanations** for why specific values are outliers based on conditional distributions.

### Key Features

*   Detects outliers in context (e.g., "salary is high _for a Junior Developer_")
*   Returns natural language explanations with statistical backing
*   Uses robust statistics (median + MAD) resistant to outliers
*   Supports both numeric and categorical columns

### anofox\_tab\_outlier\_tree

#### Signatures

```
-- Simple (3 params, uses defaults)anofox_tab_outlier_tree(table_name, columns, output_mode) -> TABLE-- Full (9 params)anofox_tab_outlier_tree(    table_name VARCHAR,    columns VARCHAR,    output_mode VARCHAR,    max_depth INTEGER,    max_perc_outliers DOUBLE,    min_size_numeric INTEGER,    min_size_categ INTEGER,    z_norm DOUBLE,    z_outlier DOUBLE) -> TABLE
```

#### Parameters

| Parameter | Type | Default | Description |
| --- | --- | --- | --- |
| `table_name` | VARCHAR | required | Source table name |
| `columns` | VARCHAR | required | Comma-separated column names |
| `output_mode` | VARCHAR | 'summary' | `'summary'` or `'outliers'` |
| `max_depth` | INTEGER | 4   | Maximum tree depth |
| `max_perc_outliers` | DOUBLE | 0.01 | Max fraction of outliers per cluster |
| `min_size_numeric` | INTEGER | 25  | Min cluster size for numeric targets |
| `min_size_categ` | INTEGER | 75  | Min cluster size for categorical targets |
| `z_norm` | DOUBLE | 2.67 | Z-threshold for confidence intervals |
| `z_outlier` | DOUBLE | 8.0 | Z-threshold for outlier flagging |

#### Output Modes

**Summary Mode Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `status` | VARCHAR | `'pass'` or `'fail'` |
| `total_rows` | BIGINT | Rows analyzed |
| `outlier_count` | BIGINT | Outliers detected |
| `columns_analyzed` | INTEGER | Columns analyzed |
| `clusters_evaluated` | BIGINT | Clusters evaluated |
| `message` | VARCHAR | Summary message |

**Outliers Mode Returns:**

| Column | Type | Description |
| --- | --- | --- |
| `row_id` | BIGINT | Row index (1-indexed) |
| `column_name` | VARCHAR | Column with outlier |
| `outlier_value` | VARCHAR | The anomalous value |
| `cluster_mean` | DOUBLE | Mean in cluster |
| `cluster_sd` | DOUBLE | SD in cluster |
| `z_score` | DOUBLE | Robust z-score |
| `lower_bound` | DOUBLE | Lower CI bound |
| `upper_bound` | DOUBLE | Upper CI bound |
| `conditions` | VARCHAR | JSON array of split conditions |
| `explanation` | VARCHAR | Human-readable explanation |
| `outlier_score` | DOUBLE | Rarity score |

#### Examples

```
-- Summary mode: quick pass/fail checkSELECT * FROM anofox_tab_outlier_tree('employees', 'department,salary', 'summary');-- Outliers mode: get detailed explanationsSELECT row_id, column_name, outlier_value, explanationFROM anofox_tab_outlier_tree('employees', 'job_title,salary,years_exp', 'outliers');-- Returns explanations like:-- "Value 150000 for column 'salary' is unusually high (expected: 52333 +/- 7413)--  when job_title = 'Junior Developer'"-- With custom parameters for small datasetsSELECT * FROM anofox_tab_outlier_tree(    'test_data', 'category,value', 'outliers',    4,          -- max_depth    0.5,        -- max_perc_outliers    3,          -- min_size_numeric    2,          -- min_size_categ    2.67,       -- z_norm    3.0         -- z_outlier);
```

---

## Method Comparison

| Method | Type | Speed | Interpretation | Use Case |
| --- | --- | --- | --- | --- |
| Z-Score | Statistical | Fast | Easy | Single column, normal dist |
| IQR | Statistical | Fast | Easy | Single column, robust |
| Isolation Forest | ML  | Medium | Hard | Multiple columns, complex patterns |
| Extended IF | ML  | Medium | Hard | Diagonal/curved anomalies |
| DBSCAN | ML  | Slow | Medium | Density-based clusters |
| OutlierTree | ML  | Medium | **Easy** | Explainable, contextual outliers |

---

## Decision Tree

```
How many features?+-- ONE column|   +-- Normal distribution? -> Z-Score|   +-- Any distribution? -> IQR|+-- MULTIPLE columns    +-- Need explanations? -> OutlierTree    +-- Detect complex patterns? -> Extended IF (ndim > 1)    +-- Find density clusters? -> DBSCAN    +-- Maximize detection? -> Isolation Forest
```

---

## Performance Characteristics

| Function | Speed | Notes |
| --- | --- | --- |
| Z-Score | O(n) | Single pass |
| IQR | O(n log n) | Sorting for quartiles |
| Isolation Forest | O(n log n) | Tree construction |
| Extended IF | O(n log n) | Additional hyperplane calc |
| DBSCAN | O(n^2) | Distance calculations |
| OutlierTree | O(n log n) | Tree building + scoring |

The Isolation Forest implementation in AnoFox goes beyond the standard algorithm with 3 advanced modes: Extended IF uses multi-dimensional hyperplane splits (configurable via `ndim`) to detect diagonal and curved anomaly boundaries that standard axis-aligned splits miss. SCiForest adds information-gain guided splitting (configurable via `ntry` and `prob_pick_avg_gain`) for more targeted anomaly detection. Density scoring provides an alternative metric based on points-to-volume ratio instead of tree depth. All 3 modes are accessible through the same function signature with optional parameters.

---

([🍪 Cookie Settings](#cookie-settings))

# Financial Functions

Money amount and currency functions for financial applications.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_money_is_positive`](#anofox_tab_money_is_positive)) | Check positive | `(amount) -> BOOLEAN` |
| ([`anofox_tab_money_is_valid_amount`](#anofox_tab_money_is_valid_amount)) | Check range | `(amount, min, max) -> BOOLEAN` |
| ([`anofox_tab_money_format`](#anofox_tab_money_format)) | Format currency | `(amount, currency) -> VARCHAR` |
| ([`anofox_tab_currency_is_valid`](#anofox_tab_currency_is_valid)) | Validate ISO code | `(code) -> BOOLEAN` |
| ([`anofox_tab_currency_convert`](#anofox_tab_currency_convert)) | Convert currency | `(amount, from, to) -> DECIMAL` |
| `anofox_tab_money_percentage` | Calculate percentage | `(amount, percent) -> DECIMAL` |

---

## Amount Functions (6)

### anofox\_tab\_money\_is\_positive

Check if amount is positive (> 0).

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `amount` | DECIMAL | Yes | \-  | Amount to check |

#### Example

```
SELECT    amount,    anofox_tab_money_is_positive(amount) as is_positiveFROM transactions;-- 100.50  | true-- 0.00    | false-- -50.00  | false
```

### anofox\_tab\_money\_is\_valid\_amount

Check if amount is within valid range.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `amount` | DECIMAL | Yes | \-  | Amount to validate |
| `min` | DECIMAL | Yes | \-  | Minimum valid |
| `max` | DECIMAL | Yes | \-  | Maximum valid |

#### Example

```
SELECT    anofox_tab_money_is_valid_amount(100.50, 0.01, 999999.99),    -- true    anofox_tab_money_is_valid_amount(0.00, 0.01, 999999.99);      -- false
```

### anofox\_tab\_money\_normalize

Normalize amount to specific decimal places.

```
SELECT    anofox_tab_money_normalize(100.556, 2),   -- 100.56    anofox_tab_money_normalize(100.1, 2);     -- 100.10
```

### anofox\_tab\_money\_format

Format amount with currency symbol.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `amount` | DECIMAL | Yes | \-  | Amount to format |
| `currency` | VARCHAR | Yes | \-  | ISO 4217 code |

#### Example

```
SELECT    anofox_tab_money_format(100.50, 'USD'),  -- '$100.50'    anofox_tab_money_format(100.50, 'EUR'),  -- '€100,50'    anofox_tab_money_format(100.50, 'GBP');  -- '£100.50'
```

### anofox\_tab\_money\_abs

Get absolute value.

```
SELECT anofox_tab_money_abs(-100.50);  -- 100.50
```

### anofox\_tab\_money\_round

Round to nearest cent.

```
SELECT anofox_tab_money_round(100.556, 2);  -- 100.56
```

---

## Currency Functions (5)

### anofox\_tab\_currency\_is\_valid

Check if currency code is valid ISO 4217.

```
SELECT    anofox_tab_currency_is_valid('USD'),  -- true    anofox_tab_currency_is_valid('XXX');  -- false
```

### anofox\_tab\_currency\_get\_name

Get currency name.

```
SELECT anofox_tab_currency_get_name('USD');  -- 'US Dollar'
```

### anofox\_tab\_currency\_get\_symbol

Get currency symbol.

```
SELECT anofox_tab_currency_get_symbol('EUR');  -- '€'
```

### anofox\_tab\_currency\_get\_decimals

Get standard decimal places.

```
SELECT    anofox_tab_currency_get_decimals('USD'),  -- 2    anofox_tab_currency_get_decimals('JPY');  -- 0
```

### anofox\_tab\_currency\_is\_crypto

Check if cryptocurrency.

```
SELECT    anofox_tab_currency_is_crypto('BTC'),  -- true    anofox_tab_currency_is_crypto('USD');  -- false
```

---

## Conversion & Arithmetic (6)

### anofox\_tab\_currency\_convert

Convert amount from one currency to another.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `amount` | DECIMAL | Yes | \-  | Amount to convert |
| `from_code` | VARCHAR | Yes | \-  | Source currency |
| `to_code` | VARCHAR | Yes | \-  | Target currency |

**Speed:** ~50ms (calls exchange rate API)

#### Example

```
SELECT    anofox_tab_currency_convert(100, 'USD', 'EUR'),   -- ~92    anofox_tab_currency_convert(100, 'USD', 'USD');   -- 100
```

### Arithmetic Functions

```
-- Add amountsSELECT anofox_tab_money_add(100.50, 50.25);  -- 150.75-- SubtractSELECT anofox_tab_money_subtract(100.50, 50.25);  -- 50.25-- Multiply by factorSELECT anofox_tab_money_multiply(100.00, 1.1);  -- 110.00-- DivideSELECT anofox_tab_money_divide(100.00, 2);  -- 50.00-- PercentageSELECT anofox_tab_money_percentage(100.00, 10);  -- 10.00
```

---

## Practical Patterns

### Multi-Currency Consolidation

```
CREATE TABLE transactions_eur ASSELECT    transaction_id,    amount,    currency,    anofox_tab_currency_convert(amount, currency, 'EUR') as amount_eurFROM transactionsWHERE anofox_tab_currency_is_valid(currency);
```

### Amount Validation Pipeline

```
SELECT    invoice_id,    amount,    currency,    CASE        WHEN NOT anofox_tab_currency_is_valid(currency) THEN 'INVALID_CURRENCY'        WHEN NOT anofox_tab_money_is_positive(amount) THEN 'NEGATIVE_AMOUNT'        WHEN NOT anofox_tab_money_is_valid_amount(amount, 0.01, 999999.99) THEN 'OUT_OF_RANGE'        ELSE 'VALID'    END as validation_status,    anofox_tab_money_format(amount, currency) as formatted_amountFROM invoices;
```

### Tax Calculation

```
SELECT    order_id,    subtotal,    tax_rate,    anofox_tab_money_percentage(subtotal, tax_rate) as tax_amount,    anofox_tab_money_add(subtotal, anofox_tab_money_percentage(subtotal, tax_rate)) as totalFROM orders;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Installation

Install the AnoFox Tabular extension for DuckDB. You can install from the community registry with a single command or build from source for custom configurations.

---

## Prerequisites

*   **DuckDB 0.8.1+** (installed and in PATH)
*   **C++17 compiler** (GCC 7+, Clang 5+, MSVC 2017+)
*   **CMake 3.15+**
*   **vcpkg** (for dependency management, recommended)

---

## Option 1: Community Registry (Easiest)

```
# Install from official DuckDB registryduckdb -c "INSTALL anofox_tabular; LOAD anofox_tabular;"# Verify installationduckdb -c "SELECT anofox_email_validate('test@example.com', 'regex');"
```

**Expected output:**

```
true
```

---

## Option 2: Build from Source

### 1\. Clone the Repository

```
git clone https://github.com/datazoo/anofox.gitcd anofox/tabular
```

### 2\. Install Dependencies

**macOS:**

```
brew install cmake vcpkg
```

**Ubuntu/Debian:**

```
sudo apt-get install cmake build-essential libssl-devgit clone https://github.com/Microsoft/vcpkg.gitcd vcpkg && ./bootstrap-vcpkg.sh
```

**Windows:**

```
choco install cmake vcpkg
```

### 3\. Build the Extension

```
mkdir build && cd buildcmake .. -DVCPKG_ROOT=/path/to/vcpkgcmake --build . --config Release
```

### 4\. Install to DuckDB

```
# Copy the compiled extensioncp build/anofox_tabular.duckdb_extension ~/.duckdb/extensions/# Verifyduckdb -c "LOAD anofox_tabular; SELECT version();"
```

---

## Verification Script

Copy and run this SQL to verify installation:

```
-- Test email validationSELECT    anofox_email_validate('john@example.com', 'regex') as regex_check,    anofox_email_validate('jane@company.org', 'dns') as dns_check;-- Test VAT validationSELECT anofox_vat_is_valid('DE123456789');-- Test money validationSELECT anofox_money_is_positive(99.99);-- Test phone validationSELECT anofox_phone_format('+1-555-0123', 'US');-- Test data quality metricsSELECT COUNT(*) as total, COUNT(*) FILTER (WHERE value IS NOT NULL) as non_nullFROM (SELECT NULL as value UNION ALL SELECT 1);
```

**Expected output:** All TRUE or valid results.

---

## Troubleshooting

### Extension Won't Load

```
Error: Unknown function: anofox_email_validate
```

**Solution:**

```
-- Explicitly load the extensionLOAD anofox_tabular;-- Check what's installedSELECT * FROM duckdb_functions() WHERE function_name LIKE '%anofox%';
```

### Build Compilation Fails

```
error: 'optional' is not a member of 'std'
```

**Solution:** Ensure C++17 is enabled:

```
cmake .. -DCMAKE_CXX_STANDARD=17
```

### Dependency Issues

```
could not find libpostal or libphonenumber
```

**Solution:** Use vcpkg:

```
./vcpkg install libpostal libphonenumbercmake .. -DVCPKG_ROOT=/path/to/vcpkg
```

### Windows MSVC Build

If CMake doesn't auto-detect the compiler:

```
cmake .. -G "Visual Studio 16 2019" -A x64
```

---

([🍪 Cookie Settings](#cookie-settings))

# Data Operations

Dataset comparison and diffing functions.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_table_diff_hash`](#anofox_tab_table_diff_hash)) | Hash-based diff | `(table1, table2) -> TABLE` |
| ([`anofox_tab_table_diff_join`](#anofox_tab_table_diff_join)) | Join-based diff | `(table1, table2, key_columns[]) -> TABLE` |

---

## Diffing Functions (2)

### anofox\_tab\_table\_diff\_hash

Hash-based table diff for change detection.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table1` | VARCHAR | Yes | \-  | Source table name |
| `table2` | VARCHAR | Yes | \-  | Comparison table name |

**Speed:** O(n) per table, hash-based comparison

#### Output

| Column | Type | Description |
| --- | --- | --- |
| `table_name` | VARCHAR | Which table |
| `row_number` | INTEGER | Row identifier |
| `change_type` | VARCHAR | ADDED, MODIFIED, DELETED |
| `reason` | VARCHAR | Explanation |

#### Example

```
SELECT * FROM anofox_tab_table_diff_hash('customers', 'customers_backup');
```

**Pros:**

*   Fast (hash-based)
*   Works with any column types
*   Good for "what changed?" queries

**Cons:**

*   Doesn't show which columns changed
*   Can't see detailed differences

### anofox\_tab\_table\_diff\_join

Join-based table diff with detailed column-level differences.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table1` | VARCHAR | Yes | \-  | Source table |
| `table2` | VARCHAR | Yes | \-  | Comparison table |
| `key_columns` | VARCHAR\[\] | Yes | \-  | Columns to join on |

**Speed:** O(n log n) join-based

#### Output

| Column | Type | Description |
| --- | --- | --- |
| `key_column` | ANY | Key value |
| `*_match` | BOOLEAN | TRUE if column matches |
| `status` | VARCHAR | SAME, DIFFERENT, ONLY\_IN\_TABLE1, ONLY\_IN\_TABLE2 |

#### Example

```
SELECT * FROM anofox_tab_table_diff_join(    'customers',    'customers_backup',    ARRAY['customer_id']);
```

**Pros:**

*   Column-level detail
*   See exactly which columns differ
*   Better for data validation

---

## Practical Patterns

### Validate Before ETL

```
SELECT COUNT(*) as mismatch_countFROM anofox_tab_table_diff_join(    'raw_import',    'customers',    ARRAY['customer_id'])WHERE status != 'SAME';
```

### Change Tracking

```
SELECT    customer_id,    'EMAIL_CHANGED' as change_typeFROM anofox_tab_table_diff_join(    'customers_today',    'customers_yesterday',    ARRAY['customer_id'])WHERE email_match = FALSE;
```

### Data Sync Verification

```
SELECT    COUNT(*) as total_rows,    COUNT(*) FILTER (WHERE status = 'SAME') as matching_rows,    COUNT(*) FILTER (WHERE status = 'DIFFERENT') as different_rows,    ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'SAME') / COUNT(*), 2) as sync_percentageFROM anofox_tab_table_diff_join(    'customers_primary',    'customers_replica',    ARRAY['customer_id']);
```

### Migration Validation

```
SELECT    COUNT(*) as total_differences,    COUNT(*) FILTER (WHERE status = 'ONLY_IN_TABLE1') as lost_in_migration,    COUNT(*) FILTER (WHERE status = 'ONLY_IN_TABLE2') as added_in_migration,    COUNT(*) FILTER (WHERE status = 'DIFFERENT') as data_corruptionFROM anofox_tab_table_diff_join(    'customers_legacy_system',    'customers_new_system',    ARRAY['customer_id'])WHERE status != 'SAME';
```

---

## Combining with Validation

```
SELECT    diff.*,    anofox_tab_vat_is_valid(new.vat_id) as vat_validFROM anofox_tab_table_diff_join(    'customers_old',    'customers_new',    ARRAY['customer_id']) diffLEFT JOIN customers_new new USING (customer_id)WHERE diff.status = 'DIFFERENT'  AND NOT anofox_tab_vat_is_valid(new.vat_id);
```

---

## Performance Considerations

### Hash-Based (Faster)

```
-- Use when you just need "are they different?"SELECT * FROM anofox_tab_table_diff_hash('t1', 't2');
```

**Best for:** Large tables (10M+ rows), any column types

### Join-Based (More Detail)

```
-- Use when you need to know what changedSELECT * FROM anofox_tab_table_diff_join('t1', 't2', ARRAY['id']);
```

**Best for:** Medium tables (< 10M rows), detailed reports

---

([🍪 Cookie Settings](#cookie-settings))

# Function Finder

Quick reference for all 78 Tabular functions.

## Categories

[### Validation

Email, phone, VAT, and address validation APIs

`email``phone``VAT``postal`+22 more](./validation)[### Financial

Money amounts, currency validation, and conversion

`money``currency``convert``format`+13 more](./financial)[### PII Detection

Detect and mask personal data in text

`detect``mask``scan``validate`+16 more](./pii)[### Quality

Data profiling and quality metrics

`nullness``distinctness``freshness``volume`](./quality)[### Anomaly

Statistical and ML outlier detection

`Z-Score``IQR``IsolationForest``OutlierTree`](./anomaly)[### Operations

Dataset comparison and diffing functions

`diff_hash``diff_join`](./operations)

## All Functions

All CategoryAddressAnomalyCurrencyEmailMoneyOperationsPIIPhoneQualityVAT

| Function | Description | SQL Signature | Category |
| --- | --- | --- | --- |
| `anofox_tab_email_is_valid` | Validate email | `(email, mode) -> BOOLEAN` | Email |
| `anofox_tab_email_validate` | Detailed validation | `(email, mode) -> STRUCT` | Email |
| `anofox_tab_email_config` | Show config | `() -> TABLE` | Email |
| `anofox_tab_postal_parse_address` | Parse address | `(address) -> STRUCT` | Address |
| `anofox_tab_postal_expand_address` | Expand variants | `(address) -> LIST` | Address |
| `anofox_tab_postal_status` | Library status | `() -> TABLE` | Address |
| `anofox_tab_postal_load_data` | Download data | `() -> BOOLEAN` | Address |
| `anofox_tab_phonenumber_parse` | Parse number | `(number, region) -> STRUCT` | Phone |
| `anofox_tab_phonenumber_format` | Format number | `(number, region, format) -> VARCHAR` | Phone |
| `anofox_tab_phonenumber_region` | Get region | `(number, region) -> VARCHAR` | Phone |
| `anofox_tab_phonenumber_is_valid` | Validate full | `(number, region) -> BOOLEAN` | Phone |
| `anofox_tab_phonenumber_is_possible` | Quick check | `(number, region) -> BOOLEAN` | Phone |
| `anofox_tab_phonenumber_is_valid_for_region` | Region-specific | `(number, region) -> BOOLEAN` | Phone |
| `anofox_tab_phonenumber_match` | Fuzzy match | `(num1, num2, region) -> VARCHAR` | Phone |
| `anofox_tab_phonenumber_example` | Example number | `(region) -> VARCHAR` | Phone |
| `anofox_tab_phonenumber_status` | Library status | `() -> TABLE` | Phone |
| `anofox_tab_money` | Create money | `(amount, currency) -> STRUCT` | Money |
| `anofox_tab_money_from_cents` | From cents | `(cents, currency) -> STRUCT` | Money |
| `anofox_tab_money_amount` | Extract amount | `(money) -> DOUBLE` | Money |
| `anofox_tab_money_currency` | Extract currency | `(money) -> VARCHAR` | Money |
| `anofox_tab_money_format` | Format display | `(money, style) -> VARCHAR` | Money |
| `anofox_tab_money_is_positive` | Check positive | `(money) -> BOOLEAN` | Money |
| `anofox_tab_money_is_negative` | Check negative | `(money) -> BOOLEAN` | Money |
| `anofox_tab_money_is_zero` | Check zero | `(money) -> BOOLEAN` | Money |
| `anofox_tab_money_abs` | Absolute value | `(money) -> STRUCT` | Money |
| `anofox_tab_money_add` | Add amounts | `(money1, money2) -> STRUCT` | Money |
| `anofox_tab_money_subtract` | Subtract | `(money1, money2) -> STRUCT` | Money |
| `anofox_tab_money_multiply` | Multiply | `(money, factor) -> STRUCT` | Money |
| `anofox_tab_money_in_range` | Check range | `(money, min, max) -> BOOLEAN` | Money |
| `anofox_tab_money_same_currency` | Same currency | `(money1, money2) -> BOOLEAN` | Money |
| `anofox_tab_is_valid_currency` | Valid code | `(code) -> BOOLEAN` | Currency |
| `anofox_tab_currency_symbol` | Get symbol | `(code) -> VARCHAR` | Currency |
| `anofox_tab_currency_name` | Get name | `(code) -> VARCHAR` | Currency |
| `anofox_tab_vat` | Parse VAT | `(vat) -> STRUCT` | VAT |
| `anofox_tab_vat_is_valid` | Full validation | `(vat) -> BOOLEAN` | VAT |
| `anofox_tab_vat_is_valid_syntax` | Syntax check | `(vat) -> BOOLEAN` | VAT |
| `anofox_tab_vat_normalize` | Normalize | `(vat) -> VARCHAR` | VAT |
| `anofox_tab_vat_split` | Split parts | `(vat) -> STRUCT` | VAT |
| `anofox_tab_vat_exists` | Valid prefix | `(vat) -> BOOLEAN` | VAT |
| `anofox_tab_vat_is_eu_member` | EU member | `(country) -> BOOLEAN` | VAT |
| `anofox_tab_vat_country_name` | Country name | `(code) -> VARCHAR` | VAT |
| `anofox_tab_vat_format` | Format | `(vat, style) -> VARCHAR` | VAT |
| `anofox_tab_is_valid_vat_country` | Valid country | `(code) -> BOOLEAN` | VAT |
| `anofox_tab_pii_detect` | Detect all PII | `(text) -> VARCHAR` | PII |
| `anofox_tab_pii_mask` | Mask PII | `(text, strategy) -> VARCHAR` | PII |
| `anofox_tab_pii_contains` | Has PII? | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_count` | Count PII | `(text) -> BIGINT` | PII |
| `anofox_tab_pii_scan_table` | Scan table | `(table, cols) -> TABLE` | PII |
| `anofox_tab_pii_audit_table` | Audit table | `(table, cols) -> TABLE` | PII |
| `anofox_tab_pii_status` | List types | `() -> TABLE` | PII |
| `anofox_tab_pii_detect_emails` | Detect emails | `(text) -> LIST` | PII |
| `anofox_tab_pii_detect_phones` | Detect phones | `(text) -> LIST` | PII |
| `anofox_tab_pii_detect_credit_cards` | Detect cards | `(text) -> LIST` | PII |
| `anofox_tab_pii_detect_ssns` | Detect SSNs | `(text) -> LIST` | PII |
| `anofox_tab_pii_detect_names` | Detect names | `(text) -> LIST` | PII |
| `anofox_tab_pii_detect_ibans` | Detect IBANs | `(text) -> LIST` | PII |
| `anofox_tab_pii_is_valid_ssn` | Validate SSN | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_is_valid_iban` | Validate IBAN | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_is_valid_credit_card` | Validate card | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_is_valid_nino` | Validate NINO | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_is_valid_de_tax_id` | Validate DE Tax | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_is_valid_crypto_address` | Validate crypto | `(text) -> BOOLEAN` | PII |
| `anofox_tab_pii_mask_column` | Type-specific mask | `(val, type, strat) -> VARCHAR` | PII |
| `anofox_tab_pii_redact_column` | Redact column | `(val, strat) -> VARCHAR` | PII |
| `anofox_tab_metric_volume` | Row count | `(table, min, max) -> TABLE` | Quality |
| `anofox_tab_metric_null_rate` | Null percentage | `(table, col, max) -> TABLE` | Quality |
| `anofox_tab_metric_distinct_count` | Cardinality | `(table, col, min, max) -> TABLE` | Quality |
| `anofox_tab_metric_schema` | Schema check | `(table, cols[]) -> TABLE` | Quality |
| `anofox_tab_metric_freshness` | Data recency | `(table, col, max_age) -> TABLE` | Quality |
| `anofox_tab_metric_zscore` | Z-score outliers | `(table, col, threshold) -> TABLE` | Anomaly |
| `anofox_tab_metric_iqr` | IQR outliers | `(table, col, mult) -> TABLE` | Anomaly |
| `anofox_tab_metric_isolation_forest` | Isolation Forest | `(table, col, ...) -> TABLE` | Anomaly |
| `anofox_tab_metric_isolation_forest_multivariate` | Multivariate IF | `(table, cols, ...) -> TABLE` | Anomaly |
| `anofox_tab_metric_dbscan` | DBSCAN | `(table, col, eps, min_pts) -> TABLE` | Anomaly |
| `anofox_tab_metric_dbscan_multivariate` | Multivariate DBSCAN | `(table, cols, eps, min_pts) -> TABLE` | Anomaly |
| `anofox_tab_outlier_tree` | Explainable outliers | `(table, cols, mode) -> TABLE` | Anomaly |
| `anofox_tab_diff_hashdiff` | Hash-based diff | `(src, tgt, keys[]) -> TABLE` | Operations |
| `anofox_tab_diff_joindiff` | Join-based diff | `(src, tgt, keys[]) -> TABLE` | Operations |

Showing 78 of 78

---

## Performance Tips

*   **Fast functions** (<1ms): Regex, format, syntax checks, PII pattern matching
*   **Medium functions** (~100ms): DNS, format validation
*   **Slow functions** (~500ms): SMTP, carrier lookup
*   **ML functions** (~10ms/KB): NER-based PII detection (names, orgs)
*   **Data-dependent** (O(n)): Anomaly detection, metrics, table scans
*   **Parallelizable:** All functions work on single columns

---

([🍪 Cookie Settings](#cookie-settings))

# PII Detection Functions

AnoFox Tabular provides 20 PII functions that detect and mask 17 types of personally identifiable information: 13 pattern-based types (email, phone, credit card, SSN, IBAN, IP address, URL, German tax ID, MAC address, UK NINO, US passport, API keys, crypto addresses) and 4 NER-based types (person names, organizations, locations, miscellaneous entities). The module offers 4 masking strategies (redact, partial, asterisk, SHA-256 hash), 6 type-specific detection functions, 6 validation functions with checksum verification (Luhn for credit cards, MOD-97 for IBAN), and table-level scanning at approximately 100ms per 1,000 rows.

Detect and mask Personally Identifiable Information (PII) in text data.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_pii_detect`](#anofox_tab_pii_detect)) | Detect all PII | `(text) -> VARCHAR (JSON)` |
| ([`anofox_tab_pii_mask`](#anofox_tab_pii_mask)) | Mask PII in text | `(text, strategy) -> VARCHAR` |
| ([`anofox_tab_pii_contains`](#anofox_tab_pii_contains)) | Check for any PII | `(text) -> BOOLEAN` |
| ([`anofox_tab_pii_count`](#anofox_tab_pii_count)) | Count PII matches | `(text) -> BIGINT` |
| ([`anofox_tab_pii_scan_table`](#anofox_tab_pii_scan_table)) | Scan table for PII | `(table, columns) -> TABLE` |
| ([`anofox_tab_pii_audit_table`](#anofox_tab_pii_audit_table)) | Row-level audit | `(table, columns) -> TABLE` |

---

## Supported PII Types

The module detects 17 types of PII:

### Pattern-Based Detection (13 types)

| Type | Description | Example |
| --- | --- | --- |
| `EMAIL` | Email addresses | `user@example.com` |
| `PHONE` | Phone numbers | `+1-555-123-4567` |
| `CREDIT_CARD` | Credit card numbers | `4111-1111-1111-1111` |
| `US_SSN` | US Social Security Numbers | `123-45-6789` |
| `IBAN` | International Bank Account Numbers | `DE89370400440532013000` |
| `IP_ADDRESS` | IPv4 addresses | `192.168.1.100` |
| `URL` | HTTP/HTTPS URLs | `https://example.com` |
| `DE_TAX_ID` | German Tax ID | `12345678901` |
| `MAC_ADDRESS` | Network MAC addresses | `00:1A:2B:3C:4D:5E` |
| `UK_NINO` | UK National Insurance Number | `AB123456C` |
| `US_PASSPORT` | US Passport numbers | `A12345678` |
| `API_KEY` | API keys (AWS, GitHub) | `AKIAIOSFODNN7EXAMPLE` |
| `CRYPTO_ADDRESS` | Bitcoin/Ethereum addresses | `1A1zP1eP5QGefi2DMPTfTL5SLmv7DivfNa` |

### NER-Based Detection (4 types)

These require OpenVINO and are available on Linux x64 and macOS only:

| Type | Description | Example |
| --- | --- | --- |
| `NAME` | Person names | `John Smith` |
| `ORGANIZATION` | Company/org names | `Microsoft`, `Google Inc` |
| `LOCATION` | Geographic locations | `Paris`, `New York` |
| `MISC` | Miscellaneous entities | `French`, `Nobel Prize` |

---

## Masking Strategies

| Strategy | Description | Example Output |
| --- | --- | --- |
| `redact` | Replace with type label (default) | `[EMAIL]`, `[US_SSN]` |
| `partial` | Show partial value | `te**@example.com`, `***-**-6789` |
| `asterisk` | Replace with asterisks | `****************` |
| `hash` | Replace with SHA-256 hash | `a1b2c3d4e5f6...` |

---

## Core Detection Functions (4)

### anofox\_tab\_pii\_detect

Detect all PII in text and return matches as JSON.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `text` | VARCHAR | Yes | \-  | Text to scan for PII |

**Returns:** JSON array of detected PII with type, text, position, and confidence

#### Example

```
SELECT anofox_tab_pii_detect('Contact: john.doe@example.com, SSN: 123-45-6789');-- Returns: [--   {"type":"EMAIL","text":"john.doe@example.com","start":9,"end":29,"confidence":1.00},--   {"type":"US_SSN","text":"123-45-6789","start":36,"end":47,"confidence":1.00}-- ]
```

### anofox\_tab\_pii\_mask

Mask all detected PII using specified strategy.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `text` | VARCHAR | Yes | \-  | Text containing PII |
| `strategy` | VARCHAR | No  | 'redact' | Masking strategy |

**Returns:** Text with PII masked

#### Example

```
-- Redact (default)SELECT anofox_tab_pii_mask('Email: test@example.com');-- Output: 'Email: [EMAIL]'-- Partial maskingSELECT anofox_tab_pii_mask('SSN: 123-45-6789', 'partial');-- Output: 'SSN: ***-**-6789'-- Asterisk maskingSELECT anofox_tab_pii_mask('Card: 4111111111111111', 'asterisk');-- Output: 'Card: ****************'
```

### anofox\_tab\_pii\_contains

Check if text contains any PII.

```
SELECT anofox_tab_pii_contains('Contact us at support@company.com');-- Output: trueSELECT anofox_tab_pii_contains('Hello, world!');-- Output: false
```

### anofox\_tab\_pii\_count

Count PII occurrences in text.

```
SELECT anofox_tab_pii_count('Email: a@b.com, SSN: 123-45-6789, Card: 4111111111111111');-- Output: 3
```

---

## Table Scanning Functions (3)

### anofox\_tab\_pii\_scan\_table

Scan entire table for PII across VARCHAR columns.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | \-  | Table to scan |
| `columns` | VARCHAR | No  | NULL | Comma-separated columns (all VARCHAR if omitted) |

**Returns:** Table with column\_name, pii\_type, match\_count, sample\_values, confidence

#### Example

```
-- Scan all columnsSELECT * FROM anofox_tab_pii_scan_table('users');-- Scan specific columnsSELECT * FROM anofox_tab_pii_scan_table('users', 'email,phone');-- Find high-risk columnsSELECT column_name, COUNT(DISTINCT pii_type) as pii_typesFROM anofox_tab_pii_scan_table('customer_data')GROUP BY column_nameHAVING COUNT(DISTINCT pii_type) >= 2;
```

### anofox\_tab\_pii\_audit\_table

Row-level PII audit with masking for detailed inspection.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `table_name` | VARCHAR | Yes | \-  | Table to audit |
| `columns` | VARCHAR | No  | NULL | Comma-separated columns |

**Returns:** Table with row\_id, column\_name, pii\_type, original\_value, masked\_value, positions, confidence

#### Example

```
SELECT * FROM anofox_tab_pii_audit_table('customer_data')WHERE pii_type = 'US_SSN';
```

### anofox\_tab\_pii\_status

List all registered PII recognizers.

```
SELECT * FROM anofox_tab_pii_status() ORDER BY pii_type;-- Shows all 17 PII types with their recognizer info
```

---

## Type-Specific Detection Functions (6)

For targeted scans when you only need specific PII types:

| Function | Returns |
| --- | --- |
| `anofox_tab_pii_detect_emails(text)` | List of EMAIL matches |
| `anofox_tab_pii_detect_phones(text)` | List of PHONE matches |
| `anofox_tab_pii_detect_credit_cards(text)` | List of CREDIT\_CARD matches |
| `anofox_tab_pii_detect_ssns(text)` | List of US\_SSN matches |
| `anofox_tab_pii_detect_names(text)` | List of NAME matches (NER-based) |
| `anofox_tab_pii_detect_ibans(text)` | List of IBAN matches |

### Example

```
SELECT anofox_tab_pii_detect_emails('Contact: john@example.com and jane@example.com');-- Returns only EMAIL matches
```

---

## Validation Functions (6)

Validate specific PII formats with checksum verification:

| Function | Description |
| --- | --- |
| `anofox_tab_pii_is_valid_ssn(text)` | Validate US SSN format |
| `anofox_tab_pii_is_valid_iban(text)` | Validate IBAN with MOD-97 checksum |
| `anofox_tab_pii_is_valid_credit_card(text)` | Validate with Luhn algorithm |
| `anofox_tab_pii_is_valid_nino(text)` | Validate UK National Insurance |
| `anofox_tab_pii_is_valid_de_tax_id(text)` | Validate German Tax ID |
| `anofox_tab_pii_is_valid_crypto_address(text)` | Validate Bitcoin/Ethereum |

### Example

```
SELECT anofox_tab_pii_is_valid_credit_card('4111111111111111');  -- true (Luhn valid)SELECT anofox_tab_pii_is_valid_iban('DE89370400440532013000');   -- true (MOD-97 valid)
```

---

## Advanced Masking Functions (2)

### anofox\_tab\_pii\_mask\_column

Type-specific masking for a single PII type.

```
SELECT anofox_tab_pii_mask_column('123-45-6789', 'US_SSN', 'partial');-- Returns: ***-**-6789
```

### anofox\_tab\_pii\_redact\_column

Mask all PII in text with optional strategy.

```
SELECT anofox_tab_pii_redact_column('Email: test@example.com');-- Returns: Email: [EMAIL]
```

---

## Practical Patterns

### Data Privacy Audit

```
-- Find all tables with PII exposureWITH pii_summary AS (    SELECT        'customers' as table_name,        column_name,        pii_type,        match_count    FROM anofox_tab_pii_scan_table('customers')    UNION ALL    SELECT        'orders' as table_name,        column_name,        pii_type,        match_count    FROM anofox_tab_pii_scan_table('orders'))SELECT    table_name,    COUNT(DISTINCT pii_type) as pii_types,    SUM(match_count) as total_matchesFROM pii_summaryGROUP BY table_nameORDER BY pii_types DESC;
```

### GDPR Compliance Check

```
-- Identify columns needing anonymizationSELECT    column_name,    pii_type,    match_count,    CASE        WHEN pii_type IN ('US_SSN', 'CREDIT_CARD', 'IBAN') THEN 'HIGH'        WHEN pii_type IN ('EMAIL', 'PHONE', 'NAME') THEN 'MEDIUM'        ELSE 'LOW'    END as risk_levelFROM anofox_tab_pii_scan_table('customer_data')ORDER BY    CASE risk_level        WHEN 'HIGH' THEN 1        WHEN 'MEDIUM' THEN 2        ELSE 3    END;
```

### Pre-Migration PII Discovery

```
-- Before migrating data, identify all PIISELECT    column_name,    pii_type,    sample_values[1] as example,    match_countFROM anofox_tab_pii_scan_table('legacy_data')WHERE match_count > 0ORDER BY match_count DESC;
```

---

## Configuration Options

| Option | Type | Default | Description |
| --- | --- | --- | --- |
| `anofox_pii_min_confidence` | DOUBLE | 0.5 | Minimum confidence threshold |
| `anofox_pii_default_mask_strategy` | VARCHAR | 'redact' | Default masking strategy |
| `anofox_pii_enabled_types` | VARCHAR | ''  | Comma-separated enabled types (empty = all) |
| `anofox_pii_deep_validation` | BOOLEAN | false | Enable deep email/phone validation |

```
SET anofox_pii_min_confidence = 0.7;SET anofox_pii_default_mask_strategy = 'partial';
```

---

## Platform Availability

| Platform | Pattern-based (13 types) | NER-based (4 types) |
| --- | --- | --- |
| Linux x64 (glibc) | Yes | Yes |
| macOS x64 | Yes | Yes |
| macOS ARM64 | Yes | Yes |
| Windows x64 | Yes | No (dictionary fallback for NAME) |
| Linux ARM64 | Yes | No  |
| Linux musl (Alpine) | Yes | No  |

---

## Performance Characteristics

| Function | Speed | Notes |
| --- | --- | --- |
| Pattern detection | ~1ms/KB | Fast regex matching |
| NER detection | ~10ms/KB | ML model inference |
| Table scan | ~100ms/1K rows | Depends on text length |
| Validation | <1ms | Checksum calculations |

The 20-function PII module covers the full GDPR compliance workflow: discovery (scan tables to find PII columns), classification (identify which of 17 PII types are present), risk assessment (categorize findings as HIGH for SSN/credit cards/IBAN, MEDIUM for email/phone/names, LOW for others), and remediation (mask using redact, partial, asterisk, or SHA-256 hash strategies). Pattern-based detection at ~1ms/KB handles bulk scanning, while checksum validation (Luhn, MOD-97) provides mathematical proof of format correctness.

---

([🍪 Cookie Settings](#cookie-settings))

# Quality Metrics

AnoFox Tabular provides 7 quality metric functions that quantify data health across 6 dimensions: nullness (percentage of NULL values, where above 20% indicates an unusable column), distinctness (percentage of unique values for identifying key columns vs. categorical columns), freshness (days since last update, where above 7 days signals stale data), volume (row count), consistency (format uniformity, where below 70% signals a need for normalization), and schema match (compatibility check between two tables). These metrics combine into a composite quality score for automated data governance.

Functions for profiling and measuring data quality.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_metric_nullness`](#anofox_tab_metric_nullness)) | % NULL values | `(column) -> DECIMAL` |
| ([`anofox_tab_metric_distinctness`](#anofox_tab_metric_distinctness)) | % unique values | `(column) -> DECIMAL` |
| ([`anofox_tab_metric_freshness`](#anofox_tab_metric_freshness)) | Days since update | `(date_column) -> DECIMAL` |
| ([`anofox_tab_metric_volume`](#anofox_tab_metric_volume)) | Row count | `(table_name) -> INTEGER` |
| ([`anofox_tab_metric_consistency`](#anofox_tab_metric_consistency)) | Format consistency | `(column) -> DECIMAL` |
| ([`anofox_tab_metric_schema_match`](#anofox_tab_metric_schema_match)) | Schema compatible | `(table1, table2) -> BOOLEAN` |

---

## Core Metrics (7 functions)

### anofox\_tab\_metric\_nullness

Measure percentage of NULL values in a column.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `column` | ANY | Yes | \-  | Column to analyze |

**Returns:** Percentage of NULLs (0.0-100.0)

**Interpretation:**

*   0%: No missing values (complete)
*   1-5%: Mostly complete
*   5-20%: Significant gaps
*   > 20%: Unusable column
    

#### Example

```
SELECT anofox_tab_metric_nullness(email) as null_percentageFROM customers;-- Output: 2.5 (2.5% of emails are NULL)
```

### anofox\_tab\_metric\_distinctness

Measure percentage of unique values.

**Interpretation:**

*   100%: All unique (key column)
*   50-100%: High diversity
*   10-50%: Moderate categorization
*   < 10%: Heavily concentrated

#### Example

```
SELECT    anofox_tab_metric_distinctness(customer_id) as id_uniqueness,    anofox_tab_metric_distinctness(country) as country_uniquenessFROM customers;-- id_uniqueness: 100.0-- country_uniqueness: 5.2
```

### anofox\_tab\_metric\_freshness

Measure days since most recent update.

**Interpretation:**

*   0-1 days: Real-time (fresh)
*   1-7 days: Current (acceptable)
*   7-30 days: Stale (aging)
*   > 30 days: Very stale
    

#### Example

```
SELECT anofox_tab_metric_freshness(updated_at) as days_since_updateFROM customers;-- Output: 3
```

### anofox\_tab\_metric\_volume

Get row count of table.

```
SELECT anofox_tab_metric_volume('customers') as customer_count;-- Output: 125000
```

### anofox\_tab\_metric\_consistency

Measure format consistency across column.

**Interpretation:**

*   > 95%: Highly consistent
    
*   80-95%: Mostly consistent
*   50-80%: Inconsistent (may need normalization)
*   < 50%: Very inconsistent

#### Example

```
SELECT anofox_tab_metric_consistency(phone) as phone_format_consistencyFROM customers;-- Output: 75.5
```

### anofox\_tab\_metric\_schema\_match

Check if two tables have compatible schemas.

```
SELECT anofox_tab_metric_schema_match('customers', 'customers_backup');-- Output: true
```

---

## Data Quality Profile

Create a comprehensive health check:

```
SELECT    'customers' as table_name,    anofox_tab_metric_row_count('customers') as row_count,    anofox_tab_metric_nullness(email) as email_nullness_pct,    anofox_tab_metric_nullness(phone) as phone_nullness_pct,    anofox_tab_metric_distinctness(customer_id) as id_uniqueness_pct,    anofox_tab_metric_freshness(updated_at) as days_since_update,    anofox_tab_metric_consistency(phone) as phone_format_consistency_pctFROM customers;
```

---

## Quality Scoring

Create a composite quality score:

```
SELECT    'customers' as table_name,    ROUND(        (            CASE WHEN anofox_tab_metric_nullness(email) < 5 THEN 25 ELSE 0 END +            CASE WHEN anofox_tab_metric_distinctness(customer_id) > 95 THEN 25 ELSE 0 END +            CASE WHEN anofox_tab_metric_freshness(updated_at) < 7 THEN 25 ELSE 0 END +            CASE WHEN anofox_tab_metric_consistency(phone) > 80 THEN 25 ELSE 0 END        ),        1    ) as quality_scoreFROM customers;
```

---

## Quality Thresholds

| Metric | Green | Yellow | Red |
| --- | --- | --- | --- |
| Nullness | < 5% | 5-10% | \> 10% |
| Uniqueness (ID) | \> 99% | 95-99% | < 95% |
| Freshness | < 1 day | 1-7 days | \> 7 days |
| Consistency | \> 90% | 70-90% | < 70% |

---

The quality threshold framework provides actionable boundaries for automated governance: nullness below 5% is green (complete data), 5-10% is yellow (acceptable with imputation), above 10% is red (unreliable column). For ID columns, distinctness below 95% signals duplicate records. Freshness above 7 days flags stale data that may produce outdated forecasts. These thresholds translate directly into SQL CASE expressions for automated alerting and data pipeline gates.

## Alerting Rules

```
SELECT    CASE        WHEN anofox_tab_metric_nullness(email) > 10 THEN 'Alert: Email nullness > 10%'        ELSE 'OK'    END as email_alert,    CASE        WHEN anofox_tab_metric_freshness(updated_at) > 30 THEN 'Alert: Data is > 30 days old'        ELSE 'OK'    END as freshness_alertFROM customers;
```

---

([🍪 Cookie Settings](#cookie-settings))

# Validation Functions

AnoFox Tabular provides 26 validation functions across 4 domains: 3 email functions (regex in under 1ms, DNS lookup in ~100ms, SMTP verification in ~500ms), 4 address functions powered by libpostal for international address normalization, 9 phone functions built on Google's libphonenumber library with carrier detection and mobile identification, and 10 VAT functions covering format validation, EU VIES verification (~200ms), company name lookup, and standard rate retrieval for 29 European countries. All offline-capable functions execute in under 1ms per record.

Email, address, phone, and VAT validation APIs.

## Quick Reference

| Function | Description | SQL Signature |
| --- | --- | --- |
| ([`anofox_tab_email_validate`](#anofox_tab_email_validate)) | Validate email | `(email, mode) -> BOOLEAN` |
| ([`anofox_tab_phone_validate`](#anofox_tab_phone_validate)) | Validate phone | `(number, country) -> BOOLEAN` |
| ([`anofox_tab_vat_is_valid`](#anofox_tab_vat_is_valid)) | Validate VAT format | `(vat_id) -> BOOLEAN` |
| ([`anofox_tab_vat_verify_eu`](#anofox_tab_vat_verify_eu)) | Verify EU VIES | `(vat_id) -> BOOLEAN` |
| ([`anofox_tab_address_validate`](#anofox_tab_address_validate)) | Validate address | `(address) -> BOOLEAN` |
| ([`anofox_tab_postal_code_validate`](#anofox_tab_postal_code_validate)) | Validate postal | `(code, country) -> BOOLEAN` |

---

## Email Functions (3)

### anofox\_tab\_email\_validate

Validate email using one of three modes.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `email` | VARCHAR | Yes | \-  | Email address to validate |
| `mode` | VARCHAR | Yes | \-  | 'regex' (<1ms), 'dns' (~100ms), 'smtp' (~500ms) |

**Returns:** TRUE if valid, FALSE if invalid, NULL if error

#### Example

```
SELECT    email,    anofox_tab_email_validate(email, 'regex') as syntax_valid,    anofox_tab_email_validate(email, 'dns') as domain_valid,    anofox_tab_email_validate(email, 'smtp') as mailbox_validFROM customers;
```

### anofox\_tab\_email\_extract\_domain

Extract domain from email address.

```
SELECT anofox_tab_email_extract_domain('john@example.com');-- Output: example.com
```

### anofox\_tab\_email\_normalize

Normalize email (lowercase, trim whitespace).

```
SELECT anofox_tab_email_normalize('  John@Example.COM  ');-- Output: john@example.com
```

---

## Address Functions (4)

### anofox\_tab\_address\_validate

Validate street address format.

```
SELECT anofox_tab_address_validate('123 Main St, New York, NY 10001');-- Output: true
```

### anofox\_tab\_postal\_code\_validate

Validate postal code for country.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `code` | VARCHAR | Yes | \-  | Postal code |
| `country` | VARCHAR | Yes | \-  | 2-letter country code |

#### Example

```
SELECT    anofox_tab_postal_code_validate('90210', 'US'),     -- true    anofox_tab_postal_code_validate('SW1A 1AA', 'GB'),  -- true    anofox_tab_postal_code_validate('75001', 'FR');     -- true
```

### anofox\_tab\_address\_get\_state

Extract state or province.

```
SELECT anofox_tab_address_get_state('123 Main St, New York, NY 10001');-- Output: NY
```

### anofox\_tab\_address\_get\_city

Extract city.

```
SELECT anofox_tab_address_get_city('123 Main St, New York, NY 10001');-- Output: New York
```

---

## Phone Functions (9)

### anofox\_tab\_phone\_format

Format phone number to standard format.

#### Parameters

| Parameter | Type | Required | Default | Description |
| --- | --- | --- | --- | --- |
| `number` | VARCHAR | Yes | \-  | Phone number (any format) |
| `country` | VARCHAR | Yes | \-  | 2-letter country code |

#### Example

```
SELECT    anofox_tab_phone_format('+1-555-0123', 'US'),      -- '+1 555 0123'    anofox_tab_phone_format('0203 946 0958', 'GB');    -- '+44 20 3946 0958'
```

### anofox\_tab\_phone\_validate

Validate phone number for country.

```
SELECT    anofox_tab_phone_validate('+1-555-0123', 'US'),   -- true    anofox_tab_phone_validate('123', 'US');           -- false
```

### anofox\_tab\_phone\_get\_country\_code

Extract country code.

```
SELECT anofox_tab_phone_get_country_code('+44 20 3946 0958');-- Output: GB
```

### anofox\_tab\_phone\_is\_mobile

Check if mobile number.

```
SELECT anofox_tab_phone_is_mobile('+1 555 0123', 'US');  -- true
```

### anofox\_tab\_phone\_get\_carrier

Get carrier/operator name.

```
SELECT anofox_tab_phone_get_carrier('+1 555 0123', 'US');-- Output: 'Verizon' (example)
```

### anofox\_tab\_phone\_normalize

Normalize to E.164 format.

```
SELECT anofox_tab_phone_normalize('555-0123');-- Output: +15550123
```

---

## VAT Functions (10)

### anofox\_tab\_vat\_is\_valid

Validate VAT ID format.

```
SELECT    anofox_tab_vat_is_valid('DE123456789'),   -- true    anofox_tab_vat_is_valid('INVALID');       -- false
```

### anofox\_tab\_vat\_get\_country

Extract country code from VAT ID.

```
SELECT anofox_tab_vat_get_country('DE123456789');-- Output: DE
```

### anofox\_tab\_vat\_verify\_eu

Verify VAT ID in EU VIES system (~200ms).

```
SELECT anofox_tab_vat_verify_eu('DE123456789');-- Output: true (if registered)
```

### anofox\_tab\_vat\_get\_company\_name

Get company name from VIES registration (~200ms).

```
SELECT anofox_tab_vat_get_company_name('DE123456789');-- Output: 'Example GmbH'
```

### anofox\_tab\_vat\_format

Format VAT ID to country standard.

```
SELECT anofox_tab_vat_format('de123456789');-- Output: 'DE123456789'
```

### anofox\_tab\_vat\_is\_eu\_member

Check if country is EU member.

```
SELECT    anofox_tab_vat_is_eu_member('DE'),  -- true    anofox_tab_vat_is_eu_member('US'),  -- false    anofox_tab_vat_is_eu_member('GB');  -- false (post-Brexit)
```

### anofox\_tab\_vat\_get\_standard\_rate

Get standard VAT rate for country.

```
SELECT    anofox_tab_vat_get_standard_rate('DE'),  -- 0.19 (19%)    anofox_tab_vat_get_standard_rate('FR');  -- 0.20 (20%)
```

---

## Performance Characteristics

| Function | Speed | Network | Cacheable |
| --- | --- | --- | --- |
| Email regex | <1ms | No  | Yes |
| Email DNS | ~100ms | Yes | Yes |
| Email SMTP | ~500ms | Yes | No  |
| Phone | ~5-50ms | No  | No  |
| VAT format | <1ms | No  | Yes |
| VAT VIES | ~200ms | Yes | Yes |

The performance profile reveals a clear strategy for production pipelines: use offline validation (regex, format checks) for bulk processing at sub-millisecond speeds, and reserve network-dependent validation (DNS, SMTP, VIES) for targeted verification of flagged records. Email regex validation, phone formatting, and VAT format checks all execute in under 1ms per record, making them suitable for validating millions of rows in a single DuckDB query without external service dependencies.

---

([🍪 Cookie Settings](#cookie-settings))