GROUPING_ID (Transact-SQL)

Статья
10/16/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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>), используете или группирование по<column name>, используйтеGROUPING_ID (DATEPART (yyyy, <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

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

А. Определение уровней группировки с помощью GROUPING_ID

В следующем примере возвращается количество сотрудников Name и Titleобщий объем компании в AdventureWorks2022 базе данных. В этом примере функция 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;

Ниже приведен нефильтрованный результирующий набор.

Имя.	Заголовок	Grouping Level	Employee Count	Имя.
Document Control	Control Specialist	0	2	Document Control
Document Control	Document Control Assistant	0	2	Document Control
Document Control	Document Control Manager	0	1	Document Control
Document Control	`NULL`	1	5	Document Control
Facilities and Maintenance	Facilities Administrative Assistant	0	1	Facilities and Maintenance
Facilities and Maintenance	Facilities Manager	0	1	Facilities and Maintenance
Facilities and Maintenance	Janitor	0	4	Facilities and Maintenance
Facilities and Maintenance	Maintenance Supervisor	0	1	Facilities and Maintenance
Facilities and Maintenance	`NULL`	1	7	Facilities and Maintenance
`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);

Далее представлен частичный результирующий набор.

Год	Месяц	День	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452,6066	000	0	Year Month Day
2007	1	2	21772,3494	000	0	Year Month Day
2007	2	1	2705653,5913	000	0	Year Month Day
2007	2	2	21684,4068	000	0	Year Month Day
2008	1	1	1908122,0967	000	0	Year Month Day
2008	1	2	46458,0691	000	0	Year Month Day
2008	2	1	3108771,9729	000	0	Year Month Day
2008	2	2	54598,5488	000	0	Year Month Day
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);

Далее представлен частичный результирующий набор.

Год	Месяц	День	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452,6066	000	0	Year Month Day
2007	1	2	21772,3494	000	0	Year Month Day
2007	2	1	2705653,5913	000	0	Year Month Day
2007	2	2	21684,4068	000	0	Year Month Day
2008	1	1	1908122,0967	000	0	Year Month Day
2008	1	2	46458,0691	000	0	Year Month Day
2008	2	1	3108771,9729	000	0	Year Month Day
2008	2	2	54598,5488	000	0	Year Month Day
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	Year Day
2007	`NULL`	2	43456,7562	010	2	Year Day
2008	`NULL`	1	5016894,0696	010	2	Year Day
2008	`NULL`	2	101056,6179	010	2	Year Day
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	Общий итог

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

GROUPING_ID (Transact-SQL)

Синтаксис

Аргументы

<column_expression>

Типы возвращаемых данных

Замечания

Сравнение GROUPING_ID() с GROUPING()

Техническое определение GROUPING_ID()

эквиваленты GROUPING_ID()

Оператор A

Оператор B

Примеры

А. Определение уровней группировки с помощью GROUPING_ID

B. Фильтрация результирующих наборов с помощью GROUPING_ID

Простой пример

Сложный пример

В. Использование GROUPING_ID() с ROLLUP и CUBE для идентификации уровней группировки

Пример ROLLUP

Пример КУБА

Обратная связь

Дополнительные ресурсы

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

GROUPING_ID (Transact-SQL)

Синтаксис

Аргументы

<column_expression>

Типы возвращаемых данных

Замечания

Сравнение GROUPING_ID() с GROUPING()

Техническое определение GROUPING_ID()

эквиваленты GROUPING_ID()

Оператор A

Оператор B

Примеры

А. Определение уровней группировки с помощью GROUPING_ID

B. Фильтрация результирующих наборов с помощью GROUPING_ID

Простой пример

Сложный пример

В. Использование GROUPING_ID() с ROLLUP и CUBE для идентификации уровней группировки

Пример ROLLUP

Пример КУБА

Связанный контент

Обратная связь

Дополнительные ресурсы