GROUPING_ID (Transact-SQL)

Artikel
10/16/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

GROUPING_ID Ist eine Funktion, die die Gruppierungsebene berechnet. GROUPING_IDkann nur in der SELECT <select> Liste, oder ORDER BY Klauseln verwendet werden, HAVINGwenn GROUP BY angegeben wird.

Transact-SQL-Syntaxkonventionen

Syntax

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

Argumente

<column_expression>

Eine column_expression in einer SELECT - GROUP BY-Klausel.

Rückgabetypen

int

Hinweise

Der GROUPING_ID <column_expression> Ausdruck muss exakt mit dem Ausdruck in der GROUP BY Liste übereinstimmen. Wenn Sie z. B. nach DATEPART (yyyy, <column name>)gruppieren, verwenden GROUPING_ID (DATEPART (yyyy, <column name>)); oder wenn Sie nach <column name>gruppieren, verwenden Sie GROUPING_ID (<column name>).

Vergleich GROUPING_ID() mit GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) gibt das Äquivalent der GROUPING (<column_expression>) Rückgabe für jede Spalte in der Spaltenliste in jeder Ausgabezeile als eine Zeichenfolge von 1 und Nullen ein. GROUPING_ID interpretiert diese Zeichenfolge als Base-2-Zahl und gibt die entsprechende ganze Zahl zurück.

Betrachten Sie beispielsweise die folgende Anweisung:

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

Diese Tabelle enthält die GROUPING_ID() Eingabe- und Ausgabewerte.

Aggregierte Spalten	GROUPING_ID (a, b, c) Eingabe = GROUPING(a) + GROUPING(b) + GROUPING(c)	GROUPING_ID() Ausgabe
`a`	`100`	`4`
`b`	`010`	`2`
`c`	`001`	`1`
`ab`	`110`	`6`
`ac`	`101`	`5`
`bc`	`011`	`3`
`abc`	`111`	`7`

Technische Definition von GROUPING_ID()

Jedes GROUPING_ID Argument muss ein Element der GROUP BY Liste sein. GROUPING_ID()gibt eine ganzzahlige Bitmap zurück, deren niedrigste n Bit möglicherweise beleuchtet werden. Ein lit-Bit gibt an, dass das entsprechende Argument keine Gruppierungsspalte für die angegebene Ausgabezeile ist. Das Bit der niedrigsten Reihenfolge entspricht Argument n, und das n-1-Bit der niedrigsten Reihenfolge entspricht Argument 1.

GROUPING_ID() Entsprechungen

Bei einer einzelnen Gruppierungsabfrage GROUPING (<column_expression>) entspricht GROUPING_ID (<column_expression>)die Rückgabe und beide 0Rückgaben.

Beispielsweise sind die folgenden Anweisungen äquivalent:

Anweisung A

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

Anweisung 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

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

A. Verwenden von GROUPING_ID zum Identifizieren von Gruppierungsebenen

Im folgenden Beispiel wird die Anzahl der Mitarbeiter nach Name und Titlenach und und nach sowie die Gesamtsumme des Unternehmens in der AdventureWorks2022 Datenbank zurückgegeben. GROUPING_ID() wird verwendet, um einen Wert für jede Zeile in der Title-Spalte zu erstellen, die die Aggregationsebene angibt.

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. Verwenden von GROUPING_ID zum Filtern eines Resultsets

Einfaches Beispiel

Wenn Sie im folgenden Code nur die Zeilen zurückgeben möchten, die über eine Anzahl von Mitarbeitern nach Titel verfügen, entfernen Sie die Kommentarzeichen aus HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Um nur die Zeilen zurückzugeben, die die Anzahl der Mitarbeiter nach Abteilung enthalten, entfernen Sie die Kommentierungszeichen aus 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 sehen Sie das ungefilterte Resultset.

