anofox-forecast-models
Forecasting models and the `ts_forecast_by` / `ts_forecast_var_by` API surface of the anofox_forecast DuckDB extension. Covers 36 models (baseline, exponential smoothing, state-space ARIMA + Kalman, c
SKILL.mdplugins/anofox-forecast/skills/anofox-forecast-models/SKILL.md
GitHubAnofox Forecast — Models & ts_forecast_by Cheat Sheet
Extension: anofox_forecast (Rust crate anofox-forecast v0.16.2) | DuckDB: v1.4.5 LTS / v1.5.4+ | Dual naming: ts_* and anofox_fcst_ts_*
36 forecasting models exposed by SQL via three call surfaces (table macro, aggregate, scalar) + ts_forecast_var_by for multivariate VAR.
Prediction intervals (yhat_lower/yhat_upper) are each model's own crate-native
interval (corrected formulas since anofox-forecast 0.16.0: Hyndman class-1/2/3 ETS
variances, TBATS innovations state-space variance, GARCH centred on the conditional mean,
etc.) — not a fabricated formula. R forecast parity (FCST-09, Phase 17 plan 17-03):
of the ten core models, Naive/SeasonalNaive/RandomWalkDrift/CrostonClassic,
SESOptimized/ETS 'ANN' (R ses()) and Theta are in full R parity; Holt/ETS 'AAN',
HoltWinters/ETS 'AAA'/ETS 'MAM' and AutoETS are known gaps on AirPassengers with an
identified root cause (mostly R's own optimiser reaching a looser fit, not a defect — see
validation/reference/README.md); AutoARIMA matches R auto.arima's selected order and
point forecast on every series since 0.16.1 (PR #223), and since 0.16.2 (PR #230/#229) its
95% prediction intervals are full R parity too, including AirPassengers — the prior known
interval gap there is resolved. 'SES' (fixed alpha=0.3) is a different, non-optimised model from
SESOptimized — R ses() parity is checked against the latter. Some paths return NULL
bounds regardless (panel models; MSTL/AutoMSTL with regressors). See
docs/api/07-forecasting.md's "Prediction intervals, fitted values and input handling"
section for the full per-model table.
Critical gotchas
Seasonality IS auto-detected when omitted — but explicit is still recommended. When both
seasonal_periodandseasonal_periodsare omitted,ts_forecast_by/ts_cv_forecast_by/ the scalar paths /ts_forecast_aggall auto-detect the seasonal period internally (detect_seasonality) — same rule on every surface (10-10, FCST-06). For models that can run non-seasonally (ETS,AutoETS,ARIMA/AutoARIMA, the Theta family,MFLES), a period that can't be detected falls back to non-seasonal. For models that require a period (SeasonalNaive,SeasonalWindowAverage,HoltWinters,SeasonalES,SeasonalESOptimized,MSTL,AutoMSTL,TBATS,AutoTBATS), a period that can't be detected is a hard error naming the model — never a silent default of 12/2. Runts_detect_periods_byfirst and pass the result explicitly for control and reproducibility over what silent auto-detection would pick. Seeanofox-forecast-detection.Model names are case-sensitive.
'AutoETS'works,'autoets'errors.ts_forecast_byrequires frequency as the 7th positional param. No default:-- WRONG (missing frequency): ts_forecast_by('sales', product_id, ds, y, 'Naive', 12) -- CORRECT: ts_forecast_by('sales', product_id, ds, y, 'Naive', 12, '1d')STRUCT + MAP both work in params. STRUCT keeps numeric params typed (recommended):
-- STRUCT (recommended) ts_forecast_by(..., 'HoltWinters', 12, '1d', {seasonal_period: 7}) -- MAP (all strings, legacy) ts_forecast_by(..., 'HoltWinters', 12, '1d', MAP{'seasonal_period': '7'})The
_byoutput renames the target column toyand addsforecast_step,yhat,yhat_lower,yhat_upper,model_name.ts_forecast_aggtakes(date, value, model, horizon, params)directly — noLIST(...)wrapping.
ts_forecast_by (primary surface)
ts_forecast_by(
source VARCHAR, -- table name (quoted string, NOT a CTE)
group_col COLUMN, -- series identifier (unquoted)
date_col COLUMN, -- date / timestamp (unquoted)
target_col COLUMN, -- value to forecast (unquoted)
method VARCHAR, -- e.g. 'AutoETS', 'Laplace'
horizon INTEGER, -- number of periods ahead
frequency VARCHAR, -- '1d', '1mo', '1h', ...
params MAP or STRUCT -- model-specific config
) → TABLE(group_col, forecast_step INT, ds, yhat DOUBLE, yhat_lower, yhat_upper, model_name)
ts_forecast_panel_by (panel / global models)
Fit-once-emit-many panel API — pools parameter optimization across all series, then predicts per-series. Use when individual series are short but collectively form a large, homogeneous panel. Three panel methods:
ts_forecast_panel_by(
source VARCHAR, -- table name (quoted string)
group_col COLUMN, -- series identifier (unquoted)
date_col COLUMN, -- date / timestamp (unquoted)
target_col COLUMN, -- value to forecast (unquoted)
method VARCHAR, -- 'GlobalETS' | 'GlobalTheta' | 'GlobalCroston'
horizon INTEGER,
frequency VARCHAR, -- '1d', '1mo', ...
params MAP{} -- optional; see below
) → TABLE(group_col, forecast_step INT, date_col TIMESTAMP, yhat DOUBLE, model_name)
Panel methods
| Method | Best for | Key params |
|---|---|---|
'GlobalETS' |
Many related series, shared seasonal dynamics | seasonal_period (0=non-seasonal default), model_pool ('Reduced' default | 'Complete') |
'GlobalTheta' |
Trended panels, minimal config | none (seasonal_period ignored) |
'GlobalCroston' |
Intermittent/spare-parts panels (many zeros) | croston_variant ('Classic' default | 'SBA') |
Critical panel gotchas
- Series dropped below 10 obs: Short series after alignment emit
DROPPED: too_shortrows — not errors. Checkmodel_namein the result. - Minimum 3 series after drop: Fewer than 3 kept series →
InvalidInputException. Ensure your panel has enough history. - Point forecasts only (v1):
yhat_lower/yhat_upperare not populated. Usets_conformal_byseparately if intervals needed. - TABLE arg must be a subselect: The underlying
_ts_forecast_panel_nativeuses the subselect pattern internally. Pass only a table name (quoted string) tots_forecast_panel_by— do NOT pass a CTE or subquery assource. - GlobalCroston with all-zero panel fails: Ensure at least one series has ≥ 2 non-zero demand events in the aligned window.
- GlobalETS
seasonal_period=0→ non-seasonal (Reduced pool,ANN/AAdN/MNN/MAdNcandidates only). Period=1 has the same effect.
Panel quick examples
-- ts_forecast_panel_by needs >= 3 series after alignment -- 'sales' (used elsewhere in
-- this skill) only has 2 product_id groups, so panel examples use their own 3-series fixture.
CREATE OR REPLACE TABLE panel_sales AS
SELECT product_id, (DATE '2024-01-01' + INTERVAL (i) DAY) AS ds,
20.0 + product_id * 3.0 + 0.6 * i + 5.0 * sin(2 * pi() * i / 7.0) AS y
FROM range(0, 40) t(i) CROSS JOIN (SELECT unnest([1, 2, 3]) AS product_id);
CREATE OR REPLACE TABLE panel_spares AS
SELECT item_id, (DATE '2024-01-01' + INTERVAL (i) DAY) AS ds,
CASE WHEN (i + item_id) % 4 = 0 THEN 2.0 + (i % 3) ELSE 0.0 END AS qty
FROM range(0, 40) t(i) CROSS JOIN (SELECT unnest([1, 2, 3]) AS item_id);
-- GlobalETS weekly seasonal panel
SELECT * FROM ts_forecast_panel_by('panel_sales', product_id, ds, y, 'GlobalETS', 14, '1d',
MAP {'seasonal_period': '7'});
-- GlobalTheta trended panel (no config needed)
SELECT * FROM ts_forecast_panel_by('panel_sales', product_id, ds, y, 'GlobalTheta', 14, '1d');
-- GlobalCroston SBA for spare-parts panel
SELECT * FROM ts_forecast_panel_by('panel_spares', item_id, ds, qty, 'GlobalCroston', 6, '1d',
MAP {'croston_variant': 'SBA'});
ts_forecast_var_by (multivariate VAR)
Dedicated multivariate function — distinct from ts_forecast_by. Fits a VAR(p) model
across K variables simultaneously (cross-variable dynamics). Returns long format.
v1 constraints: Single-panel only (no group_col). Named param p for lag order (order is a SQL reserved word). Point forecasts only (no intervals).
ts_forecast_var_by(
source VARCHAR, -- source table name (quoted string)
date_col VARCHAR, -- date column name (quoted string)
value_cols VARCHAR[], -- array of value column names ['y1', 'y2', ...]
horizon INTEGER, -- periods to forecast
frequency VARCHAR, -- time step between observations
p INTEGER, -- lag order (named param, default: 1)
params MAP -- reserved for future use (default: MAP{})
) → TABLE(variable VARCHAR, forecast_step BIGINT, <date_col>, forecast_value DOUBLE)
Output: k_vars × horizon rows in long format. One row per (variable, forecast_step).
CREATE OR REPLACE TABLE var_src AS
SELECT (DATE '2020-01-01' + INTERVAL (i) DAY) AS ds,
(0.6 * SIN(i * 0.4) + 0.1 * COS(i * 0.2)) AS y1,
(0.05 * SIN(i * 0.4) + 0.7 * COS(i * 0.2)) AS y2
FROM range(60) t(i);
-- VAR(1) — 2-variable system, 14-step ahead
SELECT * REPLACE(ROUND(forecast_value, 6) AS forecast_value)
FROM ts_forecast_var_by('var_src', 'ds', ['y1', 'y2'], 14, '1d')
ORDER BY variable, forecast_step;
-- Returns 28 rows: y1 × 14 + y2 × 14
-- VAR(2) — higher lag order
SELECT * FROM ts_forecast_var_by('var_src', 'ds', ['y1', 'y2'], 14, '1d', p:=2);
Pitfalls:
- Use
p:=2NOTorder:=2(ORDER is a SQL reserved word). - All value columns must have the same valid observation count after null imputation.
- Minimum obs: n > k×p+1 (n=obs, k=variables, p=lag order).
- Non-stationary series → unstable coefficient matrix; difference first with
ts_diff_by. - Benchmark: VAR(1) on synthetic VAR(1) data — MAE ratio vs statsmodels = 1.000 (exact match, PASS).
Exogenous regressors — ts_forecast_exog_by / ts_forecast_exog
Add external drivers (weather, promotions, price) to selected models. Every exog-capable
model routes through the crate's OWN exogenous support (CalendarAnnotations +
predict_with_exog_intervals) — there is no separate X-suffixed model name. model_name
reports the base model's own name (including any auto-selected order/spec) with
" with regressors" appended once, e.g. AutoARIMA(3,1,0)(1,0,0)[5] with regressors,
ETS(A,A,N) with regressors.
Exog-capable models: Naive, ARIMA, AutoARIMA, Theta, OptimizedTheta,
DynamicTheta, DynamicOptimizedTheta, AutoTheta, ETS, AutoETS, MFLES, MSTL. A
model NOT in this list given a regressor is a hard error naming the model and this list.
ARIMA here still requires arima_order. A regressor that is constant throughout
history (e.g. a holiday flag never observed as 1 during training) is dropped before
fitting — 0 effect by construction, and model_name carries NO " with regressors" suffix.
You must supply a FUTURE table holding the exogenous columns over EXACTLY the
forecast horizon (one future row per horizon step — the model needs the drivers for every
period it predicts, no more, no fewer). confidence_level defaults to 0.90 here too.
ts_forecast_exog_by (multi-series — primary surface)
ts_forecast_exog_by(
source VARCHAR, -- historical table name (quoted string)
group_col COLUMN, -- series identifier (unquoted)
date_col COLUMN, -- date / timestamp (unquoted)
target_col COLUMN, -- value to forecast (unquoted)
xreg_cols VARCHAR[], -- historical exog columns, e.g. ['temp']
future_source VARCHAR, -- future exog table name (quoted string)
future_date_col COLUMN, -- future date column (unquoted)
future_xreg_cols VARCHAR[], -- future exog columns, e.g. ['temp']
frequency VARCHAR, -- '1d', '1mo', ...
method VARCHAR, -- 'AutoARIMA', 'OptimizedTheta', 'MFLES'
horizon INTEGER,
params MAP or STRUCT
) → TABLE(group_col, forecast_step INT, date TIMESTAMP, yhat DOUBLE, yhat_lower, yhat_upper, model_name)
-- AutoARIMA with a temperature regressor
CREATE TABLE hist AS
SELECT 'A' AS id, DATE '2024-01-01' + INTERVAL (g) DAY AS d,
10 + g*0.5 + 3*sin(g) AS y, 20.0 + g*0.1 AS temp
FROM range(0,40) t(g);
CREATE TABLE fut AS
SELECT 'A' AS id, DATE '2024-01-01' + INTERVAL (40+g) DAY AS d,
24.0 + g*0.1 AS temp
FROM range(0,7) t(g);
SELECT * FROM ts_forecast_exog_by('hist', id, d, y,
['temp'], 'fut', d, ['temp'], '1d', 'AutoARIMA', 7, MAP{});
-- model_name reports the selected order with ' with regressors' appended, e.g. 'AutoARIMA(3,0,1)(0,1,0)[9] with regressors'
ts_forecast_exog (single-series)
Same call without the group_col / frequency — for one series. Returns
one row with array columns point_forecasts, lower_bounds,
upper_bounds, model_name, plus (fcst).aic / (fcst).bic / (fcst).mse.
ts_forecast_exog(source, date_col, target_col, xreg_cols,
future_source, future_date_col, future_xreg_cols,
method, horizon, params)
→ STRUCT(point_forecasts DOUBLE[], lower_bounds DOUBLE[], upper_bounds DOUBLE[], model_name, ...)
-- Single-series AutoARIMA with regressors (hist/fut without the id column)
SELECT * FROM ts_forecast_exog('hist', d, y,
['temp'], 'fut', d, ['temp'], 'AutoARIMA', 7, MAP{});
ts_forecast (single-series, non-grouped)
The single-series sibling of ts_forecast_by — no group_col, no
frequency. Returns one row with array columns.
ts_forecast(source VARCHAR, date_col COLUMN, target_col COLUMN,
method VARCHAR, horizon INTEGER, params MAP or STRUCT)
→ STRUCT(point_forecasts DOUBLE[], lower_bounds DOUBLE[], upper_bounds DOUBLE[], model_name, (fcst).aic, (fcst).bic)
CREATE TABLE s AS
SELECT DATE '2024-01-01' + INTERVAL (g) DAY AS d, 10 + g*0.5 + 3*sin(g) AS y
FROM range(0,40) t(g);
SELECT * FROM ts_forecast('s', d, y, 'AutoETS', 7, MAP{});
Ensembles (v0.8.0)
Ensembles combine multiple member models per series rather than replacing them — they are a combination surface over the existing 36 models, not additional model count. Three entry points plus two introspection functions.
ts_forecast_by with method='AutoEnsemble'
Auto-fits AutoARIMA, AutoETS, and AutoTheta; ranks them by in-sample MSE; combines the top-K members.
ts_forecast_by(
source VARCHAR, group_col COLUMN, date_col COLUMN, target_col COLUMN,
'AutoEnsemble', horizon INTEGER, frequency VARCHAR,
params MAP or STRUCT -- top_k, combination_method, seasonal_period
) → TABLE(group_col, forecast_step INT, ds, yhat DOUBLE, yhat_lower, yhat_upper, model_name)
Params:
| Key | Default | Values |
|---|---|---|
top_k |
3 | Number of top-ranked members to combine |
combination_method |
'mean' |
'mean', 'median', 'weighted_mse', 'inverse_aic', 'stacking', 'horizon_adaptive' |
seasonal_period |
0 | Shared with member models; 0 = non-seasonal |
-- 60-observation linear series for reliable member convergence (reused by every
-- ensemble/introspection example below)
CREATE OR REPLACE TABLE ae_test AS
SELECT 1 AS id, '2020-01-01'::DATE + INTERVAL (i - 1) DAY AS ds, 10.0 + i * 0.5 AS y
FROM range(1, 61) t(i);
-- AutoEnsemble with default params (top_k=3, combination_method='mean')
-- (sourced from autoensemble.sql Section 2)
SELECT forecast_step, ds, ROUND(yhat, 4) AS yhat, yhat_lower, yhat_upper, model_name
FROM ts_forecast_by('ae_test', id, ds, y, 'AutoEnsemble', 5, '1d')
ORDER BY forecast_step;
-- AutoEnsemble with explicit params (sourced from autoensemble.sql Section 1)
SELECT forecast_step, yhat, model_name
FROM ts_forecast_by(
'ae_test', id, ds, y,
'AutoEnsemble', 5, '1d',
params := {top_k: 3, combination_method: 'mean', seasonal_period: 0}
);
Note: yhat_lower / yhat_upper are the crate AutoEnsemble's own predict_with_intervals bounds at confidence_level (default 0.90) — not NULL. For calibrated (conformal) coverage on top of these bounds, see anofox-forecast-backtest and ensemble_intervals.sql.
ts_forecast_ensemble_by — explicit member list
User-named member models as a VARCHAR[]. Any of the 26-member allowlist (all models except GARCH, AutoEnsemble) may appear.
ts_forecast_ensemble_by(
source VARCHAR,
group_col COLUMN,
date_col COLUMN,
target_col COLUMN,
members VARCHAR[], -- e.g. ['AutoARIMA', 'AutoETS', 'Theta']
horizon INTEGER,
frequency VARCHAR,
combination_method := 'mean', -- named param
seasonal_period := 0 -- named param
) → TABLE(group_col, forecast_step INT, ds, yhat DOUBLE, yhat_lower DOUBLE, yhat_upper DOUBLE, model_name)
-- Explicit three-member ensemble, mean combination
-- (sourced from ensemble_explicit.sql Section 1)
SELECT * FROM ts_forecast_ensemble_by(
'ae_test', id, ds, y,
['AutoARIMA', 'AutoETS', 'Theta'],
5, '1d',
combination_method := 'mean',
seasonal_period := 0
);
-- Seasonal tier-2 members (sourced from ensemble_explicit.sql Section 3 Sample D)
SELECT forecast_step, ROUND(yhat, 4) AS yhat FROM ts_forecast_ensemble_by(
'ae_test', id, ds, y,
['SeasonalNaive', 'HoltWinters'],
3, '1d',
combination_method := 'mean', seasonal_period := 12);
Errors: unknown member name, fewer than 2 members, or blocked model (GARCH) each raise a descriptive InvalidInput exception naming the offending member.
ts_ensemble_inspect_by — inspect explicit-member ensemble
Returns one row per group per member: member_name, weight, plus group column.
ts_ensemble_inspect_by(
source VARCHAR,
group_col COLUMN,
date_col COLUMN,
target_col COLUMN,
members VARCHAR[],
combination_method := 'mean',
seasonal_period := 0
) → TABLE(group_col, member_name VARCHAR, weight DOUBLE)
-- Mean combination: weight == 1/k for every member
-- (sourced from ensemble_inspect.sql Section 1)
SELECT * FROM ts_ensemble_inspect_by(
'ae_test', id, ds, y,
['AutoARIMA', 'AutoETS', 'Theta'],
combination_method := 'mean',
seasonal_period := 0
)
ORDER BY id, member_name;
-- WeightedMSE: inverse-MSE weights; non-negative, sum to 1 per series
-- (sourced from ensemble_inspect.sql Section 2)
SELECT * FROM ts_ensemble_inspect_by(
'ae_test', id, ds, y,
['AutoARIMA', 'AutoETS', 'Theta'],
combination_method := 'weighted_mse',
seasonal_period := 0
)
ORDER BY id, member_name;
ts_auto_ensemble_inspect_by — inspect AutoEnsemble members
Returns one row per group per selected member: member_name, weight, score (in-sample MSE), rank.
ts_auto_ensemble_inspect_by(
source VARCHAR,
group_col COLUMN,
date_col COLUMN,
target_col COLUMN,
top_k := 3,
combination_method := 'mean',
seasonal_period := 0
) → TABLE(group_col, member_name VARCHAR, weight DOUBLE, score DOUBLE, rank BIGINT)
-- AutoEnsemble Mean inspection: weight == 1/k, score > 0, rank in 1..k
-- (sourced from ensemble_inspect.sql Section 3)
SELECT * FROM ts_auto_ensemble_inspect_by(
'ae_test', id, ds, y,
top_k := 3,
combination_method := 'mean',
seasonal_period := 0
)
ORDER BY id, rank;
-- AutoEnsemble WeightedMSE: weight IS NULL (see gotcha b below), score > 0
-- (sourced from ensemble_inspect.sql Section 4)
SELECT * FROM ts_auto_ensemble_inspect_by(
'ae_test', id, ds, y,
top_k := 3,
combination_method := 'weighted_mse',
seasonal_period := 0
)
ORDER BY id, rank;
Ensemble gotchas
(a) ts_cv_forecast_by('AutoEnsemble') now runs end-to-end (fixed in Phase 10 — the
null-bounds dereference downstream of the CV native's zeroed ensemble_top_k/
ensemble_method is gone, since every model path is now null-safe). The manual per-fold
_ts_forecast_scalar loop below is kept as a documented alternative (useful when you need a
non-default top_k/combination_method for the backtest, since ts_cv_forecast_by still
can't carry ensemble-specific params through its own MAP argument):
-- Safe conformal CV path for AutoEnsemble (sourced from ensemble_intervals.sql Section 1)
CREATE OR REPLACE TABLE ens_series AS
SELECT CASE WHEN g = 0 THEN 'series_A' ELSE 'series_B' END AS id,
(DATE '2020-01-01' + INTERVAL (r) DAY) AS ds,
10.0 + r * 0.5 + sin(r * 0.15) * 2.0 + g * 8.0 AS y
FROM range(120) t(r), range(2) s(g);
-- Step 1: generate folds
CREATE OR REPLACE TABLE ens_folds AS
SELECT * FROM ts_cv_folds_by('ens_series', id, ds, y, 3, 5, MAP{});
-- Step 2: forecast per fold with _ts_forecast_scalar (not ts_cv_forecast_by)
-- Step 3: ts_conformal_calibrate(..., id, ...) -- per-series calibration (Phase 16 D-03)
-- Step 4: ts_forecast_by('AutoEnsemble') + ts_conformal_apply_by(..., id, ds, yhat, 'calib')
-- See examples/forecasting/ensemble_intervals.sql for the full pipeline.
(b) ts_auto_ensemble_inspect_by returns NULL weight for non-Mean combination_method (anofox-forecast ≤0.15.10). The AutoEnsemble struct's inner combination weights are stored in a private field not accessible via the public all_scores() / model_count() API. Mean combination is the only variant for which weights are returned. score (in-sample MSE) is always populated. This is a documented upstream-crate limitation.
ts_forecast_agg (aggregate)
For custom GROUP BY shapes.
ts_forecast_agg(date_col TIMESTAMP, value_col DOUBLE, method VARCHAR, horizon INTEGER, params MAP)
→ STRUCT(point_forecast DOUBLE[], lower_<pct> DOUBLE[], upper_<pct> DOUBLE[], model_name VARCHAR, insample_fitted DOUBLE[], ...)
<pct> = round(100 * confidence_level) (default lower_90/upper_90); confidence_level
is fixed at bind time and must be a constant expression.
SELECT product_id, ts_forecast_agg(ds, y, 'AutoETS', 12, MAP{}) AS fcst
FROM sales GROUP BY product_id;
Access fields: (fcst).point_forecast, (fcst).lower_90, etc.
Same params (validated against the same closed key set), same validation, and same SQL-error
behavior for invalid model/parameter values as ts_forecast_by (10-10, FCST-06). Input
handling now matches ts_forecast_by exactly (Phase 12, D-11): interior NULLs are
interpolated, and a NULL-valued trailing row keeps the forecast origin anchored to the
series' ORIGINAL last row — only a NULL timestamp drops a row. Forecast dates come
from the same Rust calendar rule ts_forecast_by uses (D-12: month-end-preserving /
day-of-month-clamp stepping), with the frequency auto-detected from the observed dates.
insample_fitted comes from each model's own crate fitted_values() — no SES(α=0.3)
surrogate for any model. Warmup rows (e.g. ARIMA/SARIMA differencing) are NULL; when the
crate's fitted-array length doesn't match the (trimmed) input length, the exported array is
empty rather than mis-aligned. See docs/api/07-forecasting.md's "Prediction intervals,
fitted values and input handling" section for the full input-handling contract.
Model catalogue (36)
Automatic selection (6)
| Model | Params | Best for |
|---|---|---|
AutoETS |
seasonal_period, model_pool |
Unknown patterns — default pick. A crate fit/predict failure or panic is a hard error naming AutoETS — never a silent HoltWinters/Holt/SES substitution. |
AutoARIMA |
seasonal_period |
Unknown patterns, ARIMA family |
AutoTheta |
seasonal_period |
Unknown patterns, Theta family (best RMSE on M5-monthly) |
AutoMFLES |
seasonal_periods[] |
Multiple seasonalities. Currently an alias for MFLES (same code path, same reported model_name = MFLES). |
AutoMSTL |
seasonal_periods[] |
Multiple seasonalities. Currently an alias for MSTL (same code path, same reported model_name = MSTL). |
AutoTBATS |
seasonal_periods[] |
Multiple seasonalities |
seasonal_period / seasonal_periods are optional everywhere above — omitting them triggers
the auto-detect fallback described in gotcha 1.
Baseline (7)
| Model | Required | Optional |
|---|---|---|
Naive |
— | — |
SMA |
— | window (default 5) |
SeasonalNaive |
seasonal_period |
— |
SeasonalWindowAverage |
seasonal_period |
— |
SES |
— | — (fixed alpha, not SQL-configurable) |
SESOptimized |
— | — |
RandomWalkDrift |
— | — |
Exponential smoothing (4)
| Model | Required | Optional |
|---|---|---|
Holt |
— | — (fixed smoothing, not SQL-configurable) |
HoltWinters |
seasonal_period |
— (fixed smoothing, not SQL-configurable) |
SeasonalES |
seasonal_period |
— (fixed smoothing, not SQL-configurable) |
SeasonalESOptimized |
seasonal_period |
— |
Theta (5)
| Model | Optional |
|---|---|
Theta |
seasonal_period (auto-detected if omitted) — no custom theta coefficient accepted |
OptimizedTheta |
seasonal_period (auto-detected if omitted) |
DynamicTheta |
seasonal_period (auto-detected if omitted) — no custom theta coefficient accepted |
DynamicOptimizedTheta |
seasonal_period (auto-detected if omitted) |
AutoTheta |
seasonal_period (listed above) |
Classical volatility (1)
| Model | Required | Optional | Important |
|---|---|---|---|
GARCH |
— | garch_p (default 1), garch_q (default 1) |
yhat is VOLATILITY (std-dev = sqrt(variance)), NOT variance. Use on returns (first differences), not raw price levels. Min obs: p+q+10. |
CREATE OR REPLACE TABLE returns AS
SELECT 'Asset_A' AS asset_id, DATE '2024-01-01' + INTERVAL (i) DAY AS ds,
0.5 * SIN(i * 0.7) + 0.3 * COS(i * 0.3) AS y
FROM range(40) t(i);
-- GARCH(1,1) — volatility forecast on returns (40 obs minimum > 12)
SELECT asset_id, forecast_step, ds, yhat AS conditional_volatility, model_name
FROM ts_forecast_by('returns', asset_id, ds, y, 'GARCH', 7, '1d');
-- GARCH(1,1) with explicit params
SELECT asset_id, forecast_step, ds, yhat AS conditional_volatility, model_name
FROM ts_forecast_by('returns', asset_id, ds, y, 'GARCH', 7, '1d',
params := MAP{'garch_p':'1','garch_q':'1'})
Critical: yhat is σ (std-dev), not σ². Square for variance: yhat * yhat.
Critical: Use on returns (LN differences of prices), not raw levels — non-stationary levels cause α+β→1 divergence.
Benchmark: ratio vs arch package = 0.897 on M4 Daily (PASS).
State-space / ARIMA (3 — AutoETS/AutoARIMA counted above)
| Model | Required | Optional |
|---|---|---|
ETS |
— | seasonal_period, model (default: runs the crate's own AutoETS selection, not a hand-picked 'AAA') |
ARIMA |
arima_order [p,d,q] |
seasonal_order [P,D,Q] (+ seasonal_period); real crate ARIMA/SARIMA fit, model_name reports the fitted order |
Kalman |
— | kalman_model ('local_level' default | 'local_linear_trend') |
ETS default: with no model spec, ETS runs the crate's own AutoETS selection and
reports the chosen spec, e.g. ETS(A,A,M) — the same construction standalone AutoETS
uses, so ['ETS','ETS'] as an ensemble member equals standalone 'ETS' bit-for-bit.
ARIMA: arima_order/seasonal_order are the only order keys; p/d/q/P/D/Q/s
(bare, unscoped) are still rejected by ValidateParamKeys. 'ARIMA' without arima_order
is a hard error naming AutoARIMA; with an order, it runs a real crate fit and model_name
appends " with drift" / " with non-zero mean" when the crate's own AICc comparison keeps
a non-zero intercept.
Kalman details:
local_level(default): random walk + noise; h-step forecast is flat at filtered level.local_linear_trend: level + trend; h-step forecast grows/shrinks linearly.- Uses fixed variance params (obs_var=1.0, level_var=0.1), NOT MLE-estimated.
- Benchmark: ratio vs statsmodels UnobservedComponents = 1.000 (local_level) / 0.992 (llt), both PASS.
-- Kalman local_level (default)
SELECT * FROM ts_forecast_by('sales', product_id, ds, y, 'Kalman', 14, '1d');
-- Kalman local_linear_trend
SELECT * FROM ts_forecast_by('sales', product_id, ds, y, 'Kalman', 14, '1d',
params := MAP{'kalman_model': 'local_linear_trend'});
Multi-seasonal (3 — Auto* counted above)
| Model | Required | Optional |
|---|---|---|
MFLES |
— | seasonal_periods[] (auto-detected single period if omitted) |
MSTL |
— | seasonal_periods[] (auto-detected single period, or [12], if omitted) |
TBATS |
— | seasonal_periods[] (auto-detected single period, or [12], if omitted); no Box-Cox toggle accepted |
iterations and stl_method are rejected by ValidateParamKeys — not SQL-configurable.
Intermittent demand (6)
| Model | Optional |
|---|---|
CrostonClassic |
— |
CrostonOptimized |
— |
CrostonSBA |
— |
ADIDA |
— |
IMAPA |
— |
TSB |
— (fixed smoothing; no custom demand/probability coefficients accepted) |
Distributional (1) — Laplace
Streaming likelihood-weighted mixture of leaves (EMA / drift / AR(1) / damped-Holt + optional seasonal / distribution-family leaves). Three zero-config selectors via laplace_variant.
| Variant | Best for | Notes |
|---|---|---|
auto (default) |
Smooth continuous series | Balanced defaults |
auto_aid |
Retail SKU / intermittent counts | AID-based distribution-family leaf selection. Best negative-forecast rate on non-negative counts. |
skaters |
Fuller ensemble | Multi-h scoring, stacking, larger leaf set. Slower, more robust. |
Extra param laplace_seasonal_batch_init: 1 batch-initialises the seasonal-EMA leaf from the last training cycle. Safe on stationary / declining amplitude; UNSAFE on growing amplitude or phase-shifted seasonality (collapses to flat). Default off.
-- Retail-tuned selector on monthly counts
SELECT * FROM ts_forecast_by('sales', product_id, ds, y, 'Laplace', 12, '1mo',
{seasonal_period: 12, laplace_variant: 'auto_aid'});
model_name tags the state: Laplace(auto,seasonal=7), Laplace(auto_aid,seasonal=12), Laplace(skaters,seasonal=12,batch_init).
Performance context (M5-monthly, 24 k series × 12-month horizon on 12-core dev box):
- Laplace(auto): MAE 18.99, wall 0.5 s
- AutoTheta: MAE 19.33, wall 0.7 s
- AutoETS: MAE 19.91, wall 15 s (~30× slower)
- SeasonalES (baseline): MAE 26.55
Parameter surface — universal keys
Every model accepts these via params:
| Key | Type | Notes |
|---|---|---|
seasonal_period |
INTEGER | e.g. 7 (weekly), 12 (monthly), 24 (hourly-daily), 365 (yearly) |
seasonal_periods |
ARRAY (JSON string in MAP form) | Multi-seasonal: '[24, 168]' or [24, 168] in STRUCT |
confidence_level |
DOUBLE | Default 0.90; range (0, 1) |
window |
INTEGER | For SMA / SeasonalWindowAverage |
model_pool |
VARCHAR | AutoETS: 'complete' (default), 'reduced' |
laplace_variant |
VARCHAR | 'auto' / 'auto_aid' / 'skaters' — Laplace only |
laplace_seasonal_batch_init |
INTEGER (0/1) | Opt-in — Laplace only |
Frequency strings
| Format | Examples |
|---|---|
| Polars-style | '1d', '1h', '30m', '1w', '1mo', '1q', '1y' |
| DuckDB INTERVAL | '1 day', '1 hour' |
| Raw integer | '1', '7' (days) |
Model selection — one-liner rules
| Data characteristics | First try | Alternative |
|---|---|---|
| Don't know | AutoETS |
Laplace (auto) |
| Trend, no seasonality | Holt |
Theta |
| Weekly seasonal | HoltWinters |
Laplace (auto) |
| Monthly retail counts | Laplace (auto_aid) |
AutoTheta |
| Multiple seasonalities | MSTL |
MFLES |
| Intermittent (many zeros) | CrostonSBA |
Laplace (auto_aid) |
| Amplitude-declining seasonal | Laplace (auto, batch_init=1) |
AutoTheta |
| Speed on large panels | Laplace (auto) |
SeasonalES |
| Short series (< 20 obs) | Naive |
SES |
Canonical forecast pipeline
-- 0. Sessions using detection / conformal need json
SET autoinstall_known_extensions = 1;
SET autoload_known_extensions = 1;
-- 1. Detect seasonality (output column is named after the group_col argument, product_id)
CREATE OR REPLACE TABLE periods AS
SELECT product_id, primary_period AS sp
FROM ts_detect_periods_by('clean', product_id, ds, y, MAP{});
-- 2. Forecast (panel-modal seasonal_period)
CREATE OR REPLACE TABLE forecasts AS
SELECT * FROM ts_forecast_by('clean', product_id, ds, y,
'Laplace', 14, '1d',
{seasonal_period: (SELECT mode() WITHIN GROUP (ORDER BY sp) FROM periods),
laplace_variant: 'auto'});
-- 3. Inspect
SELECT product_id, forecast_step, ds, yhat, yhat_lower, yhat_upper, model_name
FROM forecasts
ORDER BY product_id, forecast_step;
Explainability surfaces (Tier 6)
Two macros expose per-group fit-state and forecast decomposition, each
returning a nullable wide STRUCT so the shape is stable across model
families. Both require the json extension.
ts_forecast_inspect_by(source, group_col, date_col, target_col, method, params?)
Fit-state snapshot. Signature returns TABLE(group_col, inspection STRUCT(...)).
(inspection).model_family names which sub-fields are populated:
model_family |
Key fields |
|---|---|
Ets |
spec, alpha, beta, gamma, phi, trend_component, seasonal_component, residuals |
Arima |
order_p/d/q, seasonal_order_P/D/Q/s, coefficients, aic, bic |
Theta |
variant, theta, alpha |
Tbats |
seasonal_periods, box_cox_lambda, selected_config, aic |
Mfles |
max_rounds, multiplicative, penalty, trend_component, seasonal_component |
Mstl |
seasonal_periods, iterations, trend_component, seasonal_component |
Laplace |
leaf_names, leaf_weights, horizon_dists_json |
Fields not in the current family are NULL. raw_json carries the full serde payload.
Supported models: AutoETS, AutoARIMA, AutoTheta, AutoTBATS, MFLES, AutoMFLES, MSTL, AutoMSTL, Laplace. Others raise does not implement Inspectable.
-- Read AutoETS smoothing params per group
SELECT product_id, (inspection).spec, (inspection).alpha
FROM ts_forecast_inspect_by('sales', product_id, ds, y, 'AutoETS',
{seasonal_period: 12});
ts_forecast_explain_by(source, group_col, date_col, target_col, method, horizon, params?)
Per-horizon decomposition. Returns TABLE(group_col, decomposition STRUCT(horizon, level DOUBLE[], trend DOUBLE[], seasonal DOUBLE[], residual DOUBLE[], named_components_json, raw_json)). Each array has horizon elements; components not produced by a family are NULL.
Supported models: ETS (fixed spec), MSTL, AutoMSTL, Theta. Others raise does not implement Explainable.
-- Reconstruct: yhat = level + trend + seasonal + residual
SELECT product_id, (decomposition).level, (decomposition).trend, (decomposition).seasonal
FROM ts_forecast_explain_by('sales', product_id, ds, y, 'ETS', 12,
{seasonal_period: 12});
See also: anofox-forecast-data-prep (clean input required), anofox-forecast-detection (produce seasonal_period), anofox-forecast-backtest (any model string usable in ts_cv_forecast_by), anofox-forecast-eda (understand series before picking model).
Reference docs:
docs/api/07-forecasting.md(§ Explainability)docs/reference/models/distributional/laplace.md(Laplace deep-dive)docs/guides/02-model-selection.md