Оператор GROUP BY
В базе данных priem имеется таблица abiturient следующего содержания:
Необходимо подсчитать количество абитуриентов мужского и женского пола.
Для решения задачи необходимо сгруппировать данные по полю age после чего подсчитать количество строк в каждой из двух групп.
Запросы, которые требуют группировки данных по некоторому полю (или нескольким полям) с последующим вычислением статистических данных по каждой группе называются запросами с групповой операцией или запросами с агрегирующей операцией.
Общий вид запроса с групповой операцией выглядит следующим образом:
Группирующее_поле – это поле по которому следует группировать данные. В приведенном примере это будет поле age.
Групповая_операция (или статистическая операция) – это встроенная функция, которая вычисляет некоторые статистические данные по группе. В данном случае это будет функция, которая подсчитывает количество записей в каждой группе. Такая функция в SQL называется Count().
Некоторое_поле – это поле, по которому применяется групповая статистическая операция. В приведенном примере это может быть поле idabiturient.
Инструкция AS позволяет дать новому вычисленному полю удобный псевдоним.
Итак, чтобы решить поставленную задачу нужно написать запрос:
SELECT gender, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender;
Результат:
Не следует без надобности добавлять в групповой запрос дополнительные поля. Любое поле, которое присутствует в запросе должно быть либо группирующим, либо полем по которому выполняется групповая операция. Если в групповой запрос добавить поле без групповой операции, то результат получится бессмысленным, как в следующем примере.
SELECT fio, gender, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender;
Поле fio, которое мы видим в результате, не несет никакого смысла и сбивает с толку пользователя.
Групповые операции
Во всех «диалектах» SQL имеется стандартный набор групповых операций:
- MAX() – вычисление максимума по группе;
- MIN() – вычисление минимума по группе;
- COUNT() – подсчет значений в группе;
- AVG() – среднее значение по группе;
- SUM() – сумма по группе.
Найти средний возраст абитуриента в мужской и женской группе.
SELECT gender, AVG(age) AS avg_age FROM abiturient GROUP BY gender;
Сложная группировка
Бывают ситуации, когда нужно применить несколько группировок одновременно. В этом случае важен их порядок, так как одна группировка применяется внутри другой и фактически ее нарушает.
Разделить абитуриентов на мужскую и женскую группы и подсчитать в каждой группе количество абитуриентов каждого возраста:
SELECT gender, age, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender, age;
Результат:
Отбор в групповых запросах
Отбор данных в групповом запросе можно осуществлять с помощью двух инструкций:
- WHERE;
- HAVING.
Но эти инструкции не идентичны.
WHERE выполняет отбор данных до выполнения группировки и не позволяет накладывать условия на столбцы с групповой операцией.
НAVING выполняет отбор после выполнения групповых операций и позволяет накладывать условия на поля с групповой операцией.
Рассмотрим запрос, который подсчитывает количество абитуриентов в возрастных группах только для мужской половины :
SELECT age, count(idabiturient) AS num_abitur FROM abiturient WHERE gender = 'male' GROUP BY age;
В этом примере сначала будут отобраны записи, где в поле gender стоит значение 'male'. Потом результат будет сгруппирован по полю age и выполнится функция COUNT().
Теперь рассмотрим запрос с использованием HAVING. Необходимо показать только те возрастные группы, где больше двух абитуриентов.
SELECT age, count(idabiturient) AS num_abitur FROM abiturient GROUP BY age HAVING num_abitur > 2;
В этом случае сначала будет выполнена группировка по возрасту с вычислением функции COUNT(). А у же потом в полученном результате будут отфильтрованы строки, где число абитуриентов превысило 2.
Таким образом, HAVING имеет смысл выполнять только, если условие накладывается на столбец с групповой операцией. Во всех остальных случаях целесообразно использовать WHERE.
