Group by sql пример

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

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

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

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

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

В примерах работаем с базой данных библиотеки и ее таблицей "Книга в пользовании" (BOOKINUSE).

Author Title Pubyear Inv_No User_ID
Толстой Война и мир 2005 28 65
Чехов Вишневый сад 2000 17 31
Чехов Избранные рассказы 2011 19 120
Чехов Вишневый сад 1991 5 65
Ильф и Петров Двенадцать стульев 1985 3 31
Чехов Ранние рассказы 2001 171 31
Маяковский Поэмы 1983 2 120
Пастернак Доктор Живаго 2006 69 120
Толстой Воскресенье 2006 77 47
Толстой Анна Каренина 1989 7 205
Пушкин Капитанская дочка 2004 25 47
Гоголь Пьесы 2007 81 47
Чехов Избранные рассказы 1987 4 205
Пушкин Сочинения, т.1 1984 6 47
Пастернак Избранное 2000 137 18
Пушкин Сочинения, т.2 1984 8 205

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

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

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

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

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

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

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

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

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

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

Читайте также:  Php нижний регистр utf 8

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

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

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

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

Author Title User_ID Inv_no
Гоголь Пьесы 47 81
Ильф и Петров Двенадцать стульев 31 3
Маяковский Поэмы 120 2
Пастернак Избранное 18 137
Пастернак Доктор Живаго 120 69
Пушкин Капитанская дочка 47 25
Пушкин Сочинения, т.1 47 6
Пушкин Сочинения, т.2 205 8
Толстой Воскресенье 47 77
Толстой Война и мир 65 28
Толстой Анна Каренина 205 7
Чехов Вишневый сад 31 19
Чехов Ранние рассказы 31 171
Чехов Вишневый сад 65 5
Чехов Избранные рассказы 120 19
Чехов Избранные рассказы 205 4

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

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

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

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

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

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

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

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

Одной из важных команд в SQL является GROUP BY. Данная конструкция создана для выборки отдельных групп строк из таблицы, к каждой из которых применяются функции, указанные в SELECT (например, COUNT(), MIN() и так далее). Давайте разберём GROUP BY в SQL на конкретных примерах.

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

  • id — уникальный идентификатор.
  • shop_id — уникальный идентификатор супермаркета.
  • price — цена на молоко.
Читайте также:  Типы подсветки матрицы телевизора

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

Исходная таблица выглядит следующим образом:

id shop_id price
1 1 40
2 2 36
3 1 35
4 3 38
5 4 39
6 3 38
7 3 35

Для решения нашей задачи мы используем GROUP BY:

SELECT `shop_id`, AVG(`price`) FROM `table` GROUP BY `shop_id`

В результате получится следующее:

shop_id AVG(`price`)
1 37.5
2 36.0
3 37.0
4 39.0

Таким образом, мы узнали среднюю цену в конкретной сети супермаркетов (или в одиночном магазине).

Ещё одним очень частым применением GROUP BY в SQL является выборка уникальных записей из таблиц. В предыдущем примере Вы заметили, что в результирующей выборке нет повторяющихся shop_id, тогда как в исходной таблице они были.

Допустим, у нас есть таблица с пользователями:

  • id — уникальный идентификатор.
  • emaile-mail пользователя.
  • hash — уникальный хэш пользователя.

И перед нами встала задача выбрать уникальных пользователей, причём именно уникальных людей, а не уникальных учётных записей. Ведь у одного человека может быть и 100 аккаунтов с разными e-mail и, разумеется, id. А hash — это некая строка, характеризующая его как уникального человека.

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

SELECT * FROM `table` GROUP BY `hash`

В результате, будут извлечены только уникальные hash, то есть 2 одинаковых hash в результирующей выборке Вы не увидите.

Вот таких два практических примера использования GROUP BY в SQL мы разобрали.

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

Она выглядит вот так:

  • BB-код ссылки для форумов (например, можете поставить её в подписи):
  • Комментарии ( 6 ):

    У вас пропущена запятая в строке: "Допустим у нас есть таблица с пользователями:" Хорошие статьи. Пишите по SQL по-больше! Там много всяких встроенных функций, про которые не плохо бы рассказать

    Читайте также:  Beeline переоформление сим карты после смерти

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

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

    Рассмотрим простой пример:

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

    model Qty_model Avg_price
    1121 3 850
    1232 4 425
    1233 3 843,333333333333
    1260 1 350

    Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений <модель, дата>.

    Существует несколько определенных правил выполнения агрегатных функций.

      Если в результате выполнения запроса не получено ни одной строки (или ни одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций — NULL .
      Данное свойство может дать не всегда очевидный результат. Рассмотрим, например, такой запрос:

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