Seasonality Detection

Detect and analyze seasonal patterns in your time series data with 12 detection methods and pattern classification.

All TypeTable Macro

Function	Description	Type
ts_detect_periods_by	Multi-method period detection (12 algorithms)	Table Macro
ts_classify_seasonality_by	Classify seasonality type per group	Table Macro
ts_classify_seasonality	Classify seasonality (single series)	Table Macro

Showing 3 of 3

---

ts_detect_periods_by

Detect seasonal periods for grouped series using one of 12 algorithms.

ts_detect_periods_by(
    source VARCHAR,
    group_col COLUMN,
    date_col COLUMN,
    value_col COLUMN,
    params MAP
) → TABLE(id, periods)

Parameters:

Parameter	Type	Required	Description
source	VARCHAR	Yes	Source table name
group_col	COLUMN	Yes	Series identifier (unquoted)
date_col	COLUMN	Yes	Date/timestamp column (unquoted)
value_col	COLUMN	Yes	Value column (unquoted)
params	MAP	No	Configuration (use MAP{} for defaults)

Params MAP Options:

Key	Type	Default	Description
method	VARCHAR	'fft'	Detection algorithm (see table below)
max_period	VARCHAR	'365'	Maximum period to search
min_confidence	VARCHAR	method-specific	Minimum confidence threshold; '0' to see all

Detection Methods

The method parameter selects one of 12 algorithms, each optimized for different data characteristics:

Method	Aliases	Speed	Best For
'fft'	'periodogram'	Very Fast	Clean signals (default)
'acf'	'autocorrelation'	Fast	Cyclical patterns, noise-robust
'autoperiod'	'ap'	Fast	General purpose, robust
'cfd'	'cfdautoperiod'	Fast	Trending data
'lombscargle'	'lomb_scargle'	Medium	Irregular sampling
'aic'	'aic_comparison'	Slow	Model comparison
'ssa'	'singular_spectrum'	Medium	Complex patterns
'stl'	'stl_period'	Slow	Decomposition-based
'matrix_profile'	'matrixprofile'	Slow	Pattern repetition
'sazed'	'zero_padded'	Medium	High frequency resolution
'auto'	—	Medium	Unknown characteristics
'multi'	'multiple'	Medium	Multiple seasonalities

Returns

Returns a periods STRUCT per group:

Field	Type	Description
periods[]	STRUCT[]	Array of {period, confidence, strength, amplitude, phase, iteration}
n_periods	BIGINT	Number of detected periods
primary_period	DOUBLE	Dominant period
method	VARCHAR	Method used

Confidence Interpretation

Method	Confidence Meaning	Good Threshold
FFT	Peak-to-mean power ratio	> 5.0
ACF	Autocorrelation at lag	> 0.3

Default thresholds filter low-confidence periods automatically. Set min_confidence='0' to see all candidates.

Examples

-- Detect periods using default FFT method
SELECT id, (periods).primary_period, (periods).n_periods
FROM ts_detect_periods_by('sales', product_id, date, value, MAP{});

-- Use ACF method with limited search range
SELECT * FROM ts_detect_periods_by('sales', product_id, date, value,
    MAP{'method': 'acf', 'max_period': '28'});

-- Detect multiple seasonalities
SELECT * FROM ts_detect_periods_by('hourly_data', sensor_id, timestamp, reading,
    MAP{'method': 'multi'});

-- Use auto method for unknown data
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('new_data', series_id, date, value,
    MAP{'method': 'auto'});

Workflow: Detect → Forecast

-- Step 1: Detect seasonal period
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('sales', product_id, date, value, MAP{});
-- Returns e.g. primary_period = 7 (weekly)

-- Step 2: Use detected period in forecast
SELECT * FROM ts_forecast_by(
    'sales', product_id, date, value,
    'AutoETS', 14, '1d', MAP{'seasonal_period': '7'}
);

---

ts_classify_seasonality_by

Classify the type of seasonal pattern per group, including timing stability and amplitude modulation.

ts_classify_seasonality_by(
    source VARCHAR,
    group_col COLUMN,
    date_col COLUMN,
    value_col COLUMN,
    period DOUBLE
) → TABLE

Parameters:

Parameter	Type	Required	Description
source	VARCHAR	Yes	Source table name
group_col	COLUMN	Yes	Series identifier (unquoted)
date_col	COLUMN	Yes	Date/timestamp column (unquoted)
value_col	COLUMN	Yes	Value column (unquoted)
period	DOUBLE	Yes	Expected seasonal period

Returns:

Column	Type	Description
group_col	(input)	Series identifier
timing_classification	VARCHAR	'early', 'on_time', 'late', 'variable'
modulation_type	VARCHAR	'stable', 'growing', 'shrinking', 'variable'
has_stable_timing	BOOLEAN	Consistent peak timing?
timing_variability	DOUBLE	Lower = more stable
seasonal_strength	DOUBLE	0-1 scale
is_seasonal	BOOLEAN	Significant seasonality?
cycle_strengths	DOUBLE[]	Strength per cycle
weak_seasons	INTEGER[]	Indices of weak cycles

Example:

-- Classify weekly seasonality per product
SELECT id, seasonal_strength, is_seasonal
FROM ts_classify_seasonality_by('sales', product_id, date, quantity, 7.0)
WHERE is_seasonal AND has_stable_timing;

---

ts_classify_seasonality

Single-series variant (no grouping).

ts_classify_seasonality(
    source VARCHAR,
    date_col COLUMN,
    value_col COLUMN,
    period DOUBLE
) → TABLE

Same return columns as ts_classify_seasonality_by but without the group column.

Example:

SELECT seasonal_strength, timing_classification, modulation_type
FROM ts_classify_seasonality('monthly_sales', date, revenue, 12.0);

---

Interpretation Guide

Metric	Value	Interpretation
seasonal_strength	> 0.6	Strong seasonality, use seasonal models
seasonal_strength	0.3 - 0.6	Moderate seasonality
seasonal_strength	< 0.3	Weak or no seasonality
timing_classification	on_time	Consistent peak timing
timing_classification	variable	Peak timing varies
modulation_type	stable	Seasonal amplitude is consistent
modulation_type	growing	Seasonal effect is increasing

---

Model Selection Based on Detection

Detection Result	Recommended Models
Strong stable seasonality	AutoETS, HoltWinters, SeasonalES
Multiple periods detected	AutoTBATS, AutoMSTL, MFLES
Variable seasonality	DynamicTheta, DynamicOptimizedTheta
No seasonality detected	Naive, SES, Holt
Trending + seasonal	HoltWinters, AutoARIMA