Diagnostics & Pattern Detection Functions

Discover patterns, anomalies, and structural breaks in your time series.

Seasonality Detection

TS_DETECT_SEASONALITY

Detect if series has seasonal patterns

SELECT * FROM TS_DETECT_SEASONALITY(
    'sales_data',
    'date',
    'sales',
    {'method': 'auto'}
);

Output:

is_seasonal | detected_period | strength | p_value
true        | 7               | 0.85     | 0.001

Interpretation:

• is_seasonal: Pattern detected?
• detected_period: 7 = weekly, 12 = monthly, 365 = yearly
• strength: 0-1 scale (0 = no seasonality, 1 = perfect)
• p_value: Significance (< 0.05 = real pattern)

Use when:

• Exploring data
• Deciding on model
• Validating forecast assumptions

---

TS_DETECT_SEASONALITY_ALL

Find all seasonal periods

SELECT * FROM TS_DETECT_SEASONALITY_ALL(
    'traffic_data',
    'timestamp',
    'visitors',
    {'max_periods': 5}
);

Output:

period | strength | p_value
24     | 0.92     | 0.001
168    | 0.78     | 0.001
8760   | 0.45     | 0.005

Returns: All detected seasonal periods with strength

Use when:

• Multiple seasonality suspected
• Hourly/sub-daily data
• Complex patterns

---

Decomposition

TS_STL_DECOMPOSITION

Separate trend, seasonal, and noise (single seasonality)

CREATE TABLE decomposed AS
SELECT
    date,
    sales,
    trend,
    seasonal,
    remainder
FROM TS_STL_DECOMPOSITION(
    'sales_data',
    'date',
    'sales',
    {'seasonal_period': 7}
);

Output columns:

• trend: Long-term direction
• seasonal: Repeating pattern effect
• remainder: Noise/irregular variation

Example:

date | sales | trend | seasonal | remainder
2024-01-01 | 100.5 | 98.2 | 2.1 | 0.2
2024-01-02 | 95.3 | 98.5 | -3.5 | 0.3
2024-01-03 | 108.7 | 98.8 | 9.2 | 0.7

Use for:

• Understanding components
• Separate trend from seasonality
• Data exploration

---

TS_MSTL_DECOMPOSITION

Decompose with multiple seasonality

CREATE TABLE decomposed AS
SELECT
    timestamp,
    visitors,
    trend,
    seasonal_hourly,
    seasonal_daily,
    remainder
FROM TS_MSTL_DECOMPOSITION(
    'traffic_data',
    'timestamp',
    'visitors',
    {'seasonal_periods': [24, 168]}
);

Returns: Separate seasonal components for each period

Use for:

• Complex patterns (hourly + daily + yearly)
• Understanding overlapping seasonality
• Advanced analysis

---

Changepoint Detection

TS_DETECT_CHANGEPOINTS

Find structural breaks in the data

SELECT
    date,
    is_changepoint,
    probability
FROM TS_DETECT_CHANGEPOINTS(
    'sales_data',
    'date',
    'sales',
    {'hazard_lambda': 250}
);

Output:

date | is_changepoint | probability
2023-06-15 | true | 0.92
2023-12-01 | true | 0.87

Interpretation:

• is_changepoint: True if structural break detected
• probability: Confidence (0-1, higher = more confident)

Use when:

• Data has regime shifts
• Marketing campaign launches
• Policy changes
• Price changes

Parameter tuning:

• hazard_lambda: Expected spacing between changes
  • Lower (50) = More sensitive, detects more changes
  • Higher (500) = Less sensitive, detects major changes only

---

Example: Handling Changepoints

-- Detect changes
WITH changes AS (
    SELECT date FROM TS_DETECT_CHANGEPOINTS(...)
    WHERE is_changepoint = true
)
-- Split data at changepoints
SELECT
    *,
    SUM(CASE WHEN date IN (SELECT date FROM changes) THEN 1 ELSE 0 END)
        OVER (ORDER BY date) as regime
FROM sales_data;

-- Forecast each regime separately
SELECT regime, * FROM TS_FORECAST_BY(
    'regimes',
    'regime',
    'date',
    'sales',
    'AutoETS',
    30,
    {}
);

---

Outlier Detection

TS_DETECT_OUTLIERS

Find unusual/anomalous values

SELECT
    date,
    sales,
    is_outlier,
    anomaly_score
