GROUPING_ID (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

GROUPING_ID Je funkce, která počítá úroveň seskupení. GROUPING_IDlze použít pouze v SELECT <select> seznamu , nebo HAVING klauzulích, ORDER BYpokud GROUP BY je specifikováno.

Transact-SQL konvence syntaxe

Syntaxe

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

Arguments

<column_expression>

column_expression v klauzuli SELECT - GROUP BY.

Návratové typy

int

Poznámky

Musí přesně odpovídat GROUPING_ID <column_expression> výrazu v seznamu GROUP BY . Například pokud seskupujete podle DATEPART (yyyy, <column name>), použijte GROUPING_ID (DATEPART (yyyy, <column name>)); nebo pokud seskupujete podle <column name>, použijte GROUPING_ID (<column name>).

Porovnejte GROUPING_ID() s GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) zadává ekvivalent návratu GROUPING (<column_expression>) pro každý sloupec ve svém seznamu sloupců v každém výstupním řádku jako řetězec jedniček a nul. GROUPING_ID interpretuje tento řetězec jako číslo v soustavě 2 a vrací ekvivalentní celé číslo.

Například uvažujme následující tvrzení:

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

Tato tabulka ukazuje vstupní a výstupní hodnoty GROUPING_ID() .

Sloupce agregované	GROUPING_ID (a, b, c) vstup = SKUPINOVÁNÍ(a) + SKUPINOVÁNÍ(b) + SKUPINOVÁNÍ(c)	GROUPING_ID() výstup
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Technická definice GROUPING_ID()

Každý GROUPING_ID argument musí být součástí seznamu GROUP BY . GROUPING_ID() vrací celočíselnou bitmapu, jejíž nejméně n bitů může být svíceno. Lit bit označuje, že odpovídající argument není skupinovým sloupcem pro daný výstupní řádek. Bit nejnižšího řádu odpovídá argumentu n a n-1 bit nejnižšího řádu odpovídá argumentu 1.

GROUPING_ID() ekvivalenty

Pro jediný seskupovací dotaz GROUPING (<column_expression>) je ekvivalentní , GROUPING_ID (<column_expression>)a oba vracejí 0.

Například následující tvrzení jsou ekvivalentní:

Výrok A

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

Výrok 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

Examples

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

A. Použijte GROUPING_ID k identifikaci úrovní seskupování

Následující příklad vrací počet zaměstnanců podle Name a Title, a celkový počet AdventureWorks2025 společností v databázi. GROUPING_ID() se používá k vytvoření hodnoty pro každý řádek ve sloupci, Title která určuje jeho úroveň agregace.

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. Použijte GROUPING_ID k filtrování výsledné množiny

Základní příklad

V následujícím kódu, abyste mohli vrátit pouze řádky, které mají počet zaměstnanců podle názvu, odstraňte znaky komentáře z HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Pro vrácení pouze řádků s počtem zaměstnanců podle oddělení odstraňte znaky komentáře z 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;

Tady je nefiltrovaná sada výsledků.

Název	Title	Úroveň skupinování	Počet zaměstnanců	Název
Kontrola dokumentů	Specialista na řízení	0	2	Kontrola dokumentů
Kontrola dokumentů	Asistent pro řízení dokumentů	0	2	Kontrola dokumentů
Kontrola dokumentů	Správce řízení dokumentů	0	1	Kontrola dokumentů
Kontrola dokumentů	NULL	1	5	Kontrola dokumentů
Zařízení a údržba	Administrativní asistent pro zařízení	0	1	Zařízení a údržba
Zařízení a údržba	Správce zařízení	0	1	Zařízení a údržba
Zařízení a údržba	Domovník	0	4	Zařízení a údržba
Zařízení a údržba	Vedoucí údržby	0	1	Zařízení a údržba
Zařízení a údržba	NULL	1	7	Zařízení a údržba
NULL	NULL	3	12	NULL

Složitý příklad

Následující příklad používá GROUPING_ID() filtrování výsledné množiny obsahující více úrovní seskupení podle úrovně seskupení. Podobný kód lze použít k vytvoření pohledu s několika úrovněmi seskupení a uložené procedury, která volá pohled předáním parametru, který filtruje pohled podle úrovně seskupení.

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. Použijte GROUPING_ID() s ROLLUP a CUBE k identifikaci úrovní seskupení

Kód v následujících příkladech ukazuje použití GROUPING() pro výpočet sloupce Bit Vector(base-2) . GROUPING_ID() se používá k výpočtu odpovídajícího Integer Equivalent sloupce. Pořadí sloupců ve GROUPING_ID() funkci je opakem pořadí sloupců sloupců, které jsou funkcí spojeny GROUPING() .

