Агрегирование и группировка GROUP BY

На этом занятии рассмотрим агрегирующие функции, используемые в языке SQL и поговорим о возможности группировки записей по определенному полю. Все эти операции доступны в команде SELECT, о которой мы уже говорили на предыдущих занятиях.

Агрегирующие функции в SELECT

Чтобы лучше понять что такое агрегирующие функции, рассмотрим пример подсчета числа записей таблицы games, которые были сыграны игроком с user_id = 1.

Для этого создадим вот такой SQL-запрос:

SELECT count(user_id) FROM games WHERE user_id = 1

Здесь count(название столбца) – это агрегирующая функция, которая подсчитывает число выбранных записей. Результатом будет одна запись со значением 3:

Почему мы указали столбец user_id? В действительности, можно указать любой другой, разницы не будет, так как число записей для любых столбцов будет равно трем. Можно даже ничего не указывать и просто записать count(). Также обратите внимание, на название столбца: count(user_id). Использовать такое имя в дальнейшем не очень удобно. Для таких случаев придумали такую вещь как алиас (синоним), который можно указать так:

SELECT count() as count FROM games WHERE user_id = 1

Теперь столбец называется просто count и на выходе мы его и видим:

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

В общем случае в команде SELECT можно использовать следующие наиболее распространенные агрегирующие функции:

  • count() – подсчет числа записей;
  • sum() – подсчет суммы указанного поля по всем записям выборки;
  • avr() – вычисление среднего арифметического указанного поля;
  • min() – нахождение минимального значения для указанного поля;
  • max() – нахождение максимального значения для указанного поля.

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

SELECT count(DISTINCT user_id) as count FROM games

Смотрите, мы здесь перед полем user_id указали ключевое слово DISTINCT, которое указывает СУБД выбирать записи с уникальными user_id. Затем, с помощью агрегирующей функции count мы вычисляем число таких записей и получаем количество уникальных игроков. Чтобы все это было понятнее, можно выполнить запрос без агрегирующей функции:

SELECT DISTINCT user_id FROM games

Получим следующую выборку:

Далее, предположим, что нам нужно подсчитать суммарное число очков, которое набрал игрок с user_id = 1 за все игры. Такой запрос будет выглядеть так:

SELECT sum(score) as sum FROM games WHERE user_id = 1

И на выходе получим значение 900. Действительно, суммируя очки из таблицы games для первого игрока, получаем значение 900.

Также можно брать максимальное или минимальное число очков:

SELECT max(score) FROM games WHERE user_id = 1
SELECT min(score) FROM games WHERE user_id = 1

И так далее. По такому принципу можно применять все агрегирующие функции в команде SELECT.

Группировка GROUP BY

Язык SQL позволяет вызывать агрегирующие функции не для всех записей в выборке, а локально для указанных групп. Например, мы хотим на основе нашей таблицы games создать список ТОП лучших игроков. Для этого нужно вызвать функции sum для каждого уникального user_id.

Это делается с помощью оператора

GROUP BY <имя поля>

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

SELECT user_id, sum(score) as sum 
FROM games 
GROUP BY user_id

На выходе получим таблицу:

Но нам нужно еще отсортировать ее по убыванию очков, поэтому в конец запроса добавим оператор:

ORDER BY sum DESC

После выполнения запроса получим таблицу с отсортированными записями:

Добавим еще фильтр для выбора записей с числом очков более 300:

SELECT user_id, sum(score) as sum 
FROM games
WHERE score > 300
GROUP BY user_id
ORDER BY sum DESC

Получим результат:

Обратите внимание на порядок записи операторов в команде SELECT и порядок их работы. Сначала будут отобраны все записи с числом очков более 300, затем, они сгруппируются и отсортированы по убыванию очков. Именно поэтому теперь для user_id = 2 число очков стало 1100 (было 1400), а для user_id = 1 число очков равно 400.

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

LIMIT 1

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