FROM TS_DETECT_OUTLIERS(
    'sales_data',
    'sales',
    {'method': 'mad', 'threshold': 3.0}
);

Methods:

• 'mad': Median Absolute Deviation (robust)
• 'iqr': Interquartile Range
• 'zscore': Z-score method
• 'isolation_forest': ML-based

Parameters:

• threshold: How extreme is outlier?
  • 2.0 = More sensitive
  • 3.0 = Standard
  • 4.0 = Less sensitive (only extreme)

Output:

date | sales | is_outlier | anomaly_score
2024-01-15 | 500 | true | 0.95
2024-01-22 | 1000 | true | 0.89

---

TS_DETECT_ANOMALIES

Detect contextual anomalies

SELECT * FROM TS_DETECT_ANOMALIES(
    'sales_data',
    'date',
    'sales',
    {'window': 7, 'std_threshold': 2.0}
);

Use for:

• Context-aware anomalies
• Deviation from local pattern
• Unusual changes

---

Stationarity Testing

Check if series is stationary

-- Manual check: constant mean and variance
SELECT
    DATE_TRUNC('month', date) as month,
    ROUND(AVG(sales), 2) as monthly_mean,
    ROUND(STDDEV(sales), 2) as monthly_std
FROM sales_data
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

-- If mean/std vary greatly → non-stationary
-- Need differencing for ARIMA

---

Complete Diagnostics Example

-- Comprehensive data analysis
WITH seasonality AS (
    SELECT * FROM TS_DETECT_SEASONALITY('sales', 'date', 'sales', {})
),
changepoints AS (
    SELECT date FROM TS_DETECT_CHANGEPOINTS('sales', 'date', 'sales', {})
    WHERE is_changepoint = true
),
decomp AS (
    SELECT date, trend, seasonal, remainder
    FROM TS_STL_DECOMPOSITION('sales', 'date', 'sales', {'seasonal_period': 7})
),
outliers AS (
    SELECT date FROM TS_DETECT_OUTLIERS('sales', 'sales', {})
    WHERE is_outlier = true
)
SELECT
    'Seasonality' as finding,
    (SELECT is_seasonal::VARCHAR FROM seasonality) as result,
    (SELECT ROUND(strength, 2)::VARCHAR FROM seasonality) as detail
UNION ALL
SELECT
    'Changepoints Found',
    COUNT(*)::VARCHAR,
    null
FROM changepoints
UNION ALL
SELECT
    'Outliers Detected',
    COUNT(*)::VARCHAR,
    null
FROM outliers
UNION ALL
SELECT
    'Trend Direction',
    CASE WHEN (SELECT MAX(trend) FROM decomp) > (SELECT MIN(trend) FROM decomp) THEN 'Upward' ELSE 'Downward' END,
    ROUND((SELECT MAX(trend) FROM decomp) - (SELECT MIN(trend) FROM decomp), 2)::VARCHAR
;

---

Diagnostic Workflow

1. Check seasonality
   ↓
2. Detect changepoints
   ↓
3. Decompose series
   ↓
4. Identify outliers
   ↓
5. Make data quality decisions

---

When to Use Each Function

Function	Use Case	Timing
TS_DETECT_SEASONALITY	Explore data	Before modeling
TS_DETECT_SEASONALITY_ALL	Complex patterns	Before modeling (hourly data)
TS_STL_DECOMPOSITION	Understand components	Exploratory
TS_MSTL_DECOMPOSITION	Multiple seasonality	Complex data exploration
TS_DETECT_CHANGEPOINTS	Regime shifts	Before/after modeling
TS_DETECT_OUTLIERS	Data quality	During preparation
TS_DETECT_ANOMALIES	Contextual anomalies	Real-time monitoring

---

Next Steps

• Metrics Reference — Evaluate forecast accuracy
• Detecting Patterns Guide — Practical examples
• Data Preparation Guide — How to handle detected issues

---

Key Takeaways

• ✅ Use TS_DETECT_SEASONALITY to discover patterns
• ✅ TS_DETECT_CHANGEPOINTS finds regime shifts
• ✅ TS_STL_DECOMPOSITION separates components
• ✅ Require p_value < 0.05 for statistical significance
• ✅ Handle changepoints by splitting and forecasting separately
• ✅ Remove or use robust models for outliers
• ✅ Diagnostics inform model selection