Как работает group by в sql
Перейти к содержимому

Как работает group by в sql

  • автор:

Группировка в SQL

Группировка в SQL

SQL – язык запросов. Он позволяет работать с базами данных в различных СУБД. Наиболее распространенной системой управления является MySQL. Она проста в освоении и подойдет как новичкам, так и более опытным разработчикам.

При работе с таблицами в базах данных используются специальные команды. Они позволяют выполнять такие действия, как выборка, сортировка и пр. Особое внимание следует уделить оператору group by в SQL. Далее он будет изучен более подробно. Эта информация пригодится не только начинающим, но и уже продвинутым программистам, а также системным администраторам.

Описание

Предложения Group By отвечают за группировку. При помощи таких запросов в SQL можно сопоставлять строки. Они встречаются в операциях с агрегатными функциями (Min, AVG, SUM, Count и Max).

Функция group by в языке SQL будет сообщать системе, как агрегировать данные в неагрегированном столбце, который был запрошен пользователем. Он используется для распределения строк – результата запроса по группам. Группировка данных в SQL при помощи соответствующей команды может осуществляться как по одному параметру, так и по нескольким одновременно.

Подготовка к работе

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

  • название локации (место);
  • имя продукта;
  • стоимость;
  • время продажи.

Для работы с группировкой в SQL-запросах типы полей в столбцах будут простыми TEXT (текстовыми). В уже существующем приложении обычно используются внешние ключи к другим таблицам.

Группировка в SQL

Выше – описание таблицы, которую предстоит использовать далее для изучения принципов функционирования group by.

В предложенном примере имеются три локации: HQ, 1 st Street и Downtown. Здесь поддерживаются всего два продукта – кофе (coffee) и бублики (bagel). Продажи будут вноситься с разными значениями sold_at. Это требуется для демонстрации количества продукции, проданной в различные дни и время.

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

Принцип работы оператора

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

Дана комната, в которой много людей. Они родились в самых разных странах. Требуется вычислить средний рост людей в разрезе по месту рождения. Для этого сначала происходит разделение людей на группы по странам, из которых они прибыли. Только после этого удастся рассчитать средний рост в каждом сформированном «классе».

Аналогичным образом функционирует рассматриваемый оператор. Теперь ясно, как работает group by. Эта функция группирует информацию по строкам. Сначала нужно определить, как будет осуществляться классификация. После – произвести необходимые вычисления или агрегации.

Форма записи

Предложение Group By будет зависеть от непосредственного использования группирования. Общая форма записи следующая:

[WHERE необходимые условия для выборки полей]

GROUP BY имена_стоблцов_в_таблице

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

Множественные группы

Рассматриваемый оператор позволяет группировать информацию в самое разное количество групп и подгрупп. Представленные при помощи group by примеры далее наглядно продемонстрируют соответствующие операции.

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

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

Примеры работы

Далее будут приведены несколько наглядных примеров использования group by в программном коде. Сам оператор составляется легко: достаточно указать соответствующее ключевое слово и прописать поля, по которым осуществляется группировка:

Группировка в SQL

В Select as размещают описание локации. Данные будут группироваться по соответствующему столбцу. Чтобы увидеть имена созданных групп, необходимо воспользоваться следующей записью group by:

Группировка в SQL Группировка в SQL

Если посмотреть на необработанную часть таблицы, можно заметить 4 строчки с локацией HQ, две – с Downtown и 1 st street.

Группировка в SQL

При grouping by location база возьмет соответствующие входные строки и определит среди них уникальные локации. Они будут выступать в виде «групп», по которым осуществляется дальнейшая классификация.

Если выбрать столбец product, команда будет несколько иной:

Группировка в SQL

Результатом станет ошибка:

Группировка в SQL

Связано это с тем, что 8 строк, имеющихся в базе, при помощи group by пользователь попытался «поместить» в 3. Оставшиеся столбцы не получится возвращать просто так. Для применения всех данных из таблицы необходимо выделить соответствующую информацию из «остатка» в три локационные groups. Это значит, что сведения должны быть или агрегированы, или над ними обязательно выполняются вычисления для вывода результирующих данных.

