Time-Series Anomaly Detection

Apply statistical models to identify deviations in continuous metric streams.

In this lesson, you will learn how to identify unusual patterns in continuous metric streams using statistical modeling directly within your SQL environment.

The Logic of Deviation

When dealing with time-series data—such as CPU usage, sensor readings, or network traffic—we often look for "anomalies." An anomaly is a data point that deviates significantly from the expected behavior. Since we cannot manually inspect millions of rows, we use statistical thresholds to flag these points automatically.

The most common intermediate approach is the Z-Score (Standard Score). This method calculates how many standard deviations a specific data point is from the moving average. If a point falls outside of a predefined range (e.g., more than 3 standard deviations), we flag it as an anomaly.

Implementing Anomaly Detection with SQL

You can perform this analysis using window functions to calculate a moving average and moving standard deviation over a rolling window.

WITH Statistics AS (
    SELECT 
        time,
        metric_value,
        AVG(metric_value) OVER (ORDER BY time ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) as moving_avg,
        STDDEV(metric_value) OVER (ORDER BY time ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) as moving_std
    FROM sensor_readings
)
SELECT 
    time, 
    metric_value
FROM Statistics
WHERE ABS(metric_value - moving_avg) > (3 * moving_std);

In this query, we create a 20-row sliding window. By comparing the metric_value against the moving_avg and moving_std, we identify points that are statistically improbable compared to recent history.

Why Use This Approach?

Using SQL-native window functions is highly efficient for time-series anomaly detection because it avoids the overhead of exporting data to external processing engines. By defining a "normal" range based on the variance of the data itself, your database becomes self-monitoring. This is particularly useful when you don't know the exact "normal" threshold in advance, as the model adapts to the data’s natural fluctuations over time.

Key Takeaway: Anomaly detection in time-series data relies on identifying points that statistically deviate from the moving average and standard deviation, easily implemented via SQL window functions.