Sql пример group by: Команда GROUP BY — группировка при выборке из базы данных

Содержание

Команда GROUP BY — группировка при выборке из базы данных

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

К сгруппированным результатам можно применять любые функции
(смотрите примеры).

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

Синтаксис

SELECT * FROM имя_таблицы WHERE условие GROUP BY поле_для_группировки

Примеры

Все примеры будут по этой таблице workers, если не сказано иное:

id
айди
name
имя
age
возраст
salary
зарплата
1 Дима 23 100
2 Петя 23 200
3 Вася 23 300
4 Коля 24 1000
5 Иван 24 2000
6 Кирилл 25 1000

Пример

В данном примере записи группируются по возрасту (будет 3 группы — 23 года, 24 года и 25 лет).
Затем для каждой группы применяется функция SUM,
которая суммирует зарплаты внутри данной группы.

В результате для каждой из групп (23 года, 24 года и 25 лет) будет подсчитана суммарная
зарплата внутри этой группы:

SELECT age, SUM(salary) as sum FROM workers GROUP BY age

SQL запрос выберет следующие строки:

age
возраст
sum
сумма
23 600
24 3000
25 1000

Пример

В данном примере мы используем дополнительное условие WHERE,
чтобы взять не все записи из таблицы:

SELECT age, SUM(salary) as sum FROM workers WHERE id>=2 GROUP BY age

SQL запрос выберет следующие строки:

age
возраст
sum
сумма
23 500
24 3000
25 1000

Пример

В данном примере записи группируются по возрасту (будет 3 группы — 23 года, 24 года и 25 лет).
Затем для каждой группы применяется функция MAX,
которая находит максимальную зарплату внутри данной группы:

SELECT age, MAX(salary) as max FROM workers GROUP BY age

SQL запрос выберет следующие строки:

age
возраст
max
максимальная зарплата
23 300
24 2000
25 1000

Пример

А теперь с помощью функции MIN
найдется минимальная зарплата внутри данной группы:

SELECT age, MIN(salary) as min FROM workers GROUP BY age

SQL запрос выберет следующие строки:

age
возраст
min
минимальная зарплата
23 100
24 1000
25 1000

Пример

А теперь с помощью функции COUNT
найдется количество записей в группе:

SELECT age, COUNT(*) as count FROM workers GROUP BY age

SQL запрос выберет следующие строки:

age
возраст
count
количество
23 3
24 2
25 1

SQL GROUP BY — группировка в запросах


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


Часто оператор SQL GROUP BY применяется вместе с агрегатными функциями (COUNT, SUM, AVG, MAX, MIN).
В этих случаях агрегатные функции служат для вычисления соответствующего агрегатного значения ко всему
набору строк, для которых некоторый столбец — общий.


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


SELECT ИМЕНА_СТОЛБЦОВ
FROM ИМЯ_ТАБЛИЦЫ
[WHERE УСЛОВИЕ]
GROUP BY ИМЕНА_СТОЛБЦОВ


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


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


Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными —
в файле по этой ссылке
.


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



















Author Title Pubyear Inv_No Customer_ID
Толстой Война и мир 2005 28 65
Чехов Вишневый сад 2000 17 31
Чехов Избранные рассказы 2011 19 120
Чехов Вишневый сад 1991 5 65
Ильф и Петров Двенадцать стульев 1985 3 31
Маяковский Поэмы 1983 2 120
Пастернак Доктор Живаго 2006 69 120
Толстой Воскресенье 2006 77 47
Толстой Анна Каренина 1989 7 205
Пушкин Капитанская дочка 2004 25 47
Гоголь Пьесы 2007 81 47
Чехов Избранные рассказы 1987 4 205
Пушкин Сочинения, т.1 1984 6 47
Пастернак Избранное 2000 137 18
Пушкин Сочинения, т.2 1984 8 205
NULL Наука и жизнь 9 2018 2019 127 18
Чехов Ранние рассказы 2001 171 31

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


SELECT Author
FROM BOOKINUSE
GROUP BY Author

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










Author
NULL
Гоголь
Ильф и Петров
Маяковский
Пастернак
Пушкин
Толстой
Чехов

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

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

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


SELECT Author, Title
FROM Bookinuse
GROUP BY Author

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










Author Title
NULL Наука и жизнь 9 2018
Гоголь Пьесы
Ильф и Петров Двенадцать стульев
Маяковский Поэмы
Пастернак Доктор Живаго
Пушкин Капитанская дочка
Толстой Война и мир
Чехов Вишнёвый сад

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

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

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

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

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


SELECT Author, Title, Customer_ID, Inv_no
FROM Bookinuse
GROUP BY Author, Customer_ID, Inv_no

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


















Author Title Customer_ID Inv_no
Гоголь Пьесы 47 81
Ильф и Петров Двенадцать стульев 31 3
Маяковский Поэмы 120 2
Пастернак Избранное 18 137
Пастернак Доктор Живаго 120 69
Пушкин Капитанская дочка 47 25
Пушкин Сочинения, т.1 47 6
Пушкин Сочинения, т.2 205 8
Толстой Воскресенье 47 77
Толстой Война и мир 65 28
Толстой Анна Каренина 205 7
Чехов Вишневый сад 31 19
Чехов Ранние рассказы 31 171
Чехов Вишневый сад 65 5
Чехов Избранные рассказы 120 19
Чехов Избранные рассказы 205 4

Как видим, в результирующей таблице присутствуют все книги всех авторов, причём авторы
следуют по порядку, как если бы мы применили оператор ORDER BY. Кроме того, видно, что записи сгруппированы
и по второму указанному столбцу — Customer_ID. Так, у автора Пушкина сначала перечисляются книги, выданные
пользователю с Customer_ID 47, а затем — 205. У автора Чехова сначала перечисляются книги, выданные
пользователю с Customer_ID 31, а затем — с другими номерами. Третий столбец, по которому происходит группировка — Inv_no —
добавлен только для того, чтобы в результирующей таблице выводились все строки, соответствующие значениям
ранее перечисленных столбцов для группировки, а не только уникальные.

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


Агрегатные функции COUNT, SUM, AVG, MAX, MIN служат для вычисления соответствующего агрегатного значения ко всему
набору строк, для которых некоторый столбец — общий.

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


SELECT Author, COUNT(*) AS InUse
FROM Bookinuse
GROUP BY Author

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










Author InUse
NULL 1
Гоголь 1
Ильф и Петров 1
Маяковский 1
Пастернак 2
Пушкин 3
Толстой 3
Чехов 5

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


SELECT Customer_ID, COUNT(*) AS InUse
FROM Bookinuse
GROUP BY Customer_ID

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








User_ID InUse
18 1
31 3
47 4
65 2
120 3
205 3


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

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

Поделиться с друзьями

Реляционные базы данных и язык SQL

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

MS SQL Server и T-SQL

Операторы GROUP BY и HAVING

Последнее обновление: 19.07.2017

Для группировки данных в 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,
которая вычисляет количество строк в группе.

Стоит учитывать, что любой столбец, который используется в выражении 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

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

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

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

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


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

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

Команда SELECT Раздел GROUP BY — Группировка записей по полям

Раздел GROUP BY

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

Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин “сгруппированная таблица”.

Если утверждение SELECT содержит предложение GROUP BY(SELECT GROUP BY), список выбора может содержать только следующие типы выражений:

  • Константы.
  • Агрегатные функции.
  • Функции USER, UID, и SYSDATE.
  • Выражения, соответствующие перечисленным в предложении GROUP BY.
  • Выражения, включающие вышеперечисленные выражения.

Пример 1. Вычислить общий объем покупок для каждого товара:

SELECT stock,  SUM(quant)   FROM ordsale GROUP BY stock;

