SELECT - AGRUPAR POR cláusula (Transact-SQL)

Aplica-se a:SQL ServerBase de Dados SQL do AzureInstância Gerida do Azure SQLAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Ponto de Extremidade de Análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de Dados SQL no Microsoft Fabric

Uma SELECT cláusula de instrução que divide o resultado da consulta em grupos de linhas, normalmente realizando uma ou mais agregações em cada grupo. A SELECT instrução devolve uma linha para cada grupo.

Syntax

Transact-SQL convenções de sintaxe

Sintaxe compatível com ISO para SQL Server e Azure SQL Database:

GROUP BY {
      column-expression
    | ROLLUP ( <group_by_expression> [ , ...n ] )
    | CUBE ( <group_by_expression> [ , ...n ] )
    | GROUPING SETS ( <grouping_set> [ , ...n ]  )
    | () --calculates the grand total
} [ , ...n ]

<group_by_expression> ::=
      column-expression
    | ( column-expression [ , ...n ] )

<grouping_set> ::=
      () --calculates the grand total
    | <grouping_set_item>
    | ( <grouping_set_item> [ , ...n ] )

<grouping_set_item> ::=
      <group_by_expression>
    | ROLLUP ( <group_by_expression> [ , ...n ] )
    | CUBE ( <group_by_expression> [ , ...n ] )

Sintaxe não compatível com ISO para SQL Server e Azure SQL Database (apenas compatibilidade retroativa):

GROUP BY {
       ALL column-expression [ , ...n ]
    | column-expression [ , ...n ]  WITH { CUBE | ROLLUP }
       }

Sintaxe do Azure Synapse Analytics:

GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]
    | column-expression
    | ROLLUP ( <group_by_expression> [ , ...n ] )
} [ , ...n ]

Sintaxe para Sistema de Plataforma de Análise (PDW):

GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]
    | column-expression
} [ , ...n ]

Arguments

Expressão em coluna

Especifica uma coluna ou um cálculo não agregado numa coluna. Esta coluna pode pertencer a uma tabela, tabela derivada ou vista. A coluna deve aparecer na FROM cláusula da SELECT declaração, mas não precisa de aparecer na SELECT lista.

Para expressões válidas, consulte expressão.

A coluna deve aparecer na FROM cláusula da SELECT declaração, mas não é obrigatória a aparecer na SELECT lista. No entanto, deve incluir cada tabela ou coluna de visualização na GROUP BY lista se a usar em qualquer expressão não agregada da <select> lista.

AGRUPAR POR opções

As opções seguintes estendem a cláusula básica GROUP BY para suportar agregação hierárquica, sumarização multidimensional, combinações personalizadas de agrupamento e comportamentos de execução específicos da plataforma. As consultas podem usar estas opções para produzir subtotais e totais grandiosos numa única operação lógica.

• ROLLUP ( <group_by_expression> [ , ... n ] )

  Gera subtotais hierárquicos para as colunas listadas e um total final (por exemplo, (a,b,c), (a,b), (a), ()). Usa-o para relatórios de drill-up, como o ano>e o mês do trimestre>.

• CUBE ( <group_by_expression> [ , ... n ] )

  Produz todas as combinações das colunas especificadas (a rede completa de 2^n) mais o total geral. Use-o para análise multidimensional em cada fatia.

• CONJUNTOS AGRUPADOS ( <grouping_set> [ , ... n ] )

  Define os agrupamentos exatos a calcular (incluindo () o total geral) numa só passagem. Esta opção é funcionalmente semelhante a uma UNION ALL de múltiplas GROUP BY consultas, mas otimizada em conjunto.

• () (conjunto de agrupamento vazio)

  Abreviação para calcular apenas o total geral em todas as linhas. Use-o sozinho como GROUP BY () ou dentro GROUPING SETSde .

• ALL coluna expressão [ , ... n](não-ISO; compatibilidade retroativa)

  Abreviação para agrupar por todos os itens selecionados não agregados. Mantido para compatibilidade; A disponibilidade e a semântica variam.

• expressão de coluna [ , ... n ] COM { CUBE | 'ROLLUP }(forma legada)

  Sintaxe antiga, não ISO, equivalente a GROUP BY CUBE(...) ou GROUP BY ROLLUP(...). Suportado apenas para compatibilidade com versões anteriores. Use as subcláusulas ISO sempre que possível.

• COM (DISTRIBUTED_AGG)

  As dicas distribuíam a execução para agregações ao agrupar por uma única coluna. Os pools SQL dedicados Azure Synapse Analytics e o Analytics Platform System (PDW) são as únicas plataformas que suportam esta opção.

AGRUPAR POR coluna-expressão [ ,... n ]

Agrupa os SELECT resultados da instrução de acordo com os valores numa lista de uma ou mais expressões de coluna.

Por exemplo, esta consulta cria uma Sales tabela com colunas para Region, Territory, e Sales. Insere quatro linhas, e duas das linhas têm valores correspondentes para Region e Territory.

CREATE TABLE Sales
(
    Region VARCHAR (50),
    Territory VARCHAR (50),
    Sales INT
);
GO

INSERT INTO Sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO Sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO Sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO Sales VALUES (N'United States', N'Montana', 100);

A Sales tabela contém estas linhas:

Esta próxima consulta agrupa Region e Territory e devolve a soma agregada para cada combinação de valores.

SELECT Region,
       Territory,
       SUM(sales) AS TotalSales
FROM Sales
GROUP BY Region, Territory;

O resultado da consulta tem três linhas, pois existem três combinações de valores para Region e Territory. O TotalSales para o Canadá e a Colúmbia Britânica é a soma de duas linhas.

A expressão da coluna em GROUP BY não pode conter:

• Um alias de coluna que defines na SELECT lista. Pode usar um alias de coluna para uma tabela derivada definida na FROM cláusula.
• Uma coluna do tipo texto, ntext ou imagem. No entanto, pode usar uma coluna de texto, ntext ou imagem como argumento para uma função que devolve um valor de um tipo de dado válido. Por exemplo, a expressão pode usar SUBSTRING() e CAST(). Esta regra também se aplica a expressões na HAVING oração.
• Métodos de tipo de dados xml. Pode incluir uma função definida pelo utilizador que utiliza métodos de tipo de dados xml . Pode incluir uma coluna computada que utiliza métodos de tipo de dados xml .
• Uma subconsulta. A consulta devolve o erro 144.
• Uma coluna de um modo de exibição indexado.

São permitidas as seguintes declarações:

SELECT ColumnA,
       ColumnB
FROM T
GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB
FROM T
GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

SELECT ColumnA + ColumnB + constant
FROM T
GROUP BY ColumnA, ColumnB;

As seguintes declarações não são permitidas:

SELECT ColumnA,
       ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

SELECT ColumnA + constant + ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

AGRUPAR POR ROLAGEM ()

Cria um grupo para cada combinação de expressões de coluna. Além disso, junta os resultados em subtotais e totais grandiosos. Quando cria os grupos, move-se da direita para a esquerda, diminuindo o número de expressões de coluna para agrupamento e agregações.

A ordem das colunas afeta a ROLLUP saída e pode afetar o número de linhas no conjunto de resultados.

Por exemplo, GROUP BY ROLLUP (col1, col2, col3, col4) cria grupos para cada combinação de expressões de coluna nas seguintes listas:

• Col1, Col2, Cole3, Col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• COL1, NULO, NULO, NULO
• NULL, NULL, NULL, NULL (O grupo com os NULL valores é o total geral)

Usando a tabela do exemplo anterior, este código executa uma GROUP BY ROLLUP operação em vez de um básico GROUP BY.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Territory);

O resultado da consulta tem as mesmas agregações que o básico GROUP BY sem o ROLLUP. Além disso, cria subtotais para cada valor da Região. Finalmente, dá um total geral para todas as linhas. O resultado é assim:

GRUPO POR CUBO ()

