GROUPING_ID (Transact-SQL)

Articolo
10/16/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

GROUPING_ID Funzione che calcola il livello di raggruppamento. GROUPING_ID può essere usato solo nelle SELECT <select> clausole list, HAVING, o ORDER BY quando GROUP BY viene specificato .

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

<column_expression>

Un column_expression in una clausola SELECT - GROUP BY.

Tipi restituiti

int

Osservazioni:

Deve GROUPING_ID <column_expression> corrispondere esattamente all'espressione nell'elenco GROUP BY . Ad esempio, se si sta raggruppando per DATEPART (yyyy, <column name>), usare GROUPING_ID (DATEPART (yyyy, <column name>))o se si sta raggruppando <column name>per , usare GROUPING_ID (<column name>).

Confrontare GROUPING_ID() con GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) inserisce l'equivalente del valore restituito per ogni colonna nell'elenco GROUPING (<column_expression>) di colonne in ogni riga di output, come stringa di uno e zeri. GROUPING_ID interpreta tale stringa come numero base-2 e restituisce l'intero equivalente.

Si consideri ad esempio l'istruzione seguente:

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

Questa tabella mostra i valori di GROUPING_ID() input e output.

Colonne aggregate	Input di GROUPING_ID (a, b, c) = GROUPING (a) + GROUPING (b) + GROUPING (c)	output 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 GROUPING_ID argomento deve essere un elemento dell'elenco GROUP BY . GROUPING_ID()restituisce una bitmap integer i cui n bit più bassi potrebbero essere illuminati. Un bit illuminato indica che l'argomento corrispondente non è una colonna di raggruppamento per la riga di output specificata. Il bit più basso corrisponde all'argomento n e il bit n-1 più basso corrisponde all'argomento 1.

equivalenti GROUPING_ID()

Per una singola query di raggruppamento, GROUPING (<column_expression>) equivale a GROUPING_ID (<column_expression>)e restituisce 0entrambi .

Ad esempio, le istruzioni seguenti sono equivalenti:

Istruzione A

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

Istruzione 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

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

R. Usare GROUPING_ID per identificare i livelli di raggruppamento

Nell'esempio seguente viene restituito il numero di dipendenti per Name e Titlee il totale dell'azienda nel AdventureWorks2022 database. GROUPING_ID() viene utilizzato per creare un valore per ogni riga nella colonna Title per identificare il livello di raggruppamento.

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. Usare GROUPING_ID per filtrare un set di risultati

Esempio di base

Nel codice seguente, per restituire solo le righe con un conteggio dei dipendenti per titolo, rimuovere i caratteri di commento da HAVING GROUPING_ID(D.Name, E.JobTitle) = 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.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;

Ecco il set di risultati non filtrato.

Nome	Title	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. È possibile usare codice simile per creare una vista con diversi livelli di raggruppamento e una stored procedure che chiama la vista passando un parametro che filtra la vista in base al livello di raggruppamento.

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. Usare GROUPING_ID() con ROLLUP e CUBE per identificare i livelli di raggruppamento

Il codice negli esempi seguenti illustra l'uso GROUPING() di per calcolare la Bit Vector(base-2) colonna. 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 numeri interi che iniziano con 1 (0, 1, 2, ...n).

Nota

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

Esempio ROLLUP

In questo esempio tutti i livelli di raggruppamento non vengono visualizzati come nell'esempio seguente CUBE . Se l'ordine delle colonne nell'elenco ROLLUP viene modificato, è necessario modificare anche i valori del livello nella Grouping Level colonna.

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

Di seguito è riportato un set di risultati parziale.

Anno	Mese	Giorno	Total Due	Vettore di bit (base 2)	Equivalente integer	Livello di raggruppamento
2007	1	1	1497452.6066	000	0	Anno Mese Giorno
2007	1	2	21772.3494	000	0	Anno Mese Giorno
2007	2	1	2705653.5913	000	0	Anno Mese Giorno
2007	2	2	21684.4068	000	0	Anno Mese Giorno
2008	1	1	1908122.0967	000	0	Anno Mese Giorno
2008	1	2	46458.0691	000	0	Anno Mese Giorno
2008	2	1	3108771.9729	000	0	Anno Mese Giorno
2008	2	2	54598.5488	000	0	Anno Mese Giorno
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	`NULL`	4246562.9541	110	3	Anno
2008	`NULL`	`NULL`	5117950.6875	110	3	Anno
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	Grand Total

Esempio di 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, è necessario modificare anche i valori del livello nella Grouping Level colonna.

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

Di seguito è riportato un set di risultati parziale.

Anno	Mese	Giorno	Total Due	Vettore di bit (base 2)	Equivalente integer	Livello di raggruppamento
2007	1	1	1497452.6066	000	0	Anno Mese Giorno
2007	1	2	21772.3494	000	0	Anno Mese Giorno
2007	2	1	2705653.5913	000	0	Anno Mese Giorno
2007	2	2	21684.4068	000	0	Anno Mese Giorno
2008	1	1	1908122.0967	000	0	Anno Mese Giorno
2008	1	2	46458.0691	000	0	Anno Mese Giorno
2008	2	1	3108771.9729	000	0	Anno Mese Giorno
2008	2	2	54598.5488	000	0	Anno Mese Giorno
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	1	4203106.1979	010	2	Anno Giorno
2007	`NULL`	2	43456.7562	010	2	Anno Giorno
2008	`NULL`	1	5016894.0696	010	2	Anno Giorno
2008	`NULL`	2	101056.6179	010	2	Anno Giorno
2007	`NULL`	`NULL`	4246562.9541	110	3	Anno
2008	`NULL`	`NULL`	5117950.6875	110	3	Anno
`NULL`	1	1	3405574.7033	001	4	Mese Giorno
`NULL`	1	2	68230.4185	001	4	Mese Giorno
`NULL`	2	1	5814425.5642	001	4	Mese Giorno
`NULL`	2	2	76282.9556	001	4	Mese Giorno
`NULL`	1	`NULL`	3473805.1218	101	5	Mese
`NULL`	2	`NULL`	5890708.5198	101	5	Mese
`NULL`	`NULL`	1	9220000.2675	011	6	Giorno
`NULL`	`NULL`	2	144513.3741	011	6	Giorno
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	Grand Total

Condividi tramite

GROUPING_ID (Transact-SQL)

Sintassi

Argomenti

<column_expression>

Tipi restituiti

Osservazioni:

Confrontare GROUPING_ID() con GROUPING()

Definizione tecnica di GROUPING_ID()

equivalenti GROUPING_ID()

Istruzione A

Istruzione B

Esempi

R. Usare GROUPING_ID per identificare i livelli di raggruppamento

B. Usare GROUPING_ID per filtrare un set di risultati

Esempio di base

Esempio complesso

C. Usare GROUPING_ID() con ROLLUP e CUBE per identificare i livelli di raggruppamento

Esempio ROLLUP

Esempio di CUBE

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

GROUPING_ID (Transact-SQL)

Sintassi

Argomenti

<column_expression>

Tipi restituiti

Osservazioni:

Confrontare GROUPING_ID() con GROUPING()

Definizione tecnica di GROUPING_ID()

equivalenti GROUPING_ID()

Istruzione A

Istruzione B

Esempi

R. Usare GROUPING_ID per identificare i livelli di raggruppamento

B. Usare GROUPING_ID per filtrare un set di risultati

Esempio di base

Esempio complesso

C. Usare GROUPING_ID() con ROLLUP e CUBE per identificare i livelli di raggruppamento

Esempio ROLLUP

Esempio di CUBE

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive