PrepBro
← Назад к вопросам

Гистограмма длительности сессий

2.3 Middle🔥 71 комментариев
#SQL и базы данных

Условие

Дана таблица sessions со следующей структурой:

  • session_id (INT) — идентификатор сессии
  • length_seconds (INT) — длительность сессии в секундах

Напишите SQL-запрос для подсчёта количества сессий в интервалах по 5 секунд с корректными метками (bucket labels).

Требования

  • Интервалы: "0-5", "5-10", "10-15" и т.д.
  • Вывести метку интервала и количество сессий
  • Отсортировать по нижней границе интервала

Пример данных

session_idlength_seconds
13
27
312
44
58

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

bucketsession_count
0-52
5-102
10-151

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Задача требует создания гистограммы — распределения сессий по интервалам длительности. Это типичная задача для анализа пользовательского поведения и создания визуализаций.

Подход к решению

Логика:

  1. Определить нижнюю границу интервала для каждой сессии (floor to nearest 5)
  2. Создать метку интервала вида "X-Y"
  3. Сгруппировать по интервалам и подсчитать количество сессий
  4. Отсортировать по нижней границе

SQL-запрос (основной вариант)

WITH bucketed_sessions AS (
  SELECT 
    session_id,
    length_seconds,
    (FLOOR(length_seconds / 5.0) * 5)::INT AS bucket_start,
    (FLOOR(length_seconds / 5.0) * 5 + 5)::INT AS bucket_end
  FROM sessions
)
SELECT 
  bucket_start || '-' || bucket_end AS bucket,
  COUNT(*) AS session_count
FROM bucketed_sessions
GROUP BY bucket_start, bucket_end
ORDER BY bucket_start ASC;

Пошаговое объяснение

1. Расчёт границ интервала

  • FLOOR(length_seconds / 5.0) — определяет номер интервала (0, 1, 2, ...)
  • FLOOR(...) * 5 — нижняя граница интервала (0, 5, 10, ...)
  • FLOOR(...) * 5 + 5 — верхняя граница интервала (5, 10, 15, ...)
  • ::INT — преобразуем в целое число для читаемости

2. Метка интервала

  • bucket_start || '-' || bucket_end — конкатенация строк (0 || '-' || 5 = "0-5")

3. Группировка и подсчёт

  • GROUP BY bucket_start, bucket_end — группируем по интервалам
  • COUNT(*) — подсчитываем сессии в каждом интервале

4. Сортировка

  • ORDER BY bucket_start — выводим в порядке возрастания нижней границы

Вариант для MySQL

WITH bucketed_sessions AS (
  SELECT 
    session_id,
    length_seconds,
    FLOOR(length_seconds / 5) * 5 AS bucket_start,
    FLOOR(length_seconds / 5) * 5 + 5 AS bucket_end
  FROM sessions
)
SELECT 
  CONCAT(bucket_start, '-', bucket_end) AS bucket,
  COUNT(*) AS session_count
FROM bucketed_sessions
GROUP BY bucket_start, bucket_end
ORDER BY bucket_start ASC;

Вариант с использованием WIDTH_BUCKET (PostgreSQL)

Это более специализированная функция:

WITH bucketed_sessions AS (
  SELECT 
    session_id,
    length_seconds,
    (WIDTH_BUCKET(length_seconds, 0, 100, 20) - 1) * 5 AS bucket_start
  FROM sessions
)
SELECT 
  bucket_start || '-' || (bucket_start + 5) AS bucket,
  COUNT(*) AS session_count
FROM bucketed_sessions
GROUP BY bucket_start
ORDER BY bucket_start ASC;

Альтернативный вариант: без CTE

SELECT 
  (FLOOR(length_seconds / 5) * 5)::TEXT || '-' || 
  (FLOOR(length_seconds / 5) * 5 + 5)::TEXT AS bucket,
  COUNT(*) AS session_count
FROM sessions
GROUP BY FLOOR(length_seconds / 5)
ORDER BY FLOOR(length_seconds / 5) ASC;

Расширенный вариант с дополнительной статистикой

WITH bucketed_sessions AS (
  SELECT 
    session_id,
    length_seconds,
    FLOOR(length_seconds / 5.0) * 5 AS bucket_start
  FROM sessions
)
SELECT 
  bucket_start || '-' || (bucket_start + 5) AS bucket,
  COUNT(*) AS session_count,
  COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER () * 100 AS percentage,
  MIN(length_seconds) AS min_length,
  MAX(length_seconds) AS max_length,
  ROUND(AVG(length_seconds), 2) AS avg_length
FROM bucketed_sessions
JOIN sessions USING (session_id)
GROUP BY bucket_start
ORDER BY bucket_start ASC;

Это добавляет проценты, минимум, максимум и среднее по каждому интервалу.

Пример выполнения

Исходные данные:

session_id | length_seconds
-----------|---------------
1          | 3
2          | 7
3          | 12
4          | 4
5          | 8

Расчёт для каждой сессии:

  • Сессия 1 (3 сек):

    • FLOOR(3 / 5) = FLOOR(0.6) = 0
    • bucket_start = 0 * 5 = 0
    • bucket_end = 0 + 5 = 5
    • bucket = "0-5"

  • Сессия 2 (7 сек):

    • FLOOR(7 / 5) = FLOOR(1.4) = 1
    • bucket_start = 1 * 5 = 5
    • bucket_end = 5 + 5 = 10
    • bucket = "5-10"

  • Сессия 3 (12 сек):

    • FLOOR(12 / 5) = FLOOR(2.4) = 2
    • bucket_start = 2 * 5 = 10
    • bucket_end = 10 + 5 = 15
    • bucket = "10-15"

  • Сессия 4 (4 сек): → "0-5"

  • Сессия 5 (8 сек): → "5-10"

Группировка:

bucket  | session_count
--------|---------------
0-5     | 2  (сессии 1, 4)
5-10    | 2  (сессии 2, 5)
10-15   | 1  (сессия 3)

Для интервалов другого размера

Интервалы по 10 секунд:

SELECT 
  (FLOOR(length_seconds / 10.0) * 10)::TEXT || '-' || 
  (FLOOR(length_seconds / 10.0) * 10 + 10)::TEXT AS bucket,
  COUNT(*) AS session_count
FROM sessions
GROUP BY FLOOR(length_seconds / 10.0)
ORDER BY FLOOR(length_seconds / 10.0) ASC;

Интервалы по 1 минуте:

SELECT 
  (FLOOR(length_seconds / 60.0) * 60)::TEXT || '-' || 
  (FLOOR(length_seconds / 60.0) * 60 + 60)::TEXT AS bucket,
  COUNT(*) AS session_count
FROM sessions
GROUP BY FLOOR(length_seconds / 60.0)
ORDER BY FLOOR(length_seconds / 60.0) ASC;

Важные моменты

✓ FLOOR деление — критично для определения нижней границы интервала

✓ Целочисленное деление — использование 5.0 вместо 5 гарантирует правильное деление

✓ Граница интервала — сессия с 5 секундами попадёт в интервал "5-10" (FLOOR(5/5) = 1)

✓ Текстовая метка — конкатенация (||) для создания строки вида "0-5"

✓ Сортировка — важно сортировать по числовому значению (bucket_start), не по строке

✓ Динамические интервалы — если верхняя граница неизвестна, можно использовать MAX(length_seconds) + 5

✓ Гистограмма с нулями — если нужны интервалы без сессий, потребуется JOIN с генерируемой таблицей интервалов

Гистограмма длительности сессий | PrepBro