Sql group by это что

Руководство по предложению GROUP BY в SQL

Перевод статьи « SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained».

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

Мы рассмотрим следующие темы:

Подготовительный этап с описанием программного обеспечения и созданием базы данных мы рассматривали в двух предыдущих статьях:

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

Создание таблицы с данными

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

Давайте создадим нашу таблицу и внесем в нее кое-какие данные о продажах:

У нас были продажи сегодня, вчера и позавчера.

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

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

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

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

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

Мы можем группировать данные в любое количество групп и подгрупп.

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

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

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

Предложения GROUP BY часто используются в случаях, когда можно использовать обороты по чему-то или в каждом(ой):

Написание предложений GROUP BY

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

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

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

А как насчет остальных столбцов таблицы?

мы получим вот такую ошибку:

Проблема в том, что мы взяли восемь строк и попытались втиснуть их в три.

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

Что делать с оставшимися пятью строками данных? Какие данные из восьми строк должны быть отображены в трех строках?

На эти вопросы нет четкого и ясного ответа.

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

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

Агрегатные функции (COUNT, SUM, AVG)

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

Для начала давайте найдем количество продаж по каждой локации.

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

Чтобы получить нужный результат, нам нужно применить агрегатную функцию COUNT() — так мы вычислим количество строк в каждой группе.

( COUNT() также работает с выражениями, но при этом имеет несколько другое поведение).

Вот как база данный выполняет наш запрос:

Локация 1st Street имеет две продажи, HQ — четыре, а Downtown — две.

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

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

Для этого мы будем использовать функцию SUM() :

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

Функция AVG() позволяет находить среднее значение (AVG от Average — среднее). Давайте найдем среднюю сумму выручки по локациям. Для этого просто заменим функцию SUM() на функцию AVG() :

Работа с несколькими группами

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

Для этого нам нужно добавить к нашему предложению GROUP BY второе группирующее условие:

(Для облегчения чтения я добавил в запрос также предложения ORDER BY ).

В результатах нашего нового группирования мы видим уникальные комбинации локаций и продуктов:

Ну хорошо, у нас есть наши группы, а что мы будем делать с данными остальных столбцов?

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

(Задание «со звездочкой»: найдите общую выручку (сумму) за каждый продукт в каждой локации).

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

Давайте попытаемся найти общее число продаж в день.

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

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

Но на самом деле наши данные сгруппированы! Проблема в том, что sold_at каждой строки является уникальным значением, поэтому каждая строка образует собственную группу!

GROUP BY работает правильно, однако это не тот результат, который нам нужен.

Виной всему уникальная информация временной метки (часы/минуты/секунды).

Все эти временные метки разные, поэтому записи разбрасываются по разным группам.

Нам нужно конвертировать значения даты и времени для каждой записи в просто дату:

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

Для этого мы сведем значение временной метки sold_at к дате:

В запросе SELECT мы возвращаем то же выражение и даем ему псевдоним для более красивого вывода.

Вот результат числа продаж за день, который мы хотели увидеть:

Фильтрация групп при помощи HAVING

Давайте теперь разберем, как можно фильтровать наши сгруппированные строки. Например, попробуем найти дни, в которые у нас было больше одной продажи.

Имея группы, мы можем попробовать отфильтровать наши группы по числу строк…

К сожалению, это не сработало и мы получили ошибку:

Это предложение HAVING отфильтровывает все строки, если число строк в группе не больше одной. Вот результат:

Чисто для полноты картины вот вам порядок выполнения всех предложений SQL:

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

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

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

Мы могли бы найти общую выручку по всем локациям, просто выбрав сумму по всей таблице:

Еще один полезный сценарий — запросить первое или последнее что-нибудь.

Например, дату самой первой продажи.

Чтобы ее найти, мы моем применить функцию MIN() :

(Для поиска даты последней продажи нужно всего лишь заменить MIN() на MAX() ).

Использование MIN / MAX

Хотя эти простые запросы могут быть полезны сами по себе, они часто являются составляющими более длинных запросов.