Фраза GROUP BY не предполагает упорядочивания строк. Для упорядочивания результата этого примера по кодам товаров, следует поместить фразу  ORDER BY stock следом за фразой GROUP BY.

Пример 2. Можно использовать группировки данных GROUP BY совместно с условием. Например, выбрать для каждого покупаемого товара его код и общий объем покупок, за исключением покупок покупателя с кодом 23:

SELECT stock,  SUM(quant)   FROM ordsale WHERE customerno<>23 GROUP BY stock;

Строки, не удовлетворяющие условию WHERE, исключаются перед группированием данных.

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

Допустим, есть задача на вычисление количества какого-либо продукта. Поставщик поставляет нам продукцию по определённой цене. Вычислим общее количество каждого из продуктов. В этом нам поможет фраза GROUP BY. Результатом задачи станет таблица, состоящая из нескольких колонок. Поставки будут группироваться по ПР. Компоновка происходит по группам, которую и инициирует Group By SQL. Необходимо отметить, что данная фраза предполагает применение фразы Select, она же в свою очередь определяет единственное значение для каждого выражения сформированной группы. Бывают три случая для конкретного выражения: оно принимает арифметическое значение, оно становится SQL-функцией, которая будет сводить все значения столбца к сумме или другому заданному значению, также выражение может стать константой. Строки таблицы не обязательно должны быть строго сгруппированы, они могут группироваться по любой комбинации столбцов таблицы. Необходимо учитывать, что упорядочивание запросы по ПР возможно в том случае, если будет сделан соответствующий запрос.

Transact-SQL группировка данных GROUP BY | Info-Comp.ru

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

Многие начинающие программисты, когда сталкиваются с SQL, не знают о такой возможности как группировка данных с помощью оператора GROUP BY, хотя эта возможность требуется достаточно часто на практике, в связи с этим наш сегодняшний урок, как обычно с примерами, посвящен именно тому, чтобы Вам было проще и легче научиться использовать данный оператор, так как Вы с этим обязательно столкнетесь. Если Вам интересна тема SQL, то мы, как я уже сказал ранее, не раз затрагивали ее, например, в статьях Язык SQL – объединение JOIN или Объединение Union и union all , поэтому можете ознакомиться и с этим материалом.

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

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

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

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

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

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

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

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

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

   
   CREATE TABLE [dbo].[test_table](
        [id] [int] NULL,
        [name] [varchar](50) NULL,
        [summa] [money] NULL,
        [priz] [int] NULL
   ) ON [PRIMARY]
   GO


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

Где,

  • Id –идентификатор записи;
  • Name – фамилия сотрудника;
  • Summa- денежные средства;
  • Priz – признак денежных средств (допустим 1- Оклад; 2-Премия).

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

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

Синтаксис:

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

From источник

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

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

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


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

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

   
   SELECT SUM(summa)as summa 
   FROM test_table 
   WHERE name='Иванов'


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

   
   SELECT SUM(summa)as summa, name 
   FROM test_table 
   GROUP BY name


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

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

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

   
   SELECT SUM(summa)as [Всего денежных средств], 
          COUNT(*) as [Количество поступлений], 
          Name [Сотрудник] 
   FROM test_table 
   GROUP BY name


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

   
   SELECT  SUM(summa)as [Всего денежных средств], 
           COUNT(*) as [Количество поступлений], 
           Name [Сотрудник] ,
           Priz [Источник]
    FROM test_table 
    GROUP BY name, priz
    ORDER BY name


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

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

   
   SELECT SUM(summa) AS [Всего денежных средств], 
          COUNT(*) AS [Количество поступлений], 
          Name [Сотрудник],
          CASE WHEN priz = 1 then 'Оклад' 
               WHEN priz = 2 then 'Премия' 
               ELSE 'Без источника' 
          END AS [Источник]
     FROM test_table 
     GROUP BY name, priz
     ORDER BY name


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

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

    
   SELECT SUM(summa)as [Всего денежных средств], 
           COUNT(*) as [Количество поступлений], 
           Name [Сотрудник],
           CASE WHEN priz = 1 then 'Оклад' 
                WHEN priz = 2 then 'Премия' 
                ELSE 'Без источника' 
           END AS [Источник]
     FROM test_table 
     GROUP BY name, priz --группируем
     HAVING SUM(summa) > 200 --отбираем
     ORDER BY name -- сортируем


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

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

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

Нравится4Не нравится

Логическая обработка запросов: GROUP BY и HAVING | Windows IT Pro/RE

