Группировка и подсчет чисел в диапазонах с SQL

Быстрый ответ

Чтобы группировать данные по различным диапазонам, в SQL используется оператор CASE, позволяющий категоризировать данные:

SELECT
  CASE
    WHEN total_sale < 100 THEN '0 – 99'
    WHEN total_sale < 200 THEN '100 – 199'
    WHEN total_sale < 300 THEN '200 – 299'
    ELSE '300+'
  END AS sale_range,
  COUNT(*) AS order_count
FROM orders
GROUP BY sale_range;

Данный запрос распределяет продажи по соответствующим категориям, а также подсчитывает количество заказов в каждой из них.

Основы группирования данных по "ведрам"

Оператор CASE позволяет отнести данные к определенным "ведрам", то есть категориям с заданными границами. Например, оценки можно сгруппировать по диапазонам с шагом в 10 баллов:

SELECT
  FLOOR(score/10)*10 AS score_range_start,
  COUNT(*) AS total_counts
FROM results
GROUP BY score_range_start
ORDER BY score_range_start;

Как задать интервалы для группировки

Чтобы сгруппировать данные по возрасту с собственными интервалами, можно использовать следующий запрос:

SELECT
  CASE
    WHEN age < 20 THEN 'Младше 20'
    WHEN age BETWEEN 20 AND 29 THEN '20-е'
    WHEN age BETWEEN 30 AND 39 THEN '30-е'
    WHEN age BETWEEN 40 AND 49 THEN '40-е'
    WHEN age >= 50 THEN '50+'
  END AS age_group,
  COUNT(*) AS count
FROM users
GROUP BY age_group;

Подробнее об этом расскажет наш спикер на видео

Как перейти на новый уровень сложности с помощью продвинутой группировки

Для решения более сложных задач применяются подзапросы или Общие табличные выражения (CTE), облегчающие процесс группировки:

WITH ScoreRanges AS (
  SELECT
    id,
    CASE
      WHEN score < 50 THEN 'Неуд'
      WHEN score < 70 THEN 'Удовл'
      WHEN score >= 70 THEN 'Выше среднего'
    END AS ScoreGroup
  FROM StudentScores
)
SELECT ScoreGroup, COUNT(id) AS NumberOfStudents
FROM ScoreRanges
GROUP BY ScoreGroup;

Способы работы с динамической группировкой

С помощью функции NTILE можно осуществить равномерное распределение данных по заданному числу групп:

SELECT NTILE(4) OVER (ORDER BY value) AS quartile, COUNT(*) AS count
FROM measurements
GROUP BY quartile;

Как справиться с выбросами

Для работы со статистическими выбросами используется группировка по таким параметрам, как стандартные отклонения:

SELECT
  CASE
    WHEN value < (AVG(value) – STDDEV(value)) THEN 'Ниже среднего'
    WHEN value BETWEEN (AVG(value) – STDDEV(value)) AND (AVG(value) + STDDEV(value)) THEN 'Среднее'
    ELSE 'Выше среднего'
  END AS ValueRange,
  COUNT(*) AS Frequency
FROM data_points
GROUP BY ValueRange;

Визуализация

Процесс группировки по диапазонам в SQL можно сравнить со сгруппированными по десятилетиям выпуска книгами в библиотеке:

С помощью этого запроса книги группируются по десятилетиям:

SELECT FLOOR(year/10)*10 as Decade, COUNT(*) as NumberOfBooks
FROM library
GROUP BY FLOOR(year/10);

Полезные материалы

1. GROUP BY (Transact-SQL) – SQL Server | Microsoft Learn — подробное объяснение работы команды GROUP BY в SQL для Microsoft SQL Server.
2. PostgreSQL: Documentation: 16: 2.7. Aggregate Functions — обсуждение работы с GROUP BY и агрегатными функциями в PostgreSQL.
3. SQL Server Common Table Expression (CTE) Basics – Simple Talk — описание применения Общих табличных выражений (CTE) в SQL при группировке данных.
4. javascript – from green to red color depend on percentage – Stack Overflow — пример группировки по фиксированным диапазонам в MySQL на фоне дискуссии о JavaScript.
5. SQL GROUP BY and HAVING Clause — разъяснения принципов работы GROUP BY и HAVING Clause в SQL.