Операторы GROUP BY, HAVING и агрегатные функции в SQL на примерах

При совершении запросов в SQL есть возможность выбирать информацию путем обобщения данных таблиц. Для этого существуют специальные операторы. В этой статье мы рассмотрим, как осуществляется группировка с помощью оператора GROUP BY, фильтрация сгруппированной информации с использованием оператора HAVING в SQL, а также разберем агрегатные функции COUNT, AVG, SUM, MAX и MIN на примере.

Описание примера с GROUP BY

Для примера возьмем таблицу с названием countries, хранящую информацию о странах мира: часть света, к которой принадлежит страна, название страны, размер ее территории и количество населения:

id	part_of_world	name	area	population
1	America	USA	9372610	331002651
2	Europe	France	551695	65273511
3	Asia	China	9706961	1439323776
4	Europe	Germany	357114	83783942
5	Europe	Norway	323802	5421241
6	Asia	Japan	377930	126476461
7	Europe	Italy	301336	60461826

Перейдем к разбору вариантов выбора данных из вышеописанной таблицы с помощью различных SQL-запросов.

Структура SQL-запроса

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

SELECT (названия колонок через запятую или знак * — для выбора всех колонок)
FROM (название таблицы)
WHERE (условие фильтрации строк)
GROUP BY (название столбца, по которому производится группировка)
HAVING (условие фильтрации сгруппированным данных)
ORDER BY (название столбца, по которому производится сортировка)
ASC|DESC (сортировка восходящая|нисходящая)
LIMIT (максимальное число выводимых строк);

Для минимальной группировки достаточно использовать SELECT, FROM и GROUP BY. Остальные операторы — опциональны.

Группировка: оператор GROUP BY

Оператор GROUP BY в SQL позволяет сгруппировать данные по уникальному значению определенного столбца.

Предположим, в нашем примере мы хотим собрать группы по столбцу part_of_world. В этом столбце три уникальных значения — America, Europe и Asia. Сперва произведем простую группировку с выводом данных только по столбцу part_of_world. Пропишем его название после оператора GROUP BY, что указывает на создание групп по уникальным значениям именно этого столбца. Затем выберем сгруппированные данные с помощью оператора SELECT:

SELECT part_of_world
FROM countries
GROUP BY part_of_world;

Результат получится следующим:

part_of_world
America
Europe
Asia

Мы получили три группы строк, которые имеют уникальные значения part_of_world. Однако нам нужно сделать выборку остальных столбцов с неуникальными данными. То есть значения из нескольких ячеек строк одной группы должны каким-то образом объединиться в одну ячейку. Но какое значение должно быть в результирующей ячейке?

Чтобы выполнить такое объединение, нужно над неуникальными данными произвести дополнительные операции с получением итогового результата, который и будет записан в общую ячейку группы.

Такие операции называются агрегирующими и могут быть выполнены с помощью агрегатных функций: COUNT, AVG, SUM, MAX и MIN.

Агрегатные функции

В исходной таблице у нас было семь строк. После группировки мы получили три строки уникальных значений столбца part_of_world. Теперь начнем добавлять в выборку данные по оставшимся столбцам, агрегируя значения этих столбцов в пределах своей группы.

Агрегатная функция COUNT

Начнем с подсчета количества строк в каждой группе. Для этого используем агрегатную функцию COUNT. Добавим эту функцию после оператора SELECT, передав в нее значение «звездочки» (*), чтобы посчитать строки для всех групп.

В функцию COUNT можно также передавать и выражения. Но чтобы не усложнять пример, будем работать со всеми строками.

SELECT
    part_of_world,
    COUNT(*)
FROM countries
GROUP BY part_of_world;

part_of_world	COUNT(*)
America	1
Europe	4
Asia	2

В результате мы получим еще один столбец с названием COUNT(*), в котором отображается количество строк в каждой из групп. Название столбца не очень читабельно, заменим его на псевдоним, используя оператор AS.

SELECT
    part_of_world,
    COUNT(*) AS country_count
FROM countries
GROUP BY part_of_world;

