GROUP BY (Transact-SQL)

Группирует выбранный набор строк для получения набора сводных строк по значениям одного или нескольких столбцов или выражений в SQL Server 2008 R2. Возвращается одна строка для каждой группы. Агрегатные функции в списке <select> предложения SELECT предоставляют сведения о каждой группе, а не об отдельных строках.

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

В этом разделе предложение GROUP BY можно описать как общее или простое.

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

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

Синтаксические обозначения в Transact-SQL (Transact-SQL)

Синтаксис

        ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> )<cube spec> ::=
    CUBE ( <composite element list> )<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> )<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> )<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> )<empty group> ::=()<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

        Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

Аргументы

• <column_expression>
  Выражение, на основании которого выполняется операция группирования.

• ROLLUP ( )
  Формирует статистические строки простого предложения GROUP BY и строки подытогов или строки со статистическими вычислениями высокого уровня, а также строки общего итога.

  Количество возвращаемых группирований равно количеству выражений в <составном списке элементов> плюс один. Например, рассмотрим следующую инструкцию.

  SELECT a, b, c, SUM ( <expression> )
  FROM T
  GROUP BY ROLLUP (a,b,c);
  

  Для каждого уникального сочетания значений (a, b, c), (a, b) и (a) формируется одна строка с подытогом. Вычисляется также строка общего итога.

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

• CUBE ( )
  Формирует статистические строки простого предложения GROUP BY, строки со статистическими вычислениями высокого уровня конструкции ROLLUP и строки с результатами перекрестных вычислений.

  Выходные данные CUBE являются группированием для всех перестановок выражений в <составном списке элементов>.

  Количество формируемых группирований равно (2n), где n — количество выражений в <составном списке элементов>. Например, рассмотрим следующую инструкцию.

  SELECT a, b, c, SUM (<expression>)
  FROM T
  GROUP BY CUBE (a,b,c);
  

  Формируется одна строка для каждого уникального сочетания значений (a, b, c), (a, b), (a, c), (b, c), (a), (b) и (c) с подытогом для каждой строки и строкой общего итога.

  Выходные данные CUBE не зависят от порядка столбцов.

• GROUPING SETS ( )
  Указывает несколько группирований данных в одном запросе. Выполняется статистическая обработка только указанных групп, а не полного набора статистических данных, формируемых с помощью конструкций CUBE или ROLLUP. Результаты эквивалентны тем, что формируются с применением конструкции UNION ALL к указанным группам. Конструкция GROUPING SETS может содержать единственный элемент или список элементов. В конструкции GROUPING SETS могут быть указаны группирования, эквивалентные тем, которые возвращает конструкция ROLLUP или CUBE. Примеры см. в разделе Эквиваленты GROUPING SETS. <Список элементов группирующего набора> может содержать конструкцию ROLLUP или CUBE.

• ( )
  Пустая группа формирует итог.

Синтаксис, несовместимый с ISO

• ALL
  В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется. Включает все группы и результирующие наборы (даже не имеющие строк), которые удовлетворяют условию поиска, указанному в предложении WHERE. Если задан аргумент ALL, для сводных столбцов групп, не удовлетворяющие условию поиска, возвращаются значения NULL. Аргумент ALL нельзя указывать с операторами CUBE или ROLLUP.

  Предложение GROUP BY ALL не поддерживается в запросах с доступом к удаленным таблицам, если в запросе присутствует также предложение WHERE. Применение предложения GROUP BY ALL к столбцам, имеющим атрибут FILESTREAM, приведет к ошибке.

• group_by_expression
  Выражение, по которому выполняется группирование. Аргумент group_by_expression также называется столбцом группирования. Аргумент group_by expression может быть столбцом или нестатистическим выражением, которое ссылается на столбец, возвращаемый предложением FROM. Псевдоним столбца, определенный в списке SELECT, нельзя использовать при указании столбца группирования.

  Примечание
  Столбцы типа text, ntext и image нельзя использовать в аргументе group_by_expression.

  Для предложений GROUP BY, не содержащих операторов CUBE или ROLLUP, количество элементов group_by_expression ограничивается размером столбцов GROUP BY, статистически обрабатываемых столбцов и статистических значений, включенных в запрос. Это объясняется ограничением размера промежуточной рабочей таблицы (8 060 байт), необходимой для хранения промежуточных результатов запроса. При указании CUBE или ROLLUP максимально разрешенное количество выражений группирования равно 12.

  Методы для типа данных xml нельзя указывать непосредственно в аргументе group_by_expression. Вместо этого следует создать ссылку на пользовательскую функцию, которая включает методы для типа данных xml или ссылается на вычисляемый столбец, использующий эти методы.

• WITH CUBE
  В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется. Указывает, что помимо строк, предоставляемых GROUP BY, в результирующий набор включаются сводные строки. Сводная строка GROUP BY возвращается для всех возможных сочетаний групп и подгрупп в результирующем наборе. Чтобы определить, представляют ли значения NULL в результирующем наборе сводные значения GROUP BY, используйте функцию GROUPING.

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

