GROUPING_ID (Transact-SQL)

Artículo
10/16/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

GROUPING_ID Es una función que calcula el nivel de agrupación. GROUPING_ID solo se puede usar en las SELECT <select> cláusulas list, HAVING, o ORDER BY cuando GROUP BY se especifica .

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

<column_expression>

Un column_expression en una cláusula SELECT - GROUP BY.

Tipos de valores devueltos

int

Comentarios

GROUPING_ID <column_expression> debe coincidir exactamente con la expresión de la GROUP BY lista. Por ejemplo, si va a agrupar por DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); o si va a agrupar por <column name>, use GROUPING_ID (<column name>).

Comparar GROUPING_ID() con GROUPING()

GROUPING_ID (<column_expression> [ , ...n ]) introduce el equivalente de la GROUPING (<column_expression>) devolución para cada columna de su lista de columnas en cada fila de salida, como una cadena de uno y ceros. GROUPING_ID interpreta esa cadena como un número base-2 y devuelve el entero equivalente.

Por ejemplo, considere la siguiente instrucción:

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

En esta tabla se muestran los GROUPING_ID() valores de entrada y salida.

Columnas agregadas	Entrada de GROUPING_ID (a, b, c) = GROUPING(a) + GROUPING(b) + GROUPING(c)	salida de GROUPING_ID()
`a`	`100`	`4`
`b`	`010`	`2`
`c`	`001`	`1`
`ab`	`110`	`6`
`ac`	`101`	`5`
`bc`	`011`	`3`
`abc`	`111`	`7`

Definición técnica de GROUPING_ID()

Cada GROUPING_ID argumento debe ser un elemento de la GROUP BY lista. GROUPING_ID()devuelve un mapa de bits entero cuyos bits n más bajos se pueden iluminar. Un bit iluminado indica que el argumento correspondiente no es una columna de agrupación para la fila de salida especificada. El bit de orden más bajo corresponde al argumento n y el bit de orden inferior n-1 corresponde al argumento 1.

equivalentes de GROUPING_ID()

Para una sola consulta de agrupación, GROUPING (<column_expression>) es equivalente a GROUPING_ID (<column_expression>)y devuelven 0.

Por ejemplo, las siguientes instrucciones son equivalentes:

Instrucción A

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

Instrucción 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

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Uso de GROUPING_ID para identificar niveles de agrupación

En el ejemplo siguiente se devuelve el recuento de empleados por Name y Title, y el total de la empresa en la AdventureWorks2022 base de datos. GROUPING_ID() se usa para crear un valor en cada fila de la columna Title que identifica su nivel de agregación.

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. Uso de GROUPING_ID para filtrar un conjunto de resultados

Ejemplo básico

En el código siguiente, para devolver solo las filas que tienen un recuento de empleados por título, quite los caracteres de comentario de HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Para devolver solo las filas que tienen un recuento de empleados por departamento, quite los caracteres de comentario de 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;

Este es el conjunto de resultados sin filtrar.

Nombre	Título	Grouping Level	Employee Count	Nombre
Document Control	Control Specialist	0	2	Document Control
Document Control	Document Control Assistant	0	2	Document Control
Document Control	Document Control Manager	0	1	Document Control
Document Control	`NULL`	1	5	Document Control
Facilities and Maintenance	Facilities Administrative Assistant	0	1	Facilities and Maintenance
Facilities and Maintenance	Facilities Manager	0	1	Facilities and Maintenance
Facilities and Maintenance	Janitor	0	4	Facilities and Maintenance
Facilities and Maintenance	Maintenance Supervisor	0	1	Facilities and Maintenance
Facilities and Maintenance	`NULL`	1	7	Facilities and Maintenance
`NULL`	`NULL`	3	12	`NULL`

Ejemplo complejo

En el ejemplo siguiente se usa GROUPING_ID() para filtrar un conjunto de resultados que contiene varios niveles de agrupación por nivel de agrupación. Se puede usar código similar para crear una vista que tenga varios niveles de agrupación y un procedimiento almacenado que llame a la vista pasando un parámetro que filtre la vista por nivel de agrupación.

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. Uso de GROUPING_ID() con ROLLUP y CUBE para identificar los niveles de agrupación

El código de los ejemplos siguientes muestra cómo usar GROUPING() para calcular la Bit Vector(base-2) columna. GROUPING_ID() se usa para calcular la columna Integer Equivalent correspondiente. El orden de columnas en la función GROUPING_ID() es el contrario del orden de las columnas que concatena la función GROUPING().

En estos ejemplos, GROUPING_ID() se usa para crear un valor para cada fila de la columna Grouping Level que identifica el nivel de agrupación. Los niveles de agrupación no siempre son una lista consecutiva de enteros que comienzan por 1 (0, 1, 2, ...n).