Например, давайте попробуем найти общую выручку в последний день, когда у нас вообще были продажи.

Мы можем написать запрос так:

Этот запрос сработает, но мы захардкодили дату 2020-09-01. А ведь дата последней продажи будет постоянно меняться. Нам нужно динамическое решение.

Для этого нам нужно скомбинировать этот запрос с функцией MAX() в подзапросе:

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

Скрытое группирование

Я назвал это скрытым группированием, потому что если мы попытаемся вот так выбрать агрегированное значение с не-агрегированным столбцом —

мы получим уже знакомую ошибку:

GROUP BY — это инструмент

Как и многие другие вещи в сфере разработки, GROUP BY — это инструмент.

Источник

SELECT — GROUP BY (Transact-SQL)

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

Синтаксис

Синтаксические обозначения в Transact-SQL (Transact-SQL)

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

column-expression

Указывает на столбец или на нестатистическое вычисление в столбце. Этот столбец может принадлежать таблице, производной таблице или представлению. Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT.

Допустимые выражения см. в разделе expression.

Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT. Каждый столбец таблицы или представления в любом нестатистическом выражении в списке должен быть включен в список GROUP BY.

Следующие инструкции являются допустимыми.

Следующие инструкции не являются допустимыми.

Выражение столбца не может содержать:

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

Например, этот запрос создает таблицу Sales со столбцами Country, Region и Sales. Он вставляет четыре строки, и две строки имеют совпадающие значения для столбцов Country и Region.

В таблице Sales содержатся указанные далее строки.

Этот запрос группирует значения столбцов Country и Region и возвращает общую сумму по каждому сочетанию значений.

Результат запроса содержит 3 строки, так как существует 3 сочетания значений для Country и Region. Значение TotalSales для Canada и British Columbia является суммой двух строк.

GROUP BY ROLLUP

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

Порядок столбцов влияет на выходные данные ROLLUP и может отразиться на количестве строк в результирующем наборе.

Например, GROUP BY ROLLUP (col1, col2, col3, col4) создает группы для каждой комбинации выражений столбцов в следующих списках.

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

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

GROUP BY CUBE ( )

GROUP BY CUBE создает группы для всех возможных сочетаний столбцов. Для GROUP BY CUBE (a, b) результатами являются группы для уникальных значений (a, b) (NULL, b), (a, NULL) и (NULL, NULL).

Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY CUBE по столбцам Country и Region.

Результатом запроса являются группы для уникальных значений (Country, Region), (NULL, Region), (Country, NULL) и (NULL, NULL). Результат выглядит следующим образом:

GROUP BY GROUPING SETS ( )

Параметр GROUPING SETS позволяет объединять несколько предложений GROUP BY в одно предложение GROUP BY. Результаты эквивалентны тем, что формируются с применением конструкции UNION ALL к указанным группам.

Например, GROUP BY ROLLUP (Country, Region) и GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) возвращают одинаковые результаты.

Если параметр GROUPING SETS имеет два или более элементов, результатом будет объединение элементов. Этот пример возвращает объединение результатов ROLLUP и CUBE для Country и Region.

Результаты будут такими же, так как этот запрос возвращает объединение двух инструкций GROUP BY.

SQL не консолидирует повторяющиеся группы, созданные для списка GROUPING SETS. Например, в GROUP BY ( (), CUBE (Country, Region) ) оба элемента возвращают строку для общего итога, и в списке результатов будут указаны обе строки.

GROUP BY ()

Указывает пустую группу, что приводит к созданию общего итога. Он полезен в качестве одного из элементов GROUPING SET. Например, эта инструкция выводит общий объем продаж для каждой страны, а затем — общий итог по всем странам.

Область применения: SQL Server и база данных SQL Azure

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

Указывает включить все группы в результаты независимо от того, соответствуют ли они условиям поиска в предложении WHERE. Группы, которые не соответствуют условиям поиска, имеют значение NULL для статистического вычисления.