Name	Titel	Gruppierungsebene	Anzahl der Mitarbeiter	Name
Dokumentsteuerung	Steuerungsspezialist	0	2	Dokumentsteuerung
Dokumentsteuerung	Dokumentsteuerungs-Assistent	0	2	Dokumentsteuerung
Dokumentsteuerung	Dokumentsteuerungs-Manager	0	1	Dokumentsteuerung
Dokumentsteuerung	`NULL`	1	5	Dokumentsteuerung
Einrichtungen und Wartung	Einrichtungen-Verwaltungs-Assistent	0	1	Einrichtungen und Wartung
Einrichtungen und Wartung	Einrichtungs-Manager	0	1	Einrichtungen und Wartung
Einrichtungen und Wartung	Pförtner	0	4	Einrichtungen und Wartung
Einrichtungen und Wartung	Wartungsleiter	0	1	Einrichtungen und Wartung
Einrichtungen und Wartung	`NULL`	1	7	Einrichtungen und Wartung
`NULL`	`NULL`	3	12	`NULL`

Komplexes Beispiel

Im folgenden Beispiel wird GROUPING_ID() verwendet, um ein Resultset mit mehreren Gruppierungsebenen nach Gruppierungsebene zu filtern. Ähnlicher Code kann zum Erstellen einer Ansicht mit mehreren Gruppierungsebenen und einer gespeicherten Prozedur verwendet werden, die die Ansicht aufruft, indem ein Parameter übergeben wird, der die Ansicht nach Gruppierungsebene filtert.

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. Verwenden von GROUPING_ID() mit ROLLUP und CUBE zum Identifizieren von Gruppierungsebenen

Der Code in den folgenden Beispielen zeigt die Berechnung GROUPING() der Bit Vector(base-2) Spalte. GROUPING_ID() wird verwendet, um die entsprechende Integer Equivalent-Spalte zu berechnen. Die Spaltenreihenfolge in der GROUPING_ID()-Funktion ist die umgekehrte Reihenfolge der Spalten, die durch die GROUPING()-Funktion verkettet sind.

In diesen Beispielen wird GROUPING_ID() verwendet, um einen Wert für jede Zeile in der Grouping Level-Spalte zu erstellen, um die Gruppierungsebene zu ermitteln. Gruppierungsebenen sind nicht immer eine aufeinander folgende Liste mit ganzen Zahlen, die mit 1 beginnen (0, 1, 2, ...n).

Hinweis

GROUPING und GROUPING_ID kann in einer HAVING Klausel verwendet werden, um ein Resultset zu filtern.

ROLLUP-Beispiel

In diesem Beispiel werden alle Gruppierungsebenen nicht wie im folgenden CUBE Beispiel angezeigt. Wenn die Reihenfolge der Spalten in der ROLLUP Liste geändert wird, müssen auch die Ebenenwerte in der Grouping Level Spalte geändert werden.

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

Dies ist ein Auszug aus dem Resultset.

Jahr	Month (Monat)	Day (Tag)	Total Due	Bitvektor (Basis-2)	Ganzzahlige Entsprechung	Gruppierungsebene
2007	1	1	1\.497.452,6066	000	0	Jahr, Monat, Tag
2007	1	2	21.772,3494	000	0	Jahr, Monat, Tag
2007	2	1	2705653,5913	000	0	Jahr, Monat, Tag
2007	2	2	21.684,4068	000	0	Jahr, Monat, Tag
2008	1	1	1\.908.122,0967	000	0	Jahr, Monat, Tag
2008	1	2	46.458,0691	000	0	Jahr, Monat, Tag
2008	2	1	3\.108.771,9729	000	0	Jahr, Monat, Tag
2008	2	2	54.598,5488	000	0	Jahr, Monat, Tag
2007	1	`NULL`	1\.519.224,956	100	1	Year Month
2007	2	`NULL`	2\.727.337,9981	100	1	Year Month
2008	1	`NULL`	1954580,1658	100	1	Year Month
2008	2	`NULL`	3\.163.370,5217	100	1	Year Month
2007	`NULL`	`NULL`	4\.246.562,9541	110	3	Jahr
2008	`NULL`	`NULL`	5\.117.950,6875	110	3	Jahr
`NULL`	`NULL`	`NULL`	9\.364.513,6416	111	7	Grand Total

CUBE-Beispiel

In diesen Beispielen wird die GROUPING_ID()-Funktion verwendet, um einen Wert für jede Zeile in der Grouping Level-Spalte zu erstellen, über den die Gruppierungsebene ermittelt wird.

