anofox-forecast-backtest

Name: anofox-forecast-backtest
Author: DataZooDE

35stars3forks

How to install anofox-forecast-backtest?

datazoode/anofox-forecast/anofox-forecast-backtest

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

Installs into the current project.

Summary

Backtesting, cross-validation, evaluation metrics, and conformal prediction intervals for the anofox_forecast DuckDB extension. Use when evaluating forecast accuracy, comparing models with time-series-aware CV, computing metrics (MAE / RMSE / MAPE / MASE / coverage), or attaching

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Backtesting, CV, Metrics & Conformal Cheat Sheet
2
3	Extension: `anofox_forecast` v0.15.3 \| DuckDB: v1.4.5 LTS / v1.5.4+ \| Dual naming: `ts_` and `anofox_fcst_ts_`
4
5	Time-series-aware cross-validation, error metrics, and distribution-free intervals.
6
7	## Critical gotchas
8
9	- `ts_backtest_auto_by` was REMOVED. Use the two-step CV workflow (`ts_cv_folds_by` → `ts_cv_forecast_by`) instead. Older docs and tests may still reference the retired one-liner.
10	- Metric `_by` table macros (`ts_mae_by`, `ts_rmse_by`, …) are deprecated. They're ~2400× slower than the scalar + `GROUP BY` pattern and don't parallelise. Use scalars.
11	- Always `ORDER BY` inside `LIST()` for temporal correctness: `LIST(y ORDER BY ds)`, not `LIST(y)`.
12	- `ts_cv_forecast_by` output renames the target column to `y` (canonical). Don't try to access the original name.
13	- Folds must be pre-computed before forecasting. Passing raw data to `ts_cv_forecast_by` throws a clear error.
14
15	## The CV two-step workflow (standard)
16
17	```sql
18	-- Step 1: Create fold table (train/test rows with actual dates)
19	CREATE OR REPLACE TABLE cv_folds AS
20	SELECT * FROM ts_cv_folds_by('data', unique_id, ds, y,
21	3, -- n_folds
22	12, -- horizon per fold
23	MAP{}); -- optional params
24
25	-- Step 2: Forecast per fold's train set, predict its test set
26	CREATE OR REPLACE TABLE cv_forecasts AS
27	SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y,
28	'AutoETS',
29	MAP{'seasonal_period': '12'});
30
31	-- Step 3: Compute per-series / per-fold metrics
32	SELECT unique_id, fold_id,
33	ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS rmse,
34	ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae
35	FROM cv_forecasts
36	GROUP BY unique_id, fold_id;
37	```
38
39	## `ts_cv_folds_by`
40
41	```sql
42	ts_cv_folds_by(source VARCHAR, group_col COLUMN, date_col COLUMN, target_col COLUMN,
43	n_folds BIGINT, horizon BIGINT, params MAP/STRUCT) → TABLE
44	```
45
46	Input must be pre-cleaned (no gaps, consistent frequency). Uses position-based indexing so works with all frequencies (including calendar-based monthly / quarterly / yearly).
47
48	Params:
49
50	\| Key \| Default \| Description \|
51	\|---\|---\|---\|
52	\| `gap` \| 0 \| Periods between train end and test start \|
53	\| `embargo` \| 0 \| Periods excluded from training after previous test \|
54	\| `window_type` \| `'expanding'` \| `'expanding'`, `'fixed'`, or `'sliding'` \|
55	\| `min_train_size` \| 1 \| Min training size (fixed / sliding only) \|
56	\| `initial_train_size` \| auto \| Periods before first fold \|
57	\| `skip_length` \| horizon \| Periods between folds (1 = dense overlap) \|
58	\| `clip_horizon` \| false \| Allow partial test windows near series end \|
59
60	Output: 5 columns — `<group_col>`, `<date_col>`, `<target_col>`, `fold_id BIGINT`, `split VARCHAR` (`'train'` / `'test'`). Features NOT passed through — use `ts_cv_hydrate_by` to join extra columns.
61
62	## `ts_cv_split_by` / `ts_cv_split_folds_by` / `ts_cv_split_index_by`
63
64	Alternate fold-creation entry points for custom cutoffs, pre-computed fold boundaries, or memory-efficient index-only splits. See `docs/api/08-cross-validation.md` for their param signatures.
65
66	## `ts_cv_forecast_by`
67
68	```sql
69	ts_cv_forecast_by(cv_folds_table VARCHAR, group_col COLUMN, date_col COLUMN, target_col COLUMN,
70	method VARCHAR, params MAP/STRUCT) → TABLE
71	```
72
73	Fits `method` on each fold's train partition, forecasts the test partition.
74
75	Output columns: `fold_id`, `<group_col>`, `<date_col>`, `y` (renamed from target_col), `split`, `yhat`, `yhat_lower`, `yhat_upper`, `model_name`.
76
77	Params: same shape as `ts_forecast_by`. All model strings work (`'AutoETS'`, `'Laplace'`, `'HoltWinters'`, etc.). See `anofox-forecast-models` for the full catalogue.
78
79	## `ts_cv_hydrate_by` — attach features to fold rows
80
81	If your model needs exogenous columns (features, calendar flags, promotions), the folds table lost them. Join them back:
82
83	```sql
84	CREATE OR REPLACE TABLE cv_folds_with_features AS
85	SELECT * FROM ts_cv_hydrate_by('cv_folds', unique_id, ds, y, 'features', ['promo', 'holiday']);
86	```
87
88	## `ts_check_leakage`
89
90	Sanity-check that no test-window rows leaked into training:
91
92	```sql
93	SELECT * FROM ts_check_leakage('cv_folds', unique_id, ds, fold_id, split);
94	```
95
96	## Metrics — use scalar functions
97
98	Pattern: `SELECT group, ts_<metric>(LIST(actual ORDER BY ds), LIST(pred ORDER BY ds)) FROM ... GROUP BY group;`
99
100	\| Function \| Signature \| Description \|
101	\|---\|---\|---\|
102	\| `ts_mae` \| `(DOUBLE[], DOUBLE[]) → DOUBLE` \| Mean Absolute Error \|
103	\| `ts_mse` \| `(DOUBLE[], DOUBLE[]) → D

People who liked this also viewed

6 picks

Type

anofox-forecast-backtest

35stars3forks

View on GitHub

How to install anofox-forecast-backtest?

datazoode/anofox-forecast/anofox-forecast-backtest

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

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Backtesting, CV, Metrics & Conformal Cheat Sheet
2
3	Extension: `anofox_forecast` v0.15.3 \| DuckDB: v1.4.5 LTS / v1.5.4+ \| Dual naming: `ts_` and `anofox_fcst_ts_`
4
5	Time-series-aware cross-validation, error metrics, and distribution-free intervals.
6
7	## Critical gotchas
8
9	- `ts_backtest_auto_by` was REMOVED. Use the two-step CV workflow (`ts_cv_folds_by` → `ts_cv_forecast_by`) instead. Older docs and tests may still reference the retired one-liner.
10	- Metric `_by` table macros (`ts_mae_by`, `ts_rmse_by`, …) are deprecated. They're ~2400× slower than the scalar + `GROUP BY` pattern and don't parallelise. Use scalars.
11	- Always `ORDER BY` inside `LIST()` for temporal correctness: `LIST(y ORDER BY ds)`, not `LIST(y)`.
12	- `ts_cv_forecast_by` output renames the target column to `y` (canonical). Don't try to access the original name.
13	- Folds must be pre-computed before forecasting. Passing raw data to `ts_cv_forecast_by` throws a clear error.
14
15	## The CV two-step workflow (standard)
16
17	```sql
18	-- Step 1: Create fold table (train/test rows with actual dates)
19	CREATE OR REPLACE TABLE cv_folds AS
20	SELECT * FROM ts_cv_folds_by('data', unique_id, ds, y,
21	3, -- n_folds
22	12, -- horizon per fold
23	MAP{}); -- optional params
24
25	-- Step 2: Forecast per fold's train set, predict its test set
26	CREATE OR REPLACE TABLE cv_forecasts AS
27	SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y,
28	'AutoETS',
29	MAP{'seasonal_period': '12'});
30
31	-- Step 3: Compute per-series / per-fold metrics
32	SELECT unique_id, fold_id,
33	ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS rmse,
34	ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae
35	FROM cv_forecasts
36	GROUP BY unique_id, fold_id;
37	```
38
39	## `ts_cv_folds_by`
40
41	```sql
42	ts_cv_folds_by(source VARCHAR, group_col COLUMN, date_col COLUMN, target_col COLUMN,
43	n_folds BIGINT, horizon BIGINT, params MAP/STRUCT) → TABLE
44	```
45
46	Input must be pre-cleaned (no gaps, consistent frequency). Uses position-based indexing so works with all frequencies (including calendar-based monthly / quarterly / yearly).
47
48	Params:
49
50	\| Key \| Default \| Description \|
51	\|---\|---\|---\|
52	\| `gap` \| 0 \| Periods between train end and test start \|
53	\| `embargo` \| 0 \| Periods excluded from training after previous test \|
54	\| `window_type` \| `'expanding'` \| `'expanding'`, `'fixed'`, or `'sliding'` \|
55	\| `min_train_size` \| 1 \| Min training size (fixed / sliding only) \|
56	\| `initial_train_size` \| auto \| Periods before first fold \|
57	\| `skip_length` \| horizon \| Periods between folds (1 = dense overlap) \|
58	\| `clip_horizon` \| false \| Allow partial test windows near series end \|
59
60	Output: 5 columns — `<group_col>`, `<date_col>`, `<target_col>`, `fold_id BIGINT`, `split VARCHAR` (`'train'` / `'test'`). Features NOT passed through — use `ts_cv_hydrate_by` to join extra columns.
61
62	## `ts_cv_split_by` / `ts_cv_split_folds_by` / `ts_cv_split_index_by`
63
64	Alternate fold-creation entry points for custom cutoffs, pre-computed fold boundaries, or memory-efficient index-only splits. See `docs/api/08-cross-validation.md` for their param signatures.
65
66	## `ts_cv_forecast_by`
67
68	```sql
69	ts_cv_forecast_by(cv_folds_table VARCHAR, group_col COLUMN, date_col COLUMN, target_col COLUMN,
70	method VARCHAR, params MAP/STRUCT) → TABLE
71	```
72
73	Fits `method` on each fold's train partition, forecasts the test partition.
74
75	Output columns: `fold_id`, `<group_col>`, `<date_col>`, `y` (renamed from target_col), `split`, `yhat`, `yhat_lower`, `yhat_upper`, `model_name`.
76
77	Params: same shape as `ts_forecast_by`. All model strings work (`'AutoETS'`, `'Laplace'`, `'HoltWinters'`, etc.). See `anofox-forecast-models` for the full catalogue.
78
79	## `ts_cv_hydrate_by` — attach features to fold rows
80
81	If your model needs exogenous columns (features, calendar flags, promotions), the folds table lost them. Join them back:
82
83	```sql
84	CREATE OR REPLACE TABLE cv_folds_with_features AS
85	SELECT * FROM ts_cv_hydrate_by('cv_folds', unique_id, ds, y, 'features', ['promo', 'holiday']);
86	```
87
88	## `ts_check_leakage`
89
90	Sanity-check that no test-window rows leaked into training:
91
92	```sql
93	SELECT * FROM ts_check_leakage('cv_folds', unique_id, ds, fold_id, split);
94	```
95
96	## Metrics — use scalar functions
97
98	Pattern: `SELECT group, ts_<metric>(LIST(actual ORDER BY ds), LIST(pred ORDER BY ds)) FROM ... GROUP BY group;`
99
100	\| Function \| Signature \| Description \|
101	\|---\|---\|---\|
102	\| `ts_mae` \| `(DOUBLE[], DOUBLE[]) → DOUBLE` \| Mean Absolute Error \|
103	\| `ts_mse` \| `(DOUBLE[], DOUBLE[]) → D

People who liked this also viewed

6 picks

Type