Область применения: SQL Server и база данных SQL Azure

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

WITH (DISTRIBUTED_AGG)

Область применения: Azure Synapse Analytics и Система платформы аналитики (PDW)

Указание запроса DISTRIBUTED_AGG заставляет систему MPP перераспределять таблицу по определенному столбцу до выполнения статистического вычисления. Только один столбец в предложении GROUP BY может иметь указание запроса DISTRIBUTED_AGG. После завершения запроса перераспределенная таблица удаляется. Исходная таблица не изменяется.

Примечание. Указание запроса DISTRIBUTED_AGG предоставляется для обеспечения обратной совместимости с более ранними версиями Система платформы аналитики (PDW) и не улучшает производительность большинства запросов. По умолчанию MPP уже перераспределяет данные для улучшения производительности для статистических вычислений.

Общие замечания

Взаимодействие GROUP BY с инструкцией SELECT

Предложение ORDER BY:

Ограничения

Область применения: SQL Server (начиная с версии 2008) и Azure Synapse Analytics

Максимальная емкость

Для предложения GROUP BY, использующего ROLLUP, CUBE или GROUPING SETS, используется максимум 32 выражения. Максимальное количество групп — 4096 (2 12 ). Следующие примеры завершаются ошибкой, поскольку предложение GROUP BY имеет больше 4096 групп.

В следующем примере формируется 4097 (2 12 + 1) группирующих наборов, поэтому пример завершится ошибкой.

В этом примере используется синтаксис обратной совместимости. В примере создается 8192 (2 13 ) группирующих наборов, поэтому он завершится ошибкой.

Для предложений GROUP BY с поддержкой обратной совместимости и не содержащих операторов CUBE или ROLLUP количество элементов GROUP BY ограничивается размером столбцов GROUP BY, статистически обрабатываемых столбцов и статистических значений, включенных в запрос. Это объясняется ограничением размера промежуточной рабочей таблицы (8060 байт), необходимой для хранения промежуточных результатов запроса. При указании CUBE или ROLLUP максимально разрешенное количество выражений группирования равно 12.

Поддержка функций предложения GROUP BY, совместимых с ISO и ANSI SQL-2006

Предложение GROUP BY поддерживает все возможности предложения GROUP BY, включенные в стандарт SQL-2006, со следующими синтаксическими исключениями.

GROUP BY [ALL/DISTINCT] используется только в простом предложении GROUP BY, которое содержит выражения столбцов. Это предложение не может использоваться с конструкциями GROUPING SETS, ROLLUP, CUBE, WITH CUBE или WITH ROLLUP. Ключевое слово ALL применяется по умолчанию и задано неявно. Оно допускается только в синтаксисе обратной совместимости.

Сравнение поддерживаемых функций предложения GROUP BY

В следующей таблице описаны поддерживаемые возможности предложения GROUP BY с учетом версии SQL и уровня совместимости базы данных.

Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);

Выдается следующее сообщение об ошибке: «Применение неверного синтаксиса недалеко от ключевого слова ‘cube’|’rollup'».

Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);GROUPING SETSНе поддерживаетсяПоддерживаетсяПоддерживаетсяCUBEНе поддерживаетсяПоддерживаетсяНе поддерживаетсяROLLUPНе поддерживаетсяПоддерживаетсяНе поддерживаетсяОбщий итог, такой как GROUP BY ()Не поддерживаетсяПоддерживаетсяПоддерживаетсяФункция GROUPING_IDНе поддерживаетсяПоддерживаетсяПоддерживаетсяФункция GROUPINGПоддерживаетсяПоддерживаетсяПоддерживаетсяWITH CUBEПоддерживаетсяПоддерживаетсяПоддерживаетсяWITH ROLLUPПоддерживаетсяПоддерживаетсяПоддерживаетсяУдаление «повторяющегося» группирования с помощью конструкции WITH CUBE или WITH ROLLUPПоддерживаетсяПоддерживаетсяПоддерживается

Примеры

A. Использование простого предложения GROUP BY