• WITH ROLLUP
  В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется. Указывает, что помимо строк, предоставляемых GROUP BY, в результирующий набор включаются сводные строки. Группы обобщаются в иерархическом порядке, начиная с самого нижнего уровня в группе и заканчивая самым верхним. Иерархия группы определяется порядком, в котором заданы столбцы, по которым производится группирование. Изменение порядка столбцов, по которым производится группирование, может повлиять на количество строк в результирующем наборе.

  Важно!
  Статистические вычисления с ключевым словом DISTINCT, например AVG(DISTINCT column_name), COUNT(DISTINCT column_name) и SUM(DISTINCT column_name), при использовании CUBE и ROLLUP не поддерживаются. Если они используются, то компонент SQL Server Database Engine возвращает сообщение об ошибке и отменяет запрос.

Замечания

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

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

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

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

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

Нельзя использовать предложение GROUP BY с псевдонимом для замены имени столбца в предложении AS, если этот псевдоним не заменяет имя столбца в производной таблице в предложении FROM.

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

С конструкциями ROLLUP, CUBE и GROUPING SETS поддерживаются статистические функции с ключевым словом DISTINCT, например AVG (DISTINCT column_name), COUNT (DISTINCT column_name) и SUM (DISTINCT column_name).

Конструкции ROLLUP, CUBE и GROUPING SETS не могут быть указаны в индексированном представлении.

Конструкции GROUP BY или HAVING не могут использоваться непосредственно для столбцов типа ntext, text или image. Эти столбцы могут использоваться в качестве аргументов в функциях, которые возвращают значение другого типа данных, таких как SUBSTRING() и CAST().

Методы данных типа xml не могут быть указаны непосредственно в аргументе <column_expression>. Вместо этого следует создать ссылку на пользовательскую функцию, которая включает методы для типа данных xml или ссылается на вычисляемый столбец, использующий эти методы.

Ограничения предложения GROUP BY для конструкций GROUPING SETS, ROLLUP и CUBE

Ограничения синтаксиса

Нельзя использовать конструкции GROUPING SETS в предложении GROUP BY, если они не входят в состав списка GROUPING SETS. Например, форма GROUP BY C1, (C2,..., Cn) недопустима, но форма GROUP BY GROUPING SETS (C1, (C2, ..., Cn)) является допустимой.

Нельзя использовать ключевые слова GROUPING SETS внутри самих конструкций GROUPING SETS. Например, выражение GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) недопустимо.

В предложении GROUP BY с ключевыми словами ROLLUP, CUBE или GROUPING SETS нельзя использовать ключевые слова ALL, WITH CUBE и WITH ROLLUP, несовместимые с ISO.

Ограничения размера

Для простых предложений GROUP BY количество выражений не ограничено.

Для предложения GROUP BY, в котором используются ключевые слова ROLLUP, CUBE или GROUPING SETS, максимальное количество выражений равно 32, а максимальное количество группирующих наборов, которые могут быть созданы, равно 4096 (212). Следующие примеры завершаются неудачно, поскольку предложение GROUP BY является слишком сложным.

• В следующих примерах формируются 8192 (213) группирующих наборов.

  GROUP BY CUBE (a1, ..., a13) 
  GROUP BY a1, ..., a13 WITH CUBE 
  

• В следующем примере формируются 4097 (212 + 1) группирующих наборов.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
  

• В следующем примере также формируются 4097 (212 + 1) группирующих наборов. И функция CUBE (), и группирующий набор () формируют строку общего итога, а повторяющиеся группирующие наборы не устраняются.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
  

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

В SQL Server 2008 и более поздних версиях предложение GROUP BY не может содержать вложенный запрос в выражении, используемом для группирования по списку. Возвращается ошибка 144.

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

• Применение группирующих наборов в предложении GROUP BY недопустимо, если они не составляют часть явного списка GROUPING SETS. Например, предложение GROUP BY Column1, (Column2, ...ColumnN) допускается в этом стандарте, но не в SQL Server. Можно использовать выражение 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, но не в SQL Server. В SQL Server 2008 и более поздних версиях можно использовать выражение 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 Server и уровня совместимости базы данных.

Функция	Службы SQL Server 2005 Integration Services	Уровень совместимости SQL Server 2008 — 100	Уровень совместимости SQL Server 2008 — 90 или меньше
Статистические функции DISTINCT	Не поддерживаются для конструкций WITH CUBE или WITH ROLLUP.	Поддерживаются для конструкций WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE или ROLLUP.	То же, что и уровень совместимости SQL Server 2008, равный 100.
Определяемая пользователем функция с именем CUBE или ROLLUP в предложении GROUP BY	Определяемая пользователем функция dbo.cube(arg1,...argN) или 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) или 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	Поддерживается	Поддерживается	Поддерживается

Примеры

Примеры использования ключевых слов GROUPING SETS, ROLLUP и CUBE см. в разделе Использование предложения GROUP BY с операторами ROLLUP, CUBE и GROUPING SETS.

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

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

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

Б. Использование предложения GROUP BY с несколькими таблицами

В следующем примере из таблицы Address, соединенной с таблицей EmployeeAddress, получается количество работников для каждого города City.

USE AdventureWorks2008R2;
GO
SELECT a.City, COUNT(bea.AddressID) AS 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.

USE AdventureWorks2008R2;
GO
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, должна быть включена в результирующий набор.

USE AdventureWorks2008R2;
GO
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);

См. также

Справочник

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

Выражение SELECT (Transact-SQL)

Основные понятия

Использование предложения GROUP BY с операторами ROLLUP, CUBE и GROUPING SETS

Эквиваленты GROUPING SETS

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