anofox-forecast-detection
Seasonality, changepoint, peak, and decomposition detection for the anofox_forecast DuckDB extension. Use when identifying seasonal periods before configuring seasonal forecasting models, detecting st
SKILL.mdplugins/anofox-forecast/skills/anofox-forecast-detection/SKILL.md
GitHubAnofox Forecast — Detection & Decomposition Cheat Sheet
Extension: anofox_forecast v2026.09.26 (Rust crate anofox-forecast v0.16.2) | DuckDB: v1.4.5 LTS / v1.5.4+ | Dual naming: ts_* and anofox_fcst_ts_*
Detect signal structure. When both seasonal_period and seasonal_periods are omitted,
ts_forecast_by / ts_cv_forecast_by / the scalar paths / ts_forecast_agg all auto-detect
the seasonal period internally (first detected period, 1 if none found) — same rule on every
surface. Running detection first and passing seasonal_period explicitly is still recommended
for control and reproducibility over what silent auto-detection would pick.
Requires: json extension
Detection functions use the json extension for parameter marshalling. Enable auto-load once per session:
SET autoinstall_known_extensions = 1;
SET autoload_known_extensions = 1;
Period detection — 12 methods
ts_detect_periods_by — primary entry point
ts_detect_periods_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
params MAP/STRUCT) → TABLE
Output columns (group column name is preserved):
| Column | Type | Description |
|---|---|---|
<group_col> |
(input type) | Preserved group column (e.g. product_id) |
periods |
STRUCT(period, confidence, strength, amplitude, phase, iteration, ...)[] |
Array of detected periods |
n_periods |
BIGINT | Count of detected periods |
primary_period |
DOUBLE | Top-level primary period (convenience, avoids struct indexing) |
method |
VARCHAR | Method that produced the result |
Params:
| Key | Default | Description |
|---|---|---|
method |
'fft' |
Detection method (see table below) |
max_period |
series length / 2 | Upper bound on detected period |
min_period |
2 | Lower bound |
-- Default (FFT) — use the top-level primary_period column
SELECT product_id, primary_period, method
FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
-- Autoperiod (FFT + ACF validation) — access full period list via struct-array unnest
SELECT product_id, method, unnest(periods).period AS p, unnest(periods).confidence AS conf
FROM ts_detect_periods_by('sales', product_id, ds, y,
MAP{'method': 'autoperiod'});
-- Both of these return primary_period = 12.0 on a clean period-12 sine
-- (DET-01 fix, 18-06: the default MAP{} call used to return an all-NULL
-- row -- fixed via FunctionNullHandling::SPECIAL_HANDLING on the
-- underlying _ts_detect_periods overloads, see CHANGELOG.md v2026.10.16).
CREATE TABLE sea AS
SELECT 'A' AS id, TIMESTAMP '2024-01-01' + INTERVAL (g) HOUR AS ts,
10 + 5*sin(2*pi()*g/12) AS y
FROM range(0,48) t(g);
SELECT * FROM ts_detect_periods_by('sea', id, ts, y, MAP{});
ts_detect_periods_agg — aggregate period detection
The GROUP BY aggregate form of period detection — an alternative to the _by table macro above when you want the result inline in a larger aggregate query rather than as its own table-returning call. Overloads: ts_detect_periods_agg(ts TIMESTAMP, value DOUBLE) and ts_detect_periods_agg(ts, value, method VARCHAR). Returns the same STRUCT as ts_detect_multiple_periods (periods[], n_periods, primary_period, method).
-- Setup: sea(id, ts, y) -- same shape as above.
CREATE OR REPLACE TABLE sea AS
SELECT 'A' AS id, TIMESTAMP '2024-01-01' + INTERVAL (g) HOUR AS ts,
10 + 5*sin(2*pi()*g/12) AS y
FROM range(0,48) t(g);
SELECT id, ts_detect_periods_agg(ts, y, 'fft').primary_period AS sp
FROM sea GROUP BY id;
Methods available
| Method string | Underlying algorithm | Best for |
|---|---|---|
'fft' |
FFT periodogram, linearly detrended first (D-11); picks the strongest bin 3 <= k <= floor(n/2), so period is never n |
Clean signals, fast (default) |
'acf' |
statsmodels-definition autocorrelation; smallest significant local-max lag >= 2 (D-11) |
Noisy signals, cyclical |
'autoperiod' |
FFT + ACF cross-check | General purpose, robust |
'aic' |
AIC criterion | Model-selection style |
'lomb_scargle' |
Lomb-Scargle | Irregularly sampled data |
'sazed' |
SAZED | Ensemble of methods |
'stl' |
STL decomposition | Trend + seasonal separation |
'ssa' |
Singular Spectrum Analysis | Multi-component |
'matrix_profile' |
Matrix profile | Motif-based |
'cfd_autoperiod' |
Clipped-FD autoperiod | Robust variant |
'instantaneous' |
Instantaneous frequency | Time-varying periods |
'auto' |
Auto-select | Unknown data |
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.
ts_detect_multiple_periods — multi-seasonal series
Some series have both weekly (7) and yearly (365) seasonality — use this for hourly / high-frequency data.
ts_detect_multiple_periods is a scalar aggregate over LIST(value ORDER BY date), used with GROUP BY. It returns a STRUCT with fields:
periods STRUCT(period DOUBLE, confidence DOUBLE, strength DOUBLE, amplitude DOUBLE, phase DOUBLE, iteration BIGINT, ...)[]n_periods BIGINTprimary_period DOUBLEmethod VARCHAR
Overloads: ts_detect_multiple_periods(list), ts_detect_multiple_periods(list, max_periods INTEGER), ts_detect_multiple_periods(list, max_periods, min_confidence DOUBLE, min_strength DOUBLE).
-- Scalar-aggregate GROUP BY form (canonical)
SELECT product_id,
(mp).primary_period,
(mp).n_periods,
unnest((mp).periods).period AS detected_period,
unnest((mp).periods).confidence AS confidence
FROM (
SELECT product_id,
ts_detect_multiple_periods(LIST(y ORDER BY ds)) AS mp
FROM sales
GROUP BY product_id
)
ORDER BY product_id;
Alternatively, use the ts_detect_periods_by table macro with MAP{'method': 'multi'} — same result, one-liner:
-- Setup: dual_seasonal(series_id, ds, value), 120 days, weekly + monthly pattern
-- (deterministic variant of examples/period_detection/synthetic_period_examples.sql's
-- Section 4 fixture -- that one uses RANDOM(), this one doesn't).
CREATE OR REPLACE TABLE dual_seasonal AS
SELECT
'dual_pattern' AS series_id,
DATE '2024-01-01' + INTERVAL (i) DAY AS ds,
100.0 + 30.0 * sin(2 * pi() * i / 7.0) + 15.0 * sin(2 * pi() * i / 30.0) AS value
FROM range(0, 120) t(i);
-- Table-macro form via ts_detect_periods_by (source: synthetic_period_examples.sql lines 175-179)
SELECT series_id, primary_period, n_periods
FROM ts_detect_periods_by('dual_seasonal', series_id, ds, value, MAP{'method': 'multi'});
Detect-then-forecast workflow (the standard pattern)
-- Step 1: Detect per-series period (use the top-level primary_period column)
CREATE OR REPLACE TABLE periods AS
SELECT product_id, primary_period AS sp
FROM ts_detect_periods_by('sales', product_id, ds, y, MAP{});
-- Step 2: Forecast with per-group detected period
-- Common: pick the mode across the panel, apply uniformly
CREATE OR REPLACE TABLE forecasts AS
SELECT * FROM ts_forecast_by('sales', product_id, ds, y,
'AutoETS', 14, '1d',
MAP{'seasonal_period': (SELECT mode() WITHIN GROUP (ORDER BY sp) FROM periods)::VARCHAR}
);
Changepoint detection
ts_detect_changepoints_by — Bayesian Online Changepoint Detection (Adams & MacKay 2007)
ts_detect_changepoints_by(source, group_col, date_col, value_col, params) → TABLE
Params:
| Key | Default | Description |
|---|---|---|
hazard_lambda |
250.0 | Expected run length between changepoints. Lower → more changepoints (and more false alarms) |
Returns one row per input point with <group_col>, <date_col>, is_changepoint BOOLEAN,
and changepoint_probability DOUBLE only — the value column is not included in the
output.
-- Detect changepoints (value column is NOT in the output; select from the source if needed)
SELECT product_id, ds, is_changepoint, changepoint_probability
FROM ts_detect_changepoints_by('sales', product_id, ds, y,
MAP{'hazard_lambda': '100'})
WHERE is_changepoint;
Note: the single-series ts_detect_changepoints (no group_col) has a different output
shape that DOES include the value column: (date_col, value_col, is_changepoint, changepoint_probability). It is not a simple alias of the _by form.
Also: ts_detect_changepoints (scalar) and ts_detect_changepoints_agg (aggregate).
Algorithm (Phase 15 plan 15-03, DET-02): the series is standardised first
(z = (x - median(x)) / sigma_hat, sigma_hat a MAD-of-first-differences robust
scale), making detection scale/shift invariant. The run-length posterior uses a
fully normalised Student-t predictive and a Normal-Gamma update on each run's
sample mean; run lengths beyond 500 are merged (not dropped) into the last kept
entry. A changepoint is flagged on a MAP run-length reset (the point that
started the new, shorter run) — changepoint_probability is the separate
quantity P(r_t=1) (this point opened a brand-new run). Measured on 100
independent N(0,1) series (no real changepoint): hazard_lambda=250 (default)
flags ~20 false alarms total, 500 flags ~11, 1000 flags ~5 — while a 3-sigma
step is still detected within 3 samples in ~94/100 cases at every one of those
hazard values. See docs/api/06-changepoint-detection.md for the full writeup.
PELT has no SQL surface. The crate's PELT implementation
(anofox_fcst_core::changepoint::detect_changepoints) is sigma-normalised and
exact-safe-pruned, but is Rust/FFI-only — its SQL wrapper was removed as dead
code in Phase 12, and ts_detect_changepoints always dispatches to BOCPD above.
Seasonality analysis — classify / measure strength
ts_classify_seasonality_by — timing / modulation / strength
ts_classify_seasonality_by(source, group_col, date_col, value_col, period DOUBLE) → TABLE
Output columns (group column preserved):
| Column | Type | Description |
|---|---|---|
<group_col> |
(input) | Preserved group column |
timing_classification |
VARCHAR | 'stable_seasonal' / 'variable_timing' / 'intermittent_seasonal' / 'non_seasonal' (NOT Regular/Weekly/Irregular — that is not the real label set) |
modulation_type |
VARCHAR | 'stable' / 'emerging' / 'fading' / 'oscillating' / 'non_seasonal' (NOT Additive/Multiplicative/None — that is not the real label set) |
has_stable_timing |
BOOLEAN | Peak timing regularity |
timing_variability |
DOUBLE | Numeric variability score |
seasonal_strength |
DOUBLE | ∈ [0, 1] |
is_seasonal |
BOOLEAN | Overall verdict |
cycle_strengths |
DOUBLE[] | Per-cycle strengths |
weak_seasons |
BIGINT[] | Indices of weak seasonal peaks |
Phase 15: a clean, clearly-seasonal series (e.g. a sine wave at the correct period) now
correctly comes back as timing_classification = 'stable_seasonal', is_seasonal = true,
with seasonal_strength close to 1.0 (previously a fdars-core dimension-swap bug made every
call return 'non_seasonal' / NaN).
SELECT product_id, timing_classification, modulation_type, is_seasonal, seasonal_strength
FROM ts_classify_seasonality_by('sales', product_id, ds, y, 7.0);
Scalar variants: ts_classify_seasonality, ts_classify_seasonality_agg.
ts_seasonal_strength, ts_seasonal_strength_windowed, ts_analyze_seasonality
Numeric measures over LIST(y ORDER BY ds). windowed variant tracks strength changes across the series.
method is 'variance' (default, alias var) or 'spectral' (alias spec, fft). Both
LINEARLY DETREND the series first (D-12) — a trend + seasonal series previously measured
variance strength ~0.003 on the raw series; detrended it reports >= 0.95. 'wavelet' is removed (it returned 1.0 even for white noise in fdars-core 0.47) and now raises an
error naming the two remaining methods; any other unrecognized method name, or a non-positive
period, also raises. ts_analyze_seasonality's trend_strength is |r| (absolute
correlation with time, the square root of the linear-fit R^2).
ts_detect_seasonality, ts_detect_seasonality_changes
Detect the presence and any regime shifts in the seasonal pattern.
Peak detection & timing
ts_detect_peaks_by
ts_detect_peaks_by(source, group_col, date_col, value_col, params) → TABLE(group_col, peaks, n_peaks, inter_peak_distances, mean_period)
Returns flat top-level columns n_peaks / inter_peak_distances / mean_period alongside
peaks (the array of individual peak info) — NOT a single nested peaks STRUCT wrapping
everything.
Algorithm (Phase 15, DET-01): a direct port of scipy.signal.find_peaks 1.18.1
(wlen=None), not a normalised-score heuristic. prominence is on the same scale as the
input data (not 0..1); a plateau reports its midpoint index; min_distance <= 1.0
(the macro default) applies no distance filter. See docs/api/05b-peak-detection.md for the
full parameter mapping.
ts_analyze_peak_timing_by
ts_analyze_peak_timing_by(source, group_col, date_col, value_col, period, params) → TABLE
Measures how tightly peaks cluster at a specific phase of the seasonal cycle. Useful for retail (do peaks land on the same day of the week?).
Also: ts_detect_peaks, ts_analyze_peak_timing, ts_detect_amplitude_modulation.
Decomposition
ts_mstl_decomposition_by — Multiple Seasonal-Trend decomposition (LOESS)
ts_mstl_decomposition_by(source, group_col, date_col, value_col, params) → TABLE(group_col, trend, seasonal, remainder, periods)
Algorithm (Phase 15, D-09): matches R forecast::mstl. params.seasonal_periods (alias
periods, a '[7, 365]' string or [7, 365] list) honours every period that needs fewer
than n/2 points — those that don't are silently dropped (R's own behaviour); survivors are
sorted ascending and fit in two passes (s.window = 7 + 4*position-stepped STL, trend from
the last fit). With seasonal_periods omitted or every requested period too large, the trend
is stats::supsmu (non-seasonal fallback) and seasonal/periods come back empty. There is
no seasonal_1/seasonal_2-style per-period column naming — seasonal is DOUBLE[][],
one array per entry of periods, same order.
-- One row per group with array-valued trend/seasonal/remainder (no per-point date column)
SELECT product_id, trend, seasonal, remainder, periods
FROM ts_mstl_decomposition_by('sales', product_id, ds, y, MAP{'seasonal_periods': '[7, 365]'});
-- No seasonal_periods -> non-seasonal supsmu trend, seasonal/periods come back empty
SELECT periods FROM ts_mstl_decomposition_by('sales', product_id, ds, y, MAP{});
-- []
ts_decompose_seasonal (scalar) — fdars LOESS+Fourier, or R classical (centred moving-average)
Signature ts_decompose_seasonal(DOUBLE[] values, DOUBLE period [, VARCHAR method]) → STRUCT(trend DOUBLE[], seasonal DOUBLE[], remainder DOUBLE[], period DOUBLE, method VARCHAR).
method: 'additive' (alias 'add', default, data = trend + seasonal + remainder) or
'multiplicative' (alias 'mult'/'mul', data = trend * seasonal * remainder) — fdars
LOESS trend (bandwidth 0.3, degree 2) + a 3-harmonic Fourier seasonal fit to the detrended
residual; or 'classical_additive' (alias 'classical') / 'classical_multiplicative' — R
stats::decompose's centred moving-average trend (Phase 15, D-10): a 2 x period filter for
an even period, the plain period-term mean for an odd period; trend/remainder are NaN
(check with isnan(...), not IS NULL) at the floor(period/2) points at each end, matching
R's own NA edges exactly. classical_* requires an INTEGER period >= 2;
classical_multiplicative additionally requires every value > 0 (unlike R, which would
silently divide by a non-positive value). Any other method name, or a non-positive period,
is a SQL error naming the valid choices.
ts_detrend_by
Remove linear or polynomial trend:
ts_detrend_by(source, group_col, date_col, value_col, method) → TABLE
method: 'linear'; 'quadratic' (alias 'polynomial', 'poly', degree 2); 'cubic'
(degree 3); 'diff' (alias 'diff1', 'difference'); 'diff2'; 'loess' (alias
'lowess'); 'auto' (default — AIC-selects among linear/quadratic/cubic/loess). Any other
name is a SQL error naming every valid choice — there is no silent fallback. 'loess' and
'auto' are O(n^2) in series length; prefer 'linear'/'quadratic'/'cubic' on long
series.
ts_detrend (scalar) — array-level trend removal
The array-level primitive behind ts_detrend_by. Signature ts_detrend(DOUBLE[] values [, VARCHAR method]) → STRUCT(trend DOUBLE[], detrended DOUBLE[], method VARCHAR, coefficients DOUBLE[], rss DOUBLE, n_params BIGINT). coefficients for 'quadratic'/'cubic' are ascending powers of the raw 0-based time index. For grouped series prefer ts_detrend_by — it handles the per-group GROUP BY shape and is the documented path.
Gotchas
- Single-series NULL policy (Phase 12 D-03/D-04) applies to every period/seasonality/diagnostic/
decomposition/peak/changepoint function (period estimators,
ts_detect_seasonality/ts_analyze_seasonality/ts_classify_seasonality, the diagnostics inanofox-forecast-backtest'sts_adf/ts_kpss/etc. family,ts_detrend/ts_decompose_seasonal/ts_seasonal_strength/ts_seasonal_strength_windowed/ts_instantaneous_period,ts_detect_peaks/ts_analyze_peak_timing,_ts_mstl_decomposition, and_ts_detect_changepoints_bocpd): leading/trailing NULLs are trimmed before the analysis, an interior NULL is a hard error naming its 1-based position (e.g.interior NULL at position 6), and an all-NULL list returns SQL NULL rather than erroring. Per-observation array outputs (trend,detrended,seasonal, peakindex,is_changepoint, ...) describe the trimmed series — position 1 is the first non-NULL input value, not the original row 0; the single-seriests_detect_changepointsmacro shifts its row-level output back by the leading-NULL row count so those rows get NULL flags. Impute interior gaps first (e.g.ts_fill_nulls_mean_by) if your series has them; wrap intry(...)to convert the error to NULL instead of failing the query. - Seasonality auto-detection applies uniformly across surfaces.
AutoETS/AutoARIMA/AutoTheta(and other models) viats_forecast_by,ts_cv_forecast_by, the scalar paths, andts_forecast_aggall auto-detect whenseasonal_period/seasonal_periodsare both omitted (10-10, FCST-06). Run detection first and passseasonal_periodexplicitly if you want control/reproducibility rather than relying on silent auto-detection. ts_detect_periods_byreturns one row per group with aperiodsSTRUCT. Access fields withprimary_period.- Detection needs sufficient history: FFT-family methods need ≥ 2 full cycles; ACF-family needs ≥ 3. On short-history panels,
ts_detect_periodsmay returnprimary_period = 1(no seasonality) even when a period exists. _ts_mstl_decomposition/ts_mstl_decomposition_byreturnperiodsascending, de-duplicated — if you request[28, 7]you getperiods = [7, 28]back, withseasonal[1]corresponding to period 7 andseasonal[2]to period 28, not the request order.- FFT/ACF/regression period estimators and seasonal-strength methods were fixed in Phase 15
(D-11, D-12):
ts_estimate_period_fftnow linearly detrends first and never returnsperiod = nor>= n/2(previously a trend + seasonal series reported period= n);ts_estimate_period_acfcan now return lag 2 and requires statistical significance (previously the search started at lag 3 with no significance test);_ts_detect_periods(..., 'regression')'s default grid is every integer, so an exact period like 12 is never skipped.ts_seasonal_strength/ts_seasonal_strength_windowednow detrend first — a trend-dominated series that is ALSO strongly seasonal now correctly reports a high strength instead of a near-zero one.'wavelet'as a strength method is removed (always returned1.0, even for white noise); use'variance'(default) or'spectral'.
Canonical detection pipeline
-- 1. Detect periods across the panel (aggregate form shown here; the ts_detect_periods_by
-- table macro returns the same values and is an equally valid alternative -- see its
-- example above)
CREATE OR REPLACE TABLE detected AS
SELECT product_id,
ts_detect_periods_agg(ds, y, 'fft').primary_period AS sp,
ts_detect_periods_agg(ds, y, 'fft').periods[1].confidence AS conf
FROM sales
GROUP BY product_id;
-- 2. Classify seasonality mode at the modal period (real column names are
-- timing_classification / modulation_type, not classification / confidence)
CREATE OR REPLACE TABLE modes AS
SELECT product_id, timing_classification, modulation_type
FROM ts_classify_seasonality_by('sales', product_id, ds, y,
(SELECT mode() WITHIN GROUP (ORDER BY sp) FROM detected));
-- 3. Optional: flag changepoints for post-hoc review
CREATE OR REPLACE TABLE breaks AS
SELECT product_id, ds, is_changepoint
FROM ts_detect_changepoints_by('sales', product_id, ds, y, MAP{'hazard_lambda': '250'})
WHERE is_changepoint;
See also: anofox-forecast-data-prep (fill gaps before detection — needs regular grid), anofox-forecast-eda (ts_stats's STL-based trend_strength / seasonality_strength, D-02, gate the need for detection — 0.3/0.6 rule-of-thumb thresholds, consistent with this skill's own strength measures), anofox-forecast-models (pass detected seasonal_period to ts_forecast_by or to ts_stats's own seasonal_period parameter).
Reference docs:
docs/api/05-period-detection.mddocs/api/05a-decomposition.mddocs/api/05b-peak-detection.mddocs/api/06-changepoint-detection.md