Anofox Forecast DuckDB Extension — Cheat Sheet

Extension: anofox_forecast v0.4.6 | DuckDB: v1.4.x+ | Dual naming: ts_* and anofox_fcst_ts_*

Installation

LOAD anofox_forecast;
-- All functions available as ts_* and anofox_fcst_ts_* (identical)

Critical Gotchas

1. Seasonality is NOT auto-detected. You must pass seasonal_period explicitly. Detect first with ts_detect_periods_by, then pass to forecasting.

2. DO NOT chain _by table functions in CTEs. Returns 0 rows silently under parallel execution. Always CREATE TABLE between pipeline steps:

   -- BROKEN (0 rows):
   WITH step1 AS (SELECT * FROM ts_fill_gaps_by(...))
   SELECT * FROM ts_fill_nulls_const_by('step1', ...);
   
   -- CORRECT:
   CREATE TABLE step1 AS SELECT * FROM ts_fill_gaps_by(...);
   SELECT * FROM ts_fill_nulls_const_by('step1', ...);
   

3. Model names are case-sensitive. 'AutoETS' works, 'autoets' errors.

4. ts_cv_forecast_by requires pre-created folds. 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.

5. ts_forecast_by requires frequency as 7th positional parameter. No default — you must specify it:

   -- WRONG: missing frequency
   SELECT * FROM ts_forecast_by('sales', id, date, val, 'Naive', 12);
   -- CORRECT:
   SELECT * FROM ts_forecast_by('sales', id, date, val, 'Naive', 12, '1d');
   

6. Metric _by table macros are deprecated. Use scalar functions with GROUP BY:

   -- Deprecated: SELECT * FROM ts_mae_by(...)
   -- Use instead:
   SELECT id, ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae
   FROM results GROUP BY id;
   

7. Always use ORDER BY in LIST() for temporal correctness:

   LIST(value ORDER BY date)  -- correct
   LIST(value)                -- wrong: order not guaranteed
   

Three API Styles

1. Table Macros (primary — use these)

Operate on table names as strings. Handle grouping automatically.

SELECT * FROM ts_forecast_by('sales', product_id, date, revenue, 'AutoETS', 14, '1d',
    MAP{'seasonal_period': '7'});

2. Scalar Functions

Operate on arrays. Use with LIST() aggregation and GROUP BY.

SELECT product_id,
    ts_mae(LIST(actual ORDER BY date), LIST(forecast ORDER BY date)) AS mae
FROM results GROUP BY product_id;

3. Aggregate Functions

Return structs. Access fields with (result).field_name.

SELECT product_id, (ts_stats(LIST(value ORDER BY date))).*
FROM sales GROUP BY product_id;

Parameter Syntax

STRUCT (recommended)

MAP{'seasonal_period': '7'}
MAP{'seasonal_periods': '[7, 365]'}
MAP{'method': 'autoperiod', 'max_period': '28'}

All param values are strings (even numbers). Arrays use JSON syntax: '[7, 365]'.

Frequency Strings

Common Workflows

1. Basic Forecast

-- Forecast 14 days ahead with weekly seasonality
SELECT * FROM ts_forecast_by(
    'sales', product_id, date, revenue,
    'HoltWinters', 14, '1d',
    MAP{'seasonal_period': '7'}
);

2. Data Preparation Pipeline (CREATE TABLE between steps!)

-- Step 1: Fill gaps
CREATE TABLE gaps_filled AS
SELECT * FROM ts_fill_gaps_by('raw_data', product_id, date, value, '1d');

-- Step 2: Impute NULLs
CREATE TABLE nulls_filled AS
SELECT * FROM ts_fill_nulls_const_by('gaps_filled', product_id, date, value, 0.0);

-- Step 3: Drop short series
CREATE TABLE clean_data AS
SELECT * FROM ts_drop_short_by('nulls_filled', product_id, 20);

3. Detect Seasonality → Forecast

-- Step 1: Detect
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('sales', product_id, date, value, MAP{});
-- Returns e.g. primary_period = 7 (weekly)

-- Step 2: Forecast with detected period
SELECT * FROM ts_forecast_by(
    'sales', product_id, date, value,
    'AutoETS', 14, '1d', MAP{'seasonal_period': '7'}
);

4. Cross-Validation & Model Comparison

-- Step 1: Create folds
CREATE TABLE cv_folds AS
SELECT * FROM ts_cv_folds_by('data', unique_id, ds, y, 3, 12, MAP{});

-- Step 2: Forecast per fold (for each model)
CREATE TABLE cv_naive AS
SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y, 'Naive', MAP{});

CREATE TABLE cv_autoets AS
SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y, 'AutoETS',
    MAP{'seasonal_period': '7'});

-- Step 3: Compare metrics
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 rmse
FROM cv_naive GROUP BY ALL
UNION ALL
SELECT '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;

5. Full Production Pipeline

-- 1. Quality check
SELECT id, (stats).length, (stats).n_nulls, (stats).n_gaps
FROM ts_stats_by('raw', product_id, date, value, '1d');

-- 2. Prep (materialize each step!)
CREATE TABLE step1 AS
SELECT * FROM ts_fill_gaps_by('raw', product_id, date, value, '1d');

CREATE TABLE step2 AS
SELECT * FROM ts_fill_nulls_const_by('step1', product_id, date, value, 0.0);

CREATE TABLE clean AS
SELECT * FROM ts_drop_short_by('step2', product_id, 20);

-- 3. Detect seasonality
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('clean', product_id, date, value, MAP{});

-- 4. Backtest
CREATE TABLE cv_folds AS
SELECT * FROM ts_cv_folds_by('clean', product_id, date, value, 5, 14, MAP{});

CREATE TABLE backtest AS
SELECT * FROM ts_cv_forecast_by('cv_folds', product_id, date, value, 'AutoETS',
    MAP{'seasonal_period': '7'});

-- 5. Evaluate
SELECT product_id,
    ts_mae(LIST(y ORDER BY date), LIST(yhat ORDER BY date)) AS mae,
    ts_rmse(LIST(y ORDER BY date), LIST(yhat ORDER BY date)) AS rmse
FROM backtest GROUP BY product_id;

-- 6. Forecast
CREATE TABLE forecasts AS
SELECT * FROM ts_forecast_by('clean', product_id, date, value,
    'AutoETS', 14, '1d', MAP{'seasonal_period': '7'});

-- 7. Conformal intervals
CREATE TABLE calibration AS
SELECT * FROM ts_conformal_calibrate('backtest', value, yhat, {'alpha': 0.1});

SELECT * FROM ts_conformal_apply_by(
    'forecasts', product_id, yhat,
    (SELECT conformity_score FROM calibration)
);

Model Quick Reference (32 Models)

Automatic Selection (6)

Basic (6)

Exponential Smoothing (4)

Theta Methods (5)