Все, что вам нужно знать об операторе SQL GROUP BY

Дядя Влад

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

Группировка позволяет извлекать сводную информацию из базы данных. Он позволяет комбинировать результаты для создания полезных статистических данных. Группировка избавляет вас от написания кода для распространенных случаев, таких как усреднение списков цифр. И это может сделать системы более эффективными.

Что делает предложение GROUP BY?

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

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

 +----+---------------------------+-----------+--------------+-------+
 | id | name | artist_id | release_year | sales |
 +----+---------------------------+-----------+--------------+-------+
 | 1 | Abbey Road | 1 | 1969 | 14 |
 | 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
 | 3 | Rumours | 3 | 1977 | 28 |
 | 4 | Nevermind | 4 | 1991 | 17 |
 | 5 | Animals | 2 | 1977 | 6 |
 | 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
 | 7 | 21 | 6 | 2011 | 25 |
 | 8 | 25 | 6 | 2015 | 22 |
 | 9 | Bat Out of Hell | 7 | 1977 | 28 |
 +----+---------------------------+-----------+--------------+-------+

Таблица художников еще проще. Он состоит из семи строк с столбцами id и name:

 +----+---------------+
 | id | name |
 +----+---------------+
 | 1 | The Beatles |
 | 2 | Pink Floyd |
 | 3 | Fleetwood Mac |
 | 4 | Nirvana |
 | 5 | Elton John |
 | 6 | Adele |
 | 7 | Meat Loaf |
 +----+---------------+

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

Группировка по одному столбцу

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

 SELECT artist_id FROM albums

Это возвращает все девять строк, как и ожидалось:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 2 |
 | 5 |
 | 6 |
 | 6 |
 | 7 |
 +-----------+

Чтобы сгруппировать эти результаты по исполнителю, добавьте фразу GROUP BY artist_id :

 SELECT artist_id FROM albums GROUP BY artist_id

Что дает следующие результаты:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 +-----------+

В результирующем наборе семь строк, уменьшенных по сравнению с девятью в таблице альбомов . Каждый уникальный artist_id имеет одну строку. Наконец, чтобы получить фактическое количество, добавьте COUNT (*) к выбранным столбцам:

 SELECT artist_id, COUNT(*)
 FROM albums
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 1 |
 | 4 | 1 |
 | 5 | 1 |
 | 6 | 2 |
 | 7 | 1 |
 +-----------+----------+

Результаты группируют две пары строк для исполнителей с идентификаторами 2 и 6 . У каждого в базе по два альбома.

Связанный: Шпаргалка по основным командам SQL для начинающих

Как получить доступ к сгруппированным данным с помощью агрегатной функции

Возможно, вы уже использовали функцию COUNT раньше, особенно в форме COUNT (*), как показано выше. Получает количество результатов в наборе. Вы можете использовать его для получения общего количества записей в таблице:

 SELECT COUNT(*) FROM albums
 +----------+
 | COUNT(*) |
 +----------+
 | 9 |
 +----------+

СЧЁТ – это агрегатная функция. Этот термин относится к функциям, которые переводят значения из нескольких строк в одно значение. Они часто используются вместе с оператором GROUP BY.

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

 SELECT artist_id, SUM(sales)
 FROM albums
 GROUP BY artist_id
 +-----------+------------+
 | artist_id | SUM(sales) |
 +-----------+------------+
 | 1 | 14 |
 | 2 | 30 |
 | 3 | 28 |
 | 4 | 17 |
 | 5 | 8 |
 | 6 | 47 |
 | 7 | 28 |
 +-----------+------------+

Показанные выше общие продажи артистов 2 и 6 представляют собой продажи их нескольких альбомов вместе:

 SELECT artist_id, sales
 FROM albums
 WHERE artist_id IN (2, 6)
 +-----------+-------+
 | artist_id | sales |
 +-----------+-------+
 | 2 | 24 |
 | 2 | 6 |
 | 6 | 25 |
 | 6 | 22 |
 +-----------+-------+

