Home-theater-designers
Большая часть возможностей реляционных баз данных достигается за счет фильтрации данных и объединения таблиц. Поэтому мы в первую очередь представляем эти отношения. Но современные системы баз данных предоставляют еще один ценный прием: группировку.
Группировка позволяет извлекать сводную информацию из базы данных. Он позволяет комбинировать результаты для создания полезных статистических данных. Группировка избавляет вас от написания кода для общих случаев, таких как усреднение списков цифр. И это может сделать системы более эффективными.
Что делает предложение GROUP BY?
GROUP BY, как следует из названия, группирует результаты в меньший набор. Результаты состоят из одной строки для каждого отдельного значения сгруппированного столбца. Мы можем показать его использование, посмотрев на некоторые образцы данных со строками, которые имеют некоторые общие значения.
почему видео Amazon Prime не работает
Ниже приводится очень простая база данных с двумя таблицами, представляющими альбомы записей. Вы можете создать такую базу данных, написание базовой схемы для выбранной вами системы баз данных. В альбомы таблица имеет девять строк с первичным ключом я бы столбец и столбцы для имени, исполнителя, года выпуска и продаж:
+----+---------------------------+-----------+--------------+-------+
| 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 |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+Вы можете понять различные аспекты GROUP BY с помощью такого простого набора данных, как этот. Конечно, в реальном наборе данных будет намного больше строк, но принципы остаются теми же.
Группировка по одному столбцу
Допустим, мы хотим узнать, сколько альбомов у нас есть для каждого исполнителя. Начните с типичного ВЫБРАТЬ запрос для получения столбца artist_id:
SELECT artist_id FROM albumsЭто возвращает все девять строк, как и ожидалось:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+Чтобы сгруппировать эти результаты по исполнителю, добавьте фразу ГРУППА ПО идентификатору исполнителя :
SELECT artist_id FROM albums GROUP BY artist_idЧто дает следующие результаты:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+В результирующем наборе семь строк, уменьшенных по сравнению с девятью строками в альбомы стол. Каждый уникальный artist_id имеет одну строку. Наконец, чтобы получить фактическое количество, добавьте СЧИТАТЬ(*) к выбранным столбцам:
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 для начинающих
Как получить доступ к сгруппированным данным с помощью агрегатной функции
Возможно, вы использовали СЧИТАТЬ функции раньше, особенно в СЧИТАТЬ(*) форма, как показано выше. Получает количество результатов в наборе. Вы можете использовать его для получения общего количества записей в таблице:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+COUNT - это агрегатная функция. Этот термин относится к функциям, которые переводят значения из нескольких строк в одно значение. Они часто используются вместе с оператором 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
Итак, как отфильтровать набор результатов после того, как произошло группирование? В ИМЕЮЩИЕ статья касается этой потребности:
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. Он может предоставлять сводную информацию о данных, например, для страницы с содержанием. Это отличная альтернатива получению больших объемов данных. База данных хорошо справляется с этой дополнительной рабочей нагрузкой, поскольку сама ее конструкция делает ее оптимальной для работы.
Как только вы поймете группировку и то, как объединить несколько таблиц, вы сможете использовать большую часть возможностей реляционной базы данных.
Делиться Делиться Твитнуть Эл. адрес Как запросить сразу несколько таблиц базы данных с помощью соединений SQLУзнайте, как использовать объединения SQL для оптимизации запросов, экономии времени и создания ощущения опытного пользователя SQL.
передавать файлы между Mac и ПКЧитать далее Похожие темы
- Программирование
- SQL
Бобби - энтузиаст технологий, проработавший разработчиком программного обеспечения большую часть двух десятилетий. Он увлечен играми, работает редактором обзоров в Switch Player Magazine и занимается всеми аспектами онлайн-публикации и веб-разработки.
Ещё от Bobby JackПодписывайтесь на нашу новостную рассылку
Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!
Нажмите здесь, чтобы подписаться