V těchto příkladech se používá k GROUPING_ID() vytvoření hodnoty pro každý řádek ve sloupci Grouping Level , aby se identifikovala úroveň seskupení. Úrovně seskupování nejsou vždy po sobě jdoucí seznam celých čísel začínajících na 1 (0, 1, 2, ... n).

Poznámka:

GROUPING a GROUPING_ID lze použít ve vězle HAVING k filtrování množiny výsledků.

Příklad ROLLUP

V tomto příkladu se všechny úrovně seskupování neobjevují tak, jak v následujícím CUBE příkladu. Pokud se pořadí sloupců v ROLLUP seznamu změní, musí být změněny i hodnoty úrovní ve sloupci Grouping Level .

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

Tady je částečná sada výsledků.

Rok	Měsíc	Day	Celkový splatný	Bitový vektor (základ 2)	Celočíselný ekvivalent	Úroveň skupinování
2007	1	1	1497452.6066	000	0	Rok Měsíc Den
2007	1	2	21772.3494	000	0	Rok Měsíc Den
2007	2	1	2705653.5913	000	0	Rok Měsíc Den
2007	2	2	21684.4068	000	0	Rok Měsíc Den
2008	1	1	1908122.0967	000	0	Rok Měsíc Den
2008	1	2	46458.0691	000	0	Rok Měsíc Den
2008	2	1	3108771.9729	000	0	Rok Měsíc Den
2008	2	2	54598.5488	000	0	Rok Měsíc Den
2007	1	NULL	1519224.956	100	1	Měsíc roku
2007	2	NULL	2727337.9981	100	1	Měsíc roku
2008	1	NULL	1954580.1658	100	1	Měsíc roku
2008	2	NULL	3163370.5217	100	1	Měsíc roku
2007	NULL	NULL	4246562.9541	110	3	Rok
2008	NULL	NULL	5117950.6875	110	3	Rok
NULL	NULL	NULL	9364513.6416	111	7	Celkový součet

Příklad s CUBE

V tomto příkladu GROUPING_ID() se funkce používá k vytvoření hodnoty pro každý řádek ve sloupci, Grouping Level která určuje úroveň seskupování.

Na rozdíl od ROLLUP předchozího příkladu výstup CUBE vydává všechny úrovně seskupování. Pokud se pořadí sloupců v CUBE seznamu změní, musí být změněny i hodnoty úrovní ve sloupci Grouping Level .

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

Tady je částečná sada výsledků.

Rok	Měsíc	Day	Celkový splatný	Bitový vektor (základ 2)	Celočíselný ekvivalent	Úroveň skupinování
2007	1	1	1497452.6066	000	0	Rok Měsíc Den
2007	1	2	21772.3494	000	0	Rok Měsíc Den
2007	2	1	2705653.5913	000	0	Rok Měsíc Den
2007	2	2	21684.4068	000	0	Rok Měsíc Den
2008	1	1	1908122.0967	000	0	Rok Měsíc Den
2008	1	2	46458.0691	000	0	Rok Měsíc Den
2008	2	1	3108771.9729	000	0	Rok Měsíc Den
2008	2	2	54598.5488	000	0	Rok Měsíc Den
2007	1	NULL	1519224.956	100	1	Měsíc roku
2007	2	NULL	2727337.9981	100	1	Měsíc roku
2008	1	NULL	1954580.1658	100	1	Měsíc roku
2008	2	NULL	3163370.5217	100	1	Měsíc roku
2007	NULL	1	4203106.1979	010	2	Den roku
2007	NULL	2	43456.7562	010	2	Den roku
2008	NULL	1	5016894.0696	010	2	Den roku
2008	NULL	2	101056.6179	010	2	Den roku
2007	NULL	NULL	4246562.9541	110	3	Rok
2008	NULL	NULL	5117950.6875	110	3	Rok
NULL	1	1	3405574.7033	001	4	Měsíční den
NULL	1	2	68230.4185	001	4	Měsíční den
NULL	2	1	5814425.5642	001	4	Měsíční den
NULL	2	2	76282.9556	001	4	Měsíční den
NULL	1	NULL	3473805.1218	101	5	Měsíc
NULL	2	NULL	5890708.5198	101	5	Měsíc
NULL	NULL	1	9220000.2675	011	6	Day
NULL	NULL	2	144513.3741	011	6	Day
NULL	NULL	NULL	9364513.6416	111	7	Celkový součet

Související obsah

• SKUPINOVÁNÍ (Transact-SQL)
• VYBRAT - SESKUPIT PODLE (Transact-SQL)