Articolo
09/28/2010

GROUPING_ID (Transact-SQL)

È una funzione che calcola il livello di raggruppamento. È possibile utilizzare GROUPING_ID solo in un elenco di <selezione> SELECT o nelle clausole ORDER BY quando GROUP BY è specificato.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

<<column_expression>>
È un parametro column_expression in una clausola GROUP BY.

Tipo restituito

int

Osservazioni

GROUPING_ID <column_expression> deve corrispondere esattamente all'espressione presente nell'elenco GROUP BY. Ad esempio, se si effettua il raggruppamento per DATEPART (yyyy, <column name>), utilizzare GROUPING_ID (DATEPART (yyyy, <column name>)). Se invece si effettua il raggruppamento per <column name>, utilizzare GROUPING_ID (<column name>).

Confronto tra GROUPING_ID () e GROUPING ()

GROUPING_ID (<column_expression> [ ,...n ]) ]) immette l'equivalente del valore GROUPING (<column_expression>) restituito per ogni colonna nell'elenco di colonne in ogni riga di output come stringa di valori uno e zero. GROUPING_ID interpreta tale stringa come numero in base 2 e restituisce il valore integer equivalente. Ad esempio, si consideri l'istruzione seguente: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>. Nella tabella seguente vengono mostrati i valori di input e output di GROUPING_ID ().

Colonne aggregate	Input di GROUPING_ID (a, b, c) = GROUPING (a) + GROUPING (b) + GROUPING (c)	Output di GROUPING_ID ()
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Definizione tecnica di GROUPING_ID ()

Ogni argomento GROUPING_ID deve essere un elemento dell'elenco GROUP BY. GROUPING_ID () restituisce una mappa di bit integer i cui N bit più bassi possono essere attivati. Un bit attivato indica che l'argomento corrispondente non è una colonna di raggruppamento per la riga di output specificata. Il bit dell'ordine più basso corrisponde all'argomento N e il bit dell'ordine più basso alla posizione N-1 corrisponde all'argomento 1.

Equivalenti GROUPING_ID ()

Per una singola query di raggruppamento, GROUPING (<column_expression>) è equivalente a GROUPING_ID (<column_expression>) ed entrambi restituiscono 0.

Ad esempio, le istruzioni seguenti sono equivalenti:

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,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

Esempi

A. Utilizzo di GROUPING_ID per identificare i livelli di raggruppamento

Nell'esempio seguente viene restituito il conteggio di dipendenti per Name e Title, Name, e totale della società. La funzione GROUPING_ID() viene utilizzata per creare un valore per ogni riga nella colonna Title che identifica il livello di aggregazione.

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title);

B. Utilizzo di GROUPING_ID per filtrare un set di risultati

Esempio semplice

Nel codice seguente, per restituire solo le righe che hanno un conteggio dei dipendenti per titolo, rimuovere i caratteri di commento da HAVING GROUPING_ID(D.Name, E.Title); = 0. Per restituire solo le righe con un conteggio dei dipendenti per reparto, rimuovere i caratteri di commento da HAVING GROUPING_ID(D.Name, E.Title) = 1;.

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

Di seguito è riportato il set di risultati non filtrato.

Nome	Titolo	Livello di raggruppamento	Conteggio dipendenti	Nome
Controllo documenti	Specialista controllo	0	2	Controllo documenti
Controllo documenti	Assistente controllo documenti	0	2	Controllo documenti
Controllo documenti	Responsabile controllo documenti	0	1	Controllo documenti
Controllo documenti	NULL	1	5	Controllo documenti
Facilities and Maintenance	Assistente amministrativo Facilities	0	1	Facilities and Maintenance
Facilities and Maintenance	Responsabile strutture	0	1	Facilities and Maintenance
Facilities and Maintenance	Custode	0	4	Facilities and Maintenance
Facilities and Maintenance	Supervisore di manutenzione	0	1	Facilities and Maintenance
Facilities and Maintenance	NULL	1	7	Facilities and Maintenance
NULL	NULL	3	12	NULL

Esempio complesso

Nell'esempio seguente, GROUPING_ID() viene utilizzato per filtrare un set di risultati che contiene più livelli di raggruppamento suddivisi per livello di raggruppamento. Un codice simile può essere utilizzato per creare una vista contenente molti livelli di raggruppamento e una stored procedure che chiama la vista passando un parametro che filtra la vista per livello di raggruppamento.

USE AdventureWorks;
GO
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 C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = 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 C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.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. Utilizzo di GROUPING_ID () con ROLLUP e CUBE per identificare i livelli di raggruppamento

