GROUPING_ID (Transact-SQL)

Article
10/16/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

GROUPING_ID Fonction qui calcule le niveau de regroupement. GROUPING_ID ne peut être utilisé que dans la SELECT <select> liste, HAVINGou ORDER BY les clauses quand GROUP BY elles sont spécifiées.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

<column_expression>

Un column_expression dans une clause SELECT - GROUP BY.

Types de retour

int

Notes

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

Comparer GROUPING_ID() à GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) entre l’équivalent du GROUPING (<column_expression>) retour de chaque colonne dans sa liste de colonnes dans chaque ligne de sortie, sous la forme d’une chaîne de zéros et d’un. GROUPING_ID interprète cette chaîne en tant que nombre en 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>

Ce tableau affiche les valeurs d’entrée GROUPING_ID() et de sortie.

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 GROUPING_ID argument doit être un élément de la GROUP BY liste. GROUPING_ID()retourne une bitmap entière dont les n bits les plus bas peuvent être allumés. Un bit éclairé indique que l’argument correspondant n’est pas une colonne de regroupement pour la ligne de sortie donnée. Le bit de l’ordre le plus bas correspond à l’argument n, et le bit n-1^de l’ordre le plus bas correspond à l’argument 1.

équivalents GROUPING_ID()

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

Par exemple, les instructions suivantes sont équivalentes :

Instruction A

SELECT GROUPING_ID(A, B)
FROM T
GROUP BY CUBE(A, B)

Instruction 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

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

A. Utiliser GROUPING_ID pour identifier les niveaux de regroupement

L’exemple suivant retourne le nombre d’employés par Name et Titlele total de l’entreprise dans la AdventureWorks2022 base de données. GROUPING_ID() est utilisé pour créer une valeur pour chaque ligne de la colonne Title afin d'identifier le niveau de regroupement.

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. Utiliser GROUPING_ID pour filtrer un jeu de résultats

Exemple de base

Dans le code suivant, pour renvoyer 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.JobTitle) = 0;. Pour retourner uniquement des lignes avec un nombre d'employés par département, supprimez les caractères de commentaire de 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;

Voici le jeu de résultats non filtré.

Nom	Intitulé	Grouping Level	Employee Count	Nom
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

L'exemple suivant utilise GROUPING_ID() pour filtrer un jeu de résultats qui contient plusieurs niveaux de regroupement par niveau de regroupement. Le code similaire peut être utilisé pour créer une vue qui a plusieurs niveaux de regroupement et une procédure stockée qui appelle la vue en passant un paramètre qui filtre l’affichage en regroupant le niveau.

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;

C. Utiliser GROUPING_ID() avec ROLLUP et CUBE pour identifier les niveaux de regroupement

Le code des exemples suivants montre comment GROUPING() calculer la Bit Vector(base-2) colonne. 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 par 1 (0, 1, 2, ...n).

Remarque

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

Exemple ROLLUP

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

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

Voici un jeu de résultats partiel.

Year	Month	jour	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	Year Month
2007	2	`NULL`	2727337,9981	100	1	Year Month
2008	1	`NULL`	1954580,1658	100	1	Year Month
2008	2	`NULL`	3163370,5217	100	1	Year Month
2007	`NULL`	`NULL`	4246562,9541	110	3	Year
2008	`NULL`	`NULL`	5117950,6875	110	3	Year
`NULL`	`NULL`	`NULL`	9364513,6416	111	7	Total général

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 de la CUBE liste est modifié, les valeurs de niveau de la Grouping Level colonne doivent également être modifiées.

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

Voici un jeu de résultats partiel.

Year	Month	jour	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	Year Month
2007	2	`NULL`	2727337,9981	100	1	Year Month
2008	1	`NULL`	1954580,1658	100	1	Year Month
2008	2	`NULL`	3163370,5217	100	1	Year Month
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	Year
2008	`NULL`	`NULL`	5117950,6875	110	3	Year
`NULL`	1	1	3405574,7033	001	4	Month Day
`NULL`	1	2	68230,4185	001	4	Month Day
`NULL`	2	1	5814425,5642	001	4	Month Day
`NULL`	2	2	76282,9556	001	4	Month Day
`NULL`	1	`NULL`	3473805,1218	101	5	Month
`NULL`	2	`NULL`	5890708,5198	101	5	Month
`NULL`	`NULL`	1	9220000,2675	011	6	jour
`NULL`	`NULL`	2	144513,3741	011	6	jour
`NULL`	`NULL`	`NULL`	9364513,6416	111	7	Total général

Partager via

GROUPING_ID (Transact-SQL)

Syntaxe

Arguments

<column_expression>

Types de retour

Notes

Comparer GROUPING_ID() à GROUPING()

Définition technique de GROUPING_ID()

équivalents GROUPING_ID()

Instruction A

Instruction B

Exemples

A. Utiliser GROUPING_ID pour identifier les niveaux de regroupement

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

Exemple de base

Exemple complexe

C. Utiliser GROUPING_ID() avec ROLLUP et CUBE pour identifier les niveaux de regroupement

Exemple ROLLUP

Exemple CUBE

Commentaires

Ressources supplémentaires

Partager via

GROUPING_ID (Transact-SQL)

Syntaxe

Arguments

<column_expression>

Types de retour

Notes

Comparer GROUPING_ID() à GROUPING()

Définition technique de GROUPING_ID()

équivalents GROUPING_ID()

Instruction A

Instruction B

Exemples

A. Utiliser GROUPING_ID pour identifier les niveaux de regroupement

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

Exemple de base

Exemple complexe

C. Utiliser GROUPING_ID() avec ROLLUP et CUBE pour identifier les niveaux de regroupement

Exemple ROLLUP

Exemple CUBE

Contenu connexe

Commentaires

Ressources supplémentaires