Эта статья продолжает серию публикаций о логической обработке запросов, которая описывает логическую или концептуальную интерпретацию запросов. В первой части (см. Windows IT Pro/RE № 3 за 2016 год) был дан обзор темы, приведена тестовая база данных TSQLV4 (http://tsql.solidq.com/SampleDatabases/TSQLV4.zip) и показаны примеры запросов, простой и сложный. Те же примеры базы данных и запросов используются и в этой статье. В следующих частях были рассмотрены особенности логической обработки первого основного предложения запросов — FROM. В предыдущей статье (опубликованной в Windows IT Pro/RE № 1 за 2017 год) речь шла о втором основном предложении запросов — WHERE. В данной статье мы рассмотрим, как выполняется логическая обработка третьего и четвертого основных предложений запросов — GROUP BY и HAVING соответственно.

Блок-схема логической обработки запросов, содержащих предложения GROUP BY и HAVING

Необязательное предложение GROUP BY обрабатывается на третьем шаге логической обработки запросов, а необязательное предложение HAVING — на четвертом. Третий шаг, на котором обрабатывается предложение GROUP BY, работает с виртуальной таблицей, возвращаемой на втором шаге в результате обработки предложения WHERE. На третьем шаге строки из входной таблицы упорядочиваются в группы на основе набора группирования, определенного в предложении GROUP BY. Затем на четвертом шаге группы фильтруются на основе предиката, указанного в предложении HAVING. Возвращаются только те группы, для которых предикаты принимают значение true, а группы, для которых значением предиката является false или unknown, отбрасываются. На рисунке 1 показана блок-схема с логической обработкой предложений FROM, WHERE, GROUP BY и HAVING.

 

Рисунок 1. Блок-схема с логической обработкой предложений FROM, WHERE, GROUP BY и HAVING

 

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

На экране 1 показано состояние данных после обработки предложения WHERE (шаг 2) и перед обработкой предложения GROUP BY (шаг 3).

 

Экран 1. Состояние данных после обработки предложения WHERE (шаг 2) и перед обработкой предложения GROUP BY (шаг 3)

 

Этот входной набор состоит из 24 строк (на экране 1 показаны только столбцы C.custid и O.orderid). Набор группирования, определенный в нашем запросе, — (C.custid), и вследствие того, что существует пять неповторяющихся значений C.custid во входном наборе, результат фазы GROUP BY упорядочивает входные строки в пяти группах (см. экран 2).

 

Экран 2. Упорядочение результатов на фазе GROUP BY 

 

Фаза HAVING применяет предикат COUNT (O.orderid)

 

Экран 3. Результат фазы HAVING

 

Приведенный в листинге 2 программный код содержит фрагменты GROUP BY и HAVING нашего сложного тестового запроса.

Фаза WHERE возвращает результат, содержащий 27 строк, он показан на экране 4.

 

Экран 4. Результаты фазы WHERE

 

Данный набор используется как входной в фазе GROUP BY, которая в этом запросе упорядочивает строки по наборам группирования (C.custid, A.custlocation). Шаг организует строки из входного набора в шесть групп, приведенные на экране 5.

 

Экран 5. GROUP BY упорядочивает строки по наборам группирования

 

Затем запрос фильтрует группы на основе предиката HAVING COUNT (DISTINCT O.orderid)

 

Экран 6. Фильтр группы на основе предиката HAVING COUNT

 

Возникает вопрос, почему предикат HAVING определяет число различных идентификаторов заказа. Дело в том, что запрос объединяет таблицу Sales.Orders с таблицей Sales.OrderDetails, что приводит к строке на строку заказа, а не строке на заказ. Предполагается, что фильтр HAVING в нашем запросе возвращает только группы, имеющие число заказов, а не число строк заказов, меньшее или равное трем.

Удаление подробностей

Фаза GROUP BY упорядочивает строки в группах, и с этого этапа логической обработки запроса выражения не имеют прямого доступа к подробному состоянию данных. Это относится ко всем последующим фазам, на которых выполняются предложения HAVING (шаг 4), SELECT (шаг 5) и ORDER BY (шаг 6) запроса. Если в этих предложениях нужно ссылаться на столбец во входных таблицах, сделать это напрямую можно только в том случае, если данный столбец представляет собой часть набора группирования запроса. Чтобы сослаться на столбец, который не является частью набора группирования запроса, этот столбец должен содержаться в групповой агрегатной функции. Например, следующий запрос недействителен, так как в списке SELECT вы ссылаетесь на столбец orderid, а этот столбец не является частью набора группирования и не содержится в групповой агрегатной функции:

SELECT custid, orderid
FROM Sales.Orders
GROUP BY custid;

Выдается сообщение об ошибке, приведенное на экране 7.

 

Экран 7. Сообщение об ошибке при ссылке на столбец

 

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

SELECT custid, MAX (orderid) AS lastorder
FROM Sales.Orders
GROUP BY custid;

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

Ограничение первичного ключа, определенное для столбца custid в таблице Sales.Customers, обеспечивает уникальность значений custid. Это означает, что все остальные столбцы в таблице функционально зависимы от столбца custid. Поэтому в результате соединения Sales.Customers и Sales.Orders все строки с одним значением custid всегда имеют одинаковые значения во всех остальных столбцах. Поэтому в приведенном запросе SQL может теоретически заключить, что, поскольку C.custid является частью набора группирования, всегда будет существовать только одно значение C.companyname, связанное с каждой группой. Однако, как уже отмечалось, SQL (то же относится и к T-SQL) не поддерживает такого рода вмешательство, и при попытке выполнить этот запрос вы получаете сообщение об ошибке, приведенное на экране 8.

 

Экран 8. Сообщение об ошибке при выполнении запроса из листинга 3

 

Существует два обходных приема, поддерживаемых SQL. Первый — добавить столбец C.companyname к набору группирования, например как в листинге 4 (назовем этот запрос Query 1).

Второй способ — применить искусственную агрегатную функцию, такую как MIN или MAX, к столбцу C.companyname, например как в листинге 5 (назовем этот запрос Query 2).

Чтобы выяснить, какое решение более эффективно, ознакомьтесь с планами запросов на рисунке 2.

 

Рисунок 2. Планы запросов Query 1 и Query 2

 

Обратите внимание, что в плане для запроса Query 1 оптимизатор передает работу по группированию и агрегированию сразу после просмотра данных из таблицы Orders, перед соединением с таблицей Customers. Это соединение уравнивающее, поэтому группирование по O.custid — то же самое, что группирование по C.custid, а затем возможна передача при сохранении значения первоначального запроса. Таким образом, соединение происходит с меньшими затратами по сравнению с группированием и агрегированием после соединения. На мой взгляд, запрос Query 1 более ясный и естественный, так что я предпочитаю этот вариант.

Агрегирование данных без удаления подробностей

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

SELECT custid, SUM (val) AS custtotal
FROM Sales.OrderValues
GROUP BY custid;

В исходном представлении имеется строка для заказа, но, поскольку запрос группирует данные по custid, выходные данные содержат одну строку для заказчика с общими значениями заказа клиента (см. экран 9).

 

Экран 9. Результат агрегирования данных с удалением подробностей

 

Но что делать, если вы не хотите избавляться от подробностей? Например, если нужно получить детальную информацию о заказе и, кроме того, вычислить процент значения текущего заказа в общей сумме заказов клиента. В процессе вычисления процента необходимо разделить подробное значение заказа на агрегат всех значений заказа для того же клиента. Это можно сделать, подготовив запрос, который группирует данные по custid и вычисляет общее значение для клиента, а затем определить табличное выражение на основе этого запроса и задействовать внешний запрос для соединения табличного выражения с представлением Sales.OrderValues, чтобы сопоставить подробности с агрегатами. Однако в результате увеличивается сложность программного кода. Альтернативный способ: вычислить агрегат с использованием оконной функции вместо групповой (см. листинг 6).

В отличие от группирования, при котором удаляются подробности, оконная функция подробностей не удаляет. Агрегат вычисляется для окна строк, полученного из набора результатов базового запроса, и определяется предложением OVER функции. Результат базового запроса определяется только тогда, когда логическая обработка запроса достигает фазы SELECT (после обработки фаз FROM, WHERE, GROUP BY и HAVING), поэтому оконные функции разрешены только в предложениях SELECT и ORDER BY запроса. Если указано пустое предложение OVER, функция работает с полным результатом базового запроса, и вы получаете общий итог. Если добавлено предложение разбиения окна, как в приведенном выше запросе, функция работает с ограниченной секцией; в нашем примере строки ограничены лишь теми, в которых значение custid такое же, как в текущей строке. Другими словами, наша оконная функция вычисляет итог клиента. Оконная функция возвращает результат, не отбрасывая детали, поэтому вы можете объединить элементы подробностей из строки с результатом функции агрегирования окна. Наш запрос делит значение текущего заказа на итог клиента, чтобы вычислить процент: val/SUM (val) OVER (PARTITION BY custid). Этот запрос выдает выходные данные, показанные на экране 10.

 

Экран 10. Результаты агрегирования с сохранением подробностей

 

Примечательно, что группирование и работа с окнами не исключают друг друга. Вы можете применять оконные функции к сгруппированным данным. Помните, что группирование выполняется на шаге 3 логической обработки запроса и работа с окнами происходит после группирования, в фазах SELECT (шаг 5) или ORDER BY (шаг 6). Основное правило: следует помнить, что, в отличие от функций агрегирования группы, которые можно применить к элементам подробностей как к входным данным, оконные функции могут применяться только к элементам, обычно разрешенным в предложении SELECT. Например, предположим, нам нужно подготовить сгруппированный запрос, в котором вычисляются итоговые значения заказов клиента и, кроме того, процент итога клиента от общего итога. Можно попробовать применить запрос как в листинге 7.

Ссылка на столбец val в функциях агрегирования группы SUM корректна, но ссылка на столбец val в функции агрегированного значения окна SUM недопустима, точно так же как такая ссылка была бы недопустимой непосредственно в предложении SELECT. В этом случае вы получите сообщение об ошибке, приведенное на экране 11.

 

Экран 11. Сообщение об ошибке при выполнении листинга 7

 

Вы можете попробовать альтернативный вариант, применяя оконную функцию к псевдониму custtotal (см. листинг 8).

Однако эта попытка также заканчивается неудачей, так как подобная ссылка была бы некорректной непосредственно в предложении SELECT из-за обработки выражений на основе наборов в той же логической фазе. Вы получите сообщение об ошибке, как на экране 12.

 

Экран 12. Сообщение об ошибке выполнения листинга 8

 

Вероятно, вам это покажется удивительным, но вы можете применить оконную функцию к функции группирования (см. листинг 9).

На этот раз запрос будет выполнен успешно, и вы получите выходные данные, показанные на экране 13.

 

Экран 13. Результат применения оконной функции к функции группирования

 

Аналогично предположим, что нужно сгруппировать данные из представления Sales.OrderValues по custid и orderdate и наряду с вычислением ежедневного итога требуется вычислять значения с нарастающим итогом от начала операций клиента до текущей даты. Как показано в предыдущем примере, можно объединить группирование и работу с окнами для выполнения этой задачи (см. листинг 10).

Этот запрос выдает выходные данные, представленные на экране 14.

 

Экран 14. Результаты вычисления ежедневного итога и значения с нарастающим итогом

 

HAVING против WHERE

Предложение HAVING служит для целей фильтрации, подобно предложению WHERE, где данные фильтруются на основе предиката. Ключевое различие между ними в том, что WHERE фильтрует строки перед группированием, а предложение HAVING фильтрует целые группы после группирования. Поэтому на уровне группы предложение HAVING определяет, следует ли сохранить группу или удалить ее в зависимости от результата предиката (если true — сохранить, если false или unknown — удалить). Например, нам нужно направить запрос к представлению Sales.OrderValues и фильтровать только заказы, размещенные 1 мая 2016 года и позже. Вы хотите группировать остальные заказы по сотрудникам и фильтровать только группы не более чем с тремя заказами. Для подходящих групп нужно возвратить идентификатор сотрудника и число заказов. Необходимо применить фильтр к столбцу orderdate в предложении WHERE, поскольку он рассматривается как фильтр строк: WHERE orderdate >= ‘20160501’. Поскольку orderdate не является частью набора группирования запроса, к нему нельзя обращаться в предложении HAVING, если только он не содержится в агрегатной функции. Но это не лучший способ фильтрации групп в тех случаях, когда результат агрегации столбца orderdate удовлетворяет некоторому условию; требуется фильтровать строки, представляющие заказы, размещенные в определенный день или позднее. Однако после группирования нужно применить фильтр группы, который сохраняет только группы сотрудников с тремя или меньшим числом заказов. Поскольку этот фильтр предусматривает агрегатный подсчет заказов и должен применяться на групповом уровне, его необходимо указывать в предложении HAVING: HAVING COUNT (*) листинге 11 приводится полный запрос.

Выходные данные этого запроса показаны на экране 15.

 

Экран 15. Результаты запроса с фильтром в HAVING

 

Если нужно применить фильтр на основе столбца, который является частью набора группирования запроса, например empid > 0 в нашем запросе, вы можете выбрать: применить его как фильтр строк в предложении WHERE или как фильтр групп в предложении HAVING. Результат будет один и тот же. Для большинства пользователей в таком случае более естественно применить фильтр в предложении WHERE. Это известно оптимизатору SQL Server, который с большой вероятностью сформирует одинаковый план в обоих случаях.

GROUP BY ALL

T-SQL поддерживает нестандартную функцию GROUP BY ALL. Это довольно интересная функция, но следует помнить, что в официальной документации SQL Server есть примечание, рекомендующее воздержаться от ее использования, так как Microsoft планирует отказаться от нее в будущем. Рассмотрим особенности этой функции, относящиеся к логической обработке запросов, и рекомендуемую альтернативу.

В сущности, GROUP BY ALL сохраняет пустые группы, отфильтрованные предложением WHERE. Любой агрегат, применяемый к этим группам, работает с пустым набором. Агрегат COUNT (*) для таких групп возвращает 0. Например, рассмотрим запрос, показанный в листинге 12 (назовем его Query 3).

Фильтр в предложении WHERE полностью исключает все строки для клиентов 3, 5, 6 и 9. Ранее вы видели, что результат похожего запроса без GROUP BY ALL не возвращает группы для этих клиентов. Но поскольку GROUP BY ALL сохраняет пустые группы, выходные данные этого запроса охватывают группы для клиентов с числом заказов, равным 0 (см. экран 16).

 

Экран 16. Результаты запроса Query 3

 

Как уже отмечалось, Microsoft объявила о намерении отказаться от этой функции в будущем. Альтернатива для предыдущего запроса, которая должна быть работоспособной в дальнейшем (назовем ее запросом Query 4), приведена в листинге 13.

Вместо фильтрации строк в предложении WHERE запрос использует выражение CASE на основе того же предиката, чтобы определить, сохранить значение (столбец tokeep — 1) или игнорировать (столбец tokeep — NULL). Затем вы применяете вычисление агрегата к столбцу tokeep. В этом решении используются поддерживаемые элементы, которые, скорее всего, будут поддерживаться и в дальнейшем, кроме того, оно более эффективное, чем решение с GROUP BY ALL. На рисунке 3 показаны планы запросов Query 3 (решение GROUP BY ALL) и Query 4 (решение с выражением CASE) с использованием обозревателя планов SQL Sentry (http://www.sqlsentry.com/products/plan-explorer).

 

Рисунок 3. Планы запросов Query 3 (решение с GROUP BY ALL) и Query 4 (решение с выражением CASE)

 

План для решения GROUP BY ALL (Query 3) просматривает входные данные дважды: один раз (верхняя ветвь плана) — чтобы возвратить все строки без применения фильтра WHERE наряду в постоянным флагом NULL, и второй раз (нижняя ветвь плана) с применением фильтра WHERE наряду с постоянным флагом 0. Затем план сцепляет результаты, группирует строки по empid, вычисляет агрегаты COUNT (флаг) и, наконец, применяет фильтр HAVING. План для решения на основе выражения CASE (Query 4) просматривает входные данные только один раз.

Наборы группирования

Традиционные запросы группы вычисляют агрегаты для одного набора группирования (единственного набора выражения, по которому выполняется группирование). Но иногда необходимо составить запросы, выдающие агрегаты для нескольких наборов группирования, обычно при подготовке отчетов. Например, требуется направить запрос к таблице Sales.Orders и вычислить ежедневные, ежемесячные, ежегодные и совокупные итоги заказов. Это можно сделать, составив отдельный группированный запрос для каждого набора группирования, а затем объединить результаты, как показано в листинге 14.

Обратите внимание, что в программном коде листинга 14 используются значения NULL как заполнитель для элементов, не являющихся частью текущей группировки, но релевантные в других наборах группирования. Выражения в предложении ORDER BY обеспечивают иерархический порядок представления (за дневными показателями следуют месячные итоги, за ними годовые итоги и, наконец, совокупный итог). Этот запрос формирует выходные данные, показанные в сокращенном виде на экране 17.

 

Экран 17. Результаты группированного запроса в сокращенном виде

 

SQL (и T-SQL) поддерживает гораздо более изящное решение для наших нужд с использованием предложения GROUPING SETS, которое указывается в предложении GROUP BY и содержит несколько наборов группирования, например как в листинге 15.

Функция GROUPING возвращает 0 для входного элемента, если он является частью набора группирования, и 1 в противном случае (когда это агрегат). В нашем запросе она упрощает выражения в предложении ORDER BY, которое обеспечивает иерархический порядок представления. В ходе логической обработки запросов предложение GROUPING SETS позволяет определить несколько наборов группирования и в результате связать каждую строку, возвращенную из WHERE, возможно, с несколькими группами вместо одной.

В последнем запросе, когда нужно вычислить все наборы группирования, представляющие ведущую комбинацию в иерархии, такой как наша временная иерархия, не обязательно явно перечислять все наборы группирования в предложении GROUPING SETS; можно использовать сокращенный синтаксис с предложением ROLLUP (см. листинг 16).

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

Итак, мы рассмотрели аспекты применения предложений GROUP BY и HAVING для логической обработки запросов. В статье была представлена блок-схема логической обработки запросов с предложениями FROM, WHERE, GROUP BY и HAVING, показано, что группирование удаляет подробности, и предложена альтернатива в виде оконной работы, при которой подробности не удаляются. Я пояснил различия между предикатами WHERE и HAVING, а также рассказал о нестандартной функции GROUP BY ALL и предоставил более эффективную альтернативу, которая в отличие от GROUP BY ALL, вероятно, будет поддерживаться в дальнейшем. Наконец, мы разобрали, как определить несколько наборов группирования в одном запросе с использованием предложений GROUPSING SETS и ROLLUP. В следующей статье цикла мы продолжим рассмотрение логической обработки запросов и речь пойдет о предложениях SELECT и ORDER BY.

Листинг 1. Предложения GROUP BY и HAVING из простого тестового запроса

SELECT ...
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE C.country = N'Spain'
GROUP BY C.custid
HAVING COUNT( O.orderid ) 

  Листинг 2. Фрагменты GROUP BY и HAVING сложного тестового запроса
SELECT ...
FROM Sales.Customers AS C
  LEFT OUTER JOIN
      ( Sales.Orders AS O
          INNER JOIN Sales.OrderDetails AS OD
            ON O.orderid = OD.orderid
            AND O.orderdate >= '20160101' )
    ON C.custid = O.custid
  CROSS APPLY ( VALUES( CONCAT(C.country, N'.' + C.region, N'.' + C.city),
                        OD.qty * OD.unitprice * (1 - OD.discount) )
              ) AS A(custlocation, val)
WHERE A.custlocation IN (N'Spain.Madrid', N'France.Paris', N'USA.WA.Seattle')
GROUP BY C.custid, A.custlocation
HAVING COUNT( DISTINCT O.orderid ) 

  Листинг 3. Пример запроса по столбцам с неповторяющимися значениями для группы
SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid;

Листинг 4. Запрос Query 1

SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid, C.companyname;

Листинг 5. Запрос Query 2

SELECT C.custid, MAX(C.companyname) AS companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid;

Листинг 6. Вычисление агрегата с использованием оконной функции вместо групповой

SELECT custid, orderid, val,
  val / SUM(val) OVER(PARTITION BY custid) AS pctcust
FROM Sales.OrderValues;

Листинг 7. Сгруппированный запрос с итоговыми значениями заказов клиента и процентом от общего итога

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(val) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

Листинг 8. Альтернативный вариант с оконной функцией к псевдониму custtotal

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(custtotal) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

Листинг 9. Применение оконной функции к функции группирования

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(SUM(val)) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

Листинг 10. Вычисление ежедневного итога и значения с нарастающим итогом от начала операций клиента до текущей даты

SELECT custid, orderdate, SUM(val) AS daytotal,
  SUM(SUM(val)) OVER(PARTITION BY custid
                     ORDER BY orderdate
                     ROWS UNBOUNDED PRECEDING) AS runtotal
FROM Sales.OrderValues
GROUP BY custid, orderdate;

Листинг 11. Пример запроса с фильтром в HAVING

SELECT empid, COUNT(*) AS numorders
FROM Sales.OrderValues
WHERE orderdate >= '20160501'
GROUP BY empid
HAVING COUNT(*) 

  Листинг 12. Запрос Query 3
SELECT empid, COUNT(*) AS numorders
FROM Sales.OrderValues
WHERE orderdate >= '20160501'
GROUP BY ALL empid
HAVING COUNT(*) 

  Листинг 13. Запрос Query 4
SELECT empid, COUNT(tokeep) AS numorders
FROM Sales.OrderValues
  CROSS APPLY ( VALUES( CASE WHEN orderdate >= '20160501' THEN 1 END ) )
    AS A(tokeep)
GROUP BY empid
HAVING COUNT(tokeep) 

  Листинг 14. Группированный запрос для каждого набора группирования и объединение результатов
WITH C AS
(
  SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
      AS A(orderyear, ordermonth, orderday)
  GROUP BY orderyear, ordermonth, orderday

  UNION ALL

  SELECT orderyear, ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate)) )
      AS A(orderyear, ordermonth)
  GROUP BY orderyear, ordermonth

  UNION ALL

  SELECT orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate)) ) AS A(orderyear)
  GROUP BY orderyear

  UNION ALL

  SELECT NULL AS orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
)
SELECT orderyear, ordermonth, orderday, numorders
FROM C
ORDER BY
  CASE WHEN orderyear IS NOT NULL THEN 0 ELSE 1 END, orderyear,
  CASE WHEN ordermonth IS NOT NULL THEN 0 ELSE 1 END, ordermonth,
  CASE WHEN orderday IS NOT NULL THEN 0 ELSE 1 END, orderday;

