Condividi tramite


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 argomentoConvenzioni 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