GROUPING_ID (Transact-SQL)

A következőkre vonatkozik:SQL Server Azure SQL Database Felügyelt Azure SQL-példány SQL-adatbázis a Microsoft Fabricben

GROUPING_ID A függvény, amely kiszámítja a csoportosítás szintjét. GROUPING_ID csak SELECT <select> a listában használható HAVING, , vagy ORDER BY klauzulákban, ha GROUP BY van megadva.

Transact-SQL szintaxis konvenciók

Szemantika

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

Arguments

<column_expression>

Egy column_expression egy SELECT - GROUP BY klauzulatban.

Visszatérési típusok

int

Megjegyzések

Pontosan GROUPING_ID <column_expression> meg kell egyeznie a listán szereplő kifejezéssel GROUP BY . Például, ha csoportosítjuk DATEPART (yyyy, <column name>), használd GROUPING_ID (DATEPART (yyyy, <column name>)); vagy ha , <column name>GROUPING_ID (<column name>)használd .

Hasonlítsd össze a GROUPING_ID() a GROUPING()-hez

GROUPING_ID (<column_expression> [ , ...n ]) minden GROUPING (<column_expression>) egyes sorban az oszloplistájának minden oszlopának visszacsatolását vezeti be, egyesekből és nullákból álló láncsorként. GROUPING_ID ezt a láncszálat alapszámként értelmezi, és az ekvivalens egész számot adja vissza.

Például vegyük a következő állítást:

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

Ez a táblázat a GROUPING_ID() bemeneti és kimeneti értékeket mutatja.

Oszlopok összesítette	GROUPING_ID (a, b, c) bemenet = CSOPORTOSÍTÁS(a) + CSOPORTOSÍTÁS(b) + CSOPORTOSÍTÁS(c)	GROUPING_ID() kimenet
`a`	`100`	`4`
`b`	`010`	`2`
`c`	`001`	`1`
`ab`	`110`	`6`
`ac`	`101`	`5`
`bc`	`011`	`3`
`abc`	`111`	`7`

A GROUPING_ID() műszaki definíciója

Minden GROUPING_ID érvnek a lista egyik eleme GROUP BY kell legyen. GROUPING_ID() egy egész számú bitképet ad vissza, amelynek legkisebb n bitje lehet megvilágítani. Egy lit bit azt jelzi, hogy a megfelelő érv nem csoportosító oszlop a megadott kimeneti sorhoz. A legalacsonyabb rendű bit az n érvnek felel meg, az n-1^. legalacsonyabb rendű bit pedig az 1-es érvnek felel meg.

GROUPING_ID() megfelelői

Egy csoportosító lekérdezés esetén GROUPING (<column_expression>) ekvivalens , GROUPING_ID (<column_expression>)és mindkettő visszaadja 0.

Például a következő állítások ekvivalensek:

A állítás

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

B állítás

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

Példák

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

A. Használd GROUPING_ID a csoportosítási szintek azonosítására

A következő példa adja vissza az alkalmazottak számát Name és Title, valamint a vállalat összesítését az AdventureWorks2025 adatbázisban. GROUPING_ID() az oszlop minden sorához Title egy értéket hoznak létre, amely meghatározza annak aggregációs szintjét.

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. Használj GROUPING_ID eredményhalmazra szűrni

Egyszerű példa

A következő kódban, hogy csak azokat a sorokat küldjük vissza, amelyekben a cím szerint alkalmazottak száma van, távolítsuk el a hozzászólás karaktereket -ről HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Csak a sorok visszaadásához, ahol osztályonként dolgozók száma van, távolítsuk el a hozzászólás karaktereit .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;

Íme a szűretlen eredményhalmaz.

Név	Cím	Csoportosítási szint	Alkalmazotti létszám	Név
Dokumentumellenőrzés	Vezérlő szakértő	0	2	Dokumentumellenőrzés
Dokumentumellenőrzés	Dokumentumvezérlő asszisztens	0	2	Dokumentumellenőrzés
Dokumentumellenőrzés	Dokumentumvezérlési menedzser	0	1	Dokumentumellenőrzés
Dokumentumellenőrzés	`NULL`	1	5	Dokumentumellenőrzés
Létesítmények és karbantartás	Létesítmények adminisztratív asszisztense	0	1	Létesítmények és karbantartás
Létesítmények és karbantartás	Létesítményvezető	0	1	Létesítmények és karbantartás
Létesítmények és karbantartás	Gondnok	0	4	Létesítmények és karbantartás
Létesítmények és karbantartás	Karbantartó felügyelő	0	1	Létesítmények és karbantartás
Létesítmények és karbantartás	`NULL`	1	7	Létesítmények és karbantartás
`NULL`	`NULL`	3	12	`NULL`

Komplex példa

A következő példa egy olyan eredményhalmazt szűr, GROUPING_ID() amely több csoportosítási szintet tartalmaz. Hasonló kód használható olyan nézet létrehozására, amelynek több csoportosítási szintje van, valamint egy tárolt eljárás létrehozására, amely egy paramétert továbbít, amely a nézetet csoportosítva szűri a nézetet.

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. Használd a GROUPING_ID() gombot a ROLLUP és CUBE segítségével a csoportosítási szintek azonosítására

Az alábbi példákban szereplő kód azt mutatja GROUPING() , hogyan számolják ki az Bit Vector(base-2) oszlopot. GROUPING_ID() a megfelelő Integer Equivalent oszlop kiszámításához használják. A függvényben az oszlopsorrend GROUPING_ID() ellentéte az oszlopok sorrendjével, amelyeket a GROUPING() függvény összeköt.

Ezekben a példákban GROUPING_ID() minden sorhoz Grouping Level értéket hoznak létre az oszlopban, hogy azonosítsák a csoportosítási szintet. A csoportosítási szintek nem mindig egymást követő egész számlisták, amelyek 1-cel kezdődnek (0, 1, 2, ... n).

Megjegyzés:

GROUPING és GROUPING_IDHAVING egy záradékban használható egy eredményhalmaz szűrésére.

ROLLUP példa

Ebben a példában minden csoportosítási szint nem úgy jelenik meg, mint a következő CUBE példában. Ha a listában lévő oszlopok ROLLUP sorrendjét megváltoztatjuk, akkor az oszlopban lévő szintértékeket Grouping Level is meg kell változtatni.

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

Íme egy részleges eredményhalmaz.

Év	Hónap	Day	Teljes esedékesség	Bitvektor (bázis-2)	Egész szám ekvivalens	Csoportosítási szint
2007	1	1	1497452.6066	000	0	Év hónap nap
2007	1	2	21772.3494	000	0	Év hónap nap
2007	2	1	2705653.5913	000	0	Év hónap nap
2007	2	2	21684.4068	000	0	Év hónap nap
2008	1	1	1908122.0967	000	0	Év hónap nap
2008	1	2	46458.0691	000	0	Év hónap nap
2008	2	1	3108771.9729	000	0	Év hónap nap
2008	2	2	54598.5488	000	0	Év hónap nap
2007	1	`NULL`	1519224.956	100	1	Év hónap
2007	2	`NULL`	2727337.9981	100	1	Év hónap
2008	1	`NULL`	1954580.1658	100	1	Év hónap
2008	2	`NULL`	3163370.5217	100	1	Év hónap
2007	`NULL`	`NULL`	4246562.9541	110	3	Év
2008	`NULL`	`NULL`	5117950.6875	110	3	Év
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	Végösszeg

CUBE példa

Ebben a példában a GROUPING_ID() függvény minden sorhoz Grouping Level értéket hozhat létre az oszlopban, hogy azonosítsa a csoportosítás szintjét.

Az előző példával ellentétben ROLLUP minden CUBE csoportosítási szintet eredményez. Ha a listában lévő oszlopok CUBE sorrendjét megváltoztatjuk, akkor az oszlopban lévő szintértékeket Grouping Level is meg kell változtatni.

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