Листинг 15. Решение с использованием предложения GROUPING SETS

SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
    AS A(orderyear, ordermonth, orderday)
GROUP BY GROUPING SETS
(
  (orderyear, ordermonth, orderday),
  (orderyear, ordermonth),
  (orderyear),
  ()
)
ORDER BY
  GROUPING(orderyear), orderyear,
  GROUPING(ordermonth), ordermonth,
  GROUPING(orderday), orderday;

Листинг 16. Сокращенный синтаксис с предложением ROLLUP

SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
    AS A(orderyear, ordermonth, orderday)
GROUP BY ROLLUP(orderyear, ordermonth, orderday)
ORDER BY
  GROUPING(orderyear), orderyear,
  GROUPING(ordermonth), ordermonth,
  GROUPING(orderday), orderday;

Поделитесь материалом с коллегами и друзьями

SQL | GROUP BY — GeeksforGeeks

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

Важные моменты:

  • Предложение GROUP BY используется с оператором SELECT.
  • В запросе предложение GROUP BY помещается после предложения WHERE.
  • В запросе предложение GROUP BY помещается перед предложением ORDER BY, если оно используется.

Синтаксис :

 ВЫБРАТЬ столбец1, имя_функции (столбец2)
FROM table_name
ГДЕ условие
ГРУППА ПО столбцу 1, столбцу 2
ЗАКАЗАТЬ столбец1, столбец2;

  имя_функции : имя используемой функции, например SUM (), AVG ().
  имя_таблицы : Имя таблицы.
  состояние : Состояние использованное.
 

