Skip to main content

Detecting Seasonality & Patterns

Discover hidden patterns in your time series data to improve forecasts.

Why Pattern Detection Matters​

Correct pattern identification → Better model choice → More accurate forecasts

Data with weekly seasonality
↓
Detected with TS_DETECT_SEASONALITY
↓
Use AutoETS with seasonal_period=7
↓
Accurate forecasts ✓

Detecting Seasonality​

Method 1: Visual Inspection​

Plot data and look for repeating patterns:

-- Export data for visualization
SELECT
    date,
    sales,
    DAYNAME(date) as day_name,
    WEEK(date) as week_num,
    MONTH(date) as month_num
FROM sales_data
ORDER BY date
LIMIT 100;

Signs of seasonality:

  • Repeating peaks and valleys
  • Regular spikes every N days/weeks/months
  • Waves that recur at fixed intervals

Method 2: Autocorrelation Function (ACF)​

Correlation between values at different time lags:

-- High correlation at lag 7 = weekly seasonality
-- High correlation at lag 365 = yearly seasonality

Method 3: Automatic Detection​

Let AnoFox detect seasonality:

-- Simple detection
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=true: Seasonality exists
  • detected_period=7: Pattern repeats every 7 days
  • strength=0.85: Strong seasonality (scale 0-1)
  • p_value=0.001: Statistically significant (p < 0.05)

Method 4: Multi-Period Detection​

Find all seasonal periods:

-- Detect multiple seasonality (hourly + daily + yearly)
SELECT * FROM TS_DETECT_SEASONALITY_ALL(
    'traffic_data',
    'timestamp',
    'visitors',
    {'max_periods': 5}
);

-- Output: 24 (hourly), 168 (weekly), 8760 (yearly)

Understanding Seasonality Results​

Seasonality Strength​

strength = 0.0-0.2  → Very weak (might be noise)
strength = 0.2-0.5  → Moderate seasonality
strength = 0.5-0.8  → Strong seasonality
strength = 0.8-1.0  → Very strong seasonality

Action based on strength:

  • < 0.3: Can ignore, use non-seasonal model
  • 0.3-0.7: Include in model with seasonal_period

  • 0.7: Critical to include seasonality

Statistical Significance (p-value)​

  • p_value < 0.05: Seasonality is real, not just noise
  • p_value > 0.05: Could be random fluctuation

Rule: Only use detected seasonality if p_value < 0.05

Decomposing Your Series​

Break down into components:

STL Decomposition (Single Seasonality)​

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

-- View components
SELECT
    date,
    ROUND(sales, 2) as original,
    ROUND(trend, 2) as trend,
    ROUND(seasonal, 2) as seasonal,
    ROUND(remainder, 2) as noise
FROM decomposed
LIMIT 20;

Interpretation:

  • trend: Long-term direction (increasing/decreasing)
  • seasonal: Repeating pattern effect
  • remainder: Random variation/noise

Example output:

date       | original | trend | seasonal | noise
-----------|----------|-------|----------|------
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

What this tells us:

  • Sales trending up slightly
  • Strong weekly pattern (±2-9 units around trend)
  • Low noise level

MSTL Decomposition (Multiple Seasonality)​

For complex data with hourly + daily + yearly patterns:

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

-- View multiple seasonal effects
SELECT
    timestamp,
    ROUND(visitors, 0) as actual,
    ROUND(trend, 1) as trend,
    ROUND(seasonal_hourly, 1) as hour_effect,
    ROUND(seasonal_daily, 1) as day_effect,
    ROUND(remainder, 1) as noise
FROM mstl_decomposed
LIMIT 48;

Detecting Changepoints​

Find structural breaks where data pattern changes:

-- Detect changepoints (regime shifts)
SELECT
    date,
    is_changepoint,
    probability
FROM TS_DETECT_CHANGEPOINTS(
    'sales_data',
    'date',
    'sales',
    {'hazard_lambda': 250}  -- Expected spacing between changepoints
);

-- View only confirmed changepoints
SELECT date
FROM TS_DETECT_CHANGEPOINTS(...)
WHERE is_changepoint = true
ORDER BY date;

Output example:

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

Interpretation:

  • is_changepoint=true: Structural break detected
  • probability=0.92: 92% confident this is a real changepoint

Parameter Tuning​

-- Adjust sensitivity
SELECT * FROM TS_DETECT_CHANGEPOINTS(
    'sales_data',
    'date',
    'sales',
    {'hazard_lambda': 50}  -- More sensitive (more changepoints)
);

SELECT * FROM TS_DETECT_CHANGEPOINTS(
    'sales_data',
    'date',
    'sales',
    {'hazard_lambda': 500}  -- Less sensitive (fewer changepoints)
);

Analyzing Detected Patterns​

Summary Statistics​

-- Seasonality summary
WITH seasonality_analysis AS (
    SELECT * FROM TS_DETECT_SEASONALITY(
        'sales_data',
        'date',
        'sales',
        {}
    )
)
SELECT
    CASE is_seasonal
        WHEN true THEN 'Seasonal'
        ELSE 'Non-seasonal'
    END as data_type,
    detected_period as period_days,
    ROUND(strength, 2) as strength,
    CASE
        WHEN strength > 0.7 THEN 'Strong'
        WHEN strength > 0.5 THEN 'Moderate'
        ELSE 'Weak'
    END as seasonality_level,
    CASE
        WHEN p_value < 0.05 THEN 'Significant'
        ELSE 'Not significant'
    END as statistical_significance
