Partilhar via

GROUPING_ID (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

GROUPING_ID É uma função que calcula o nível de agrupamento. GROUPING_ID pode ser usado apenas na SELECT <select> lista, HAVING, ou ORDER BY nas cláusulas quando GROUP BY for especificado.

Transact-SQL convenções de sintaxe

Sintaxe

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

Arguments

<column_expression>

Uma column_expression numa cláusula SELECT - GROUP BY.

Tipos de devolução

int

Observações

Devem GROUPING_ID <column_expression> corresponder exatamente à expressão da GROUP BY lista. Por exemplo, se estiver a agrupar por DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); ou se estiver a agrupar por <column name>, use GROUPING_ID (<column name>).

Compare GROUPING_ID() com AGRUPAMENTO()

GROUPING_ID (<column_expression> [ , ...n ]) insere o equivalente ao retorno GROUPING (<column_expression>) de cada coluna na sua lista de colunas em cada linha de saída, como uma cadeia de uns e zeros. GROUPING_ID interpreta essa cadeia como um número na base 2 e devolve o número inteiro equivalente.

Por exemplo, considere a seguinte afirmação:

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

Esta tabela mostra os valores de GROUPING_ID() entrada e saída.

Colunas agregadas GROUPING_ID (a, b, c) entrada = AGRUPAMENTO(a) + AGRUPAMENTO(b) + AGRUPAMENTO(c) GROUPING_ID() saída
a 100 4
b 010 2
c 001 1
ab 110 6
ac 101 5
bc 011 3
abc 111 7

Definição técnica de GROUPING_ID()

Cada GROUPING_ID argumento deve ser um elemento da GROUP BY lista. GROUPING_ID() devolve um bitmap inteiro cujo menor n bits pode estar iluminado. Um bit iluminado indica que o argumento correspondente não é uma coluna de agrupamento para a linha de saída dada. O bit de ordem mais baixa corresponde ao argumento n, e o bit n-1de ordem mais baixa corresponde ao argumento 1.

Equivalentes GROUPING_ID()

Para uma única consulta de agrupamento, GROUPING (<column_expression>) é equivalente a GROUPING_ID (<column_expression>), e ambos retornam 0.

Por exemplo, as seguintes afirmações são equivalentes:

Enunciado A

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

Afirmação 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

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.

A. Use GROUPING_ID para identificar os níveis de agrupamento

O exemplo seguinte devolve a contagem de funcionários por Name e Title, e o total da empresa na AdventureWorks2025 base de dados. GROUPING_ID() é usado para criar um valor para cada linha na Title coluna que identifica o seu nível de agregação.

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. Use GROUPING_ID para filtrar um conjunto de resultados

Exemplo básico

No código seguinte, para devolver apenas as linhas que têm uma contagem de funcionários por título, remova os caracteres de comentário de HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Para devolver apenas linhas com contagem de funcionários por departamento, remova os caracteres de comentário 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;

Aqui está o conjunto de resultados sem filtros.

Nome Title Nível de Agrupamento Número de Funcionários Nome
Controlo de Documentos Especialista em Controlo 0 2 Controlo de Documentos
Controlo de Documentos Assistente de Controlo de Documentos 0 2 Controlo de Documentos
Controlo de Documentos Gestor de Controlo de Documentos 0 1 Controlo de Documentos
Controlo de Documentos NULL 1 5 Controlo de Documentos
Instalações e Manutenção Assistente Administrativo de Instalações 0 1 Instalações e Manutenção
Instalações e Manutenção Gestor de Instalações 0 1 Instalações e Manutenção
Instalações e Manutenção Zelador 0 4 Instalações e Manutenção
Instalações e Manutenção Supervisor de Manutenção 0 1 Instalações e Manutenção
Instalações e Manutenção NULL 1 7 Instalações e Manutenção
NULL NULL 3 12 NULL

Exemplo complexo

O exemplo seguinte é usado GROUPING_ID() para filtrar um conjunto de resultados que contém múltiplos níveis de agrupamento por nível de agrupamento. Código semelhante pode ser usado para criar uma vista com vários níveis de agrupamento, e um procedimento armazenado que chama a vista passando um parâmetro que filtra a vista por nível de agrupamento.

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. Use GROUPING_ID() com ROLLUP e CUBE para identificar os níveis de agrupamento

