SELECT — GROUP BY (Transact-SQL)

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure ДаУправляемый экземпляр SQL Azure даAzure Synapse Analytics даПараллельное хранилище данных

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

Синтаксис

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL (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 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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 содержатся указанные далее строки.

Country Регион Sales
Canada Alberta 100
Canada British Columbia 200
Canada British Columbia 300
США Montana 100

Этот запрос группирует значения столбцов Country и Region и возвращает общую сумму по каждому сочетанию значений.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Результат запроса содержит 3 строки, так как существует 3 сочетания значений для Country и Region. Значение TotalSales для Canada и British Columbia является суммой двух строк.

Country Регион TotalSales
Canada Alberta 100
Canada British Columbia 500
США Montana 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. Наконец, выводится общий итог для всех строк. Результат имеет следующий вид:

Country Регион TotalSales
Canada Alberta 100
Canada British Columbia 500
Canada NULL 600
США Montana 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). Результат выглядит следующим образом:

Country Регион TotalSales
Canada Alberta 100
NULL Alberta 100
Canada British Columbia 500
NULL British Columbia 500
США Montana 100
NULL Montana 100
NULL NULL 700
Canada 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, приведет к ошибке.

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, они рассматриваются как равные и помещаются в одну группу.

Ограничения

Область применения: 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 ограничивается размером столбцов GROUP BY, статистически обрабатываемых столбцов и статистических значений, включенных в запрос. Это объясняется ограничением размера промежуточной рабочей таблицы (8060 байт), необходимой для хранения промежуточных результатов запроса. При указании CUBE или ROLLUP максимально разрешенное количество выражений группирования равно 12.

Поддержка функций предложения GROUP BY, совместимых с ISO и ANSI SQL-2006

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

  • Применение группирующих наборов в предложении GROUP BY недопустимо, если они не составляют часть явного списка GROUPING SETS. Например, предложение 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 ) or 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 Поддерживается Поддерживается Поддерживается

Примеры

A. Использование простого предложения GROUP BY

В следующем примере извлекается сумма для каждого SalesOrderID из таблицы SalesOrderDetail. В примере используется база данных AdventureWorks.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
FROM Sales.SalesOrderDetail AS sod  
GROUP BY SalesOrderID  
ORDER BY SalesOrderID;  

Б. Использование предложения 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;  

В. Использование предложения 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);  

Г. Использование предложения 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 и Parallel Data Warehouse

Д. Базовое использование предложения GROUP BY

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

-- Uses AdventureWorksDW  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales  
GROUP BY OrderDateKey ORDER BY OrderDateKey;  

Е. Базовое использование указания 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;  

Ж. Варианты синтаксиса для 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;  

З. Использование 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;  

И. Использование предложения 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)