Anofox Forecast — Models & tsforecastby Cheat Sheet

Extension: anofox_forecast v0.15.3 (Rust crate anofox-forecast v0.15.3) | 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.

Critical gotchas

1. Seasonality is NOT auto-detected. All models — including the Auto* family — treat seasonal_period as user-supplied. Run ts_detect_periods_by first and pass the result explicitly. See anofox-forecast-detection.

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

3. ts_forecast_by requires frequency as the 7th positional param. No default:

   -- WRONG (missing frequency):
   ts_forecast_by('sales', id, ds, y, 'Naive', 12)
   -- CORRECT:
   ts_forecast_by('sales', id, ds, y, 'Naive', 12, '1d')
   

4. 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'})
   

5. The _by output renames the target column to y and adds forecast_step, yhat, yhat_lower, yhat_upper, model_name.

6. ts_forecast_agg takes (date, value, model, horizon, params) directly — no LIST(...) 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

1. Series dropped below 10 obs: Short series after alignment emit DROPPED: too_short rows — not errors. Check model_name in the result.
2. Minimum 3 series after drop: Fewer than 3 kept series → InvalidInputException. Ensure your panel has enough history.
3. Point forecasts only (v1): yhat_lower/yhat_upper are not populated. Use ts_conformal_by separately if intervals needed.
4. TABLE arg must be a subselect: The underlying _ts_forecast_panel_native uses the subselect pattern internally. Pass only a table name (quoted string) to ts_forecast_panel_by — do NOT pass a CTE or subquery as source.
5. GlobalCroston with all-zero panel fails: Ensure at least one series has ≥ 2 non-zero demand events in the aligned window.
6. GlobalETS seasonal_period=0 → non-seasonal (Reduced pool, ANN/AAdN/MNN/MAdN candidates only). Period=1 has the same effect.

Panel quick examples

-- GlobalETS weekly seasonal panel
SELECT * FROM ts_forecast_panel_by('sales', product_id, ds, y, 'GlobalETS', 14, '1d',
    MAP {'seasonal_period': '7'});

-- GlobalTheta trended panel (no config needed)
SELECT * FROM ts_forecast_panel_by('sales', product_id, ds, y, 'GlobalTheta', 14, '1d');

-- GlobalCroston SBA for spare-parts panel
SELECT * FROM ts_forecast_panel_by('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).

-- 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:=2 NOT order:=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).

---

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_90 DOUBLE[], upper_90 DOUBLE[], model_name VARCHAR, insample_fitted DOUBLE[], ...)

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.

Model catalogue (36)

Automatic selection (6)

Model	Params	Best for
AutoETS	seasonal_period, model_pool	Unknown patterns — default pick
AutoARIMA	seasonal_period	Unknown patterns, ARIMA family
AutoTheta	seasonal_period	Unknown patterns, Theta family (best RMSE on M5-monthly)
AutoMFLES	seasonal_periods[]	Multiple seasonalities
AutoMSTL	seasonal_periods[]	Multiple seasonalities
AutoTBATS	seasonal_periods[]	Multiple seasonalities

Baseline (6)

Model	Required	Optional
Naive	—	—
SMA	—	window (default 5)
SeasonalNaive	seasonal_period	—
SES	—	alpha (default 0.3)
SESOptimized	—	—
RandomWalkDrift	—	—

Exponential smoothing (5)

Model	Required	Optional
Holt	—	alpha, beta
HoltWinters	seasonal_period	alpha, beta, gamma
SeasonalES	seasonal_period	alpha, gamma
SeasonalESOptimized	seasonal_period	—
SeasonalWindowAverage	seasonal_period	—

Theta (5)

Model	Optional
Theta	seasonal_period, theta
OptimizedTheta	seasonal_period
DynamicTheta	seasonal_period, theta
DynamicOptimizedTheta	seasonal_period
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.

-- 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
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 ('AAA', 'AAN', …)
ARIMA	p, d, q	P, D, Q, s
Kalman	—	kalman_model ('local_level' default | 'local_linear_trend')

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[]	iterations
MSTL	seasonal_periods[]	stl_method
TBATS	seasonal_periods[]	use_box_cox

Intermittent demand (6)

Model	Optional
CrostonClassic	—
CrostonOptimized	—
CrostonSBA	—
ADIDA	—
IMAPA	—
TSB	alpha_d, alpha_p

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', 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
CREATE OR REPLACE TABLE periods AS
SELECT 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