O código nos exemplos seguintes mostra usar GROUPING() para calcular a Bit Vector(base-2) coluna. GROUPING_ID() é usado para calcular a coluna correspondente Integer Equivalent . A ordem das colunas na GROUPING_ID() função é o oposto da ordem das colunas que são concatenadas pela GROUPING() função.

Nestes exemplos, GROUPING_ID() é usado para criar um valor para cada linha da Grouping Level coluna para identificar o nível de agrupamento. Os níveis de agrupamento nem sempre são uma lista consecutiva de inteiros que começam por 1 (0, 1, 2, ... n).

Observação

GROUPING e GROUPING_ID podem ser usados numa HAVING cláusula para filtrar um conjunto de resultados.

Exemplo de ROLLUP

Neste exemplo, nem todos os níveis de agrupamento aparecem como no exemplo seguinte CUBE . Se a ordem das colunas na ROLLUP lista for alterada, os valores de nível na Grouping Level coluna também têm de ser alterados.

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

Aqui está um conjunto de resultados parciais.

Ano Mês Day Total devido Vetor de bits (base-2) Equivalente inteiro Nível de Agrupamento
2007 1 1 1497452.6066 000 0 Ano Mês Dia
2007 1 2 21772.3494 000 0 Ano Mês Dia
2007 2 1 2705653.5913 000 0 Ano Mês Dia
2007 2 2 21684.4068 000 0 Ano Mês Dia
2008 1 1 1908122.0967 000 0 Ano Mês Dia
2008 1 2 46458.0691 000 0 Ano Mês Dia
2008 2 1 3108771.9729 000 0 Ano Mês Dia
2008 2 2 54598.5488 000 0 Ano Mês Dia
2007 1 NULL 1519224.956 100 1 Mês do Ano
2007 2 NULL 2727337.9981 100 1 Mês do Ano
2008 1 NULL 1954580.1658 100 1 Mês do Ano
2008 2 NULL 3163370.5217 100 1 Mês do Ano
2007 NULL NULL 4246562.9541 110 3 Ano
2008 NULL NULL 5117950.6875 110 3 Ano
NULL NULL NULL 9364513.6416 111 7 Total geral

Exemplo CUBE

Neste exemplo, a GROUPING_ID() função é usada para criar um valor para cada linha da Grouping Level coluna para identificar o nível de agrupamento.

Ao contrário ROLLUP do exemplo anterior, CUBE gera todos os níveis de agrupamento. Se a ordem das colunas na CUBE lista for alterada, os valores de nível na Grouping Level coluna também têm de ser alterados.

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

Aqui está um conjunto de resultados parciais.

Ano Mês Day Total devido Vetor de bits (base-2) Equivalente inteiro Nível de Agrupamento
2007 1 1 1497452.6066 000 0 Ano Mês Dia
2007 1 2 21772.3494 000 0 Ano Mês Dia
2007 2 1 2705653.5913 000 0 Ano Mês Dia
2007 2 2 21684.4068 000 0 Ano Mês Dia
2008 1 1 1908122.0967 000 0 Ano Mês Dia
2008 1 2 46458.0691 000 0 Ano Mês Dia
2008 2 1 3108771.9729 000 0 Ano Mês Dia
2008 2 2 54598.5488 000 0 Ano Mês Dia
2007 1 NULL 1519224.956 100 1 Mês do Ano
2007 2 NULL 2727337.9981 100 1 Mês do Ano
2008 1 NULL 1954580.1658 100 1 Mês do Ano
2008 2 NULL 3163370.5217 100 1 Mês do Ano
2007 NULL 1 4203106.1979 010 2 Dia do Ano
2007 NULL 2 43456.7562 010 2 Dia do Ano
2008 NULL 1 5016894.0696 010 2 Dia do Ano
2008 NULL 2 101056.6179 010 2 Dia do Ano
2007 NULL NULL 4246562.9541 110 3 Ano
2008 NULL NULL 5117950.6875 110 3 Ano
NULL 1 1 3405574.7033 001 4 Dia do Mês
NULL 1 2 68230.4185 001 4 Dia do Mês
NULL 2 1 5814425.5642 001 4 Dia do Mês
NULL 2 2 76282.9556 001 4 Dia do Mês
NULL 1 NULL 3473805.1218 101 5 Mês
NULL 2 NULL 5890708.5198 101 5 Mês
NULL NULL 1 9220000.2675 011 6 Day
NULL NULL 2 144513.3741 011 6 Day
NULL NULL NULL 9364513.6416 111 7 Total geral

Conteúdo relacionado

  • Last updated on