GROUP BY CUBE cria grupos para todas as combinações possíveis de colunas. Para GROUP BY CUBE (a, b), os resultados têm grupos para valores únicos de (a, b), (NULL, b), (a, NULL), e (NULL, NULL).

Usando a tabela dos exemplos anteriores, este código executa uma GROUP BY CUBE operação sobre Região e Território.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Territory);

O resultado da consulta tem grupos para valores únicos de (Region, Territory), (NULL, Territory), (Region, NULL), e (NULL, NULL). Os resultados são os seguintes:

AGRUPAR POR AGRUPAMENTO DE CONJUNTOS ()

A GROUPING SETS opção combina múltiplas GROUP BY cláusulas numa só GROUP BY oração. Os resultados são os mesmos que se usam UNION ALL nos grupos especificados.

Por exemplo, GROUP BY ROLLUP (Region, Territory) e GROUP BY GROUPING SETS ( ROLLUP (Region, Territory)) retornar os mesmos resultados.

Quando GROUPING SETS tem dois ou mais elementos, o resultado é uma união dos elementos. Este exemplo devolve a união dos ROLLUP resultados e CUBE para Região e Território.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(ROLLUP(Region, Territory), CUBE(Region, Territory));

Os resultados são os mesmos desta consulta que devolve uma união das duas GROUP BY afirmações.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Territory)
UNION ALL
SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Territory);

O SQL não consolida grupos duplicados gerados para uma GROUPING SETS lista. Por exemplo, em GROUP BY ((), CUBE (Region, Territory)), ambos os elementos retornam uma linha para o total geral, e ambas as linhas aparecem nos resultados.

Suporte para funcionalidades ISO e ANSI SQL-2006 GROUP BY

A GROUP BY cláusula suporta todas GROUP BY as funcionalidades incluídas no padrão SQL-2006, com as seguintes exceções de sintaxe:

• Agrupar conjuntos não são permitidos na GROUP BY cláusula, a menos que façam parte de uma lista explícita GROUPING SETS . Por exemplo, GROUP BY Column1, (Column2, ...ColumnN) é permitido no standard mas não no Transact-SQL. Transact-SQL suportes GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) e GROUP BY Column1, Column2, ... ColumnN, que são semanticamente equivalentes. Estas orações são semanticamente equivalentes ao exemplo anterior GROUP BY . Esta restrição evita a possibilidade de GROUP BY Column1, (Column2, ...ColumnN) poderem ser mal interpretados como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), que não são semanticamente equivalentes.

• Conjuntos de agrupamento não são permitidos dentro de conjuntos de agrupamento. Por exemplo, é permitido no padrão SQL-2006, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) mas não no Transact-SQL. Transact-SQL permite GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn) ou GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn)), que são semanticamente equivalentes ao primeiro GROUP BY exemplo e têm uma sintaxe mais clara.

GRUPO POR ()

Especifica o grupo vazio, que gera o total geral. Este grupo é útil como um dos elementos de um GROUPING SET. Por exemplo, esta afirmação apresenta o total de vendas para cada região e depois apresenta o total geral para todas as regiões.

SELECT Region,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(Region, ());

AGRUPAR POR TODOS coluna-expressão [ ,... n ]

Aplica-se a: SQL Server e Azure SQL Database

Especifica se devem incluir todos os grupos nos resultados, independentemente de cumprirem ou não os critérios de pesquisa da WHERE cláusula. Os grupos que não cumprem os critérios de pesquisa têm NULL para a agregação.

GROUP BY ALL:

• Não é suportado em consultas que acedam a tabelas remotas se também houver uma WHERE cláusula na consulta.
• Falha em colunas que têm o atributo FILESTREAM.

Suporte para funcionalidades ISO e ANSI SQL-2006 GROUP BY

A GROUP BY cláusula suporta todas GROUP BY as funcionalidades incluídas no padrão SQL-2006, com as seguintes exceções de sintaxe:

• Só pode usar GROUP BY ALL e GROUP BY DISTINCT numa cláusula básica GROUP BY que contenha expressões de coluna. Não podes usá-los com os GROUPING SETS, ROLLUP, CUBE, WITH CUBE, ou WITH ROLLUP construtos. ALL é o padrão e é implícito. Só podes usá-lo na sintaxe retrocompatível.

AGRUPAR POR coluna-expressão [ ,... n ] COM { CUBO | ROLLUP }

Aplica-se a: SQL Server e Azure SQL Database

COM (DISTRIBUTED_AGG)

Aplica-se a: Azure Synapse Analytics and Analytics Platform System (PDW)

A DISTRIBUTED_AGG dica de consulta obriga o sistema de processamento massivamente paralelo (MPP) a redistribuir uma tabela numa coluna específica antes de realizar uma agregação. Podes usar a DISTRIBUTED_AGG dica de consulta apenas numa coluna da GROUP BY cláusula. Após a conclusão da consulta, a tabela redistribuída é descartada. A tabela original não foi alterada.

Observações

Como GROUP BY interage com a instrução SELECT

SELECT Lista:

• Agregados vetoriais. Se incluir funções agregadas na SELECT lista, GROUP BY calcula um valor resumo para cada grupo. Estas funções são conhecidas como agregados vetoriais.
• Agregados distintos. Os agregados , , e AVG(DISTINCT <column_name>) trabalham com COUNT(DISTINCT <column_name>), SUM(DISTINCT <column_name>), e ROLLUP. CUBEGROUPING SETS

WHERE cláusula:

• O SQL remove linhas que não cumprem as condições da WHERE cláusula antes de realizar qualquer operação de agrupamento.

HAVING cláusula:

• O SQL utiliza a HAVING cláusula para filtrar grupos no conjunto de resultados.

ORDER BY cláusula:

• Use a ORDER BY cláusula para ordenar o conjunto de resultados. A GROUP BY cláusula não ordena o conjunto de resultados.

NULL valores:

• Se uma coluna de agrupamento contiver NULL valores, o Motor de Base de Dados trata todos NULL os valores como iguais e reúne-os num único grupo.

Limitações

Aplica-se a: SQL Server e Azure Synapse Analytics

Para uma GROUP BY cláusula que usa ROLLUP, CUBE, ou GROUPING SETS, o número máximo de expressões é 32. O número máximo de grupos é de 4.096 (2¹²). Os exemplos seguintes falham porque a GROUP BY cláusula tem mais de 4.096 grupos.

• O exemplo seguinte gera 4.097 (2¹² + 1) conjuntos de agrupamento e depois falha.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b)
  

• O exemplo seguinte gera 4.097 (^{2 12} + 1) grupos e depois falha. Ambos e CUBE () o () conjunto de agrupamento produzem uma linha total geral e os conjuntos de agrupamento duplicados não são eliminados.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
  

• Este exemplo utiliza a sintaxe retrocompatível. Gera 8.192 (2¹³) conjuntos de agrupamento e depois falha.

  GROUP BY CUBE (a1, ..., a13)
  GROUP BY a1, ..., a13 WITH CUBE
  

  Para cláusulas retrocompatíveis GROUP BY que não contêm CUBE ou ROLLUP, os GROUP BY tamanhos das colunas, as colunas agregadas e os valores agregados envolvidos na consulta limitam o número de GROUP BY itens. Este limite origina-se do limite de 8.060 bytes na tabela de trabalho intermédia que contém os resultados das consultas intermédias. Pode usar um máximo de 12 expressões de agrupamento quando especifica CUBE ou ROLLUP.

Comparação das funcionalidades GROUP BY suportadas

A tabela seguinte descreve as GROUP BY funcionalidades que diferentes produtos suportam.

¹Compatibilidade de base de dados nível 100 ou superior.

² A mensagem de erro devolvida é: Incorrect syntax near the keyword 'cube'|'rollup'.

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 uma cláusula básica GROUP BY

O exemplo a seguir recupera o total de cada SalesOrderID um da SalesOrderDetail tabela. Este exemplo usa AdventureWorks.