Il codice negli esempi seguenti mostra l'utilizzo di GROUPING() per calcolare la colonna Bit Vector(base-2). GROUPING_ID() viene utilizzato per calcolare la colonna Integer Equivalent corrispondente. L'ordine delle colonne nella funzione GROUPING_ID() è il contrario dell'ordine delle colonne delle colonne concatenate dalla funzione GROUPING().

In questi esempi, GROUPING_ID() viene utilizzato per creare un valore per ogni riga nella colonna Grouping Level per identificare il livello di raggruppamento. I livelli di raggruppamento non sono sempre un elenco consecutivo di valori integer che iniziano per 1 (0, 1, 2...n).

[!NOTA]

GROUPING e GROUPING_ID possono essere utilizzati in una clausola HAVING per filtrare un set di risultati.

Esempio ROLLUP

In questo esempio, tutti i livelli di raggruppamento non vengono visualizzati nel modo presentanto nell'esempio CUBE seguente. Se l'ordine delle colonne nell'elenco ROLLUP viene modificato, i valori del livello nella colonna Grouping Level vengono anch'essi modificati.

USE AdventureWorks;
GO
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'2003',N'2004')
    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);

Set di risultati parziale:

Year	Month	Day	Total Due	Vettore di bit (base 2)	Equivalente integer	Livello di raggruppamento
2003	1	1	1762381	000	0	Anno Mese Giorno
2003	1	2	21772.35	000	0	Anno Mese Giorno
2003	2	1	3185233	000	0	Anno Mese Giorno
2003	2	2	21684.41	000	0	Anno Mese Giorno
2004	1	1	2239208	000	0	Anno Mese Giorno
2004	1	2	46458.07	000	0	Anno Mese Giorno
2004	2	1	3653194	000	0	Anno Mese Giorno
2004	2	2	54598.55	000	0	Anno Mese Giorno
2003	1	NULL	1784153	100	1	Anno Mese
2003	2	NULL	3206917	100	1	Anno Mese
2004	1	NULL	2285666	100	1	Anno Mese
2004	2	NULL	3707793	100	1	Anno Mese
2003	NULL	NULL	4991070	110	3	Anno
2004	NULL	NULL	5993459	110	3	Anno
NULL	NULL	NULL	10984529	111	7	Totale complessivo

Esempio CUBE

In questo esempio, la funzione GROUPING_ID() viene utilizzata per creare un valore per ogni riga nella colonna Grouping Level per identificare il livello di raggruppamento.

A differenza di ROLLUP nell'esempio precedente, CUBE restituisce tutti i livelli di raggruppamento. Se l'ordine delle colonne nell'elenco CUBE viene modificato, i valori del livello nella colonna Grouping Level vengono anch'essi modificati.

USE AdventureWorks;
GO
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'2003',N'2004')
    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);

Set di risultati parziale:

Year	Month	Day	Total Due	Vettore di bit (base 2)	Equivalente integer	Livello di raggruppamento
2003	1	1	1762381	000	0	Anno Mese Giorno
2003	1	2	21772.35	000	0	Anno Mese Giorno
2003	2	1	3185233	000	0	Anno Mese Giorno
2003	2	2	21684.41	000	0	Anno Mese Giorno
2004	1	1	2239208	000	0	Anno Mese Giorno
2004	1	2	46458.07	000	0	Anno Mese Giorno
2004	2	1	3653194	000	0	Anno Mese Giorno
2004	2	2	54598.55	000	0	Anno Mese Giorno
2003	1	NULL	1784153	100	1	Anno Mese
2003	2	NULL	3206917	100	1	Anno Mese
2004	1	NULL	2285666	100	1	Anno Mese
2004	2	NULL	3707793	100	1	Anno Mese
2003	NULL	1	4947613	010	2	Anno Giorno
2003	NULL	2	43456.76	010	2	Anno Giorno
2004	NULL	1	5892402	010	2	Anno Giorno
2004	NULL	2	101056.6	010	2	Anno Giorno
2003	NULL	NULL	4991070	110	3	Anno
2004	NULL	NULL	5993459	110	3	Anno
NULL	1	1	4001589	001	4	Mese Giorno
NULL	1	2	68230.42	001	4	Mese Giorno
NULL	2	1	6838427	001	4	Mese Giorno
NULL	2	2	76282.96	001	4	Mese Giorno
NULL	1	NULL	4069819	101	5	Month
NULL	2	NULL	6914710	101	5	Mese
NULL	NULL	1	10840016	011	6	Giorno
NULL	NULL	2	144513.4	011	6	Giorno
NULL	NULL	NULL	10984529	111	7	Totale complessivo