Группировка

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

Если список выборки содержит только агрегатные столбцы или столбцы, значения которых не зависит от отдельных строк основного множества, то предложение GROUP BY необязательно. Когда предложение GROUP BY пропущено, результирующее множество будет состоять из одной строки (при условии, что хотя бы один агрегатный столбец присутствует).

Если в списке выборки содержатся как агрегатные столбцы, так и столбцы, чьи значения зависит от выбираемых строк, то предложение GROUP BY становится обязательным.

Синтаксис:

SELECT ... FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>
<non-aggr-select-item> ::= column-copy | column-alias | column-position

, где

non-aggr-expression - Любое не агрегатное выражение, которое не включено в список выборки, т.е. невыбираемые столбцы из набора источника или выражения, которые не зависит от набора данных вообще.

column-copy - Дословная копия выражения из списка выбора, не содержащего агрегатной функции.

column-alias - Псевдоним выражения (столбца) из списка выбора, не содержащего агрегатной функции.

column-position - Номер позиции выражения (столбца) из списка выбора, не содержащего агрегатной функции.

Общее правило гласит, что каждый не агрегированный столбец в SELECT списке, должен быть так же включён в GROUP BY список. Вы можете это сделать тремя способами:

1. Копировать выражение дословно из списка выбора;

2. Указать псевдоним, если он существует;

3. Задать положение столбца в виде целого числа, которое находится в диапазоне от 1 до количества столбцов в списке SELECT.

В дополнении к требуемым элементам, список группировки так же может содержать:

• Столбцы исходной таблицы, которые не включены в список выборки SELECT, или неагрегатные выражения, основанные на таких столбцах. Добавление таких столбцов может дополнительно разбить группы. Но так как эти столбцы не в списке выборки SELECT, вы не можете сказать, какому значению столбца соответствует значение агрегированной строки. Таким образом, если вы заинтересованы в этой информации, вы так же должны включить этот столбец или выражение в список выборки SELECT, что возвращает вас к правилу «каждый не агрегированный столбце в списке выборки SELECT должен быть включён в список группировки GROUP BY».

• Выражения, которые не зависят от данных из основного набора, т.е. константы, контекстные переменные, некоррелированные подзапросы, возвращающие единственное значение и т.д. Это упоминается только для полноты картины, т.к. добавление этих элементов является абсолютно бессмысленным, поскольку они не повлияют на группировку вообще. «Безвредные, но бесполезные» элементы так же могут фигурировать в списке выбора SELECT без их копирования в список группировки GROUP BY.

Рассмотрим несколько примеров использования группировки. Для этого допустим у нас есть таблица сотрудников (WORKERS).

NAME	PROJECT_ID	AGE	GENDER	SHIFT
Иванов	1	20	М	1
Петров	1	22	М	2
Виноградова	1	21	Ж	1
Сидоров	2	18	М	2
Кузнецова	2	19	Ж	2
Дмитриева	3	21	Ж	1
Ершова	3	21	Ж	2

Когда в списке выбора SELECT содержатся только агрегатные столбцы, предложение GROUP BY необязательно:

SELECT COUNT(*), AVG(age) FROM workers WHERE gender = 'M'

Этот запрос вернёт одну строку с указанием количества сотрудников мужского пола и их средний возраст.

COUNT	AVG
3	20

Добавление выражения, которое не зависит от строк таблицы WORKERS, ничего не меняет:

SELECT COUNT(*), AVG(age), current_date FROM workers WHERE gender = 'M'

COUNT	AVG	CORRENT_DATE
3	20	2022-05-29

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

Тем не менее, в обоих приведённых выше примерах это разрешено. Например, Можно добавить группировку по группам:

SELECT COUNT(*), AVG(age) FROM workers WHERE gender = 'M' GROUP BY project_id

Этот запрос вернёт результат для каждого проекта, в котором есть сотрудники мужского пола, перечисляя количество сотрудников и их средний возраст в этой конкретном проекте.

COUNT	AVG
2	21
1	18

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

Такой запрос имеет не говорит вам, какая запись к какому проекту относится. Для того чтобы получить эту дополнительную часть информации, не агрегатный столбец PROJECT_ID должен быть добавлен в список выборки SELECT:

SELECT project_id, COUNT(*), AVG(age) FROM workers WHERE gender = 'M' GROUP BY project_id

Теперь у нас есть полезный запрос. Обратите внимание, что добавление столбца PROJECT_ID делает предложение GROUP BY обязательным. Результат будет следующим:

PROJECT_ID	COUNT	AVG
1	2	21
2	1	18

Допустим, что кроме этого мы хотим знать еще смену, в которую работает сотрудник. Просто добавить смену в список выбора нельзя. Требуется обязательно добавить это поле в список группировки.

SELECT project_id, shift, COUNT(*), AVG(age) FROM workers
    GROUP BY project_id, shift

Такой запрос изменит состав групп и вернет следующий результат:

PROJECT_ID	SHIFT	COUNT	AVG
1	Да	2	20,5
1	Нет	1	22
2	Нет	2	18,5
3	Да	1	21
3	Нет	1	21

Использование HAVING

Так же, как и предложение WHERE ограничивает строки в наборе данных, теми которые удовлетворяют условию поиска, с той разницей, что предложение HAVING накладывает ограничения на агрегированные строки сгруппированного набора. Предложение HAVING не является обязательным и может быть использовано только в сочетании с предложением GROUP BY.

Условие в предложении HAVING может ссылаться на:

• Любой агрегированный столбец в списке выбора SELECT. Это наиболее широко используемый случай;

• Любое агрегированное выражение, которое не находится в списке выбора SELECT, но разрешено в контексте запроса. Иногда это полезно;

• Любой столбец в списке GROUP BY. Однако более эффективно фильтровать не агрегированные данные на более ранней стадии в предложении WHERE;

• Любое выражение, значение которого не зависит от содержимого набора данных (например, константа или контекстная переменная). Это допустимо, но совершенно бессмысленно, потому что такое условие, не имеющее никакого отношения к самому набору данных, либо подавит весь набор, либо оставит его не тронутым.

Предложение HAVING не может содержать:

• Не агрегированные выражения столбца, которые не находятся в списке GROUP BY;

• Позицию столбца. Целое число в предложении HAVING – просто целое число;

• Псевдонимы столбца – даже, если они появляются в предложении GROUP BY.

Например, мы можем использовать предложение HAVING для исключения проектов с малым составом:

SELECT project_id, COUNT(*), AVG(age) FROM workers
    GROUP BY project_id HAVING COUNT(*) < 3

Либо выбрать проекты где разброс возраста участников меньше 1.2 года.

SELECT project_id, COUNT(*), AVG(age) FROM workers
    GROUP BY project_id HAVING MAX(age) - MIN(age) < 1.2