• Статья

GROUPING_ID (Transact-SQL)

Представляет собой функцию, которая вычисляет уровень группирования. Если задано предложение GROUP BY, функция GROUPING_ID может использоваться только в предложениях SELECT <список выборки>, HAVING или ORDER BY.

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

GROUPING_ID ( <column_expression>[ ,...n ] )

Аргументы

  • <column_expression>
    Представляет собой аргумент column_expression в предложении GROUP BY.

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

int

Замечания

Аргумент <column_expression> функции GROUPING_ID должен точно соответствовать выражению в списке 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 интерпретирует эту строку как двоичное число и выполняет возврат эквивалентного целого числа. Рассмотрим, например, следующую инструкцию: 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 () возвращает битовую карту типа integer, в которой N самых младших битов могут быть установлены. Установленный bit указывает, что соответствующий аргумент не является столбцом группирования для указанной выходной строки. Самый младший bit соответствует аргументу N, а самый младший N-1ыйbit соответствует аргументу 1.

Функции, эквивалентные функции GROUPING_ID ()

Применительно к одиночному запросу группирования функция GROUPING (<column_expression>) эквивалентна GROUPING_ID (<column_expression>) и обе эти функции возвращают 0.

Например, следующие инструкции эквивалентны.

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,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

Примеры

А. Использование функции GROUPING_ID для обозначения уровней группирования

В следующем примере показано, как определить итоговое значение количества сотрудников по столбцам Name и Title, Name,, а также как вычислить общее количество сотрудников во всей компании. Функция GROUPING_ID() используется для создания для каждой строки столбца Title значения, которое обозначает его уровень статистической обработки.

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title);

Б. Использование функции GROUPING_ID для фильтрации результирующего набора

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

Чтобы воспользоваться следующим кодом для получения только тех строк, в которых подсчитаны данные о количестве сотрудников с разными должностями, удалите символы комментария из предложения HAVING GROUPING_ID(D.Name, E.Title); = 0. Чтобы получить только строки с данными о количестве сотрудников в разных отделах, удалите символы комментария из предложения HAVING GROUPING_ID(D.Name, E.Title) = 1;.

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

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

Name

Title

Grouping Level

Employee Count

Name

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

USE AdventureWorks;
GO
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 C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = 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 C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.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 также должны быть изменены.

USE AdventureWorks;
GO
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'2003',N'2004')
    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);

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

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

NULL

NULL

10984529

111

7

Grand Total

Пример применения предложения CUBE

В этом примере функция GROUPING_ID() используется для создания значения, которое обозначает уровень группирования, в каждой строке столбца Grouping Level.

В отличие от оператора ROLLUP в предыдущем примере, оператор CUBE выводит все уровни группирования. Если порядок столбцов в списке CUBE изменяется, значения уровня в столбце Grouping Level также должны быть изменены.

USE AdventureWorks;
GO
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'2003',N'2004')
    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);

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

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

1

4947613

010

2

Year Day

2003

NULL

2

43456.76

010

2

Year Day

2004

NULL

1

5892402

010

2

Year Day

2004

NULL

2

101056.6

010

2

Year Day

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

1

1

4001589

001

4

Month Day

NULL

1

2

68230.42

001

4

Month Day

NULL

2

1

6838427

001

4

Month Day

NULL

2

2

76282.96

001

4

Month Day

NULL

1

NULL

4069819

101

5

Month

NULL

2

NULL

6914710

101

5

Month

NULL

NULL

1

10840016

011

6

Day

NULL.

NULL

2

144513.4

011

6

Day

NULL

NULL

NULL

10984529

111

7

Grand Total