Time Series Decomposition

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

---

anofox_fcst_ts_mstl_decomposition

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

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Source table
group_col	VARCHAR	No	NULL	Group column for multi-series
date_col	VARCHAR	Yes	-	Date column
value_col	VARCHAR	Yes	-	Value column
params	MAP	No	MAP	Configuration

Options MAP:

Option	Type	Default	Description
seasonal_periods	INTEGER[]	[7]	Seasonal periods to decompose

Output

Returns table with original columns plus:

Field	Type	Description
trend	DOUBLE	Trend component
seasonal_P	DOUBLE	Seasonal component for period P (one column per period)
residual	DOUBLE	Remainder after removing trend and seasonality

Example

-- Single seasonality (weekly)
SELECT * FROM anofox_fcst_ts_mstl_decomposition(
    'sales_data',
    NULL,  -- group_col
    date,
    sales,
    MAP{'seasonal_periods': [7]}
);

Multi-Seasonal Decomposition

-- Multiple seasonalities (weekly + monthly)
SELECT * FROM anofox_fcst_ts_mstl_decomposition(
    'hourly_demand',
    NULL,
    timestamp,
    demand,
    MAP{'seasonal_periods': [24, 168]}  -- daily (24h) + weekly (168h)
);

Multi-Series Decomposition

-- Decompose multiple products
SELECT * FROM anofox_fcst_ts_mstl_decomposition(
    'sales_by_product',
    product_id,  -- group column
    date,
    sales,
    MAP{'seasonal_periods': [7, 365]}
);

---

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 * FROM anofox_fcst_ts_analyze_seasonality(
    'sales_data', NULL, date, sales, MAP{}
);

-- 2. Use detected periods for decomposition
SELECT * FROM anofox_fcst_ts_mstl_decomposition(
    'sales_data',
    NULL,
    date,
    sales,
    MAP{'seasonal_periods': [7, 365]}  -- periods from step 1
);

-- 3. Analyze components
SELECT
    AVG(trend) as avg_trend,
    STDDEV(residual) as residual_volatility,
    MIN(trend) as min_trend,
    MAX(trend) as max_trend
FROM (
    SELECT * FROM anofox_fcst_ts_mstl_decomposition(
        'sales_data', NULL, date, sales, MAP{'seasonal_periods': [7]}
    )
);

---