|
Агрегирование и группировка 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 можно использовать следующие наиболее распространенные агрегирующие функции:
Давайте для примера подсчитаем в таблице 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. На следующем занятии мы продолжим о ней говорить и посмотрим как создается сводная выборка из нескольких таблиц. Видео по теме |
