SQL 異常検知 — 移動平均・z-score・IQRの基礎

SELECT
  dt,
  amount,
  ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
  ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev  -- 標本標準偏差（N-1）
FROM  daily_sales
WINDOW w AS (                     -- ウィンドウ定義を名前付きで共有
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM daily_sales        → 行を読み込む
  2. WINDOW w AS (...)       → 名前付きウィンドウを定義
  3. AVG(amount) OVER w      → ウィンドウ関数を評価（行数は保持）
  4. STDDEV(amount) OVER w   → ウィンドウ関数を評価（行数は保持）
  5. ROUND(..., 2)           → 値を整形
  6. SELECT / ORDER BY dt    → 列を評価し並び替えて出力
*/

WITH stats AS (
  SELECT
    dt, amount,
    ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
    ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev
  FROM  daily_sales
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW  -- 直近5日間
  )
)
SELECT
  dt, amount, moving_avg, moving_stddev,
  ROUND(
    (amount - moving_avg)
      / NULLIF(moving_stddev, 0),  -- ゼロ除算をNULLに変換
    2
  ) AS z_score,
  CASE
    WHEN ABS(
           (amount - moving_avg) / NULLIF(moving_stddev, 0)
         ) >= 2
    THEN 'anomaly'
    ELSE 'normal'
  END AS anomaly_flag
FROM  stats
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE stats を定義     → ウィンドウ関数を評価（行数は保持）
  2. 外側クエリを評価     → 列を評価（z_score, anomaly_flag）
  3. ORDER BY dt          → 並び替えて出力
*/

WITH fences AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
  FROM  daily_sales
)
SELECT
  s.dt,
  s.amount,
  f.q1,
  f.q3,
  f.q3 - f.q1                     AS iqr,
  f.q1 - 1.5 * (f.q3 - f.q1)     AS lower_fence,  -- Tukey の下限
  f.q3 + 1.5 * (f.q3 - f.q1)     AS upper_fence,  -- Tukey の上限
  CASE
    WHEN s.amount < f.q1 - 1.5 * (f.q3 - f.q1)
      OR  s.amount > f.q3 + 1.5 * (f.q3 - f.q1)
    THEN 'outlier'
    ELSE 'normal'
  END                              AS outlier_flag
FROM  daily_sales s
CROSS JOIN fences f            -- 全行に同じ q1/q3 を結合（1行CTE）
ORDER BY s.dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE fences を定義     → 集計関数を評価（Q1, Q3）
  2. FROM daily_sales s    → 行を読み込む
  3. CROSS JOIN fences f   → 結合（直積）
  4. SELECT                → 列を評価（lower_fence, upper_fence, flag）
  5. ORDER BY s.dt         → 並び替えて出力
*/

WITH with_prev AS (
  SELECT
    dt,
    amount,
    LAG(amount) OVER (ORDER BY dt) AS prev_amount  -- 前日の amount を取得
  FROM  daily_sales
)
SELECT
  dt,
  amount,
  prev_amount,
  ROUND(
    (amount - prev_amount) * 100.0
      / NULLIF(prev_amount, 0),  -- % 変化率（ゼロ除算ガード）
    1
  ) AS pct_change,
  CASE
    WHEN (amount - prev_amount) * 1.0 / NULLIF(prev_amount, 0) >= 0.5   -- +50% 以上
      THEN 'spike'
    WHEN (amount - prev_amount) * 1.0 / NULLIF(prev_amount, 0) <= -0.3  -- -30% 以下
      THEN 'drop'
    ELSE 'normal'
  END AS alert_type
FROM  with_prev
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE with_prev を定義  → ウィンドウ関数を評価（行数は保持）
  2. 外側クエリを評価      → 列を評価（pct_change, flag）
  3. ORDER BY dt           → 並び替えて出力
*/