Пример таблицы:

Сотрудник

Студент

Пример:

  • Группировать по одному столбцу : Группировать по одному столбцу означает, чтобы разместить все строки с одинаковым значением только этот конкретный столбец в одной группе.Рассмотрим запрос, как показано ниже:
     ВЫБЕРИТЕ ИМЯ, СУММУ (ЗАРПЛАТА) ОТ сотрудника
    ГРУППА ПО ИМЕНИ;
     

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

    Как вы можете видеть в вышеприведенном выводе, строки с повторяющимися ИМЕНАМИ сгруппированы под тем же ИМЯ, а их соответствующая ЗАРПЛАТА является суммой ЗАРПЛАТЫ повторяющихся строк. Здесь для вычисления суммы используется функция SQL SUM ().

  • Группировать по нескольким столбцам : Группировать по нескольким столбцам, например, GROUP BY column1, column2 .Это означает размещение всех строк с одинаковыми значениями обоих столбцов column1 и column2 в одной группе. Рассмотрим следующий запрос:
     SELECT SUBJECT, YEAR, Count (*)
    ОТ Студента
    ГРУППА ПО ПРЕДМЕТАМ, ГОД;
     

    Выходные данные :

    Как вы можете видеть в выходных данных выше, учащиеся с одинаковыми ПРЕДМЕТАМИ и ГОДОМ помещаются в одну группу. И те, у которых один и тот же SUBJECT, но не YEAR, принадлежат к разным группам. Итак, здесь мы сгруппировали таблицу по двум или более чем одному столбцу.

Пункт HAVING

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

Здесь используется условие HAVING. Мы можем использовать предложение HAVING для размещения условий, чтобы решить, какая группа будет частью окончательного набора результатов. Также мы не можем использовать агрегатные функции, такие как SUM (), COUNT () и т. Д., С предложением WHERE. Поэтому нам нужно использовать предложение HAVING, если мы хотим использовать любую из этих функций в условиях.

Синтаксис :

 ВЫБРАТЬ столбец1, имя_функции (столбец2)
