anofox-forecast-models

Name: anofox-forecast-models
Author: DataZooDE

35stars3forks

How to install anofox-forecast-models?

datazoode/anofox-forecast/anofox-forecast-models

$npx -y skills add DataZooDE/anofox-forecast --skill anofox-forecast-models

Installs into the current project.

Summary

Forecasting models and the ts_forecast_by API surface of the anofox_forecast DuckDB extension. Covers 33 models (baseline, exponential smoothing, state-space, ARIMA, Theta, multi-seasonal, intermittent-demand, distributional Laplace with three variants), parameter surfaces (MAP

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Models & `ts_forecast_by` Cheat Sheet
2
3	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_`
4
5	33 forecasting models exposed by SQL via three call surfaces (table macro, aggregate, scalar).
6
7	## Critical gotchas
8
9	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`.
10
11	2. Model names are case-sensitive. `'AutoETS'` works, `'autoets'` errors.
12
13	3. `ts_forecast_by` requires frequency as the 7th positional param. No default:
14
15	```sql
16	-- WRONG (missing frequency):
17	ts_forecast_by('sales', id, ds, y, 'Naive', 12)
18	-- CORRECT:
19	ts_forecast_by('sales', id, ds, y, 'Naive', 12, '1d')
20	```
21
22	4. STRUCT + MAP both work in params. STRUCT keeps numeric params typed (recommended):
23
24	```sql
25	-- STRUCT (recommended)
26	ts_forecast_by(..., 'HoltWinters', 12, '1d', {seasonal_period: 7})
27	-- MAP (all strings, legacy)
28	ts_forecast_by(..., 'HoltWinters', 12, '1d', MAP{'seasonal_period': '7'})
29	```
30
31	5. The `_by` output renames the target column to `y` and adds `forecast_step`, `yhat`, `yhat_lower`, `yhat_upper`, `model_name`.
32
33	6. `ts_forecast_agg` takes `(date, value, model, horizon, params)` directly — no `LIST(...)` wrapping.
34
35	## `ts_forecast_by` (primary surface)
36
37	```sql
38	ts_forecast_by(
39	source VARCHAR, -- table name (quoted string, NOT a CTE)
40	group_col COLUMN, -- series identifier (unquoted)
41	date_col COLUMN, -- date / timestamp (unquoted)
42	target_col COLUMN, -- value to forecast (unquoted)
43	method VARCHAR, -- e.g. 'AutoETS', 'Laplace'
44	horizon INTEGER, -- number of periods ahead
45	frequency VARCHAR, -- '1d', '1mo', '1h', ...
46	params MAP or STRUCT -- model-specific config
47	) → TABLE(group_col, forecast_step INT, ds, yhat DOUBLE, yhat_lower, yhat_upper, model_name)
48	```
49
50	## `ts_forecast_agg` (aggregate)
51
52	For custom `GROUP BY` shapes.
53
54	```sql
55	ts_forecast_agg(date_col TIMESTAMP, value_col DOUBLE, method VARCHAR, horizon INTEGER, params MAP)
56	→ STRUCT(point_forecast DOUBLE[], lower_90 DOUBLE[], upper_90 DOUBLE[], model_name VARCHAR, insample_fitted DOUBLE[], ...)
57	```
58
59	```sql
60	SELECT product_id, ts_forecast_agg(ds, y, 'AutoETS', 12, MAP{}) AS fcst
61	FROM sales GROUP BY product_id;
62	```
63
64	Access fields: `(fcst).point_forecast`, `(fcst).lower_90`, etc.
65
66	## Model catalogue (33)
67
68	### Automatic selection (6)
69
70	\| Model \| Params \| Best for \|
71	\|---\|---\|---\|
72	\| `AutoETS` \| `seasonal_period`, `model_pool` \| Unknown patterns — default pick \|
73	\| `AutoARIMA` \| `seasonal_period` \| Unknown patterns, ARIMA family \|
74	\| `AutoTheta` \| `seasonal_period` \| Unknown patterns, Theta family (best RMSE on M5-monthly) \|
75	\| `AutoMFLES` \| `seasonal_periods[]` \| Multiple seasonalities \|
76	\| `AutoMSTL` \| `seasonal_periods[]` \| Multiple seasonalities \|
77	\| `AutoTBATS` \| `seasonal_periods[]` \| Multiple seasonalities \|
78
79	### Baseline (6)
80
81	\| Model \| Required \| Optional \|
82	\|---\|---\|---\|
83	\| `Naive` \| — \| — \|
84	\| `SMA` \| — \| `window` (default 5) \|
85	\| `SeasonalNaive` \| `seasonal_period` \| — \|
86	\| `SES` \| — \| `alpha` (default 0.3) \|
87	\| `SESOptimized` \| — \| — \|
88	\| `RandomWalkDrift` \| — \| — \|
89
90	### Exponential smoothing (5)
91
92	\| Model \| Required \| Optional \|
93	\|---\|---\|---\|
94	\| `Holt` \| — \| `alpha`, `beta` \|
95	\| `HoltWinters` \| `seasonal_period` \| `alpha`, `beta`, `gamma` \|
96	\| `SeasonalES` \| `seasonal_period` \| `alpha`, `gamma` \|
97	\| `SeasonalESOptimized` \| `seasonal_period` \| — \|
98	\| `SeasonalWindowAverage` \| `seasonal_period` \| — \|
99
100	### Theta (5)
101
102	\| Model \| Optional \|
103	\|---\|---\|
104	\| `Theta` \| `seasonal_period`, `theta` \|
105	\| `OptimizedTheta` \| `seasonal_period` \|
106	\| `DynamicTheta` \| `seasonal_period`, `theta` \|
107	\| `DynamicOptimizedTheta` \| `seasonal_period` \|
108	\| `AutoTheta` \| `seasonal_period` (listed above) \|
109
110	### State-space / ARIMA (2 — `AutoETS`/`AutoARIMA` counted above)
111
112	\| Model \| Required \| Optional \|
113	\|---\|---\|---\|
114	\| `ETS` \| — \| `seasonal_period`, `model` (`'AAA'`, `'AAN'`, …) \|
115	\| `ARIMA` \| `p`, `d`, `q` \| `P`, `D`, `Q`, `s` \|
116
117	### Multi-seasonal (3 — `Auto*` counted above)
118
119	\| Model \| Required \| Optional \|
120	\|---\|---\|---\|
121	\| `MFLES` \| `seasonal_periods[]` \| `iterations` \|
122	\| `MSTL` \| `seasonal_periods[]` \| `stl_method` \|
123	\| `TBATS` \| `seasonal_periods[]` \| `use_box_cox` \|

People who liked this also viewed

6 picks

Type

anofox-forecast-models

35stars3forks

View on GitHub

How to install anofox-forecast-models?

datazoode/anofox-forecast/anofox-forecast-models

$npx -y skills add DataZooDE/anofox-forecast --skill anofox-forecast-models

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Models & `ts_forecast_by` Cheat Sheet
2
3	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_`
4
5	33 forecasting models exposed by SQL via three call surfaces (table macro, aggregate, scalar).
6
7	## Critical gotchas
8
9	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`.
10
11	2. Model names are case-sensitive. `'AutoETS'` works, `'autoets'` errors.
12
13	3. `ts_forecast_by` requires frequency as the 7th positional param. No default:
14
15	```sql
16	-- WRONG (missing frequency):
17	ts_forecast_by('sales', id, ds, y, 'Naive', 12)
18	-- CORRECT:
19	ts_forecast_by('sales', id, ds, y, 'Naive', 12, '1d')
20	```
21
22	4. STRUCT + MAP both work in params. STRUCT keeps numeric params typed (recommended):
23
24	```sql
25	-- STRUCT (recommended)
26	ts_forecast_by(..., 'HoltWinters', 12, '1d', {seasonal_period: 7})
27	-- MAP (all strings, legacy)
28	ts_forecast_by(..., 'HoltWinters', 12, '1d', MAP{'seasonal_period': '7'})
29	```
30
31	5. The `_by` output renames the target column to `y` and adds `forecast_step`, `yhat`, `yhat_lower`, `yhat_upper`, `model_name`.
32
33	6. `ts_forecast_agg` takes `(date, value, model, horizon, params)` directly — no `LIST(...)` wrapping.
34
35	## `ts_forecast_by` (primary surface)
36
37	```sql
38	ts_forecast_by(
39	source VARCHAR, -- table name (quoted string, NOT a CTE)
40	group_col COLUMN, -- series identifier (unquoted)
41	date_col COLUMN, -- date / timestamp (unquoted)
42	target_col COLUMN, -- value to forecast (unquoted)
43	method VARCHAR, -- e.g. 'AutoETS', 'Laplace'
44	horizon INTEGER, -- number of periods ahead
45	frequency VARCHAR, -- '1d', '1mo', '1h', ...
46	params MAP or STRUCT -- model-specific config
47	) → TABLE(group_col, forecast_step INT, ds, yhat DOUBLE, yhat_lower, yhat_upper, model_name)
48	```
49
50	## `ts_forecast_agg` (aggregate)
51
52	For custom `GROUP BY` shapes.
53
54	```sql
55	ts_forecast_agg(date_col TIMESTAMP, value_col DOUBLE, method VARCHAR, horizon INTEGER, params MAP)
56	→ STRUCT(point_forecast DOUBLE[], lower_90 DOUBLE[], upper_90 DOUBLE[], model_name VARCHAR, insample_fitted DOUBLE[], ...)
57	```
58
59	```sql
60	SELECT product_id, ts_forecast_agg(ds, y, 'AutoETS', 12, MAP{}) AS fcst
61	FROM sales GROUP BY product_id;
62	```
63
64	Access fields: `(fcst).point_forecast`, `(fcst).lower_90`, etc.
65
66	## Model catalogue (33)
67
68	### Automatic selection (6)
69
70	\| Model \| Params \| Best for \|
71	\|---\|---\|---\|
72	\| `AutoETS` \| `seasonal_period`, `model_pool` \| Unknown patterns — default pick \|
73	\| `AutoARIMA` \| `seasonal_period` \| Unknown patterns, ARIMA family \|
74	\| `AutoTheta` \| `seasonal_period` \| Unknown patterns, Theta family (best RMSE on M5-monthly) \|
75	\| `AutoMFLES` \| `seasonal_periods[]` \| Multiple seasonalities \|
76	\| `AutoMSTL` \| `seasonal_periods[]` \| Multiple seasonalities \|
77	\| `AutoTBATS` \| `seasonal_periods[]` \| Multiple seasonalities \|
78
79	### Baseline (6)
80
81	\| Model \| Required \| Optional \|
82	\|---\|---\|---\|
83	\| `Naive` \| — \| — \|
84	\| `SMA` \| — \| `window` (default 5) \|
85	\| `SeasonalNaive` \| `seasonal_period` \| — \|
86	\| `SES` \| — \| `alpha` (default 0.3) \|
87	\| `SESOptimized` \| — \| — \|
88	\| `RandomWalkDrift` \| — \| — \|
89
90	### Exponential smoothing (5)
91
92	\| Model \| Required \| Optional \|
93	\|---\|---\|---\|
94	\| `Holt` \| — \| `alpha`, `beta` \|
95	\| `HoltWinters` \| `seasonal_period` \| `alpha`, `beta`, `gamma` \|
96	\| `SeasonalES` \| `seasonal_period` \| `alpha`, `gamma` \|
97	\| `SeasonalESOptimized` \| `seasonal_period` \| — \|
98	\| `SeasonalWindowAverage` \| `seasonal_period` \| — \|
99
100	### Theta (5)
101
102	\| Model \| Optional \|
103	\|---\|---\|
104	\| `Theta` \| `seasonal_period`, `theta` \|
105	\| `OptimizedTheta` \| `seasonal_period` \|
106	\| `DynamicTheta` \| `seasonal_period`, `theta` \|
107	\| `DynamicOptimizedTheta` \| `seasonal_period` \|
108	\| `AutoTheta` \| `seasonal_period` (listed above) \|
109
110	### State-space / ARIMA (2 — `AutoETS`/`AutoARIMA` counted above)
111
112	\| Model \| Required \| Optional \|
113	\|---\|---\|---\|
114	\| `ETS` \| — \| `seasonal_period`, `model` (`'AAA'`, `'AAN'`, …) \|
115	\| `ARIMA` \| `p`, `d`, `q` \| `P`, `D`, `Q`, `s` \|
116
117	### Multi-seasonal (3 — `Auto*` counted above)
118
119	\| Model \| Required \| Optional \|
120	\|---\|---\|---\|
121	\| `MFLES` \| `seasonal_periods[]` \| `iterations` \|
122	\| `MSTL` \| `seasonal_periods[]` \| `stl_method` \|
123	\| `TBATS` \| `seasonal_periods[]` \| `use_box_cox` \|

People who liked this also viewed

6 picks

Type