Агрегатные функции

Далее предстоит рассмотреть несколько примеров с агрегатными функциями относительно group by. Каждая запись в sales – это информация об одной продаже. Это значит, что число продаж по локации = строки в каждой группе при разделении по локациям.

Для получения информации о том, сколько в каждом «месте» было продано продукции, используется такая запись с group by:

Группировка в SQL

При помощи count вычисляется количество строк в каждой группе. С выражениями команда тоже совмещается.

База выполняет запрос так:

  • from sales – получение всех записей из таблицы sales;
  • group by location – определение уникальных групп по типу локаций;
  • select… – выбор имени «места» и счет количества строк в группе.

Для вывода информации в читабельной форме, количество строк (результат) будет иметь псевдоним as number_of_sales. Итоговая таблица предлагает две колонки:

Группировка в SQL

Сумма

Sum – команда, которая помогает при помощи group by суммировать информацию. Пример – расчет общей выручки в каждом городе:

Группировка в SQL Группировка в SQL

Выше – пример обработки соответствующего запроса.

Среднее значение

Для расчета среднего значения в group by необходимо пользоваться AVG:

Группировка в SQL

Вот – пример средней суммы выручки.

Несколько групп

Чтобы разделить группы на подгруппы достаточно добавить к предложению group by второе условие классификации:

Группировка в SQL

Результат может быть возвращен при помощи select. Order by добавлено для удобства:

Группировка в SQL

Вот пример продаж по каждому продукту:

Группировка в SQL

Ничего трудного в процессе нет. Но group by может работать с функциями.

Использование функций

Можно попытаться найти общее количество продаж в день. Для этого используется шаблон, который применялся со столбцом sold_at:

Группировка в SQL

Для получения данных в нужном формате необходимо конвертировать значения даты и времени для каждой записи в обычную дату. После – все записи о продажах, совершенных в один и тот же день, отнести к одной «общей» группе.

Группировка в SQL

Здесь можно увидеть больше примеров использования изученного оператора. Прокачать навыки работы с базами данных можно на курсах Otus.

Как работает group by в sql

Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING , для использования которых применяется следующий формальный синтаксис:

SELECT столбцы FROM таблица [WHERE условие_фильтрации_строк] [GROUP BY столбцы_для_группировки] [HAVING условие_фильтрации_групп] [ORDER BY столбцы_для_сортировки]

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например, сгруппируем товары по производителю

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer

Первый столбец в выражении SELECT — Manufacturer представляет название группы, а второй столбец — ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.

Группировка и GROUP BY в T-SQL

Стоит учитывать, что любой столбец, который используется в выражении SELECT (не считая столбцов, которые хранят результат агрегатных функций), должны быть указаны после оператора GROUP BY. Так, например, в случае выше столбец Manufacturer указан и в выражении SELECT, и в выражении GROUP BY.

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

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products

Другой пример, добавим группировку по количеству товаров:

SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer, ProductCount

Оператор GROUP BY может выполнять группировку по множеству столбцов.

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

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE , но до выражения ORDER BY :

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products WHERE Price > 30000 GROUP BY Manufacturer ORDER BY ModelsCount DESC

GROUP BY в MS SQL Server

Фильтрация групп. HAVING

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

Применение HAVING во многом аналогично применению WHERE. Только есть WHERE применяется к фильтрации строк, то HAVING используется для фильтрации групп.

Например, найдем все группы товаров по производителям, для которых определено более 1 модели:

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer HAVING COUNT(*) > 1

HAVING в T-SQL

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

SELECT Manufacturer, COUNT(*) AS ModelsCount FROM Products WHERE Price * ProductCount > 80000 GROUP BY Manufacturer HAVING COUNT(*) > 1

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы — выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units FROM Products WHERE Price * ProductCount > 80000 GROUP BY Manufacturer HAVING SUM(ProductCount) > 2 ORDER BY Units DESC

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