Nota:

GROUPING y GROUPING_ID se pueden usar en una HAVING cláusula para filtrar un conjunto de resultados.

Ejemplo de ROLLUP

En este ejemplo, todos los niveles de agrupación no aparecen como lo hacen en el ejemplo siguiente CUBE . Si se cambia el orden de las columnas de la ROLLUP lista, también se deben cambiar los valores de nivel de la Grouping Level columna.

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

A continuación se muestra un conjunto parcial de resultados.

Year	Mes	Día	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452,6066	000	0	Year Month Day
2007	1	2	21772,3494	000	0	Year Month Day
2007	2	1	2705653,5913	000	0	Year Month Day
2007	2	2	21684,4068	000	0	Year Month Day
2008	1	1	1908122,0967	000	0	Year Month Day
2008	1	2	46458,0691	000	0	Year Month Day
2008	2	1	3108771,9729	000	0	Year Month Day
2008	2	2	54598,5488	000	0	Year Month Day
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	Year
2008	`NULL`	`NULL`	5117950,6875	110	3	Year
`NULL`	`NULL`	`NULL`	9364513,6416	111	7	Total general

Ejemplo de CUBE

En este ejemplo, la función GROUPING_ID() se usa para crear un valor para cada fila de la columna Grouping Level que identifica el nivel de agrupación.

A diferencia de ROLLUP en el ejemplo anterior, CUBE genera todos los niveles de agrupación. Si se cambia el orden de las columnas de la CUBE lista, también se deben cambiar los valores de nivel de la Grouping Level columna.

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

A continuación se muestra un conjunto parcial de resultados.

Year	Mes	Día	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452,6066	000	0	Year Month Day
2007	1	2	21772,3494	000	0	Year Month Day
2007	2	1	2705653,5913	000	0	Year Month Day
2007	2	2	21684,4068	000	0	Year Month Day
2008	1	1	1908122,0967	000	0	Year Month Day
2008	1	2	46458,0691	000	0	Year Month Day
2008	2	1	3108771,9729	000	0	Year Month Day
2008	2	2	54598,5488	000	0	Year Month Day
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	Year Day
2007	`NULL`	2	43456.7562	010	2	Year Day
2008	`NULL`	1	5016894.0696	010	2	Year Day
2008	`NULL`	2	101056.6179	010	2	Year Day
2007	`NULL`	`NULL`	4246562,9541	110	3	Year
2008	`NULL`	`NULL`	5117950,6875	110	3	Year
`NULL`	1	1	3405574.7033	001	4	Month Day
`NULL`	1	2	68230.4185	001	4	Month Day
`NULL`	2	1	5814425.5642	001	4	Month Day
`NULL`	2	2	76282.9556	001	4	Month Day
`NULL`	1	`NULL`	3473805.1218	101	5	Month
`NULL`	2	`NULL`	5890708.5198	101	5	Month
`NULL`	`NULL`	1	9220000.2675	011	6	Día
`NULL`	`NULL`	2	144513.3741	011	6	Día
`NULL`	`NULL`	`NULL`	9364513,6416	111	7	Total general

Compartir vía

GROUPING_ID (Transact-SQL)

Sintaxis

Argumentos

<column_expression>

Tipos de valores devueltos

Comentarios

Comparar GROUPING_ID() con GROUPING()

Definición técnica de GROUPING_ID()

equivalentes de GROUPING_ID()

Instrucción A

Instrucción B

Ejemplos

A Uso de GROUPING_ID para identificar niveles de agrupación

B. Uso de GROUPING_ID para filtrar un conjunto de resultados

Ejemplo básico

Ejemplo complejo

C. Uso de GROUPING_ID() con ROLLUP y CUBE para identificar los niveles de agrupación

Ejemplo de ROLLUP

Ejemplo de CUBE

Comentarios

Recursos adicionales

Compartir vía

GROUPING_ID (Transact-SQL)

Sintaxis

Argumentos

<column_expression>

Tipos de valores devueltos

Comentarios

Comparar GROUPING_ID() con GROUPING()

Definición técnica de GROUPING_ID()

equivalentes de GROUPING_ID()

Instrucción A

Instrucción B

Ejemplos

A Uso de GROUPING_ID para identificar niveles de agrupación

B. Uso de GROUPING_ID para filtrar un conjunto de resultados

Ejemplo básico

Ejemplo complejo

C. Uso de GROUPING_ID() con ROLLUP y CUBE para identificar los niveles de agrupación

Ejemplo de ROLLUP

Ejemplo de CUBE

Contenido relacionado

Comentarios

Recursos adicionales