SELECT SalesOrderID,
       SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

B. Usar uma cláusula GROUP BY com várias tabelas

O exemplo a seguir recupera o número de funcionários de cada City um da Address tabela unida à EmployeeAddress tabela. Este exemplo usa AdventureWorks.

SELECT a.City,
       COUNT(bea.AddressID) AS EmployeeCount
FROM Person.BusinessEntityAddress AS bea
     INNER JOIN Person.Address AS a
         ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;

C. Usar uma cláusula GROUP BY com uma expressão

O exemplo a seguir recupera o total de vendas de cada ano usando a DATEPART função. Deve incluir a mesma expressão tanto na SELECT lista como GROUP BY na cláusula.

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
       SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy, OrderDate)
ORDER BY DATEPART(yyyy, OrderDate);

D. Use uma cláusula GROUP BY com uma cláusula HAVING

O exemplo seguinte usa a HAVING cláusula para especificar quais os grupos gerados na GROUP BY cláusula que devem ser incluídos no conjunto de resultados.

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
       SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy, OrderDate)
HAVING DATEPART(yyyy, OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy, OrderDate);

Exemplos: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Utilização básica da cláusula GROUP BY

O exemplo a seguir localiza o valor total de todas as vendas em cada dia. A consulta devolve uma linha contendo a soma de todas as vendas de cada dia.

-- Uses AdventureWorksDW
SELECT OrderDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
ORDER BY OrderDateKey;

F. Uso básico da dica DISTRIBUTED_AGG

Este exemplo usa a dica de consulta DISTRIBUTED_AGG para forçar o appliance a embaralhar a tabela na CustomerKey coluna antes de realizar a agregação.

-- Uses AdventureWorksDW
SELECT CustomerKey,
       SUM(SalesAmount) AS sas
FROM FactInternetSales
GROUP BY CustomerKey WITH(DISTRIBUTED_AGG)
ORDER BY CustomerKey DESC;

G. Variações sintáticas para GROUP BY

Quando a lista de seleção não tem agregações, deve incluir cada coluna da lista de seleção da GROUP BY lista. Podes incluir colunas calculadas na lista de seleção, mas não és obrigado a incluí-las na GROUP BY lista. Estes exemplos mostram afirmações sintaticamente válidas SELECT :

-- Uses AdventureWorks
SELECT LastName,
       FirstName
FROM DimCustomer
GROUP BY LastName, FirstName;

SELECT NumberCarsOwned
FROM DimCustomer
GROUP BY YearlyIncome, NumberCarsOwned;

SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost
FROM FactInternetSales
GROUP BY SalesAmount, TaxAmt, Freight;

SELECT SalesAmount,
       SalesAmount * 1.10 AS SalesTax
FROM FactInternetSales
GROUP BY SalesAmount;

SELECT SalesAmount
FROM FactInternetSales
GROUP BY SalesAmount, SalesAmount * 1.10;

H. Use uma cláusula GROUP BY com múltiplas expressões GROUP BY

O exemplo a seguir agrupa resultados usando vários GROUP BY critérios. Se, dentro de cada OrderDateKey grupo, existirem subgrupos que o DueDateKey valor diferencia, a consulta define um novo agrupamento para o conjunto de resultados.

-- Uses AdventureWorks
SELECT OrderDateKey,
       DueDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey
ORDER BY OrderDateKey;

I. Use uma cláusula GROUP BY com uma cláusula HAVING

O exemplo seguinte usa a HAVING cláusula para especificar os grupos gerados na GROUP BY cláusula que devem ser incluídos no conjunto de resultados. Apenas os grupos com datas de encomenda em 2004 ou posteriores são incluídos nos resultados.

-- Uses AdventureWorks
SELECT OrderDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20040000
ORDER BY OrderDateKey;

Conteúdo relacionado

• GROUPING_ID (Transact-SQL)
• AGRUPAMENTO (Transact-SQL)
• SELECIONAR (Transact-SQL)
• Cláusula SELECT (Transact-SQL)