sql зачем group by

SQL/GROUP BY

GROUP BY (группировка по) — оператор языка SQL служащий для группировки (сливания воедино) выбранного набора строк для получения набора сводных строк по значениям одного или нескольких столбцов, или выражений в выходных данных (таблице результата). Т.о. возвращается одна строка для каждой группы.

Данный оператор, по своей работе, очень похож на DISTINCT т.к. тоже сортирует данные по уникальности/неповторимости. Т.е. считаются все значение для какого-либо уникального значения столбца и сливаются (группируются) в один результат, причём группировка производится по значениям указанных столбцов.

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

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

Предложение GROUP BY имеет 2 синтаксиса: совместимый и несовместимый с ISO. В каждой отдельной инструкции SELECT может использоваться только 1 стиль синтаксиса. Во всех новых разработках используйте совместимый с ISO синтаксис. Синтаксис, несовместимый с ISO, служит для обеспечения обратной совместимости.

Общее предложение GROUP BY включает конструкции GROUPING SETS, CUBE, ROLLUP, WITH CUBE и WITH ROLLUP (с общим итогом).

Простое предложение GROUP BY не включает конструкции GROUPING SETS, CUBE, ROLLUP, WITH CUBE и WITH ROLLUP. Предложение GROUP BY (), предназначенное для определения общего итога, рассматривается как простое предложение GROUP BY.

Подробнее

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

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

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

Источник

Transact-SQL группировка данных GROUP BY

Мы с Вами рассмотрели много материала по SQL, в частности Transact-SQL, но мы не затрагивали такую, на самом деле простую тему как группировка данных GROUP BY. Поэтому сегодня мы научимся использовать оператор group by для группировки данных.

И для вступления небольшая теория.

Что такое оператор GROUP BY

GROUP BY – это оператор (или конструкция, кому как удобней) SQL для группировки данных по полю, при использовании в запросе агрегатных функций, таких как sum, max, min, count и других.

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

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

Примечание! Все примеры будем писать в Management Studio SQL сервера 2008.

Примеры использования оператора GROUP BY

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

Создаем таблицу

Я ее заполнил следующими данными:

Группируем данные с помощью запроса group by

И в самом начале давайте разберем синтаксис group by, т.е. где писать данную конструкцию:

Синтаксис:

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

Where Условия отбора

Group by поля группировки

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

Order by поля сортировки

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

А если нужно просуммировать другого сотрудника, то мы просто меняем условие. Согласитесь, если таких сотрудников много, зачем суммировать каждого, да и это как-то не наглядно, поэтому нам на помощь приходит оператор group by. Пишем запрос:

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

Примечание! Сразу отмечу то, что, сколько полей мы пишем в запросе (т.е. поля группировки), помимо агрегатных функций, столько же полей мы пишем в конструкции group by. В нашем примере мы выводим одно поле, поэтому в group by мы указали только одно поле (name), если бы мы выводили несколько полей, то их все пришлось бы указывать в конструкции group by (в последующих примерах Вы это увидите).

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

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

Теперь у нас все отображается, т.е. сколько денег поступило сотруднику, сколько раз, а также из какого источника.

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

Вот теперь все достаточно наглядно и не так уж сложно, даже для начинающих.

Также давайте затронем условия по итоговым результатам агрегатных функций (having). Другими словами, мы добавляем условие не по отбору самих строк, а уже на итоговое значение функций, в нашем случае это sum или count. Например, нам нужно вывести все то же самое, но только тех, у которых «всего денежных средств» больше 200. Для этого добавим условие having:

Теперь у нас вывелись все значения sum(summa), которые больше 200, все просто.

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Надеюсь, после сегодняшнего урока Вам стало понятно, как и зачем использовать конструкцию group by. Удачи! А SQL мы продолжим изучать в следующих статьях.

Источник

Оператор 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.

Источник

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

Источник