Time Series Decomposition

Time series decomposition is the process of breaking a time series into its constituent components: trend (long-term direction), seasonal (repeating cyclical patterns), and residual (random fluctuations remaining after trend and seasonality are removed). Understanding these components helps you choose the right forecasting model and diagnose data quality issues.

Function	Description
ts_mstl_decomposition_by	Multiple Seasonal-Trend decomposition (MSTL)
ts_detrend_by	Remove trend using multiple methods
ts_detect_peaks_by	Detect local maxima with prominence
ts_analyze_peak_timing_by	Analyze peak timing variability

Showing 4 of 4

---

ts_mstl_decomposition_by

Multiple Seasonal-Trend decomposition using Loess (MSTL). Decomposes a time series into trend, one or more seasonal components, and residual.

Parameters

Parameter	Type	Required	Description
source	VARCHAR	Yes	Source table name
group_col	IDENTIFIER	Yes	Group column for multi-series (unquoted)
date_col	IDENTIFIER	Yes	Date column (unquoted)
value_col	IDENTIFIER	Yes	Value column (unquoted)
seasonal_periods	INTEGER[]	Yes	Seasonal periods to decompose
params	MAP	No	Configuration (use MAP{} for defaults)

Output

Field	Type	Description
group_col	(input)	Series identifier
trend	DOUBLE[]	Trend component
seasonal	DOUBLE[][]	Seasonal components (one per period)
remainder	DOUBLE[]	Remainder after removing trend and seasonality
periods	INTEGER[]	Seasonal periods used

Examples

-- Single seasonality (weekly)
SELECT * FROM ts_mstl_decomposition_by(
    'sales_data',
    product_id,
    date,
    sales,
    [7],
    MAP{}
);

-- Multiple seasonalities (daily + weekly for hourly data)
SELECT * FROM ts_mstl_decomposition_by(
    'hourly_demand',
    sensor_id,
    timestamp,
    demand,
    [24, 168],
    MAP{}
);

-- Weekly + yearly for daily data
SELECT * FROM ts_mstl_decomposition_by(
    'daily_sales',
    product_id,
    date,
    revenue,
    [7, 365],
    MAP{}
);

---

Interpretation Guide

Component	What It Shows	Business Use
Trend	Long-term direction	Growth/decline analysis
Seasonal	Repeating patterns	Capacity planning, promotions
Residual	Random fluctuations	Anomaly detection, forecast uncertainty

Residual Analysis:

• Large residuals indicate unusual events or data quality issues
• Patterns in residuals suggest missing seasonality
• Residual variance indicates forecast uncertainty

---

Common Seasonal Periods

Data Frequency	Common Periods
Daily	7 (weekly), 365 (yearly)
Hourly	24 (daily), 168 (weekly)
Weekly	52 (yearly)
Monthly	12 (yearly)
Quarterly	4 (yearly)

---

Decomposition Workflow

-- 1. Detect seasonality first
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('sales_data', product_id, date, sales, MAP{});

-- 2. Use detected periods for decomposition
SELECT * FROM ts_mstl_decomposition_by(
    'sales_data',
    product_id,
    date,
    sales,
    [7, 365],
    MAP{}
);

---

ts_detrend_by

Remove trend from grouped time series.

ts_detrend_by(
    source VARCHAR,
    group_col IDENTIFIER,
    date_col IDENTIFIER,
    value_col IDENTIFIER,
    method VARCHAR
) → TABLE

Methods:

Method	Description	Best For
'linear'	Linear trend via least squares	Simple linear trends
'quadratic'	Quadratic polynomial	Curved trends
'cubic'	Cubic polynomial	Complex curves
'auto'	Automatic selection	Unknown trend type

Returns:

Column	Type	Description
group_col	(input)	Series identifier
trend	DOUBLE[]	Extracted trend component
detrended	DOUBLE[]	Detrended values
method	VARCHAR	Method used
coefficients	DOUBLE[]	Polynomial coefficients
rss	DOUBLE	Residual sum of squares
n_params	BIGINT	Number of parameters

Example:

-- Linear detrending
SELECT * FROM ts_detrend_by('sales', product_id, date, revenue, 'linear');

-- Automatic method selection
SELECT * FROM ts_detrend_by('sales', product_id, date, revenue, 'auto');

Use Cases:

• Preparing data for stationarity tests
• Isolating seasonal patterns
• Removing growth effects for comparison

---

ts_detect_peaks_by

Detect peaks (local maxima) in grouped time series data with prominence calculation.

ts_detect_peaks_by(
    source VARCHAR,
    group_col COLUMN,
    date_col COLUMN,
    value_col COLUMN,
    params MAP
) → TABLE(id, peaks)

Params MAP Options:

Key	Type	Default	Description
min_distance	VARCHAR	'1.0'	Minimum distance between peaks
min_prominence	VARCHAR	'0.0'	Minimum peak prominence threshold
smooth_first	VARCHAR	'false'	Smooth data before peak detection

Returns peaks STRUCT:

Field	Type	Description
peaks[]	STRUCT[]	Array of {index, time, value, prominence}
n_peaks	INTEGER	Number of peaks detected
inter_peak_distances[]	DOUBLE[]	Distances between consecutive peaks
mean_period	DOUBLE	Average inter-peak distance

Example:

-- Detect all peaks
SELECT id, (peaks).n_peaks, (peaks).mean_period
FROM ts_detect_peaks_by('daily_data', series_id, date, value, MAP{});

-- Detect significant peaks only (prominence > 2.0)
SELECT id, (peaks).n_peaks
FROM ts_detect_peaks_by('sales', product_id, date, revenue,
    MAP{'min_prominence': '2.0'});

Use Cases:

• Finding demand spikes
• Identifying seasonal peaks
• Anomaly detection

---

ts_analyze_peak_timing_by

Analyze peak timing regularity across seasonal cycles.

ts_analyze_peak_timing_by(
    source VARCHAR,
    group_col COLUMN,
    date_col COLUMN,
    value_col COLUMN,
    period DOUBLE,
    params MAP
) → TABLE(id, timing)

Returns timing STRUCT:

Field	Type	Description
n_peaks	INTEGER	Number of peaks analyzed
peak_times[]	DOUBLE[]	Peak positions
variability_score	DOUBLE	Lower = more stable
is_stable	BOOLEAN	Consistent peak timing?

Example:

-- Analyze weekly peak timing
SELECT id, (timing).is_stable, (timing).variability_score
FROM ts_analyze_peak_timing_by('sales', product_id, date, value, 7.0, MAP{});

Interpretation:

• is_stable = TRUE: Peaks occur at predictable times (good for planning)
• is_stable = FALSE: Peak timing varies (need flexible capacity)

---