Windowing for Anomaly Detection

In this lesson, you will learn how to identify data anomalies by comparing an individual row’s value against the moving average of its surrounding window.

The Logic of Detection

An anomaly is often defined as a significant deviation from a "normal" baseline. In SQL, we can establish this baseline using a moving average window. By calculating the average of a specific number of preceding rows, we create a flexible benchmark that adjusts as your data trends change. If the current row's value is significantly higher or lower than that moving average, we flag it as a potential outlier.

Implementation with Window Functions

To implement this, we use the AVG() window function with a frame definition. We compare the current_value to the moving_avg. If the difference exceeds a certain threshold, we identify it as an anomaly.

Consider a stream of server CPU usage metrics:

SELECT 
    timestamp,
    cpu_usage,
    AVG(cpu_usage) OVER (
        ORDER BY timestamp 
        ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
    ) AS moving_avg,
    CASE 
        WHEN ABS(cpu_usage - AVG(cpu_usage) OVER (
            ORDER BY timestamp ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
        )) > 20 THEN 'Anomaly'
        ELSE 'Normal'
    END AS status
FROM server_metrics;

Why This Works

In this query, we define a frame that looks at the 5 records immediately before the current one. By excluding the current row from the average (1 PRECEDING), we prevent the outlier itself from "pulling" the average toward it, which would mask the anomaly. The CASE statement then acts as a simple filter: if the spike in cpu_usage is greater than 20 units above our moving average, we classify it as an anomaly.

This approach is highly effective because it doesn't require a static global threshold. Instead, it respects the natural volatility of your data, allowing for "context-aware" detection that evolves alongside the data stream.

Key Takeaway: By using ROWS BETWEEN to calculate a moving average that excludes the current row, you can establish a dynamic baseline to mathematically identify significant outliers in real-time.