FROM table_name
ГДЕ условие
ГРУППА ПО столбцу 1, столбцу 2
ИМЕЮЩИЕ условие
ЗАКАЗАТЬ столбец1, столбец2;

  имя_функции : имя используемой функции, например SUM (), AVG ().
  имя_таблицы : Имя таблицы.
  состояние : Состояние использованное.
 

Пример :

 ВЫБЕРИТЕ ИМЯ, СУММУ (ЗАРПЛАТУ) ОТ сотрудника
ГРУППА ПО ИМЕНИ
ИМЕЕТ СУММУ (ЗАРПЛАТУ)> 3000;
 

Выходные данные :

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

Автор статьи: Harsh Agarwal . Если вам нравится GeeksforGeeks, и вы хотели бы внести свой вклад, вы также можете написать статью с помощью provide.geeksforgeeks.org или отправить ее по электронной почте на [email protected] Посмотрите, как ваша статья появляется на главной странице GeeksforGeeks, и помогите другим гикам.

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

SQL Server GROUP BY

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

Введение в SQL Server

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

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

Синтаксис предложения GROUP BY показан ниже:

 

SELECT select_list ИЗ table_name ГРУППА ПО column_name1, имя_столбца2,...;

Язык кода: SQL (язык структурированных запросов) (sql)

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

Рассмотрим следующий пример:

 

SELECT Пользовательский ИД, YEAR (order_date) order_year ИЗ заказы на продажу ГДЕ customer_id IN (1, 2) СОРТИРОВАТЬ ПО Пользовательский ИД;

Язык кода: SQL (язык структурированных запросов) (sql)

В этом примере мы получили идентификатор клиента и год заказа клиентов с идентификатором клиента один и два.

Как ясно видно из выходных данных, клиент с идентификатором один разместил один заказ в 2016 году и два заказа в 2018 году. Клиент с идентификатором два разместил два заказа в 2017 году и один заказ в 2018 году.

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

 

SELECT Пользовательский ИД, YEAR (order_date) order_year ИЗ заказы на продажу ГДЕ customer_id IN (1, 2) ГРУППА ПО Пользовательский ИД, ГОД (заказ_дата) СОРТИРОВАТЬ ПО Пользовательский ИД;

Язык кода: SQL (язык структурированных запросов) (sql)

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

С функциональной точки зрения, предложение GROUP BY в приведенном выше запросе дало тот же результат, что и следующий запрос, в котором используется предложение DISTINCT :

 

SELECT DISTINCT Пользовательский ИД, YEAR (order_date) order_year ИЗ заказы на продажу ГДЕ customer_id IN (1, 2) СОРТИРОВАТЬ ПО Пользовательский ИД;

Язык кода: SQL (язык структурированных запросов) (sql)

SQL Server

Предложение GROUP BY и агрегатные функции

На практике предложение GROUP BY часто используется с агрегатными функциями для генерации сводки отчеты.

Агрегатная функция выполняет вычисление для группы и возвращает уникальное значение для каждой группы. Например, COUNT () возвращает количество строк в каждой группе. Другие часто используемые агрегатные функции: СУММ () , СРЕД () (среднее), МИН () (минимум), МАКС () (максимум).

Предложение GROUP BY объединяет строки в группы, а агрегатная функция возвращает сводку (количество, минимум, максимум, среднее, сумму и т. Д.) Для каждой группы.

Например, следующий запрос возвращает количество заказов, размещенных клиентом по годам:

 

SELECT Пользовательский ИД, YEAR (order_date) order_year, COUNT (order_id) order_placed ИЗ заказы на продажу ГДЕ customer_id IN (1, 2) ГРУППА ПО Пользовательский ИД, ГОД (заказ_дата) СОРТИРОВАТЬ ПО Пользовательский ИД;

Язык кода: SQL (язык структурированных запросов) (sql)

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

 

SELECT Пользовательский ИД, YEAR (order_date) order_year, статус заказа ИЗ заказы на продажу ГДЕ customer_id IN (1, 2) ГРУППА ПО Пользовательский ИД, ГОД (заказ_дата) СОРТИРОВАТЬ ПО Пользовательский ИД;

Язык кода: SQL (язык структурированных запросов) (sql)

Подробнее

Примеры предложения GROUP BY

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

Использование предложения

GROUP BY с примером функции COUNT ()

Следующий запрос возвращает количество клиентов в каждом городе:

 

SELECT Город, COUNT (customer_id) customer_count ИЗ sales.customers ГРУППА ПО город СОРТИРОВАТЬ ПО город;

Язык кода: SQL (язык структурированных запросов) (sql)

В этом примере предложение GROUP BY группирует клиентов по городам, а функция COUNT () возвращает количество клиентов в каждом городе. .

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

 

ВЫБРАТЬ Город, государственный, COUNT (customer_id) customer_count ИЗ sales.customers ГРУППА ПО государственный, город СОРТИРОВАТЬ ПО Город, государственный;

Язык кода: SQL (язык структурированных запросов) (sql)

Использование предложения

GROUP BY с функциями MIN и MAX , пример

Следующий оператор возвращает минимальную и максимальную прайс-лист всех продуктов с моделью 2018 по марке:

 

SELECT название бренда, MIN (list_price) min_price, MAX (list_price) max_price ИЗ производство.продукты p ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ production.brands b ON b.brand_id = p.brand_id ГДЕ model_year = 2018 ГРУППА ПО название бренда СОРТИРОВАТЬ ПО название бренда;

Язык кода: SQL (язык структурированных запросов) (sql)

В этом примере, как всегда, предложение WHERE обрабатывается перед предложением GROUP BY .

Использование предложения

GROUP BY с примером функции AVG ()

В следующем операторе используется функция AVG () для возврата средней прейскурантной цены по брендам для всех продуктов с модельным годом 2018:

 

ВЫБРАТЬ название бренда, AVG (list_price) avg_price ИЗ производство.продукты p ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ production.brands b ON b.brand_id = p.brand_id ГДЕ model_year = 2018 ГРУППА ПО название бренда СОРТИРОВАТЬ ПО название бренда;

Язык кода: SQL (язык структурированных запросов) (sql)

Использование предложения

GROUP BY с функцией SUM , пример

См. Следующую таблицу order_items :

В следующем запросе используется SUM ( ) функция для получения чистой стоимости каждого заказа:

 

SELECT номер заказа, СУММ ( количество * list_price * (1 - скидка) ) чистая стоимость ИЗ продажи.order_items ГРУППА ПО номер заказа;

Язык кода: SQL (язык структурированных запросов) (sql)

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

Как это применить эффективно

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

Введение в предложение SQL GROUP BY

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

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

Вы часто используете GROUP BY вместе с агрегатной функцией, такой как MIN, MAX, AVG, SUM или COUNT, чтобы вычислить показатель, который предоставляет информацию для каждой группы.

Ниже показан синтаксис предложения GROUP BY.

 

ВЫБРАТЬ column1, столбец2, AGGREGATE_FUNCTION (столбец 3) ИЗ Таблица 1 ГРУППА ПО column1, column2;

Язык кода: SQL (язык структурированных запросов) (sql)

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

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

Важно подчеркнуть, что предложение WHERE применяется до группировки строк, тогда как предложение HAVING применяется после группировки строк. Другими словами, предложение WHERE применяется к строкам, тогда как предложение HAVING применяется к группам.

Чтобы отсортировать группы, добавьте предложение ORDER BY после предложения GROUP BY.

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

SQL, примеры GROUP BY

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

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

 

SELECT Department_id, COUNT (employee_id) штат ИЗ сотрудники ГРУППА ПО Department_id;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

SQL GROUP BY с INNER JOIN пример

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

 

SELECT е.Department_id, название отдела, COUNT (employee_id) штат ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделов d ON d.department_id = e.department_id ГРУППА ПО e.department_id;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

SQL GROUP BY с ORDER BY, пример

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

 

ВЫБРАТЬ e.department_id, название отдела, COUNT (employee_id) штат ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделы д НА д.Department_id = e.department_id ГРУППА ПО e.department_id ЗАКАЗАТЬ ПО КОЛИЧЕСТВУ DESC;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотрите в действии

Обратите внимание, что вы можете использовать псевдоним headcount или COUNT (employee_id) в предложении ORDER BY.

SQL GROUP BY с примером HAVING

Чтобы найти отдел, численность персонала которого превышает 5, используйте предложение HAVING в качестве следующего запроса:

 

SELECT е.Department_id, название отдела, COUNT (employee_id) штат ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделы d ON d.department_id = e.department_id ГРУППА ПО e.department_id ИМЕЮЩИЙ персонал> 5 ЗАКАЗАТЬ ПО КОЛИЧЕСТВУ DESC;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

SQL GROUP BY с MIN, MAX и AVG, пример

Следующий запрос возвращает минимальную, максимальную и среднюю зарплату сотрудники в каждом отделе.

 

ВЫБРАТЬ e.department_id, название отдела, MIN (зарплата) min_salary, MAX (зарплата) max_salary, КРУГЛЫЙ (AVG (зарплата), 2) average_salary ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделы d ON d.department_id = e.department_id ГРУППА ПО e.department_id;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

SQL GROUP BY с примером функции СУММ

Чтобы получить общую зарплату по отделам, вы примените функцию СУММ к зарплате и сгруппируйте сотрудников по столбцу Department_id следующим образом:

 

SELECT е.Department_id, название отдела, SUM (зарплата) total_salary ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделы d ON d.department_id = e.department_id ГРУППА ПО e.department_id;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

SQL GROUP BY по нескольким столбцам

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

 

GROUP BY Department_id

Язык кода: SQL (язык структурированных запросов) (sql)

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

Как насчет группировки сотрудников по значениям в столбцах Department_id и job_id ?

 

GROUP BY Department_id, job_id

Язык кода: SQL (язык структурированных запросов) (sql)

В этом разделе все сотрудники с одинаковыми значениями сгруппированы в столбцах Department_id и job_id в одну группу .

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

 

ВЫБРАТЬ e.department_id, название отдела, e.job_id, название работы, COUNT (идентификатор сотрудника) ИЗ сотрудники е ВНУТРЕННЕЕ СОЕДИНЕНИЕ отделы d ON d.department_id = e.department_id ВНУТРЕННЕЕ СОЕДИНЕНИЕ вакансии j ВКЛ j.job_id = e.job_id ГРУППА ПО e.department_id, e.job_id;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

Отдел 2, 3 и 5 отображается более чем одним.

Это связано с тем, что в этих отделах есть сотрудники, занимающие разные должности.Например, в отделе отгрузки 2 сотрудника занимают должность клерка, 1 сотрудник — складского клерка и 4 сотрудника — менеджера склада.

SQL GROUP BY и DISTINCT

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

Следующее получает номера телефонов сотрудников, а также группирует строки по номерам телефонов.

 

ВЫБРАТЬ телефонный номер ИЗ сотрудники ГРУППА ПО телефонный номер;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

Обратите внимание, что номера телефонов отсортированы.

Следующий оператор также извлекает телефонные номера, но вместо предложения GROUP BY он использует оператор DISTINCT .

 

ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ телефонный номер ИЗ сотрудники;

Язык кода: SQL (язык структурированных запросов) (sql)

Посмотреть в действии

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

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

  • Было ли это руководство полезным?
  • Да Нет

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

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

Обычно вы
используйте предложение GROUP BY вместе с агрегатным выражением.

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

Синтаксис

 
ГРУППА ПО
{
      имя-столбца  [, -имя-столбца ] *
|
    ROLLUP ( имя столбца  [,  имя столбца ] *)
}  

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

SelectItems дюйм
SelectExpression с
Предложение GROUP BY должно содержать только агрегаты или группирующие столбцы.

Примеры

  - найти среднее время полета рейсов, сгруппированных по
- аэропорт
ВЫБРАТЬ СРЕДНЕЕ (время полета), orig_airport
ОТ Рейсы
ГРУППА ПО orig_airport

ВЫБРАТЬ МАКС. (Название города), регион
ИЗ городов, стран
ГДЕ Cities.country_ISO_code = Country.country_ISO_code
ГРУППА ПО РЕГИОНАМ

- группа по маленькому
ВЫБЕРИТЕ ID, СРЕДНЕЕ (ЗАРПЛАТА)
ОТ SAMP.STAFF
ГРУППА ПО ID

- Получите столбцы AVGSALARY и EMPCOUNT, а также столбец DEPTNO, используя предложение AS.
- И сгруппируйте по столбцу WORKDEPT, используя имя корреляции ДРУГИЕ.
ВЫБЕРИТЕ ДРУГОЕ.РАБОТА В КАЧЕСТВЕ ОТДЕЛЕНИЯ,
СРЕДНЯЯ ЗАРПЛАТА КАК СРЕДНЯЯ ЗАРПЛАТА,
COUNT (*) AS EMPCOUNT
ОТ SAMP. СОТРУДНИКИ ДРУГИЕ
ГРУППА ПО ДРУГИМ.

- Вычислить промежуточные итоги данных Sales_History, сгруппировав их по регионам, по
- (Регион, Штат) и (Регион, Штат, Продукт), а также вычисления
- общая сумма продаж для всех регионов, штатов и продуктов:
ВЫБЕРИТЕ регион, штат, продукт, SUM (продажи) Total_Sales
ИЗ Sales_History
ГРУППА ПО РОЛИКЕ (регион, штат, продукт)
  

SQL GROUP BY

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

Введение в предложение SQL GROUP BY

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

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

Типичный синтаксис GROUP BY выглядит следующим образом:

SELECT

column1, column2, агрегатная_функция (выражение)

FROM

table_name

WHERE condition

столбец

Примеры SQL GROUP BY

Давайте посмотрим на продукты Таблица :

SQL GROUP BY с функцией SUM, пример

Чтобы получить общее количество единиц на складе для каждой категории продуктов, вы используете GROUP BY с функцией SUM следующим образом:

SELECT

categoryid, SUM (unitsinstock)

FROM

продуктов

GROUP BY categoryid;

Механизм базы данных выполняет следующие шаги:

  • Сначала проверяет предложение GROUP BY и делит продукты на группы на основе категории продукта categoryid .
  • Во-вторых, вычисляет общее количество единиц на складе с помощью функции СУММ для каждой группы.

SQL GROUP BY с функцией COUNT, пример

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

ВЫБРАТЬ

categoryid, COUNT (productid)

FROM

продуктов

GROUP BY categoryid;

SQL GROUP BY с функцией AVG

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

ВЫБРАТЬ

categoryid, FLOOR (AVG (unitsinstock))

FROM

продуктов

GROUP BY categoryid;

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

SQL GROUP BY с функциями MIN и MAX

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

SELECT

categoryid, MIN (количество единиц на складе), МАКС (шт. На складе)

ИЗ

товаров

ГРУППА ПО categoryid;

SQL GROUP BY с ORDER BY, пример

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

SELECT

categoryid, COUNT (productid)

FROM

продуктов

GROUP BY categoryid

ORDER BY COUNT (productid) DESC;

SQL GROUP BY по нескольким столбцам

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

Схема базы данных связанных таблиц выглядит следующим образом:

Следующий запрос иллюстрирует идею:

SELECT

b.customerid,

b.CompanyName,

COUNT (a.orderid) AS ‘Заказы’,

CONCAT (e.lastname, e.firstname) as ‘Продавец’

FROM

заказов a

ВНУТРЕННИЙ ПРИСОЕДИНЕНИЕ клиентов b НА a.customerid = b.customerid

