Группировка

Предложение GROUP BY

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

Синтаксис:

SELECT <поле1>, <поле2>, <агрегатная функция>(<поле3>)
    FROM <источник>
    WHERE <условие>
    GROUP BY <поле1>, <поле2>
    HAVING <условие>
    ORDER BY <поле1>;

Каждый не агрегированный столбец в SELECT списке, должен быть так же включён в GROUP BY список.

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

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

SELECT COUNT(*), AVG(AGE) FROM WORKERS;

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

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

SELECT COUNT(*), AVG(AGE), CURRENT_DATE FROM WORKERS;

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

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

SELECT COUNT(*), AVG(AGE) FROM WORKERS GROUP BY PROJECT_ID;

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

В нашем случе это будет.

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

• для группы записей где PROJECT_ID=1 будут использованы значения 20, 22, 21.
• для группы записей где PROJECT_ID=2 будут использованы значения 18, 24.
• для группы записей где PROJECT_ID=3 будут использованы значения 21, 21.

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

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

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

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

SELECT PROJECT_ID, COUNT(*), AVG(AGE) FROM WORKERS GROUP BY PROJECT_ID;

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

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

SELECT PROJECT_ID, SHIFT, COUNT(*), AVG(AGE) FROM WORKERS
    GROUP BY PROJECT_ID, SHIFT

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

Предложение GROUP BY работает после фильтрации записей WHERE и, следовательно, уже не учитывает данные, которые были отфильтрованы. Например,

SELECT COUNT(*), AVG(AGE) FROM WORKERS WHERE GENDER='М';

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

Предложение HAVING

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

Условие в предложении HAVING может ссылаться на любой агрегированный столбец в списке выбора SELECT или любой столбец в списке GROUP BY. В последнем случае эффективнее фильтровать не агрегированные данные на более ранней стадии в предложении WHERE.

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

SELECT PROJECT_ID, COUNT(*), AVG(AGE) FROM WORKERS 
    GROUP BY PROJECT_ID HAVING COUNT(*) > 2

Либо выбрать проекты, где разброс возраста участников больше 2-х лет.

SELECT PROJECT_ID, COUNT(*), AVG(AGE) FROM WORKERS
    GROUP BY PROJECT_ID HAVING MAX(AGE) - MIN(AGE) > 2

Обратите внимание, что в этом запросе мы не указываем MIN и MAX в списке выбора, однако используем их в предложении HAVING. Фактически они вычисляются, но не выводятся. Если бы они были, результат запроса был бы следующий.

SELECT PROJECT_ID, COUNT(*), AVG(AGE), MAX(AGE), MIN(AGE) FROM WORKERS
    GROUP BY PROJECT_ID HAVING MAX(AGE) - MIN(AGE) > 2

Контрольные вопросы

1. Опишите алгоритм работы группировки записей?
2. Какие ограничения накладывает группировка записей на список выбора?
3. Что допускается использовать в списке выбора при группировке записей?
4. Чем определяется количество записей, возвращаемых запросами с группировкой?
5. Для чего используется предложение HAVING?
6. Чем HAVING отличается от WHERE?
7. В каких случаях рекомендуется использовать WHERE, а не HAVING?