Postgresqlを使用した時系列の異常検知の簡単な例

首先，テーブルに時間系列データを含めます。例えば、time_series_data という名前のテーブルを作成し、タイムスタンプ（timestamp）と値（value）の 2 つのフィールドを含めます：

CREATE TABLE time_series_data (  
    timestamp TIMESTAMP NOT NULL,  
    value NUMERIC NOT NULL  
);

ウィンドウ関数を使用して移動平均値と移動標準偏差を計算します。例えば、以下のクエリを使用して、各データポイントの直前の 5 つの時間単位の移動平均値（moving_avg）と移動標準偏差（moving_stddev）を計算できます：

SELECT  
    timestamp,  
    value,  
    AVG(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg,  
    STDDEV(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_stddev  
FROM  
    time_series_data;

各データポイントの Z スコア（z_score）を計算し、移動平均値からの距離を確認します。以下のクエリを使用して、Z スコアを計算できます：

WITH moving_stats AS (  
    SELECT  
        timestamp,  
        value,  
        AVG(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg,  
        STDDEV(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_stddev  
    FROM  
        time_series_data  
)  
SELECT  
    timestamp,  
    value,  
    (value - moving_avg) / moving_stddev AS z_score  
FROM  
    moving_stats;

Z スコアに基づいてデータポイントが異常かどうかを判断します。例えば、Z スコアが 3 より大きいか - 3 より小さい場合、データポイントを異常値と見なすことができます。以下のクエリを使用して、異常なデータポイントをフィルタリングできます：

WITH moving_stats AS (  
    SELECT  
        timestamp,  
        value,  
        AVG(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg,  
        STDDEV(value) OVER (ORDER BY timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_stddev  
    FROM  
        time_series_data  
),  
z_scores AS (  
    SELECT  
        timestamp,  
        value,  
        (value - moving_avg) / moving_stddev AS z_score  
    FROM  
        moving_stats  
)  
SELECT  
    timestamp,  
    value,  
    z_score  
FROM  
    z_scores  
WHERE  
    z_score > 3 OR z_score < -3;

この方法は統計学の原理に基づいており、時間系列データが正規分布に従うと仮定しています。移動ウィンドウのサイズ（例：ROWS BETWEEN 4 PRECEDING AND CURRENT ROW）や異常判断の閾値（例：z_score > 3 OR z_score < -3）を実際の状況に応じて調整することができます。