Базы данных
и язык SQL

Оператор SQL GROUP BY для группировки в запросах

Назад<<<ЛистатьВперёд>>>

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

Часто оператор SQL GROUP BY применяется вместе с агрегатными функциями (COUNT, SUM, AVG, MAX, MIN). В этих случаях агрегатные функции служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец - общий.

Оператор GROUP BY имеет следующий синтаксис:

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ [WHERE УСЛОВИЕ] GROUP BY ИМЕНА_СТОЛБЦОВ

Группировка по одному столбцу без агрегатных функций

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.

В примерах работаем с базой данных библиотеки и ее таблицей "Книга в пользовании" (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.

AuthorTitlePubyearInv_NoCustomer_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПушкинСочинения, т.11984647
ПастернакИзбранное200013718
ПушкинСочинения, т.219848205
NULLНаука и жизнь 9 2018201912718
ЧеховРанние рассказы200117131

Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:

SELECT Author FROM BOOKINUSE GROUP BY Author

Этот запрос вернёт следующий результат:

Author
NULL
Гоголь
Ильф и Петров
Маяковский
Пастернак
Пушкин
Толстой
Чехов

Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.

В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.

Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос, который допустим в MySQL:

SELECT Author, Title FROM Bookinuse GROUP BY Author

Этот запрос вернёт следующий результат:

AuthorTitle
NULLНаука и жизнь 9 2018
ГогольПьесы
Ильф и ПетровДвенадцать стульев
МаяковскийПоэмы
ПастернакДоктор Живаго
ПушкинКапитанская дочка
ТолстойВойна и мир
ЧеховВишнёвый сад

Как видим, в таблице каждому автору соответствует лишь одна книга, причём та, которая в таблице BOOKINUSE является первой по порядку записей.

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

По-другому ведёт себя оператор GROUP BY в MS SQL Server.

Группировка по нескольким столбцам без агрегатных функций

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

Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. На MySQL запрос будет следующим:

SELECT Author, Title, Customer_ID, Inv_no FROM Bookinuse GROUP BY Author, Customer_ID, Inv_no

Этот запрос вернёт следующий результат:

AuthorTitleCustomer_IDInv_no
ГогольПьесы4781
Ильф и ПетровДвенадцать стульев313
МаяковскийПоэмы1202
ПастернакИзбранное18137
ПастернакДоктор Живаго12069
ПушкинКапитанская дочка4725
ПушкинСочинения, т.1476
ПушкинСочинения, т.22058
ТолстойВоскресенье4777
ТолстойВойна и мир6528
ТолстойАнна Каренина2057
ЧеховВишневый сад3119
ЧеховРанние рассказы31171
ЧеховВишневый сад655
ЧеховИзбранные рассказы12019
ЧеховИзбранные рассказы2054

Как видим, в результирующей таблице присутствуют все книги всех авторов, причём авторы следуют по порядку, как если бы мы применили оператор ORDER BY. Кроме того, видно, что записи сгруппированы и по второму указанному столбцу - Customer_ID. Так, у автора Пушкина сначала перечисляются книги, выданные пользователю с Customer_ID 47, а затем - 205. У автора Чехова сначала перечисляются книги, выданные пользователю с Customer_ID 31, а затем - с другими номерами. Третий столбец, по которому происходит группировка - Inv_no - добавлен только для того, чтобы в результирующей таблице выводились все строки, соответствующие значениям ранее перечисленных столбцов для группировки, а не только уникальные.

По-другому ведёт себя оператор GROUP BY в MS SQL Server и в случае этого запроса.

Группировка с агрегатными функциями

Агрегатные функции COUNT, SUM, AVG, MAX, MIN служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец - общий.

Пример 4. Вывести количество выданных книг каждого автора. Запрос будет следующим:

SELECT Author, COUNT(*) AS InUse FROM Bookinuse GROUP BY Author

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

AuthorInUse
NULL1
Гоголь1
Ильф и Петров1
Маяковский1
Пастернак2
Пушкин3
Толстой3
Чехов5

Пример 5. Вывести количество книг, выданных каждому пользователю. Запрос будет следующим:

SELECT Customer_ID, COUNT(*) AS InUse FROM Bookinuse GROUP BY Customer_ID

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

User_IDInUse
181
313
474
652
1203
2053

Примеры запросов к базе данных "Библиотека" есть также в уроках по оператору IN, предикату EXISTS и функциям CONCAT, COALESCE.

На сайте есть более подробный материал об агрегатных функциях и их совместном использовании с оператором GROUP BY.

Поделиться с друзьями

Назад<<<ЛистатьВперёд>>>