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

Как работает GROUP BY в MySQL?

То есть, в столбце DEPARTMENT_ID ищется уникальное (похоже на DISTINCT ) значение отдела, например, 30, затем ищутся все строки, где упоминается отдел 30 в данной таблице, из этих строк берутся значения из столбца SALARY и суммируются ( SUM ). Потом ищется другой покупатель и все повторяется. В итоге я получаю сколько получил вообще денег каждый отдел. Не понимаю момент: у меня есть 6 строк, в которых есть столбец DEPARTMENT_ID со значением 30. Какая из строк пойдет в таблицу- SELECT и почему? То есть, в таблице Employees было шесть строк с DEPARTMENT_ID 30, а в таблице- SELECT такая строка только одна. Как вообще эта группировка работает?

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

Отслеживать
13.7k 12 12 золотых знаков 43 43 серебряных знака 75 75 бронзовых знаков
задан 5 дек 2016 в 9:35
2,059 3 3 золотых знака 18 18 серебряных знаков 37 37 бронзовых знаков

А что вас смущает? Да, при группировке выбирается первая прочитанная строка из группы, т. е., если в таблице Employees было ШЕСТЬ строк с DEPARTMENT_ID 30, то в таблице SELECT такая строка будет только ОДНА, и остальные поля, которые выбирались из первой таблицы SELECT ‘ом, но не вошли в GROUP BY , будут соответствовать первой прочитанной строке из группы с DEPARTMENT_ID = 30

5 дек 2016 в 9:51
где это написано, что выбирается именно ПЕРВАЯ строка? не встречал
5 дек 2016 в 9:53
webnotes.by/docs/sql/osobennosti-group-by-v-mysql например, тут
5 дек 2016 в 9:55
@Mike +1 про другие СУБД. Вообще странное поведение MySQL, первый раз счас про него прочитал 😀
5 дек 2016 в 10:18

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

В выборку после group by не попадет ни одна из изначальных строк. На выходе агрегат — сумма данных в нужном разрезе. К колонкам, к которым вы явно не применили никаких групповых функций (таких как sum() ), будет применена функция «первое попавшееся». Причем только в MySQL и только при выключенной опции ONLY_FULL_GROUP_BY . В остальных СУБД запрос, в котором хотя бы к одной колонке, не являющейся разрезом указанным в group by, «забыли» применить групповую функцию выдаст ошибку.

Как работает group by можно прикинуть в экселе. Запишите данные на лист, отсортируйте по тем полям, которые должны быть в group by . Читая отсортированные данные подряд в любом случае когда значение в очередной строке в колонках, указанных в group by отличается от значений в предыдущей — вставьте новую строку, скопируйте значения колонок group by , а в остальные поместите формулы вроде СУММ() ячеек группы под которой подводится итог. Результат group by — это именно эти вставленные итоговые записи. СУБД работает примерно по такому же алгоритму — сначала сортирует, потом суммирует идущие подряд одинаковые записи.

Добавлю про MySQL — он все таки слишком вольно к этому относится. Старайтесь всегда явно применять групповые функции ко всем колонкам, что бы самому понимать что именно в них окажется, ибо ‘первое попавшееся’ ни чем не стандартизировано и может меняться от версии к версии и в зависимости от физического расположения записей на диске и плана выполнения запроса.

Группировка по выборке — Основы SQL

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

Сначала выберем уникальных пользователей в таблице course_members :

SELECT DISTINCT user_id FROM course_members ORDER BY user_id;

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

SELECT COUNT(*) FROM course_members WHERE user_id = 2; SELECT COUNT(*) FROM course_members WHERE user_id = 3; -- И так далее для остальных пользователей

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

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

Оператор GROUP BY

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

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

SELECT user_id, COUNT(*) FROM course_members GROUP BY user_id ORDER BY user_id;

Мы могли бы создавать по отдельному запросу для каждого пользователя, но вместо этого использовали конструкцию GROUP BY user_id . В ней мы указали, что нам нужно объединить строки с одинаковыми идентификаторами user_id , вывести идентификатор и количество строк в каждой группе COUNT(*) .

Для удобства мы отсортировали данные по идентификатору, но это необязательно:

Псевдонимы для столбцов

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

SELECT user_id AS user, COUNT(*) as courses FROM course_members GROUP BY user_id ORDER BY user_id;

Чтобы присвоить столбцу псевдоним, нужно после его определения записать AS и указать желаемое имя. Оно должно начинаться с буквы и не должно содержать пробелов.

Как работает GROUP BY

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

SELECT user_id AS user, COUNT(*) as courses, created_at FROM course_members GROUP BY user_id ORDER BY user_id;

Запрос завершится с ошибкой:

Query Error: error: column "course_members.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Ошибка в том, что СУБД не понимает, что делать со столбцом created_at — либо он должен быть в конструкции GROUP BY , либо к нему надо применить агрегатную функцию.

Чтобы лучше понять работу GROUP BY , разберемся, почему запрос выше не работает.

Дело в том, что группировка обращается к записям в таблице. Она создает из них независимые группы записей, по которым проводится анализ.

При работе с группой мы можем выбрать один из двух вариантов:

• Либо вывести поле, по которому проводим группировку. Его значение будет одинаковым для группы
• Либо применить к полю какую-либо агрегатную функцию — например, COUNT() , MAX() , MIN() или AVG() . В этом случае СУБД будет знать, как обработать несколько разных значений.

Вернемся к примеру выше. В своем запросе мы не группируем данные по столбцу created_at — это не то, что нам нужно. Если бы мы это сделали, пришлось бы работать с группами, в которых совпадают идентификатор пользователя и дата создания.

Кроме того, мы не указали никакой агрегатной функции для этого столбца. СУБД не понимает, что именно нужно сделать с группой разных дат создания. Нужно посчитать среднее? Или взять максимальную дату?

Давайте исправим запрос. Выведем максимальное значение поля created_at для группы. Это будет дата последней регистрации пользователя на курс:

SELECT user_id AS user, COUNT(*) as courses, MAX(created_at) as last_reg FROM course_members GROUP BY user_id ORDER BY user_id;

Предложение GROUP BY

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

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

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

Консоль

Выполнить

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

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

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

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

Консоль

Выполнить

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

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

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

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

Описание

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

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

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

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

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

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

Выше – описание таблицы, которую предстоит использовать далее для изучения принципов функционирования 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 в программном коде. Сам оператор составляется легко: достаточно указать соответствующее ключевое слово и прописать поля, по которым осуществляется группировка:

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

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

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

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

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

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

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

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

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

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

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

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

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

Сумма

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

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

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

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

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

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

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

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

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

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

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

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

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

Здесь можно увидеть больше примеров использования изученного оператора. Лучше SQL и его запросы помогут изучить дистанционные компьютерные курсы.