part_of_world	country_count
America	1
Europe	4
Asia	2

Агрегатная функция AVG

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

SELECT
    part_of_world,
    AVG(population) AS population_avg
FROM countries
GROUP BY part_of_world;

Мы передали в качестве значения функции AVG название столбца population, задали ему псевдоним и получили следующий результат:

part_of_world	population_avg
America	331002651.0000
Europe	53735130.0000
Asia	782900118.5000

Округлим получившиеся средние значения, передав результат функции AVG функции ROUND:

SELECT
    part_of_world,
    ROUND(AVG(population)) AS population_avg
FROM countries
GROUP BY part_of_world;

part_of_world	population_avg
America	331002651
Europe	53735130
Asia	782900119

Агрегатная функция SUM

Подсчитать сумму значений определенной колонки в каждой из групп поможет агрегатная функция SUM. Вычислим общую площадь стран в пределах каждой части света.

SELECT
    part_of_world,
    SUM(area) AS area_sum
FROM countries
GROUP BY part_of_world;

part_of_world	area_sum
America	9372610
Europe	1533947
Asia	10084891

Агрегатные функции MAX и MIN

Когда необходимо найти максимальное либо минимальное значение в группах, используются функции MAX и MIN. Сделаем выборку максимальных и минимальных значений площадей и количества населения среди стран, относящихся к конкретной части света.

SELECT
    part_of_world,
    MAX(area) AS area_max,
    MIN(area) AS area_min,
    MAX(population) AS population_max,
    MIN(population) AS population_min
FROM countries
GROUP BY part_of_world;

Итог будет следующим:

part_of_world	area_max	area_min	population_max	population_min
America	9372610	9372610	331002651	331002651
Europe	551695	301336	83783942	5421241
Asia	9706961	377930	1439323776	126476461

Мы рассмотрели агрегатные функции SQL. Далее перейдем к разбору фильтрации групп и оператору HAVING.

Фильтрация: оператор HAVING

Оператор HAVING в SQL фильтрует сгруппированные строки по заданному условию. Например, мы хотим выбрать только те части света, суммарный размер территорий стран которых меньше 10 000 000.

Сделаем следующий SQL-запрос:

SELECT
    part_of_world,
    SUM(area) AS area_sum
FROM countries
GROUP BY part_of_world
HAVING area_sum < 10000000;

part_of_world	area_sum
America	9372610
Europe	1533947

В итоге из выборки будет исключен регион Asia.

Оператор HAVING с группами работает подобным образом, как и оператор WHERE со строками.

Итоговый SQL-запрос с GROUP BY

В заключение напишем более сложный запрос. Опишем условие задачи.

Из имеющихся данных в таблице countries выбрать максимум две части света, имеющих в своем составе страны:

• общий размер территорий которых меньше 9 000 000;
• среднее количество населения меньше 400 000 000.

Также необходимо вывести в отдельном столбце количество стран, находящихся в группе результирующей части света. Вывод отсортировать по размеру территорий начиная с наибольшей.

Помимо уже рассмотренных операторов и функций, в этой задаче используем сортировку и ограничение вывода.

Сортировка производится установлением оператора ORDER BY после HAVING и указанием столбца, по которому эта сортировка происходит. Направление сортировки задается операторами ASC (по возрастанию) и DESC (по убыванию).

Количество строк в выводе ограничивается оператором LIMIT, который добавляется в самый конец запроса с установлением числа лимита.

SELECT
    part_of_world,
    SUM(area) AS area_sum,
    ROUND(AVG(population)) AS population_avg,
    COUNT(*) as country_count
FROM countries
GROUP BY part_of_world
HAVING area_sum < 9500000 AND population_avg < 400000000
ORDER BY area_sum
DESC
LIMIT 2;

Результатом запроса будет нижеследующая таблица:

part_of_world	area_sum	population_avg	country_count
America	9372610	331002651	1
Europe	1533947	53735130	4

Понятно ли вам, как работает GROUP BY и HAVING в SQL? Напишите в комментариях.