キャッシュミスとスタンピードのリスクを見つける

SELECT
  cache_key_pattern,
  COUNT(*) AS requests,
  SUM(CASE WHEN cache_hit THEN 1 ELSE 0 END) AS hits,
  SUM(CASE WHEN cache_hit THEN 0 ELSE 1 END) AS misses,
  100.0 * SUM(CASE WHEN cache_hit THEN 0 ELSE 1 END)
    / NULLIF(COUNT(*), 0) AS miss_rate_pct,
  MAX(concurrent_misses) AS maximum_concurrent_misses,
  AVG(CASE WHEN cache_hit THEN NULL ELSE backend_duration_ms END)
    AS average_backend_duration_ms
FROM cache_request_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND environment = 'production'
GROUP BY cache_key_pattern
ORDER BY maximum_concurrent_misses DESC, cache_key_pattern;