Sql что такое group by

Оператор SQL GROUP BY для группировки в запросах

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

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

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

В примерах работаем с базой данных библиотеки и ее таблицей «Книга в пользовании» (Bookinuse). Отметим, что оператор GROUP BY ведёт себя несколько по-разному в MySQL и в MS SQL Server. Эти различия будут показаны на примерах.

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

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

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

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

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

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

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

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

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

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

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

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

По-другому ведёт себя оператор GROUP BY в MS SQL Server и в случае этого запроса.

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

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

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

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

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

Примеры запросов к базе данных «Библиотека» есть также в уроках по оператору IN, предикату EXISTS и функциям CONCAT, COALESCE.

Источник

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

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

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

Источник

Руководство по предложению 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 — это инструмент.

Источник

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 со следующими данными:

Источник