Базовый курс SQL. Группировка данных.

[к оглавлению]

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

Группировка данных

Рассмотрим на примере. Мы уже знаем, что команда SELECT COUNT (*) посчитает количество всех строк в таблице:

SELECT COUNT(*) as students_count
FROM Students;

Результат:

students_count
---------------
  10

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

SELECT student_age, COUNT(*) as students_count
FROM Students
GROUP BY student_age;

Результат:

student_age  |  students_count
--------------------------------------
   24	    |    1
   19	    |    1
   25	    |    1
   21	    |    2
   20	    |    1
   22	    |    3
   23	    |    1

Мы получаем два столбца: в одном все возможные варианты возрастов, представленные в таблице Students, в другом - количество студентов данного возраста. При использовании оператора GROUP BY СУБД делит таблицу на группы строк, в каждой из которых, значение столбца, указанного в качестве аргумента (в нашем случае - student_age) уникально. Это помогает избежать множественных запросов для каждого возраста в отдельности.

Важные правила применения GROUP BY:

• В качестве аргументов GROUP BY может быть сколько угодно столбцов. Это приведёт к тому, что группы будут формироваться внутри более крупных групп.
• Более того, каждый столбец (кроме вычисляемого столбца с итоговой функцией), представленный в SELECT, обязан быть и в GROUP BY
• Каждый аргумент GROUP BY должен быть извлекаемым столбцом или выражением (не итоговой функцией). Если в SELECT есть вычисляемое выражение, оно должно быть указано в GROUP BY так же - псевдоним не сработает.
• Если в GROUP BY используются вложенные группы, данные подытоживаются для последней указанной группы. Иными словами, при группировке вычисления осуществляются для всех указанных столбцов (вы не сможете получить данные для каждого столбца в отдельности).
• В большинстве СУБД нельзя создать группировку по текстовым полям (или любым другим полям с изменяемой длинной).
• NULL. Строки, содержащие NULL в столбце, по которому производится группировка, будут выведены как отдельная группа.
• Порядок операторов в запросе строго закреплён: сначала WHERE, затем GROUP BY, и после ORDER BY.

Относительное именование в GROUP BY

Чтобы не перечислять все столбцы дважды: в SELECT() и GROUP BY(), во втором случае можно указать только относительное положение столбца (порядковый номер, под которым он уже указан в SELECT). Например, GROUP BY 2, 1 означает, что группировать данные требуется сначала по столбцу, указанному вторым, а потом по первому.

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

Фильтрация по группам

Мы научились выводить данные, группируя их, но что если нам требуется информация не обо всех группах? Для фильтрации на уровне групп существует оператор HAVING. По принципу работы он идентичен оператору WHERE, но применяет условия не ко всем строкам, а к группам, полученным с помощью оператора GROUP BY и выполняется ПОСЛЕ GROUP BY.

Напомнить себе, как формулировать условия, можно в главе о WHERE. Синтаксис в HAVING будет такой же, метасимволы, математические и логические операторы поддерживаются. А мы перейдём к примерам:

Снова подсчитаем количество студентов разного возраста, но добавим фильтр "только группы, количество студентов в которых больше, либо равно 2м":

SELECT student_age, COUNT(*) as students_count
FROM Students
GROUP BY student_age
HAVING COUNT(*) >= 2;

Получим 2 группы студентов: двое тех, кому 21 год и трое тех, кому 22:

student_age  |  students_count
--------------------------------------
   21	    |    2
   22	    |    3

Можно ли использовать WHERE вместо HAVING?

Получить результат запроса, рассмотренного выше, с помощью WHERE невозможно, т.к. фильтрация происходит уже по итоговым данным групп, о которых WHERE ничего не "знает". Поэтому ответ на вопрос, можно ли использовать WHERE вместо HAVING, - нет.

Можно ли использовать HAVING вместо WHERE?

Оператор HAVING применяется ПОСЛЕ того, как группы созданы, а WHERE - ПЕРЕД. Таким образом, использование WHERE позволяет получить совершенно иной состав в группах и даже другой набор групп. Соответственно - тоже нет.

А как?

Надо помнить об их различии и использовать оба оператора по назначению: HAVING только вместе с GROUP BY, WHERE - для фильтрации всей выборки.

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

SELECT student_age, COUNT(*) as students_count
FROM Students
WHERE student_country = 'РФ'
GROUP BY student_age
HAVING COUNT(*) >= 2;

СУБД сначала выберет только студентов, поле student_country которых равно 'РФ'. Затем сгруппирует их по полю student_age. И после этого, применит фильтр к группам "количество студентов в группе не меньше 2х". Получим уже другой результат: т.к. в группе 21-летних остался один студент, она не попадёт в выборку:

student_age  |  students_count
--------------------------------------
   22	    |    3

Различия группировки и сортировки

Логически GROUP BY и ORDER BY выполняют разные функции, но почему-то нередко их путают. Давайте напомним себе, чем же они отличаются

1. Что делает. ORDER BY - сортирует результаты. GROUP BY - формирует группы, отображаемый результат может оказаться не отсортированным.
2. Аргументы. ORDER BY - сортировка возможна по любому из столбцов таблицы (даже не указанному в качестве аргумента SELECT). GROUP BY - аргументами являются все указанные в SELECT столбцы (кроме итоговой функции), и только они.
3. Необходимость. ORDER BY - используется по желанию. GROUP BY - строго необходим при итоговых вычислениях (COUNT(), MAX(), SUM() и др).

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

Например, мы можем отсортировать наши группы студентов по полю student_age:

SELECT student_age, COUNT(*) as students_count
FROM Students
GROUP BY student_age
ORDER BY student_age;

Результат:

student_age  |  students_count
--------------------------------------
   19	    |    1
   20	    |    1
   21	    |    2
   22	    |    3
   23	    |    1
   24	    |    1
   25	    |    1

Или по вычисляемому полю students_count, а затем по students_age:

SELECT student_age, COUNT(*) as students_count
FROM Students
GROUP BY student_age
ORDER BY students_count DESC, student_age;

Результат:

student_age  |  students_count
--------------------------------------
   22	    |    3
   21	    |    2
   19	    |    1
   20	    |    1
   23	    |    1
   24	    |    1
   25	    |    1

Сортировка применяется в последнюю очередь, ко всем отфильтрованным и сгруппированным результатам.

Порядок операторов в запросе

Мы изучили уже много операторов и следует знать, в каком порядке они должны следовать в запросе. Изменение данного порядка приведёт к ошибке.

1. SELECT [обязательный] (искомые столбцы или выражения)
2. FROM [обязательный, если извлекаются данные из таблицы] (название таблицы)
3. WHERE [необязательный] (условия фильтрации на уровне строк)
4. GROUP BY [необязательный] (столбцы для группировки)
5. HAVING [необязательный] (условия фильтрации на уровне групп)
6. ORDER BY [необязательный] (порядок сортировки результата)

Итак

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

Следующая страница. Подзапросы.
Оглавление.