Partager via

GROUPING_ID (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

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 de cet article utilisent les bases de données d'exemple AdventureWorks2025 ou AdventureWorksDW2025, que vous pouvez télécharger à partir de la page d'accueil Microsoft SQL Server Samples and Community Projects.

R : 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 AdventureWorks2025 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é Niveau de regroupement Nombre d’employés Nom
Contrôle des documents Spécialiste du contrôle 0 2 Contrôle des documents
Contrôle des documents Assistant de contrôle documentaire 0 2 Contrôle des documents
Contrôle des documents Gestionnaire de contrôle documentaire 0 1 Contrôle des documents
Contrôle des documents NULL 1 5 Contrôle des documents
Installations et maintenance Assistant administratif des installations 0 1 Installations et maintenance
Installations et maintenance Responsable des installations 0 1 Installations et maintenance
Installations et maintenance Janitor 0 4 Installations et maintenance
Installations et maintenance Superviseur de maintenance 0 1 Installations et maintenance
Installations et maintenance NULL 1 7 Installations et 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 dû Vecteur de bits (base-2) Équivalent entier Niveau de regroupement
2007 1 1 1497452,6066 000 0 Année Jour du mois
2007 1 2 21772,3494 000 0 Année Jour du mois
2007 2 1 2705653,5913 000 0 Année Jour du mois
2007 2 2 21684,4068 000 0 Année Jour du mois
2008 1 1 1908122,0967 000 0 Année Jour du mois
2008 1 2 46458,0691 000 0 Année Jour du mois
2008 2 1 3108771,9729 000 0 Année Jour du mois
2008 2 2 54598,5488 000 0 Année Jour du mois
2007 1 NULL 1519224,956 100 1 Mois de l’année
2007 2 NULL 2727337,9981 100 1 Mois de l’année
2008 1 NULL 1954580,1658 100 1 Mois de l’année
2008 2 NULL 3163370,5217 100 1 Mois de l’année
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 dû Vecteur de bits (base-2) Équivalent entier Niveau de regroupement
2007 1 1 1497452,6066 000 0 Année Jour du mois
2007 1 2 21772,3494 000 0 Année Jour du mois
2007 2 1 2705653,5913 000 0 Année Jour du mois
2007 2 2 21684,4068 000 0 Année Jour du mois
2008 1 1 1908122,0967 000 0 Année Jour du mois
2008 1 2 46458,0691 000 0 Année Jour du mois
2008 2 1 3108771,9729 000 0 Année Jour du mois
2008 2 2 54598,5488 000 0 Année Jour du mois
2007 1 NULL 1519224,956 100 1 Mois de l’année
2007 2 NULL 2727337,9981 100 1 Mois de l’année
2008 1 NULL 1954580,1658 100 1 Mois de l’année
2008 2 NULL 3163370,5217 100 1 Mois de l’année
2007 NULL 1 4203106,1979 010 2 Jour de l’année
2007 NULL 2 43456,7562 010 2 Jour de l’année
2008 NULL 1 5016894,0696 010 2 Jour de l’année
2008 NULL 2 101056,6179 010 2 Jour de l’année
2007 NULL NULL 4246562,9541 110 3 Year
2008 NULL NULL 5117950,6875 110 3 Year
NULL 1 1 3405574,7033 001 4 Jour du mois
NULL 1 2 68230,4185 001 4 Jour du mois
NULL 2 1 5814425,5642 001 4 Jour du mois
NULL 2 2 76282,9556 001 4 Jour du mois
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

Contenu connexe

  • Last updated on