Time Series Decomposition

Separate your time series into trend, seasonal, and residual components.

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)

---