Б. Использование предложения GROUP BY с несколькими таблицами

В. Использование предложения GROUP BY в выражениях

Г. Использование предложения GROUP BY с предложением HAVING

Примеры: Azure Synapse Analytics и Parallel Data Warehouse

Д. Базовое использование предложения GROUP BY

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

Е. Базовое использование указания DISTRIBUTED_AGG

В этом примере показано указание запроса DISTRIBUTED_AGG для принудительного перемещения в таблице по столбцу CustomerKey перед выполнением статистического вычисления.

Ж. Варианты синтаксиса для GROUP BY

Если в списке Select статистические вычисления, каждый столбец в списке Select должен быть включен в список GROUP BY. Вычисляемые столбцы в списке Select можно указать в списке GROUP BY (делать это необязательно). Ниже приведены примеры синтаксически правильных инструкций SELECT.

З. Использование GROUP BY с несколькими выражениями GROUP BY

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

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL оператор GROUP BY

В этом учебном материале вы узнаете, как использовать SQL оператор GROUP BY с синтаксисом и примерами.

Описание

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

Синтаксис

Синтаксис оператора GROUP BY в SQL:

Параметры или аргументы

Давайте посмотрим, как использовать GROUP BY с функцией SUM в SQL.
В этом примере у нас есть таблица employees со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 2 записи. Вот результаты, которые вы получите:

Давайте посмотрим, как использовать предложение GROUP BY с функцией COUNT в SQL.

В этом примере у нас есть таблица products со следующими данными:

Введите следующий SQL оператор:

Будет выбрано 3 записи. Вот результаты, которые вы должны получить:

Давайте теперь посмотрим, как использовать предложение GROUP BY с функцией MIN в SQL.

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

Источник

SQL Group By: Полное руководство

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

Когда вы работаете с агрегатными функциями в SQL, часто бывает необходимо сгруппировать строки по общим значениям столбцов.

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

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

Обновление агрегатных функций

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

В SQL есть несколько встроенных функций, которые позволяют получить эту информацию. Они называются агрегатными функциями.

Например, предположим, что вы хотите узнать, сколько сотрудников являются торговыми партнёрами, вы можете использовать функцию COUNT. Функция COUNT подсчитывает и возвращает количество строк, соответствующих определённому набору критериев. Другие агрегатные функции включают SUM, AVG, MIN и MAX.

SQL Group By

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

Синтаксис оператора GROUP BY:

SELECT COUNT(column1_name), column2_name
FROM table1_name
GROUP BY column2_name;

Мы использовали агрегатную функцию в нашем запросе и указали другой столбец.

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

Предложение GROUP BY используется только в операторах SQL SELECT.

Давайте посмотрим на пример предложения GROUP BY в SQL.

Пример SQL Group By

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

Мы могли бы использовать следующий запрос для получения этой информации:

SELECT title, COUNT(title)
FROM employees
GROUP BY title;

Запрос возвращает несколько записей:

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

Когда следует использовать GROUP BY в SQL?

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

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

Наш запрос группирует результат и возвращает:

Если вы хотите узнать, сколько клиентов входит в каждый из ваших планов лояльности, вам нужно будет использовать оператор GROUP BY. Вот пример запроса, который может получить список планов лояльности и количество клиентов по каждому плану:

SELECT loyalty_plan, COUNT(loyalty_plan)
FROM customers
GROUP BY loyalty_plan;

Наш запрос собирает данные. Затем наш запрос возвращает:

SQL Group By по нескольким столбцам

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

SELECT branch, title, COUNT(title)
FROM employees
GROUP BY branch, title;

Наш набор результатов запроса показывает:

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

Вывод

Предложение SQL GROUP BY необходимо в любом операторе, где используется агрегатная функция и запрашивается дополнительная таблица. Вы должны сгруппировать по столбцу, не упомянутому в агрегатной функции.

Вы ищете вызов? Напишите выписку, в которой узнайте, сколько сотрудников работает в каждом филиале.

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

Источник