FROM seasonality_analysis;

Trend Analysis​

-- Analyze trend component
SELECT
    MIN(date) as period_start,
    MAX(date) as period_end,
    ROUND(MIN(trend), 2) as trend_min,
    ROUND(MAX(trend), 2) as trend_max,
    ROUND(MAX(trend) - MIN(trend), 2) as total_trend_change,
    ROUND((MAX(trend) - MIN(trend)) / COUNT(*) * 365, 2) as annual_trend
FROM TS_STL_DECOMPOSITION(
    'sales_data',
    'date',
    'sales',
    {'seasonal_period': 7}
)
GROUP BY true;

Pattern Detection Examples​

Example 1: Weekly Seasonality​

-- Detect weekly pattern
SELECT * FROM TS_DETECT_SEASONALITY(
    'retail_sales',
    'date',
    'sales',
    {}
);

-- Result: detected_period=7, strength=0.82
-- Action: Use seasonal_period=7 in forecasting

SELECT * FROM TS_FORECAST(
    'retail_sales',
    'date',
    'sales',
    'AutoETS',
    28,
    {'seasonal_period': 7}  -- Weekly seasonality
);

Example 2: Multiple Seasonal Periods​

-- Website traffic with hourly + daily + weekly patterns
SELECT * FROM TS_DETECT_SEASONALITY_ALL(
    'web_traffic',
    'timestamp',
    'page_views',
    {'max_periods': 5}
);

-- Result: [24, 168, 8760]
-- Action: Use TBATS or MSTL with multiple periods

SELECT * FROM TS_FORECAST(
    'web_traffic',
    'timestamp',
    'page_views',
    'TBATS',
    336,  -- 2 weeks
    {'seasonal_periods': [24, 168]}
);

Example 3: Trend + Seasonal​

-- Decompose to see both components
CREATE TABLE analysis AS
SELECT
    date,
    sales,
    trend,
    seasonal,
    remainder,
    ROUND((trend - LAG(trend) OVER (ORDER BY date)) * 365, 2) as annual_trend_pct
FROM TS_STL_DECOMPOSITION(
    'sales_data',
    'date',
    'sales',
    {'seasonal_period': 7}
);

-- View growth rate and seasonal effect
SELECT
    ROUND(AVG(annual_trend_pct), 2) as avg_annual_growth,
    ROUND(MAX(seasonal) - MIN(seasonal), 2) as seasonal_amplitude
FROM analysis;

Common Pattern Detection Issues​

❌ False Positive: Detecting Seasonality That Doesn't Exist​

-- WRONG: Accepting low-strength seasonality
WHERE strength < 0.3 AND p_value > 0.05

-- RIGHT: Require both strong AND significant
WHERE strength > 0.5 AND p_value < 0.05

❌ Wrong Seasonal Period​

-- WRONG: Detecting daily data as having 30-day seasonality
-- RIGHT: Check detected_period makes sense for your data
-- Daily data likely has 7-day (weekly) or 365-day (yearly)

❌ Missing Multiple Seasonality​

-- WRONG: Only detecting one seasonal period
WHERE detected_period = (SELECT MAX(detected_period) FROM ...)

-- RIGHT: Use TS_DETECT_SEASONALITY_ALL to find all
SELECT * FROM TS_DETECT_SEASONALITY_ALL(...)

Pattern Detection Workflow​

1. Check for seasonality
   ↓
2. If seasonal: Detect period(s)
   ↓
3. Decompose to understand components
   ↓
4. Check for changepoints
   ↓
5. Choose model based on patterns found
   ↓
6. Include detected periods in forecasting

SQL Pattern Detection Scripts​

Quick Pattern Check​

-- One query to understand your data
SELECT
    'Total records' as metric, COUNT(*)::VARCHAR as value
FROM sales_data
UNION ALL
SELECT 'Date range', MIN(date)::VARCHAR || ' to ' || MAX(date)::VARCHAR
FROM sales_data
UNION ALL
SELECT 'Is seasonal?',
    CASE WHEN (SELECT is_seasonal FROM TS_DETECT_SEASONALITY(...)) THEN 'Yes' ELSE 'No' END
FROM (SELECT 1)
UNION ALL
SELECT 'Seasonal period',
    (SELECT detected_period::VARCHAR FROM TS_DETECT_SEASONALITY(...))
FROM (SELECT 1)
UNION ALL
SELECT 'Seasonality strength',
    (SELECT ROUND(strength, 2)::VARCHAR FROM TS_DETECT_SEASONALITY(...))
FROM (SELECT 1)
UNION ALL
SELECT 'Trend direction',
    CASE WHEN (SELECT MAX(trend) FROM TS_STL_DECOMPOSITION(...)) > (SELECT MIN(trend) FROM TS_STL_DECOMPOSITION(...))
    THEN 'Upward' ELSE 'Downward' END
FROM (SELECT 1);

Next Steps​

  1. Choosing Models — Pick best model based on patterns
  2. Basic Workflow — Apply pattern detection to complete workflow
  3. Model Comparison — Compare models that match your patterns

Key Takeaways​

  • ✅ Detect seasonality automatically with TS_DETECT_SEASONALITY
  • ✅ Decompose series to understand trend + seasonal + noise
  • ✅ Look for multiple seasonal periods in complex data
  • ✅ Detect changepoints to identify structural breaks
  • ✅ Require p_value < 0.05 for statistical significance
  • ✅ Use detected patterns to inform model selection
  • ✅ Match seasonal_period to detected period in forecasting
🍪 Cookie Settings