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

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 ]

Аргументы

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)