Оператор SQL GROUP BY

Оператор SQL GROUP BY используется для объединения результатов выборки по одному или нескольким столбцам.

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

GROUP BY column_name

С использованием оператора SQL GROUP BY тесно связано использование агрегатных функций и оператор SQL HAVING

Примеры оператора SQL GROUP BY. Имеется следующая таблица Artists:

Singer Album Year Sale
The Prodigy Invaders Must Die 2008 1200000
Drowning Pool Sinner 2001 400000
Massive Attack Mezzanine 1998 2300000
The Prodigy Fat of the Land 1997 600000
The Prodigy Music For The Jilted Generation 1994 1500000
Massive Attack 100th Window 2003 1200000
Drowning Pool Full Circle 2007 800000
Massive Attack Danny The Dog 2004 1900000
Drowning Pool Resilience 2013 500000

Пример 1. Используя оператор SQL GROUP BY найти сумму продаж альбомов (Sale) всех исполнителей (Singer):

SELECT Singer, SUM(Sale) AS AllSales FROM Artists GROUP BY Singer
Singer AllSales
Drowning Pool 1700000
Massive Attack 5400000
The Prodigy 3300000

В данном запросе используется оператор SQL AS, позволяющий задать новое имя столбца AllSales на выходе. В нашем случае это сделано для наглядности.

Пример 2. Узнать в каком году был выпущен последний альбом каждой из групп используя оператор SQL GROUP BY:

SELECT Singer, MAX(Year) AS LastAlbumYear FROM Artists GROUP BY Singer
Singer LastAlbumYear
Drowning Pool 2013
Massive Attack 2004
The Prodigy 2008

Как работает group by в sql

Давайте выполним запрос:

MySQL
SELECT id, home_type, has_tv, price FROM Rooms; 
id home_type has_tv price
1 Private room 1 149
2 Entire home/apt 0 225
3 Private room 1 150
4 Entire home/apt 1 89
5 Entire home/apt 0 80
6 Entire home/apt 0 200
7 Private room 0 60
8 Private room 1 79
9 Private room 1 79
10 Entire home/apt 1 150
11 Entire home/apt 1 135
12 Private room 0 85
13 Private room 0 89
14 Private room 0 85
15 Entire home/apt 1 120
40 Shared room 1 40

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

Например, такими группами могут выступать записи разбитые по типу жилья:

  • Shared room (аренда комнаты на несколько человек)
  • Private room (аренда целой комнаты)
  • Entire home/apt (аренда целой квартиры)

Эти группы включают разные записи в таблице и, соответственно, обладают разными характеристиками, которые нам могут быть весьма полезны.

Такой полезной информацией о группах может быть:

  • средняя стоимость аренды комнаты или целого жилого помещения
  • количество сдаваемых жилых помещений каждого типа

Для ответов на все эти и многие другие вопросы есть оператор GROUP BY .

MySQL
SELECT [литералы, агрегатные_функции, поля_группировки] FROM имя_таблицы GROUP BY поля_группировки; 

Для того, чтобы записи у нас образовали группы по типу жилья мы должны после GROUP BY указать home_type , т.е. поле, по которому будет происходить группировка.

MySQL
SELECT home_type FROM Rooms GROUP BY home_type 
home_type
Private room
Entire home/apt
Shared room

Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL -значения, все такие строки попадут в одну группу

При использовании оператора GROUP BY мы перешли от работы с отдельными записями на работу с образовавшимися группами. В связи с этим мы не можем просто вывести любое поле из записи (например, has_tv или price ), как мы это могли делать раньше. Так как в каждой группе может быть несколько записей и в каждой из них в этом поле может быть разное значение.

При использовании GROUP BY мы можем выводить только:

    литералы, т.е. указанное явным образом фиксированные значения. Мы можем их выводить, так как это фиксированные значения, которые ни от чего не зависят.
    Например,


Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *