SELECT — GROUP BY (Transact-SQL)
Область применения: SQL Server
База данных SQL Azure Управляемый экземпляр SQL Azure
конечной точке аналитики платформы Аналитики Azure Synapse Analytics
(PDW)
в Microsoft Fabric
Хранилище в Microsoft Fabric
Предложение инструкции SELECT, разделяющее результат запроса на группы строк, обычно путем выполнения одной или нескольких агрегатов для каждой группы. Инструкция SELECT возвращает одну строку для каждой группы.
Синтаксис
Соглашения о синтаксисе Transact-SQL
-- Syntax for SQL Server and Azure SQL Database
-- ISO-Compliant Syntax
GROUP BY {
column-expression
| ROLLUP ( <group_by_expression> [ ,...n ] )
| CUBE ( <group_by_expression> [ ,...n ] )
| GROUPING SETS ( <grouping_set> [ ,...n ] )
| () --calculates the grand total
} [ ,...n ]
<group_by_expression> ::=
column-expression
| ( column-expression [ ,...n ] )
<grouping_set> ::=
() --calculates the grand total
| <grouping_set_item>
| ( <grouping_set_item> [ ,...n ] )
<grouping_set_item> ::=
<group_by_expression>
| ROLLUP ( <group_by_expression> [ ,...n ] )
| CUBE ( <group_by_expression> [ ,...n ] )
-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database
GROUP BY {
ALL column-expression [ ,...n ]
| column-expression [ ,...n ] WITH { CUBE | ROLLUP }
}
-- Syntax for Azure Synapse Analytics
GROUP BY {
column-name [ WITH (DISTRIBUTED_AGG) ]
| column-expression
| ROLLUP ( <group_by_expression> [ ,...n ] )
} [ ,...n ]
-- Syntax for Parallel Data Warehouse
GROUP BY {
column-name [ WITH (DISTRIBUTED_AGG) ]
| column-expression
} [ ,...n ]
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
column-expression
Указывает на столбец или на нестатистическое вычисление в столбце. Этот столбец может принадлежать таблице, производной таблице или представлению. Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT.
Допустимые выражения см. в разделе expression.
Столбец должен быть указан в предложении FROM инструкции SELECT, но не обязательно должен присутствовать в списке SELECT. Каждый столбец таблицы или представления в любом нестатистическом выражении в списке <select> должен быть включен в список GROUP BY:
Следующие инструкции являются допустимыми.
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;
Следующие инструкции не являются допустимыми.
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;
Выражение столбца не может содержать:
- псевдоним столбца, определенный в списке SELECT. Выражение может использовать псевдоним столбца для производной таблицы, определенной в предложении FROM.
- столбец типа text, ntext, или image. Однако столбец типа text, ntext или image можно использовать как аргумент для функции, возвращающей значение допустимого типа данных. Например, выражение может использовать SUBSTRING() и CAST(). Это также относится к выражениям в предложении HAVING.
- методы типа данных xml. Сюда может входить определяемой пользователем функции, которая использует методы типа данных xml. Сюда может входить вычисляемый столбец, который использует методы типа данных xml.
- вложенный запрос. Возвращается ошибка 144.
- столбец из индексированного представления.
GROUP BY column-expression [ ,...n ]
Группирует результаты инструкции SELECT в соответствии со значениями в списке одного или нескольких выражений столбцов.
Например, этот запрос создает таблицу Sales со столбцами Country, Region и Sales. Он вставляет четыре строки, и две строки имеют совпадающие значения для столбцов Country и Region.
CREATE TABLE Sales ( Country VARCHAR(50), Region VARCHAR(50), Sales INT );
INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);
В таблице Sales содержатся указанные далее строки.
Страна/регион | Область/регион | Продажи |
---|---|---|
Канада | Альберта | 100 |
Канада | Британская Колумбия | 200 |
Канада | Британская Колумбия | 300 |
Соединенные Штаты | Монтана | 100 |
Этот запрос группирует значения столбцов Country и Region и возвращает общую сумму по каждому сочетанию значений.
SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;
Результат запроса содержит 3 строки, так как существует 3 сочетания значений для Country и Region. Значение TotalSales для Canada и British Columbia является суммой двух строк.
Страна/регион | Область/регион | TotalSales |
---|---|---|
Канада | Альберта | 100 |
Канада | Британская Колумбия | 500 |
Соединенные Штаты | Монтана | 100 |
GROUP BY ROLLUP
Создает группу для каждого сочетания выражений столбцов. Кроме того, выполняет сведение результатов в промежуточные и общие итоги. Для этого запрос перемещается справа налево, уменьшая количество выражений столбцов, по которым он создает группы и агрегаты.
Порядок столбцов влияет на выходные данные ROLLUP и может отразиться на количестве строк в результирующем наборе.
Например, GROUP BY ROLLUP (col1, col2, col3, col4)
создает группы для каждой комбинации выражений столбцов в следующих списках.
- col1, col2, col3, col4
- col1, col2, col3, NULL
- col1, col2, NULL, NULL
- col1, NULL, NULL, NULL
- NULL, NULL, NULL, NULL — это общий итог.
Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY ROLLUP вместо простого предложения GROUP BY.
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);
Результатом запроса являются те же статистические вычисления, что и в простом предложении GROUP BY без ROLLUP. Кроме того, здесь создаются промежуточные итоги для каждого значения в столбце Country. Наконец, выводится общий итог для всех строк. Результат выглядит следующим образом.
Страна/регион | Область/регион | TotalSales |
---|---|---|
Канада | Альберта | 100 |
Канада | Британская Колумбия | 500 |
Канада | NULL | 600 |
Соединенные Штаты | Монтана | 100 |
Соединенные Штаты | NULL | 100 |
NULL | NULL | 700 |
GROUP BY CUBE ( )
GROUP BY CUBE создает группы для всех возможных сочетаний столбцов. Для GROUP BY CUBE (a, b) результатами являются группы для уникальных значений (a, b) (NULL, b), (a, NULL) и (NULL, NULL).
Принимая во внимание таблицу из предыдущего примера, этот код выполняет операцию GROUP BY CUBE по столбцам Country и Region.
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);
Результатом запроса являются группы для уникальных значений (Country, Region), (NULL, Region), (Country, NULL) и (NULL, NULL). Результат выглядит следующим образом:
Страна/регион | Область/регион | TotalSales |
---|---|---|
Канада | Альберта | 100 |
NULL | Альберта | 100 |
Канада | Британская Колумбия | 500 |
NULL | Британская Колумбия | 500 |
Соединенные Штаты | Монтана | 100 |
NULL | Монтана | 100 |
NULL | NULL | 700 |
Канада | NULL | 600 |
Соединенные Штаты | NULL | 100 |
GROUP BY GROUPING SETS ( )
Параметр GROUPING SETS позволяет объединять несколько предложений GROUP BY в одно предложение GROUP BY. Результаты эквивалентны тем, что формируются с применением конструкции UNION ALL к указанным группам.
Например, GROUP BY ROLLUP (Country, Region)
и GROUP BY GROUPING SETS ( ROLLUP (Country, Region) )
возвращают одинаковые результаты.
Если параметр GROUPING SETS имеет два или более элементов, результатом будет объединение элементов. Этот пример возвращает объединение результатов ROLLUP и CUBE для Country и Region.
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );
Результаты будут такими же, так как этот запрос возвращает объединение двух инструкций GROUP BY.
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);
SQL не консолидирует повторяющиеся группы, созданные для списка GROUPING SETS. Например, в GROUP BY ( (), CUBE (Country, Region) )
оба элемента возвращают строку для общего итога, и в списке результатов будут указаны обе строки.
GROUP BY ()
Указывает пустую группу, которая создает общий итог. Он полезен в качестве одного из элементов GROUPING SET. Например, это заявление дает общий объем продаж для каждой страны или региона, а затем дает общий итог для всех стран или регионов.
SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );
GROUP BY ALL column-expression [ ,...n ]
Применимо к: SQL Server и база данных SQL Azure
Примечание.
Этот синтаксис поддерживается только для обратной совместимости. В будущей версии он будет удален. Избегайте использования этого синтаксиса в новых разработках и учитывайте необходимость изменения в будущем приложений, использующих этот синтаксис сейчас.
Указывает включить все группы в результаты независимо от того, соответствуют ли они условиям поиска в предложении WHERE. Группы, которые не соответствуют условиям поиска, имеют значение NULL для статистического вычисления.
GROUP BY ALL
- Не поддерживается в запросах с доступом к удаленным таблицам, если в запросе присутствует также предложение WHERE.
- Применение предложения к столбцам, имеющим атрибут FILESTREAM, приведет к ошибке.
GROUP BY column-expression [ ,...n ] WITH { CUBE | ROLLUP }
Применимо к: SQL Server и база данных SQL Azure
Примечание.
Этот синтаксис поддерживается только для обратной совместимости. Избегайте использования этого синтаксиса в новых разработках и учитывайте необходимость изменения в будущем приложений, использующих этот синтаксис сейчас.
WITH (DISTRIBUTED_AGG)
Область применения: Azure Synapse Analytics и система платформы аналитики (PDW)
Указание запроса DISTRIBUTED_AGG заставляет систему MPP перераспределять таблицу по определенному столбцу до выполнения статистического вычисления. Только один столбец в предложении GROUP BY может иметь указание запроса DISTRIBUTED_AGG. После завершения запроса перераспределенная таблица удаляется. Исходная таблица не изменяется.
ПРИМЕЧАНИЕ. Указание запроса DISTRIBUTED_AGG предоставляется для обратной совместимости с более ранними версиями платформы аналитики (PDW) и не повышает производительность большинства запросов. По умолчанию MPP уже перераспределяет данные для улучшения производительности для статистических вычислений.
Общие замечания
Взаимодействие GROUP BY с инструкцией SELECT
Список SELECT:
- Векторные агрегаты. Если в список SELECT включены агрегатные функции, инструкция GROUP BY вычисляет сводные значения для каждой группы. Они известны как векторные статистические вычисления.
- Статистические функции DISTINCT. С конструкциями ROLLUP, CUBE и GROUPING SETS поддерживаются статистические функции AVG (DISTINCT column_name), COUNT (DISTINCT column_name) и SUM (DISTINCT column_name).
Предложение WHERE:
- SQL удаляет строки, которые не соответствуют условиям в предложении WHERE, до выполнения любых операций группирования.
Предложение HAVING:
- SQL использует предложение HAVING для фильтрации групп в результирующем наборе.
Предложение ORDER BY:
- Чтобы упорядочить результирующий набор, необходимо использовать предложение ORDER BY. Применение предложения GROUP BY не упорядочивает результирующий набор.
Значения NULL:
- Если столбец группировки содержит значения NULL, все значения NULL считаются равными и собираются в одну группу.
Ограничения
Область применения: SQL Server (начиная с 2008) и Azure Synapse Analytics
Максимальная вместимость
Для предложения GROUP BY, использующего ROLLUP, CUBE или GROUPING SETS, используется максимум 32 выражения. Максимальное количество групп — 4096 (212). Следующие примеры завершаются ошибкой, поскольку предложение GROUP BY имеет больше 4096 групп.
В следующем примере формируется 4097 (212 + 1) группирующих наборов, поэтому пример завершится ошибкой.
GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
В следующем примере формируется 4097 (212 + 1) групп, поэтому пример завершится ошибкой. Оба
CUBE ()
()
набора группирования создают большую общую строку и повторяющиеся наборы группирования не устраняются.GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
В этом примере используется синтаксис обратной совместимости. В примере создается 8192 (213) группирующих наборов, поэтому он завершится ошибкой.
GROUP BY CUBE (a1, ..., a13) GROUP BY a1, ..., a13 WITH CUBE
Для предложений GROUP BY, не содержащих cube или ROLLUP, количество групп по элементам ограничено размерами столбцов GROUP BY, агрегированными столбцами и статистическими значениями, участвующими в запросе. Это объясняется ограничением размера промежуточной рабочей таблицы (8060 байт), необходимой для хранения промежуточных результатов запроса. При указании CUBE или ROLLUP максимально разрешенное количество выражений группирования равно 12.
Поддержка функций предложения GROUP BY, совместимых с ISO и ANSI SQL-2006
Предложение GROUP BY поддерживает все возможности предложения GROUP BY, включенные в стандарт SQL-2006, со следующими синтаксическими исключениями.
Группирование наборов не допускается в предложении GROUP BY, если они не являются частью явного списка GROUPING SET. Например, предложение
GROUP BY Column1, (Column2, ...ColumnN
) допускается в этом стандарте, но не в Transact-SQL. Transact-SQL поддерживаетGROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))
иGROUP BY Column1, Column2, ... ColumnN
, которые семантически эквивалентны. Эти инструкции семантически эквивалентны предыдущему примеру предложенияGROUP BY
. Это позволяет избежать того, чтоGROUP BY Column1, (Column2, ...ColumnN
) может быть неправильно интерпретирован какGROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))
, который не является семантически эквивалентным.Группирование наборов не допускается внутри группирования наборов. Например, предложение
GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn))
допускается в стандарте SQL-2006, но не в Transact-SQL. Transact-SQL поддерживаетGROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn )
иGROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) )
, которые семантически эквивалентны первому примеру предложения GROUP BY и имеют более понятный синтаксис.GROUP BY [ALL/DISTINCT] используется только в простом предложении GROUP BY, которое содержит выражения столбцов. Не разрешено использовать конструкции GROUPING SETS, ROLLUP, CUBE, WITH CUBE или WITH ROLLUP. Ключевое слово ALL применяется по умолчанию и задано неявно. Оно допускается только в синтаксисе обратной совместимости.
Сравнение поддерживаемых функций предложения GROUP BY
В следующей таблице описаны поддерживаемые возможности предложения GROUP BY с учетом версии SQL и уровня совместимости базы данных.
Функция | SQL Server Integration Services | Уровень совместимости SQL Server — 100 или более | Уровень совместимости SQL Server 2008 или более поздней версии — 90. |
---|---|---|---|
Статистические функции DISTINCT | Не поддерживаются для конструкций WITH CUBE или WITH ROLLUP. | Поддерживаются для конструкций WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE или ROLLUP. | Аналогично уровню совместимости 100. |
Определяемая пользователем функция с именем CUBE или ROLLUP в предложении GROUP BY | Определяемая пользователем функция dbo.cube(arg1,...argN) or dbo.rollup(arg1,...argN) в предложении GROUP BY недопустима. Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); |
Определяемая пользователем функция dbo.cube (arg1,...argN**)** или dbo.rollup(arg1,...argN) в предложении GROUP BY недопустима. Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); Возвращается сообщение об ошибке: "Неправильный синтаксис рядом с ключевым словом 'cube'|'rollup'". Чтобы избежать этой проблемы, замените конструкцию dbo.cube на [dbo].[cube] или конструкцию dbo.rollup на [dbo].[rollup] .Следующий пример является допустимым: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y); . |
Определяемая пользователем функция dbo.cube (arg1,...argN) or dbo.rollup(arg1,...argN) в предложении GROUP BY недопустима. Например: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); |
GROUPING SETS | Не поддерживается | Поддерживается | Поддерживается |
CUBE | Не поддерживается | Поддерживается | Не поддерживается |
ROLLUP | Не поддерживается | Поддерживается | Не поддерживается |
Общий итог, такой как GROUP BY () | Не поддерживается | Поддерживается | Поддерживается |
Функция GROUPING_ID | Не поддерживается | Поддерживается | Поддерживается |
Функция GROUPING | Поддерживается | Поддерживаемые | Поддерживается |
WITH CUBE | Поддерживается | Поддерживаемые | Поддерживается |
WITH ROLLUP | Поддерживается | Поддерживаемые | Поддерживается |
Удаление "повторяющегося" группирования с помощью конструкции WITH CUBE или WITH ROLLUP | Поддерживается | Поддерживаемые | Поддерживается |
Примеры
А. Использование простого предложения GROUP BY
В следующем примере извлекается сумма для каждого SalesOrderID
из таблицы SalesOrderDetail
. В примере используется база данных AdventureWorks.
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
B. Использование предложения GROUP BY с несколькими таблицами
В следующем примере из таблицы Address
, соединенной с таблицей EmployeeAddress
, получается количество работников для каждого города City
. В примере используется база данных AdventureWorks.
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress AS bea
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;
C. Использование предложения GROUP BY в выражениях
В следующем примере показано, как извлечь данные об общем объеме продаж за каждый год с помощью функции DATEPART
. Одно и то же выражение должно присутствовать как в списке SELECT
, так и в предложении GROUP BY
.
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
D. Использование предложения GROUP BY с предложением HAVING
В следующем примере используется предложение HAVING
, чтобы указать, какая из групп, сформированная в предложении GROUP BY
, должна быть включена в результирующий набор.
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy,OrderDate);
Примеры: Azure Synapse Analytics и Параллельное хранилище данных
Е. Базовое использование предложения GROUP BY
В следующем примере вычисляется общий объем всех продаж за каждый день. Выводится только одна строка, содержащая общий объем продаж по каждому дню.
-- Uses AdventureWorksDW
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales
GROUP BY OrderDateKey ORDER BY OrderDateKey;
F. Базовое использование указания DISTRIBUTED_AGG
В этом примере показано указание запроса DISTRIBUTED_AGG для принудительного перемещения в таблице по столбцу CustomerKey
перед выполнением статистического вычисления.
-- Uses AdventureWorksDW
SELECT CustomerKey, SUM(SalesAmount) AS sas
FROM FactInternetSales
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)
ORDER BY CustomerKey DESC;
G. Варианты синтаксиса для GROUP BY
Если в списке Select статистические вычисления, каждый столбец в списке Select должен быть включен в список GROUP BY. Вычисляемые столбцы в списке Select можно указать в списке GROUP BY (делать это необязательно). Ниже приведены примеры синтаксически правильных инструкций SELECT.
-- Uses AdventureWorks
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;
H. Использование GROUP BY с несколькими выражениями GROUP BY
В следующем примере группируются результаты с помощью нескольких критериев GROUP BY
. Если в каждой группе OrderDateKey
есть подгруппы, которые могут отличаться по DueDateKey
, для результирующего набора будет определено новое группирование.
-- Uses AdventureWorks
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey
ORDER BY OrderDateKey;
I. Использование предложения GROUP BY с предложением HAVING
В следующем примере используется предложение HAVING
, чтобы указать, какие группы, сформированные в предложении GROUP BY
, должны быть включены в результирующий набор. В результаты будут включены только группы с датами заказов в 2004 году или более поздними.
-- Uses AdventureWorks
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20040000
ORDER BY OrderDateKey;
См. также
GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
Предложение SELECT (Transact-SQL)