ローリングベースラインによるエラー率のスパイクの検出

WITH hourly AS (
  SELECT
    date_trunc('hour', timestamp_utc) AS hour,
    COUNT(*) AS requests,
    100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
      / NULLIF(COUNT(*), 0) AS error_rate_pct
  FROM api_requests
  WHERE timestamp_utc >= now() - INTERVAL '72 hours'
  GROUP BY date_trunc('hour', timestamp_utc)
),
baseline AS (
  SELECT
    hour,
    requests,
    error_rate_pct,
    AVG(error_rate_pct) OVER (
      ORDER BY hour ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
    ) AS previous_seven_avg_pct
  FROM hourly
)
SELECT
  hour,
  requests,
  error_rate_pct,
  previous_seven_avg_pct,
  error_rate_pct - previous_seven_avg_pct AS increase_pct_points
FROM baseline
WHERE previous_seven_avg_pct IS NOT NULL
ORDER BY hour;