Заметка
Доступ к этой странице требует авторизации. Вы можете попробовать войти в систему или изменить каталог.
Доступ к этой странице требует авторизации. Вы можете попробовать сменить директорию.
Применимо к:
SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureБаза данных SQL в Microsoft Fabric
GROUPING_ID Функция, вычисляющая уровень группировки.
GROUPING_IDможно использовать только в списке SELECT <select> или HAVINGORDER BY предложениях при GROUP BY указании.
Соглашения о синтаксисе Transact-SQL
Синтаксис
GROUPING_ID ( <column_expression> [ , ...n ] )
Аргументы
<column_expression>
Column_expression в предложении SELECT — GROUP BY.
Типы возвращаемых данных
int
Замечания
Должно GROUPING_ID <column_expression> точно соответствовать выражению в списке GROUP BY . Например, если вы группирование поDATEPART (yyyy, <column name>), используете или группирование поGROUPING_ID (DATEPART (yyyy, <column name>)), используйте<column name>GROUPING_ID (<column name>).
Сравнение GROUPING_ID() с GROUPING()
GROUPING_ID (<column_expression> [ , ...n ]) введите эквивалент возвращаемого GROUPING (<column_expression>) значения для каждого столбца в списке столбцов в каждой выходной строке в виде строки из одного и нуля.
GROUPING_ID интерпретирует такую строку как число base-2 и возвращает эквивалентное целое число.
Например, рассмотрим следующую инструкцию:
SELECT a, b, c, SUM(d),
GROUPING_ID(a, b, c)
FROM T
GROUP BY <group_by_list>
В этой таблице показаны входные и выходные GROUPING_ID() значения.
| Статистически обработанные столбцы | Входные данные GROUPING_ID (a, b, c) = GROUPING(a) + GROUPING(b) + GROUPING(c) | выходные данные GROUPING_ID() |
|---|---|---|
a |
100 |
4 |
b |
010 |
2 |
c |
001 |
1 |
ab |
110 |
6 |
ac |
101 |
5 |
bc |
011 |
3 |
abc |
111 |
7 |
Техническое определение GROUPING_ID()
Каждый GROUPING_ID аргумент должен быть элементом GROUP BY списка.
GROUPING_ID()возвращает целочисленное растровое изображение, наименьшее значение которого может быть освещено. Бит с освещенным значением указывает, что соответствующий аргумент не является столбцом группировки для заданной выходной строки. Бит наименьшего порядка соответствует аргументу n, а бит наименьшего порядка n-1 соответствует аргументу 1.
эквиваленты GROUPING_ID()
Для одного запроса GROUPING (<column_expression>) группирования эквивалентен GROUPING_ID (<column_expression>)и возвращаемым 0.
Например, следующие инструкции эквивалентны.
Оператор A
SELECT GROUPING_ID(A, B)
FROM T
GROUP BY CUBE(A, B)
Оператор B
SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A, B
Примеры
Примеры кода в этой статье используют базу данных образца AdventureWorks2025 или AdventureWorksDW2025, которую можно скачать с домашней страницы образцов и проектов сообщества Microsoft SQL Server и.
А. Определение уровней группировки с помощью GROUPING_ID
В следующем примере возвращается количество сотрудников Name и Titleобщий объем компании в AdventureWorks2025 базе данных. В этом примере функция GROUPING_ID() используется для создания значения, обозначающего уровень агрегирования, в каждой строке столбца Title.
SELECT D.Name,
CASE
WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle
WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name
WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'
ELSE N'Unknown'
END AS N'Job Title',
COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12, 14)
GROUP BY ROLLUP(D.Name, E.JobTitle);
B. Фильтрация результирующих наборов с помощью GROUPING_ID
Простой пример
В следующем коде, чтобы вернуть только строки с количеством сотрудников по названию, удалите символы комментариев из HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Чтобы получить только строки с данными о количестве сотрудников в разных отделах, удалите символы комментария из предложения HAVING GROUPING_ID(D.Name, E.JobTitle) = 1;.
SELECT D.Name,
E.JobTitle,
GROUPING_ID(D.Name, E.JobTitle) AS [Grouping Level],
COUNT(E.BusinessEntityID) AS [Employee Count]
FROM HumanResources.Employee AS E
INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department AS D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12, 14)
GROUP BY ROLLUP(D.Name, E.JobTitle)
-- HAVING GROUPING_ID(D.Name, E.JobTitle) = 0; -- All titles
-- HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; -- Group by Name;
Ниже приведен нефильтрованный результирующий набор.
| Имя. | Заголовок | Уровень группировки | Численность сотрудников | Имя. |
|---|---|---|---|---|
| Контроль документов | Специалист по управлению | 0 | 2 | Контроль документов |
| Контроль документов | Помощник по контролю документов | 0 | 2 | Контроль документов |
| Контроль документов | Менеджер управления документами | 0 | 1 | Контроль документов |
| Контроль документов | NULL |
1 | 5 | Контроль документов |
| Инфраструктура и техническое обслуживание | Административный помощник по объектам | 0 | 1 | Инфраструктура и техническое обслуживание |
| Инфраструктура и техническое обслуживание | Менеджер по объектам | 0 | 1 | Инфраструктура и техническое обслуживание |
| Инфраструктура и техническое обслуживание | Janitor | 0 | 4 | Инфраструктура и техническое обслуживание |
| Инфраструктура и техническое обслуживание | Руководитель технического обслуживания | 0 | 1 | Инфраструктура и техническое обслуживание |
| Инфраструктура и техническое обслуживание | NULL |
1 | 7 | Инфраструктура и техническое обслуживание |
NULL |
NULL |
3 | 12 | NULL |
Сложный пример
В следующем примере функция GROUPING_ID() используется для фильтрации результирующего набора, содержащего несколько уровней группирования, по уровням группирования. Аналогичный код можно использовать для создания представления с несколькими уровнями группировки и хранимой процедуры, которая вызывает представление, передав параметр, фильтрующий представление по уровню группировки.
DECLARE @Grouping NVARCHAR(50);
DECLARE @GroupingLevel SMALLINT;
SET @Grouping = N'CountryRegionCode Total';
SELECT @GroupingLevel = (
CASE @Grouping
WHEN N'Grand Total' THEN 15
WHEN N'SalesPerson Total' THEN 14
WHEN N'Store Total' THEN 13
WHEN N'Store SalesPerson Total' THEN 12
WHEN N'CountryRegionCode Total' THEN 11
WHEN N'Group Total' THEN 7
ELSE N'Unknown'
END
);
SELECT T.[Group],
T.CountryRegionCode,
S.Name AS N'Store',
(
SELECT P.FirstName + ' ' + P.LastName
FROM Person.Person AS P
WHERE P.BusinessEntityID = H.SalesPersonID
) AS N'Sales Person',
SUM(TotalDue) AS N'TotalSold',
CAST(GROUPING(T.[Group]) AS CHAR(1)) + CAST(GROUPING(T.CountryRegionCode) AS CHAR(1)) + CAST(GROUPING(S.Name) AS CHAR(1)) + CAST(GROUPING(H.SalesPersonID) AS CHAR(1)) AS N'GROUPING base-2',
GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) AS N'GROUPING_ID',
CASE
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 15
THEN N'Grand Total'
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 14
THEN N'SalesPerson Total'
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 13
THEN N'Store Total'
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 12
THEN N'Store SalesPerson Total'
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 11
THEN N'CountryRegionCode Total'
WHEN GROUPING_ID((T.[Group]), (T.CountryRegionCode), (S.Name), (H.SalesPersonID)) = 7
THEN N'Group Total'
ELSE N'Error'
END AS N'Level'
FROM Sales.Customer AS C
INNER JOIN Sales.Store AS S
ON C.StoreID = S.BusinessEntityID
INNER JOIN Sales.SalesTerritory AS T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader AS H
ON C.CustomerID = H.CustomerID
GROUP BY GROUPING SETS(
(S.Name,H.SalesPersonID),
(H.SalesPersonID),
(S.Name),
(T.[Group]),
(T.CountryRegionCode),
()
)
HAVING GROUPING_ID(
(T.[Group]),
(T.CountryRegionCode),
(S.Name),
(H.SalesPersonID)
) = @GroupingLevel
ORDER BY
GROUPING_ID(S.Name, H.SalesPersonID),
GROUPING_ID(
(T.[Group]),
(T.CountryRegionCode),
(S.Name),
(H.SalesPersonID)
) ASC;
В. Использование GROUPING_ID() с ROLLUP и CUBE для идентификации уровней группировки
Код в следующих примерах демонстрирует использование GROUPING() для вычисления столбца Bit Vector(base-2) . Функция GROUPING_ID() служит для вычисления соответствующего столбца Integer Equivalent. Порядок столбцов в функции GROUPING_ID() противоположен порядку столбцов, для объединения которых применяется функция GROUPING().
В этих примерах функция GROUPING_ID() используется для создания значения, обозначающего уровень группирования, в каждой строке столбца Grouping Level. Уровни группировки не всегда являются последовательными целыми числами, начинающимися с 1 (0, 1, 2, ...n).
Примечание.
GROUPING и GROUPING_ID его можно использовать в HAVING предложении для фильтрации результирующих наборов.
Пример ROLLUP
В этом примере все уровни группировки не отображаются, как они выполняются в следующем CUBE примере. Если порядок столбцов в ROLLUP списке изменен, необходимо также изменить значения уровня в столбце Grouping Level .
SELECT DATEPART(yyyy, OrderDate) AS N'Year',
DATEPART(mm, OrderDate) AS N'Month',
DATEPART(dd, OrderDate) AS N'Day',
SUM(TotalDue) AS N'Total Due',
CAST(GROUPING(DATEPART(dd, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(mm, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(yyyy, OrderDate)) AS CHAR(1)) AS N'Bit Vector(base-2)',
GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) AS N'Integer Equivalent',
CASE
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 0
THEN N'Year Month Day'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 1
THEN N'Year Month'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 2
THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 3
THEN N'Year'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 4
THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 5
THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 6
THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 7
THEN N'Grand Total'
ELSE N'Error'
END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy, OrderDate) IN (N'2007', N'2008')
AND DATEPART(mm, OrderDate) IN (1, 2)
AND DATEPART(dd, OrderDate) IN (1, 2)
GROUP BY ROLLUP(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm, OrderDate), DATEPART(yyyy, OrderDate), DATEPART(dd, OrderDate)),
DATEPART(yyyy, OrderDate),
DATEPART(mm, OrderDate),
DATEPART(dd, OrderDate);
Далее представлен частичный результирующий набор.
| Год | Месяц | День | Итоговый счет | Битовый вектор (основа-2) | Целочисленный эквивалент | Уровень группировки |
|---|---|---|---|---|---|---|
| 2007 | 1 | 1 | 1497452,6066 | 000 | 0 | День года, месяца |
| 2007 | 1 | 2 | 21772,3494 | 000 | 0 | День года, месяца |
| 2007 | 2 | 1 | 2705653,5913 | 000 | 0 | День года, месяца |
| 2007 | 2 | 2 | 21684,4068 | 000 | 0 | День года, месяца |
| 2008 | 1 | 1 | 1908122,0967 | 000 | 0 | День года, месяца |
| 2008 | 1 | 2 | 46458,0691 | 000 | 0 | День года, месяца |
| 2008 | 2 | 1 | 3108771,9729 | 000 | 0 | День года, месяца |
| 2008 | 2 | 2 | 54598,5488 | 000 | 0 | День года, месяца |
| 2007 | 1 | NULL |
1519224,956 | 100 | 1 | Год Месяц |
| 2007 | 2 | NULL |
2727337,9981 | 100 | 1 | Год Месяц |
| 2008 | 1 | NULL |
1954580,1658 | 100 | 1 | Год Месяц |
| 2008 | 2 | NULL |
3163370,5217 | 100 | 1 | Год Месяц |
| 2007 | NULL |
NULL |
4246562,9541 | 110 | 3 | Год |
| 2008 | NULL |
NULL |
5117950,6875 | 110 | 3 | Год |
NULL |
NULL |
NULL |
9364513,6416 | 111 | 7 | Общий итог |
Пример КУБА
В этом примере функция GROUPING_ID() используется для создания значения, которое обозначает уровень группирования, в каждой строке столбца Grouping Level.
В отличие от оператора ROLLUP в предыдущем примере, оператор CUBE выводит все уровни группирования. Если порядок столбцов в CUBE списке изменен, необходимо также изменить значения уровня в столбце Grouping Level .
SELECT DATEPART(yyyy, OrderDate) AS N'Year',
DATEPART(mm, OrderDate) AS N'Month',
DATEPART(dd, OrderDate) AS N'Day',
SUM(TotalDue) AS N'Total Due',
CAST(GROUPING(DATEPART(dd, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(mm, OrderDate)) AS CHAR(1)) + CAST(GROUPING(DATEPART(yyyy, OrderDate)) AS CHAR(1)) AS N'Bit Vector(base-2)',
GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) AS N'Integer Equivalent',
CASE
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 0
THEN N'Year Month Day'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 1
THEN N'Year Month'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 2
THEN N'Year Day'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 3
THEN N'Year'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 4
THEN N'Month Day'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 5
THEN N'Month'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 6
THEN N'Day'
WHEN GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)) = 7
THEN N'Grand Total'
ELSE N'Error'
END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy, OrderDate) IN (N'2007', N'2008')
AND DATEPART(mm, OrderDate) IN (1, 2)
AND DATEPART(dd, OrderDate) IN (1, 2)
GROUP BY CUBE(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy, OrderDate), DATEPART(mm, OrderDate), DATEPART(dd, OrderDate)),
DATEPART(yyyy, OrderDate),
DATEPART(mm, OrderDate),
DATEPART(dd, OrderDate);
Далее представлен частичный результирующий набор.
| Год | Месяц | День | Итоговый счет | Битовый вектор (основа-2) | Целочисленный эквивалент | Уровень группировки |
|---|---|---|---|---|---|---|
| 2007 | 1 | 1 | 1497452,6066 | 000 | 0 | День года, месяца |
| 2007 | 1 | 2 | 21772,3494 | 000 | 0 | День года, месяца |
| 2007 | 2 | 1 | 2705653,5913 | 000 | 0 | День года, месяца |
| 2007 | 2 | 2 | 21684,4068 | 000 | 0 | День года, месяца |
| 2008 | 1 | 1 | 1908122,0967 | 000 | 0 | День года, месяца |
| 2008 | 1 | 2 | 46458,0691 | 000 | 0 | День года, месяца |
| 2008 | 2 | 1 | 3108771,9729 | 000 | 0 | День года, месяца |
| 2008 | 2 | 2 | 54598,5488 | 000 | 0 | День года, месяца |
| 2007 | 1 | NULL |
1519224,956 | 100 | 1 | Год Месяц |
| 2007 | 2 | NULL |
2727337,9981 | 100 | 1 | Год Месяц |
| 2008 | 1 | NULL |
1954580,1658 | 100 | 1 | Год Месяц |
| 2008 | 2 | NULL |
3163370,5217 | 100 | 1 | Год Месяц |
| 2007 | NULL |
1 | 4203106,1979 | 010 | 2 | Годовой день |
| 2007 | NULL |
2 | 43456,7562 | 010 | 2 | Годовой день |
| 2008 | NULL |
1 | 5016894,0696 | 010 | 2 | Годовой день |
| 2008 | NULL |
2 | 101056,6179 | 010 | 2 | Годовой день |
| 2007 | NULL |
NULL |
4246562,9541 | 110 | 3 | Год |
| 2008 | NULL |
NULL |
5117950,6875 | 110 | 3 | Год |
NULL |
1 | 1 | 3405574,7033 | 001 | 4 | Месяц День |
NULL |
1 | 2 | 68230,4185 | 001 | 4 | Месяц День |
NULL |
2 | 1 | 5814425,5642 | 001 | 4 | Месяц День |
NULL |
2 | 2 | 76282,9556 | 001 | 4 | Месяц День |
NULL |
1 | NULL |
3473805,1218 | 101 | 5 | Месяц |
NULL |
2 | NULL |
5890708,5198 | 101 | 5 | Месяц |
NULL |
NULL |
1 | 9220000,2675 | 011 | 6 | День |
NULL |
NULL |
2 | 144513,3741 | 011 | 6 | День |
NULL |
NULL |
NULL |
9364513,6416 | 111 | 7 | Общий итог |