Группировка по нескольким столбцам

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

 SELECT release_year, sales, count(*)
 FROM albums
 GROUP BY release_year, sales

Обычно это дает больше результатов, чем группировка по одному столбцу:

 +--------------+-------+----------+
 | release_year | sales | count(*) |
 +--------------+-------+----------+
 | 1969 | 14 | 1 |
 | 1973 | 24 | 1 |
 | 1977 | 28 | 2 |
 | 1991 | 17 | 1 |
 | 1977 | 6 | 1 |
 | 1973 | 8 | 1 |
 | 2011 | 25 | 1 |
 | 2015 | 22 | 1 |
 +--------------+-------+----------+

Обратите внимание, что в нашем небольшом примере только два альбома имеют один и тот же год выпуска и количество продаж (28 в 1977 году).

Полезные агрегатные функции

Помимо COUNT, несколько функций хорошо работают с GROUP. Каждая функция возвращает значение, основанное на записях, принадлежащих каждой группе результатов.

  • COUNT () возвращает общее количество совпадающих записей.
  • SUM () возвращает сумму всех суммированных значений в данном столбце.
  • MIN () возвращает наименьшее значение в данном столбце.
  • MAX () возвращает наибольшее значение в данном столбце.
  • AVG () возвращает среднее значение. Это эквивалент SUM () / COUNT ().

Вы также можете использовать эти функции без предложения GROUP:

 SELECT AVG(sales) FROM albums
 +------------+
 | AVG(sales) |
 +------------+
 | 19.1111 |
 +------------+

Использование GROUP BY с предложением WHERE

Как и в случае с обычным SELECT, вы все равно можете использовать WHERE для фильтрации набора результатов:

 SELECT artist_id, COUNT(*)
 FROM albums
 WHERE release_year > 1990
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 4 | 1 |
 | 6 | 2 |
 +-----------+----------+

Теперь у вас есть только альбомы, выпущенные после 1990 года, сгруппированные по исполнителям. Вы также можете использовать соединение с предложением WHERE независимо от GROUP BY:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 +---------+--------+
 | name | albums |
 +---------+--------+
 | Nirvana | 1 |
 | Adele | 2 |
 +---------+--------+

Однако обратите внимание, что если вы попытаетесь выполнить фильтрацию на основе агрегированного столбца:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND albums > 2
 GROUP BY artist_id;

Вы получите сообщение об ошибке:

 ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Столбцы, основанные на агрегированных данных, недоступны для предложения WHERE.

Использование предложения HAVING

Итак, как отфильтровать набор результатов после того, как произошло группирование? Предложение HAVING касается этой потребности:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 GROUP BY artist_id
 HAVING albums > 1;

Обратите внимание, что предложение HAVING следует после GROUP BY. В противном случае, по сути, это простая замена WHERE на HAVING. Результаты следующие:

 +------------+--------+
 | name | albums |
 +------------+--------+
 | Pink Floyd | 2 |
 | Adele | 2 |
 +------------+--------+

Вы по-прежнему можете использовать условие WHERE для фильтрации результатов перед группированием. Он будет работать вместе с предложением HAVING для фильтрации после группировки:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 HAVING albums > 1;

Только один исполнитель в нашей базе данных выпустил более одного альбома после 1990 года:

 +-------+--------+
 | name | albums |
 +-------+--------+
 | Adele | 2 |
 +-------+--------+

Объединение результатов с помощью GROUP BY

Оператор GROUP BY – невероятно полезная часть языка SQL. Он может предоставлять сводную информацию о данных, например, для страницы с содержанием. Это отличная альтернатива выборке больших объемов данных. База данных хорошо справляется с этой дополнительной рабочей нагрузкой, поскольку сама ее конструкция делает ее оптимальной для работы.

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