Delen via

GROUPING_ID (Transact-SQL)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

GROUPING_ID Is een functie die het niveau van groepering berekent. GROUPING_ID kan alleen worden gebruikt in de SELECT <select> lijst, HAVING, of ORDER BY clausules wanneer GROUP BY is gespecificeerd.

Transact-SQL syntaxis-conventies

Syntaxis

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

Arguments

<column_expression>

Een column_expression in een SELECT - GROUP BY-clausule .

Retourtypen

int

Opmerkingen

Ze GROUPING_ID <column_expression> moeten exact overeenkomen met de uitdrukking in de GROUP BY lijst. Als je bijvoorbeeld groepeert door DATEPART (yyyy, <column name>), gebruik GROUPING_ID (DATEPART (yyyy, <column name>))dan ; of als je groepeert door <column name>, gebruik GROUPING_ID (<column name>)dan .

Vergelijk GROUPING_ID() met GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) invoer het equivalent van de GROUPING (<column_expression>) return voor elke kolom in zijn kolomlijst in elke uitvoerrij, als een reeks van enen en nullen. GROUPING_ID interpreteert die string als een basis-2 getal en geeft het equivalente geheel getal terug.

Overweeg bijvoorbeeld de volgende uitspraak:

SELECT a, b, c, SUM(d),
GROUPING_ID(a, b, c)
FROM T
GROUP BY <group_by_list>

Deze tabel toont de GROUPING_ID() invoer- en uitvoerwaarden.

Kolommen samengevoegd GROUPING_ID (a, b, c) invoer = GROEPERING(a) + GROEPERING(b) + GROEPERING(c) GROUPING_ID() uitgang
a 100 4
b 010 2
c 001 1
ab 110 6
ac 101 5
bc 011 3
abc 111 7

Technische definitie van GROUPING_ID()

Elk GROUPING_ID argument moet een element van de GROUP BY lijst zijn. GROUPING_ID() geeft een gehele bitmap terug waarvan de laagste n bits mogelijk verlicht zijn. Een lit bit geeft aan dat het bijbehorende argument geen groepskolom is voor de gegeven uitvoerrij. Het bit van de laagste orde komt overeen met argument n, en het n-1van de bit van de laagste orde komt overeen met argument 1.

GROUPING_ID() equivalenten

Voor een enkele groeperingsquery GROUPING (<column_expression>) is equivalent aan GROUPING_ID (<column_expression>), en beide geven 0.

Bijvoorbeeld, de volgende uitspraken zijn equivalent:

Stelling A

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

Stelling 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

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Eén. Gebruik GROUPING_ID om groeperingsniveaus te identificeren

Het volgende voorbeeld geeft het aantal werknemers per Name en Title, en het totaal van het bedrijf in de AdventureWorks2025 database terug. GROUPING_ID() wordt gebruikt om voor elke rij in de Title kolom een waarde te creëren die het aggregatieniveau identificeert.

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. Gebruik GROUPING_ID om een resultaatset te filteren

Basisvoorbeeld

In de volgende code, om alleen de rijen terug te geven die een aantal medewerkers per titel hebben, verwijder je de opmerkingtekens uit HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Om alleen rijen met een aantal medewerkers per afdeling terug te geven, verwijder je de commentaartekens uit 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;

Hier is de ongefilterde resultaatset.

Naam Title Groeperingsniveau Aantal werknemers Naam
Documentcontrole Regelspecialist 0 2 Documentcontrole
Documentcontrole Documentbeheerassistent 0 2 Documentcontrole
Documentcontrole Documentbeheerder 0 1 Documentcontrole
Documentcontrole NULL 1 5 Documentcontrole
Faciliteiten en onderhoud Administratief assistent faciliteiten 0 1 Faciliteiten en onderhoud
Faciliteiten en onderhoud Facilitair Manager 0 1 Faciliteiten en onderhoud
Faciliteiten en onderhoud Conciërge 0 4 Faciliteiten en onderhoud
Faciliteiten en onderhoud Onderhoudssupervisor 0 1 Faciliteiten en onderhoud
Faciliteiten en onderhoud NULL 1 7 Faciliteiten en onderhoud
NULL NULL 3 12 NULL

Complex voorbeeld

Het volgende voorbeeld gebruikt GROUPING_ID() het filteren van een resultaatset die meerdere groeperingsniveaus bevat op groeperingsniveau. Vergelijkbare code kan worden gebruikt om een weergave te creëren met meerdere groeperingsniveaus, en een opgeslagen procedure die de weergave aanroept door een parameter door te geven die de weergave filtert op groeperingsniveau.

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. Gebruik GROUPING_ID() met ROLLUP en CUBE om groeperingsniveaus te identificeren

