SQL 異常検知 — ランキング・累積和・集約アラートの基礎

SELECT
  dt, amount,
  amount - 100 AS daily_dev,          -- 1日あたりの目標(100)からのズレ
  SUM(amount - 100) OVER (            -- 累積偏差（running total）
    ORDER BY dt
    ROWS UNBOUNDED PRECEDING            -- 先頭〜現在行まで
  ) AS cum_dev,
  CASE
    WHEN SUM(amount - 100) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) > 20
      THEN 'drift_alert'
    ELSE 'normal'
  END AS drift_flag
FROM daily_sales
ORDER BY dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM daily_sales         → 行を読み込む
  2. amount - 100             → 値を整形
  3. SUM(...) OVER (...)      → ウィンドウ関数を評価（行数は保持）
  4. CASE WHEN cum_dev > 20   → 値を整形
  5. SELECT / ORDER BY dt     → 並び替えて出力
*/

SELECT
  store_id, dt, amount,
  ROUND(AVG(amount)    OVER w, 2) AS store_avg,  -- 店舗内の平均
  ROUND(STDDEV(amount) OVER w, 2) AS store_std,  -- 店舗内の標準偏差
  ROUND(
    (amount - AVG(amount) OVER w)
    / NULLIF(STDDEV(amount) OVER w, 0)      -- SD=0 でのゼロ除算を回避
  , 2) AS z_score,
  CASE
    WHEN ABS(
      (amount - AVG(amount) OVER w)
      / NULLIF(STDDEV(amount) OVER w, 0)
    ) >= 1.5 THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM store_sales
WINDOW w AS (PARTITION BY store_id)     -- 店舗ごとに窓を分割（使い回し）
ORDER BY store_id, dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM store_sales                  → 行を読み込む
  2. WINDOW w (PARTITION BY store_id)  → 窓を定義
  3. AVG/STDDEV OVER w                 → ウィンドウ関数を評価（行数は保持）
  4. (amount-avg)/NULLIF(std,0)        → z-score を計算
  5. CASE WHEN ABS(z) が 1.5 以上         → 異常を判定
  6. SELECT / ORDER BY store_id, dt    → 並び替えて出力
  */

SELECT
  txn_id,
  COUNT(*)      AS occurrences,   -- グループ内の行数＝出現回数
  SUM(amount)  AS total_amount  -- グループ内の金額合計
FROM txn_log
GROUP BY txn_id              -- 同一 txn_id を1グループに畳む
HAVING COUNT(*) > 1          -- 集約後：2回以上出現したグループだけ残す
ORDER BY occurrences DESC, txn_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM txn_log           → 行を読み込む
  2. (WHERE なし)           → 行を絞り込む
  3. GROUP BY txn_id        → グループ化
  4. COUNT(*) / SUM(amount) → 集計関数を評価
  5. HAVING COUNT(*) > 1    → グループを絞り込む
  6. SELECT / ORDER BY      → 並び替えて出力
*/

SELECT
  category,
  COUNT(*)                                   AS total_days,  -- カテゴリの総日数（母数）
  COUNT(*) FILTER (WHERE amount > 200)     AS anomaly_days,  -- 異常日だけを計数
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount > 200)
    / COUNT(*)                                -- 異常率＝異常日/総日数
  , 1) AS anomaly_pct
FROM metrics
GROUP BY category                            -- カテゴリ単位に集約
HAVING COUNT(*) FILTER (WHERE amount > 200) >= 2  -- 異常2日以上のみ通知
ORDER BY anomaly_days DESC, category;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM metrics                       → 行を読み込む
  2. GROUP BY category                  → グループ化
  3. COUNT(*) と COUNT(*) FILTER(...)   → 集計関数を評価
  4. ROUND(100.0*異常/母数,1)           → 値を整形
  5. HAVING COUNT(*) FILTER(...) >= 2   → グループを絞り込む
  6. SELECT / ORDER BY anomaly_days DESC → 並び替えて出力
*/