Im Gegensatz zu ROLLUP im vorherigen Beispiel gibt CUBE alle Gruppierungsebenen aus. Wenn die Reihenfolge der Spalten in der CUBE Liste geändert wird, müssen auch die Ebenenwerte in der Grouping Level Spalte geändert werden.

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

Dies ist ein Auszug aus dem Resultset.

Jahr	Month (Monat)	Day (Tag)	Total Due	Bitvektor (Basis-2)	Ganzzahlige Entsprechung	Gruppierungsebene
2007	1	1	1\.497.452,6066	000	0	Jahr, Monat, Tag
2007	1	2	21.772,3494	000	0	Jahr, Monat, Tag
2007	2	1	2705653,5913	000	0	Jahr, Monat, Tag
2007	2	2	21.684,4068	000	0	Jahr, Monat, Tag
2008	1	1	1\.908.122,0967	000	0	Jahr, Monat, Tag
2008	1	2	46.458,0691	000	0	Jahr, Monat, Tag
2008	2	1	3\.108.771,9729	000	0	Jahr, Monat, Tag
2008	2	2	54.598,5488	000	0	Jahr, Monat, Tag
2007	1	`NULL`	1\.519.224,956	100	1	Year Month
2007	2	`NULL`	2\.727.337,9981	100	1	Year Month
2008	1	`NULL`	1954580,1658	100	1	Year Month
2008	2	`NULL`	3\.163.370,5217	100	1	Year Month
2007	`NULL`	1	4\.203.106,1979	010	2	Jahr und Tag
2007	`NULL`	2	43456,7562	010	2	Jahr und Tag
2008	`NULL`	1	5\.016.894,0696	010	2	Jahr und Tag
2008	`NULL`	2	101.056,6179	010	2	Jahr und Tag
2007	`NULL`	`NULL`	4\.246.562,9541	110	3	Jahr
2008	`NULL`	`NULL`	5\.117.950,6875	110	3	Jahr
`NULL`	1	1	3\.405.574,7033	001	4	Monat und Tag
`NULL`	1	2	68.230,4185	001	4	Monat und Tag
`NULL`	2	1	5\.814.425,5642	001	4	Monat und Tag
`NULL`	2	2	76.282,9556	001	4	Monat und Tag
`NULL`	1	`NULL`	3\.473.805,1218	101	5	Month (Monat)
`NULL`	2	`NULL`	5\.890.708,5198	101	5	Month (Monat)
`NULL`	`NULL`	1	9\.220.000,2675	011	6	Day (Tag)
`NULL`	`NULL`	2	144.513,3741	011	6	Day (Tag)
`NULL`	`NULL`	`NULL`	9\.364.513,6416	111	7	Grand Total

Freigeben über

GROUPING_ID (Transact-SQL)

Syntax

Argumente

<column_expression>

Rückgabetypen

Hinweise

Vergleich GROUPING_ID() mit GROUPING()

Technische Definition von GROUPING_ID()

GROUPING_ID() Entsprechungen

Anweisung A

Anweisung B

Beispiele

A. Verwenden von GROUPING_ID zum Identifizieren von Gruppierungsebenen

B. Verwenden von GROUPING_ID zum Filtern eines Resultsets

Einfaches Beispiel

Komplexes Beispiel

C. Verwenden von GROUPING_ID() mit ROLLUP und CUBE zum Identifizieren von Gruppierungsebenen

ROLLUP-Beispiel

CUBE-Beispiel

Feedback

Zusätzliche Ressourcen

Freigeben über

GROUPING_ID (Transact-SQL)

Syntax

Argumente

<column_expression>

Rückgabetypen

Hinweise

Vergleich GROUPING_ID() mit GROUPING()

Technische Definition von GROUPING_ID()

GROUPING_ID() Entsprechungen

Anweisung A

Anweisung B

Beispiele

A. Verwenden von GROUPING_ID zum Identifizieren von Gruppierungsebenen

B. Verwenden von GROUPING_ID zum Filtern eines Resultsets

Einfaches Beispiel

Komplexes Beispiel

C. Verwenden von GROUPING_ID() mit ROLLUP und CUBE zum Identifizieren von Gruppierungsebenen

ROLLUP-Beispiel

CUBE-Beispiel

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen