Article
09/27/2010

GROUPING_ID (Transact-SQL)

Fonction qui calcule le niveau de regroupement. GROUPING_ID peut être utilisé uniquement dans les clauses SELECT <sélection> liste, HAVING ou ORDER BY lorsque GROUP BY est spécifié.

Icône Lien de rubrique Conventions de syntaxe Transact-SQL

Syntaxe

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

Arguments

<column_expression>
column_expression dans une clause GROUP BY.

Type de retour

int

Notes

Le <column_expression> GROUPING_ID doit correspondre exactement à l'expression dans la liste GROUP BY. Par exemple, si vous groupez par DATEPART (yyyy, <column name>), utilisez GROUPING_ID (DATEPART (yyyy, <column name>)) ; ou si vous groupez par <column name>, utilisez GROUPING_ID (<column name>).

Comparaison de GROUPING_ID () à GROUPING ()

GROUPING_ID (<column_expression> [ ,...n ]) ]) entre l'équivalent du retour de GROUPING (<column_expression>) pour chaque colonne dans sa liste de colonnes dans chaque ligne de sortie en tant que chaîne de uns et de zéros. GROUPING_ID interprète cette chaîne comme nombre de base 2 et retourne l'entier équivalent. Par exemple, considérez l'instruction suivante : SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>. Le tableau suivant indique les valeurs d'entrée et de sortie de GROUPING_ID ().

Colonnes agrégées	Entrée GROUPING_ID (a, b, c) = GROUPING(a) + GROUPING(b) + GROUPING(c)	Sortie GROUPING_ID ()
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Définition technique de GROUPING_ID ()

Chaque argument GROUPING_ID doit être un élément de la liste GROUP BY. GROUPING_ID () retourne une bitmap integer dont les N bits les plus bas peuvent être activés. Un bit activé indique que l'argument correspondant n'est pas une colonne de regroupement pour la ligne de sortie donnée. Le bit d'ordre le plus bas correspond à l'argument N, et le bit d'ordre le plus bas N-1ème correspond à l'argument 1.

Équivalents de GROUPING_ID ()

Pour une requête de regroupement unique, GROUPING (<column_expression>) est équivalent à GROUPING_ID (<column_expression>) et tous deux retournent 0.

Par exemple, les instructions suivantes sont équivalentes :

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

Exemples

A. Utilisation de GROUPING_ID pour identifier des niveaux de regroupement

L'exemple suivant retourne le nombre d'employés par Name, Title, Name, et total de société. GROUPING_ID() est utilisé pour créer une valeur pour chaque ligne dans la colonne Title qui identifie son niveau d'agrégation.

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);

B. Utilisation de GROUPING_ID pour filtrer un jeu de résultats

Exemple simple

Dans le code suivant, pour retourner uniquement les lignes qui ont un nombre d'employés par titre, supprimez les caractères de commentaire de HAVING GROUPING_ID(D.Name, E.Title); = 0. Pour retourner uniquement les lignes avec un nombre d'employés par département, supprimez les caractères de commentaire de 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

Voici le jeu de résultats non filtré.

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

Exemple complexe

Dans l'exemple suivant, GROUPING_ID() est utilisé pour filtrer un jeu de résultats qui contient plusieurs niveaux de regroupement par niveau de regroupement. Du code semblable peut être utilisé pour créer une vue qui a plusieurs niveaux de regroupement et une procédure stockée qui appellent la vue en passant un paramètre qui filtre la vue par niveau de regroupement.

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;

C. Utilisation de GROUPING_ID () avec ROLLUP et CUBE pour identifier des niveaux de regroupement

Le code dans les exemples suivants illustre l'utilisation de GROUPING() pour calculer la colonne Bit Vector(base-2). GROUPING_ID() est utilisé pour calculer la colonne Integer Equivalent correspondante. L'ordre des colonnes dans la fonction GROUPING_ID() est l'inverse de celui des colonnes concaténées par la fonction GROUPING().

Dans ces exemples, GROUPING_ID() est utilisé pour créer une valeur pour chaque ligne dans la colonne Grouping Level afin d'identifier le niveau de regroupement. Les niveaux de regroupement ne sont pas toujours une liste consécutive d'entiers commençant à 1 (0, 1, 2,...n).

[!REMARQUE]

GROUPING et GROUPING_ID peuvent être utilisés dans une clause HAVING pour filtrer un jeu de résultats.

Exemple ROLLUP

Dans cet exemple, tous les niveaux de regroupement n'apparaissent pas comme dans l'exemple CUBE suivant. Si l'ordre des colonnes dans la liste ROLLUP est modifié, les niveaux de valeurs dans la colonne Grouping Level devront également être modifiés.

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);

Voici un jeu de résultats partiel.

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

Exemple CUBE

Dans cet exemple, la fonction GROUPING_ID() est utilisée pour créer une valeur pour chaque ligne dans la colonne Grouping Level afin d'identifier le niveau de regroupement.

Contrairement à ROLLUP dans l'exemple précédent, CUBE sort tous les niveaux de regroupement. Si l'ordre des colonnes dans la liste CUBE est modifié, les niveaux de valeurs dans la colonne Grouping Level devront également être modifiés.

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);

Voici un jeu de résultats partiel.

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