De code in de volgende voorbeelden toont gebruik GROUPING() om de Bit Vector(base-2) kolom te berekenen. GROUPING_ID() wordt gebruikt om de overeenkomstige Integer Equivalent kolom te berekenen. De kolomvolgorde in de GROUPING_ID() functie is het tegenovergestelde van de kolomvolgorde van de kolommen die door de GROUPING() functie worden samengevoegd.

In deze voorbeelden GROUPING_ID() wordt gebruikt om voor elke rij in de Grouping Level kolom een waarde te creëren om het groeperingsniveau te identificeren. Groeperingsniveaus zijn niet altijd een opeenvolgende lijst van gehele getallen die beginnen met 1 (0, 1, 2, ... n).

Opmerking

GROUPING en GROUPING_ID kan in een HAVING clausule worden gebruikt om een resultaatverzameling te filteren.

ROLLUP-voorbeeld

In dit voorbeeld verschijnen niet alle groeperingsniveaus zoals in het volgende CUBE voorbeeld. Als de volgorde van de kolommen in de ROLLUP lijst wordt veranderd, moeten ook de niveauwaarden in de Grouping Level kolom worden aangepast.

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

Hier volgt een gedeeltelijke resultatenset.

Jaar Maand Day Totaal verschuldigd Bitvector (basis 2) Integer Equivalent Groeperingsniveau
2007 1 1 1497452.6066 000 0 Jaar Maand Dag
2007 1 2 21772.3494 000 0 Jaar Maand Dag
2007 2 1 2705653.5913 000 0 Jaar Maand Dag
2007 2 2 21684.4068 000 0 Jaar Maand Dag
2008 1 1 1908122.0967 000 0 Jaar Maand Dag
2008 1 2 46458.0691 000 0 Jaar Maand Dag
2008 2 1 3108771.9729 000 0 Jaar Maand Dag
2008 2 2 54598.5488 000 0 Jaar Maand Dag
2007 1 NULL 1519224.956 100 1 Jaarmaand
2007 2 NULL 2727337.9981 100 1 Jaarmaand
2008 1 NULL 1954580.1658 100 1 Jaarmaand
2008 2 NULL 3163370.5217 100 1 Jaarmaand
2007 NULL NULL 4246562.9541 110 3 Jaar
2008 NULL NULL 5117950.6875 110 3 Jaar
NULL NULL NULL 9364513.6416 111 7 Eindtotaal

CUBE-voorbeeld

In dit voorbeeld wordt de GROUPING_ID() functie gebruikt om voor elke rij in de Grouping Level kolom een waarde te creëren om het groeperingsniveau te identificeren.

In tegenstelling tot ROLLUP het vorige voorbeeld geeft het CUBE alle groeperingsniveaus de uitvoer. Als de volgorde van de kolommen in de CUBE lijst wordt veranderd, moeten ook de niveauwaarden in de Grouping Level kolom worden aangepast.

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

Hier volgt een gedeeltelijke resultatenset.

Jaar Maand Day Totaal verschuldigd Bitvector (basis 2) Integer Equivalent Groeperingsniveau
2007 1 1 1497452.6066 000 0 Jaar Maand Dag
2007 1 2 21772.3494 000 0 Jaar Maand Dag
2007 2 1 2705653.5913 000 0 Jaar Maand Dag
2007 2 2 21684.4068 000 0 Jaar Maand Dag
2008 1 1 1908122.0967 000 0 Jaar Maand Dag
2008 1 2 46458.0691 000 0 Jaar Maand Dag
2008 2 1 3108771.9729 000 0 Jaar Maand Dag
2008 2 2 54598.5488 000 0 Jaar Maand Dag
2007 1 NULL 1519224.956 100 1 Jaarmaand
2007 2 NULL 2727337.9981 100 1 Jaarmaand
2008 1 NULL 1954580.1658 100 1 Jaarmaand
2008 2 NULL 3163370.5217 100 1 Jaarmaand
2007 NULL 1 4203106.1979 010 2 Jaardag
2007 NULL 2 43456.7562 010 2 Jaardag
2008 NULL 1 5016894.0696 010 2 Jaardag
2008 NULL 2 101056.6179 010 2 Jaardag
2007 NULL NULL 4246562.9541 110 3 Jaar
2008 NULL NULL 5117950.6875 110 3 Jaar
NULL 1 1 3405574.7033 001 4 Maand Dag
NULL 1 2 68230.4185 001 4 Maand Dag
NULL 2 1 5814425.5642 001 4 Maand Dag
NULL 2 2 76282.9556 001 4 Maand Dag
NULL 1 NULL 3473805.1218 101 5 Maand
NULL 2 NULL 5890708.5198 101 5 Maand
NULL NULL 1 9220000.2675 011 6 Day
NULL NULL 2 144513.3741 011 6 Day
NULL NULL NULL 9364513.6416 111 7 Eindtotaal

Verwante inhoud

  • Last updated on