ВНУТРЕННИЕ СОЕДИНЕНИЕ сотрудников e ON e .employeeid = a.employeeid

GROUP BY b.customerid,

a.employeeid

ORDER BY b.customerid ASC,

«Количество заказов» DESC;

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

  • Было ли это руководство полезным?
  • Да Нет

SQL GROUP BY — javatpoint

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

  • Оператор SELECT используется с предложением GROUP BY в запросе SQL.
  • Предложение WHERE помещается перед предложением GROUP BY в SQL .
  • Предложение ORDER BY помещается после предложения GROUP BY в SQL .

Синтаксис:

ВЫБРАТЬ столбец1, имя_функции (столбец2)
FROM table_name
ГДЕ условие
ГРУППА ПО столбцу 1, столбцу 2
ЗАКАЗАТЬ столбец1, столбец2;
function_name: имя таблицы.
Состояние: которое мы использовали.

Образец таблицы:

Сотрудник

S.no Имя ВОЗРАСТ Заработная плата
1 Джон 24 25000
2 Ник 22 22000
3 Амара 25 15000
4 Ник 22 22000
5 Джон 24 25000

Студент

ТЕМА ГОД НАЗВАНИЕ
Язык C 2 Джон
Язык C 2 Джинни
Язык C 2 Джасмин
Язык C 3 Ник
Язык C 3 Амара
Ява 1 Sifa
Ява 1 тележка

Пример:

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

Рассмотрим следующий запрос:

ВЫБЕРИТЕ ИМЯ, СУММУ (ЗАРПЛАТУ) ОТ сотрудника
ГРУППА ПО ИМЕНИ;

Результат запроса:

НАЗВАНИЕ ЗАРПЛАТА
Джон 50000
Ник 44000
Амара 15000

В выходных данных строки, содержащие дубликаты ИМЯ , сгруппированы под похожим ИМЯ, и их соответствующая ЗАРПЛАТА является суммой ЗАРПЛАТЫ повторяющихся строк.

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

Рассмотрим следующий запрос:

ВЫБЕРИТЕ ТЕМУ, ГОД, Счетчик (*)
ОТ Студента
Группа ПО ПРЕДМЕТАМ, ГОДУ;

Выход:

ТЕМА ГОД Счетчик
Язык C 2 3
Язык C 3 2
Ява 1 2

В вышеприведенных выходных данных учащиеся с аналогичными SUBJECT и YEAR сгруппированы в одном месте.Учащиеся, у которых есть только одно общее, относятся к разным группам. Например, если ИМЯ такое же, а ГОД отличается.

Теперь нам нужно сгруппировать таблицу более чем по одному или двум столбцам.

Оговорка о наличии

ГДЕ Предложение используется для определения цели. Он используется для размещения условий в столбцах, чтобы определить часть последнего набора результатов группы. Здесь нам не требуется использовать комбинированные функции, такие как COUNT (), SUM (), и т. Д.с предложением WHERE . После этого нам нужно использовать предложение HAVING .

Синтаксис предложения наличия:

ВЫБРАТЬ столбец1, имя_функции (столбец2)
FROM table_name
ГДЕ условие
ГРУППА ПО столбцу 1, столбцу 2
ИМЕЮЩИЕ условие
ЗАКАЗАТЬ столбец1, столбец2;
function_name: в основном используется для имени функции, SUM (), AVG ().
table_name: используется для имени таблицы.
Состояние: Состояние б / у.

Пример:

ВЫБЕРИТЕ ИМЯ, СУММУ (ЗАРПЛАТУ) ОТ сотрудника
ГРУППА ПО ИМЕНИ
ИМЕЮЩАЯ СУММУ (ЗАРПЛАТУ)> 23000;

Выход:

Имя СУММА (ЗАРПЛАТА)
Джон 50000

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

Его следует размещать по группам, а не по столбцам.

Очки:

  • Предложение GROUP BY используется для группировки строк с одинаковыми значениями.
  • Оператор SELECT в SQL используется с предложением GROUP BY.
  • В предложении Group BY оператор SELECT может использовать константы , агрегатные функции , , выражения, имена столбцов и .
  • Предложение GROUP BY вызывается, когда предложение HAVING используется для уменьшения результатов.

Group By, Have и Where в SQL

Введение

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

Группировать по пункту

Предложение Group by часто используется для упорядочивания идентичных повторяющихся данных в группы с помощью оператора select для группировки набора результатов по одному или нескольким столбцам.Это предложение работает с выбранным конкретным списком элементов, и мы можем использовать предложения HAVING и ORDER BY. Предложение Group by всегда работает с агрегатными функциями, такими как MAX, MIN, SUM, AVG, COUNT.

Давайте обсудим группировку по пунктам на примере. У нас есть таблица «Производство автомобилей», есть несколько моделей с ценой и некоторые повторяющиеся данные. Мы хотим отнести эти данные к другой группе с соответствующей общей ценой.

Пример:

  1. Создать таблицу VehicleProduction
  2. (
  3. Id int primary key Identity,
  4. Model varchar (50),
  5. Price money
  6. )
  7. 58

  8. ‘, 850000), (‘ L551 ‘, 850000), (‘ L551 ‘, 850000), (‘ L551 ‘, 750000),
  9. (‘ L538 ‘, 650000), (‘ L538 ‘, 650000), (‘ L538 ‘, 550000), (‘ L530 ‘, 450000), (‘ L530 ‘, 350000), (‘ L545 ‘, 250000)
  10. Выберите * из VehicleProduction

MAX () — функция возвращает максимальное значение числового столбца указанного критерия.

Пример

  1. Выберите max (Price) как «MaximumCostOfModel» из VehicleProduction

Выход

MIN () — функция возвращает минимум числового столбца указанного критерия.

Пример

  1. Выберите минимальную цену (цену) как «MinimumCostOfModel» из VehicleProduction

СУММ () — функция возвращает общую сумму числового столбца по заданным критериям.

Пример

  1. Выберите SUM (Price) как SumCostOfAllModel из VehicleProduction

Выход

AVG () — функция возвращает среднее значение числового столбца по заданным критериям.

Пример

  1. Выберите AVG (Price) как «AverageCostOfModel» из VehicleProduction

COUNT () — функция возвращает количество строк, соответствующих заданным критериям.

Пример

  1. Выберите Count (Price) As ‘TotalVehicleModels’ из VehicleProduction

Предложение Distinct

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

Пример

  1. Select Distinct (модель), цена от автомобиля

Выход

Предложение Group by часто используется для упорядочивания идентичных повторяющихся данных в группы с помощью оператора select.Это предложение работает с выбранным конкретным списком элементов, для этого мы можем использовать предложения HAVING и ORDER BY.

Синтаксис

  1. ВЫБРАТЬ Column1, Column2
  2. FROM TableName
  3. GROUP BY Column1, Column2

Пример

  1. Выбрать * из автомобиля Производство
  2. Выбрать модель, цену из автомобиля Производство
  3. Группировать по модели, цене

Давайте посмотрим на пример GROUP BY с агрегатными функциями.

GROUP BY с агрегатными функциями

Пример

  1. Выберите модель, цену, количество (*) как QtyOfModel, Sum (Price) как TotPriceOfModel из VehicleProduction
  2. Группируйте по модели, цене

Выход

Предложение Where

Предложение Where работает с предложением select, но не работает с условием функции group by или агрегирования.

Пример 1

  1. Выберите модель, цену из производства автомобиля
  2. где Модель! = ‘L530’
  3. Группировка по модели, цена

    Выход

    Пример 2

    Мы не можем использовать предложение where после группы по пункту

    1. Select Model, Price from VehicleProduction
    2. group by Model, Price
    3. where Model! = ‘L530’

    Предложение Имея

    Предложение Имея работает с предложением group by, но, в частности, работает с условием агрегированной функции.

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

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