SQL: Определить пиковые часы нагрузки

Решение

Это задача на анализ пиковых часов нагрузки по дням недели — критична для масштабирования инфраструктуры и планирования обслуживания.

SQL запрос для пиковых часов:

WITH hourly_stats AS (
  SELECT 
    CASE EXTRACT(DOW FROM request_time)
      WHEN 0 THEN 'Sunday'
      WHEN 1 THEN 'Monday'
      WHEN 2 THEN 'Tuesday'
      WHEN 3 THEN 'Wednesday'
      WHEN 4 THEN 'Thursday'
      WHEN 5 THEN 'Friday'
      WHEN 6 THEN 'Saturday'
    END as day_of_week,
    EXTRACT(HOUR FROM request_time) as hour,
    COUNT(*) as requests_count,
    ROUND(AVG(response_time_ms), 2) as avg_response_time,
    MAX(response_time_ms) as max_response_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) as p95_response_time
  FROM requests
  GROUP BY EXTRACT(DOW FROM request_time), EXTRACT(HOUR FROM request_time)
),
ranked_by_day AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY day_of_week ORDER BY requests_count DESC) as rank_by_requests
  FROM hourly_stats
)
SELECT 
  day_of_week,
  hour,
  requests_count,
  avg_response_time,
  max_response_time,
  p95_response_time,
  rank_by_requests
FROM ranked_by_day
WHERE rank_by_requests <= 3
ORDER BY day_of_week, rank_by_requests;

Общая статистика по часам (все дни вместе):

SELECT 
  EXTRACT(HOUR FROM request_time) as hour,
  COUNT(*) as total_requests,
  ROUND(AVG(response_time_ms), 2) as avg_response_time,
  MAX(response_time_ms) as max_response_time,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) as p95_response_time,
  STDDEV(response_time_ms) as stddev_response_time
FROM requests
GROUP BY EXTRACT(HOUR FROM request_time)
ORDER BY total_requests DESC
LIMIT 10;

С выделением пиковых часов:

WITH hourly_data AS (
  SELECT 
    CASE EXTRACT(DOW FROM request_time)
      WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday'
      WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' 
      WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday'
    END as day_of_week,
    EXTRACT(HOUR FROM request_time) as hour,
    COUNT(*) as requests_count,
    ROUND(AVG(response_time_ms), 2) as avg_response_time
  FROM requests
  GROUP BY EXTRACT(DOW FROM request_time), EXTRACT(HOUR FROM request_time)
),
day_stats AS (
  SELECT 
    day_of_week,
    MAX(requests_count) as peak_requests
  FROM hourly_data
  GROUP BY day_of_week
)
SELECT 
  hd.day_of_week,
  hd.hour,
  hd.requests_count,
  ROUND(100.0 * hd.requests_count / ds.peak_requests, 1) as pct_of_peak,
  hd.avg_response_time,
  CASE 
    WHEN hd.requests_count >= ds.peak_requests * 0.8 THEN 'PEAK'
    WHEN hd.requests_count >= ds.peak_requests * 0.5 THEN 'HIGH'
    ELSE 'NORMAL'
  END as load_level
FROM hourly_data hd
JOIN day_stats ds ON hd.day_of_week = ds.day_of_week
ORDER BY hd.day_of_week, hd.requests_count DESC;

Ожидаемый результат:

day_of_week | hour | requests_count | avg_response_time | p95_response_time
Monday      | 10   | 15,500         | 125.3 ms          | 450 ms (PEAK)
Monday      | 11   | 14,200         | 128.5 ms          | 480 ms (PEAK)
Monday      | 14   | 12,800         | 110.2 ms          | 350 ms (HIGH)

Friday      | 17   | 18,200         | 145.6 ms          | 550 ms (PEAK)
Friday      | 18   | 17,500         | 138.4 ms          | 520 ms (PEAK)
Friday      | 19   | 16,200         | 130.1 ms          | 450 ms (HIGH)

Ключевые метрики:

requests_count — количество запросов в час (главная метрика нагрузки) avg_response_time — средний ответ (если падает при пике = проблема) p95_response_time — 95-й процентиль (показывает worst-case) max_response_time — максимальный ответ (крайние выбросы)

Применение в бизнесе:

1. Масштабирование — если пик в 10-11:00 пн, нужно горизонтальное масштабирование
2. Обслуживание — проводить обслуживание в 02-03:00 (самые низкие нагрузки)
3. Мониторинг — усилить алерты во время пиков
4. Кэширование — закэшировать популярные запросы перед пиком
5. CDN — проверить CDN во время пиков