anofox-forecast-detection

Name: anofox-forecast-detection
Author: DataZooDE

35stars3forks

How to install anofox-forecast-detection?

datazoode/anofox-forecast/anofox-forecast-detection

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

Installs into the current project.

Summary

Seasonality, changepoint, peak, and decomposition detection for the anofox_forecast DuckDB extension. Use when identifying seasonal periods before configuring seasonal forecasting models, detecting structural breaks, analysing peak timing regularity, or decomposing a series into

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Detection & Decomposition 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	Detect signal structure — seasonality is not auto-detected by the forecasters; you must run detection first and pass `seasonal_period` explicitly to `ts_forecast_by`.
6
7	## Requires: json extension
8
9	Detection functions use the `json` extension for parameter marshalling. Enable auto-load once per session:
10
11	```sql
12	SET autoinstall_known_extensions = 1;
13	SET autoload_known_extensions = 1;
14	```
15
16	## Period detection — 12 methods
17
18	### `ts_detect_periods_by` — primary entry point
19
20	```sql
21	ts_detect_periods_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
22	params MAP/STRUCT) → TABLE
23	```
24
25	Output columns (group column name is preserved):
26
27	\| Column \| Type \| Description \|
28	\|---\|---\|---\|
29	\| `<group_col>` \| (input type) \| Preserved group column (e.g. `product_id`) \|
30	\| `periods` \| `STRUCT(period, confidence, strength, amplitude, phase, iteration, ...)[]` \| Array of detected periods \|
31	\| `n_periods` \| BIGINT \| Count of detected periods \|
32	\| `primary_period` \| DOUBLE \| Top-level primary period (convenience, avoids struct indexing) \|
33	\| `method` \| VARCHAR \| Method that produced the result \|
34
35	Params:
36
37	\| Key \| Default \| Description \|
38	\|---\|---\|---\|
39	\| `method` \| `'fft'` \| Detection method (see table below) \|
40	\| `max_period` \| series length / 2 \| Upper bound on detected period \|
41	\| `min_period` \| 2 \| Lower bound \|
42
43	```sql
44	-- Default (FFT) — use the top-level primary_period column
45	SELECT product_id, primary_period, method
46	FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
47
48	-- Autoperiod (FFT + ACF validation) — access full period list via struct-array unnest
49	SELECT product_id, method, unnest(periods).period AS p, unnest(periods).confidence AS conf
50	FROM ts_detect_periods_by('sales', product_id, ds, y,
51	MAP{'method': 'autoperiod'});
52	```
53
54	### Methods available
55
56	\| Method string \| Underlying algorithm \| Best for \|
57	\|---\|---\|---\|
58	\| `'fft'` \| FFT periodogram \| Clean signals, fast (default) \|
59	\| `'acf'` \| Autocorrelation \| Noisy signals, cyclical \|
60	\| `'autoperiod'` \| FFT + ACF cross-check \| General purpose, robust \|
61	\| `'aic'` \| AIC criterion \| Model-selection style \|
62	\| `'lomb_scargle'` \| Lomb-Scargle \| Irregularly sampled data \|
63	\| `'sazed'` \| SAZED \| Ensemble of methods \|
64	\| `'stl'` \| STL decomposition \| Trend + seasonal separation \|
65	\| `'ssa'` \| Singular Spectrum Analysis \| Multi-component \|
66	\| `'matrix_profile'` \| Matrix profile \| Motif-based \|
67	\| `'cfd_autoperiod'` \| Clipped-FD autoperiod \| Robust variant \|
68	\| `'instantaneous'` \| Instantaneous frequency \| Time-varying periods \|
69	\| `'auto'` \| Auto-select \| Unknown data \|
70
71	Also available as scalar functions over `LIST(y ORDER BY ds)`: `ts_autoperiod`, `ts_cfd_autoperiod`, `ts_aic_period`, `ts_lomb_scargle`, `ts_ssa_period`, `ts_stl_period`, `ts_sazed_period`, `ts_matrix_profile_period`, `ts_estimate_period_fft`, `ts_estimate_period_acf`, `ts_instantaneous_period`.
72
73	### `ts_detect_multiple_periods` — multi-seasonal series
74
75	Some series have both weekly (7) and yearly (365) seasonality — use this for hourly / high-frequency data.
76
77	```sql
78	SELECT id, unnest(periods) AS p
79	FROM ts_detect_multiple_periods_by('sales', product_id, ds, y, MAP{});
80	```
81
82	## Detect-then-forecast workflow (the standard pattern)
83
84	```sql
85	-- Step 1: Detect per-series period (use the top-level primary_period column)
86	CREATE OR REPLACE TABLE periods AS
87	SELECT product_id, primary_period AS sp
88	FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
89
90	-- Step 2: Forecast with per-group detected period
91	-- Common: pick the mode across the panel, apply uniformly
92	CREATE OR REPLACE TABLE forecasts AS
93	SELECT * FROM ts_forecast_by('sales', product_id, ds, y,
94	'AutoETS', 14, '1d',
95	MAP{'seasonal_period': (SELECT mode() WITHIN GROUP (ORDER BY sp) FROM periods)::VARCHAR}
96	);
97	```
98
99	## Changepoint detection
100
101	### `ts_detect_changepoints_by` — Bayesian Online Changepoint Detection (BOCD)
102
103	```sql
104	ts_detect_changepoints_by(source, group_col, date_col, value_col, params) → TABLE
105	```
106
107	Params:
108	\| Key \| Default \| Description \|
109	\|---\|---\|---\|
110	\| `hazard_lambda` \| 250.0 \| Hazard rate. Lower → more changepoints \|
111
112	Returns one row per input point with `is_changepoint BOOLEAN` and `changepoint_probability DOUBLE`.
113
114	```sql
115	-- Detect changepoints
116	SELECT product_id, ds, y, is_changepoint, changepoint_probability
117	FROM ts_detect_changepoints_by('sales', product_id, ds, y,
118	MAP{'hazard_lambda': '100'})
119	WHERE is_

People who liked this also viewed

6 picks

Type

anofox-forecast-detection

35stars3forks

View on GitHub

How to install anofox-forecast-detection?

datazoode/anofox-forecast/anofox-forecast-detection

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

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Detection & Decomposition 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	Detect signal structure — seasonality is not auto-detected by the forecasters; you must run detection first and pass `seasonal_period` explicitly to `ts_forecast_by`.
6
7	## Requires: json extension
8
9	Detection functions use the `json` extension for parameter marshalling. Enable auto-load once per session:
10
11	```sql
12	SET autoinstall_known_extensions = 1;
13	SET autoload_known_extensions = 1;
14	```
15
16	## Period detection — 12 methods
17
18	### `ts_detect_periods_by` — primary entry point
19
20	```sql
21	ts_detect_periods_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
22	params MAP/STRUCT) → TABLE
23	```
24
25	Output columns (group column name is preserved):
26
27	\| Column \| Type \| Description \|
28	\|---\|---\|---\|
29	\| `<group_col>` \| (input type) \| Preserved group column (e.g. `product_id`) \|
30	\| `periods` \| `STRUCT(period, confidence, strength, amplitude, phase, iteration, ...)[]` \| Array of detected periods \|
31	\| `n_periods` \| BIGINT \| Count of detected periods \|
32	\| `primary_period` \| DOUBLE \| Top-level primary period (convenience, avoids struct indexing) \|
33	\| `method` \| VARCHAR \| Method that produced the result \|
34
35	Params:
36
37	\| Key \| Default \| Description \|
38	\|---\|---\|---\|
39	\| `method` \| `'fft'` \| Detection method (see table below) \|
40	\| `max_period` \| series length / 2 \| Upper bound on detected period \|
41	\| `min_period` \| 2 \| Lower bound \|
42
43	```sql
44	-- Default (FFT) — use the top-level primary_period column
45	SELECT product_id, primary_period, method
46	FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
47
48	-- Autoperiod (FFT + ACF validation) — access full period list via struct-array unnest
49	SELECT product_id, method, unnest(periods).period AS p, unnest(periods).confidence AS conf
50	FROM ts_detect_periods_by('sales', product_id, ds, y,
51	MAP{'method': 'autoperiod'});
52	```
53
54	### Methods available
55
56	\| Method string \| Underlying algorithm \| Best for \|
57	\|---\|---\|---\|
58	\| `'fft'` \| FFT periodogram \| Clean signals, fast (default) \|
59	\| `'acf'` \| Autocorrelation \| Noisy signals, cyclical \|
60	\| `'autoperiod'` \| FFT + ACF cross-check \| General purpose, robust \|
61	\| `'aic'` \| AIC criterion \| Model-selection style \|
62	\| `'lomb_scargle'` \| Lomb-Scargle \| Irregularly sampled data \|
63	\| `'sazed'` \| SAZED \| Ensemble of methods \|
64	\| `'stl'` \| STL decomposition \| Trend + seasonal separation \|
65	\| `'ssa'` \| Singular Spectrum Analysis \| Multi-component \|
66	\| `'matrix_profile'` \| Matrix profile \| Motif-based \|
67	\| `'cfd_autoperiod'` \| Clipped-FD autoperiod \| Robust variant \|
68	\| `'instantaneous'` \| Instantaneous frequency \| Time-varying periods \|
69	\| `'auto'` \| Auto-select \| Unknown data \|
70
71	Also available as scalar functions over `LIST(y ORDER BY ds)`: `ts_autoperiod`, `ts_cfd_autoperiod`, `ts_aic_period`, `ts_lomb_scargle`, `ts_ssa_period`, `ts_stl_period`, `ts_sazed_period`, `ts_matrix_profile_period`, `ts_estimate_period_fft`, `ts_estimate_period_acf`, `ts_instantaneous_period`.
72
73	### `ts_detect_multiple_periods` — multi-seasonal series
74
75	Some series have both weekly (7) and yearly (365) seasonality — use this for hourly / high-frequency data.
76
77	```sql
78	SELECT id, unnest(periods) AS p
79	FROM ts_detect_multiple_periods_by('sales', product_id, ds, y, MAP{});
80	```
81
82	## Detect-then-forecast workflow (the standard pattern)
83
84	```sql
85	-- Step 1: Detect per-series period (use the top-level primary_period column)
86	CREATE OR REPLACE TABLE periods AS
87	SELECT product_id, primary_period AS sp
88	FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
89
90	-- Step 2: Forecast with per-group detected period
91	-- Common: pick the mode across the panel, apply uniformly
92	CREATE OR REPLACE TABLE forecasts AS
93	SELECT * FROM ts_forecast_by('sales', product_id, ds, y,
94	'AutoETS', 14, '1d',
95	MAP{'seasonal_period': (SELECT mode() WITHIN GROUP (ORDER BY sp) FROM periods)::VARCHAR}
96	);
97	```
98
99	## Changepoint detection
100
101	### `ts_detect_changepoints_by` — Bayesian Online Changepoint Detection (BOCD)
102
103	```sql
104	ts_detect_changepoints_by(source, group_col, date_col, value_col, params) → TABLE
105	```
106
107	Params:
108	\| Key \| Default \| Description \|
109	\|---\|---\|---\|
110	\| `hazard_lambda` \| 250.0 \| Hazard rate. Lower → more changepoints \|
111
112	Returns one row per input point with `is_changepoint BOOLEAN` and `changepoint_probability DOUBLE`.
113
114	```sql
115	-- Detect changepoints
116	SELECT product_id, ds, y, is_changepoint, changepoint_probability
117	FROM ts_detect_changepoints_by('sales', product_id, ds, y,
118	MAP{'hazard_lambda': '100'})
119	WHERE is_

People who liked this also viewed

6 picks

Type