A cláusula GROUP BY especifica uma tabela de resultados intermediária que consiste em um agrupamento das linhas de R. R é o resultado da cláusula anterior da subseleção.
Em sua forma mais simples, uma cláusula GROUP BY contém uma expressão de agrupamento. Uma expressão de agrupamento é uma expressão usada na definição do agrupamento de R. Cada expressão ou nome de coluna incluído na expressão de agrupamento deve identificar inequivocamente uma coluna de R. Uma expressão de agrupamento não pode incluir uma seleção completa escalar ou uma expressão XMLQUERY ou XMLEXISTS, ou qualquer expressão ou função que não seja determinística ou tenha uma ação externa.
Formas mais complexas da cláusula GROUP BY incluem conjuntos de agrupamento e supergrupos.
O resultado de GROUP BY é um conjunto de grupos de linhas. Cada linha neste resultado representa o conjunto de linhas para as quais a expressão de agrupamento é igual. Para agrupamento, todos os valores nulos de uma expressão de agrupamento são considerados iguais.
Se uma expressão de agrupamento contiver colunas de ponto flutuante decimais e existirem várias representações do mesmo número nessas colunas, o número retornado poderá ser qualquer uma das representações do número.
Uma expressão de agrupamento pode ser usada em uma condição de pesquisa em uma cláusula HAVING, em uma expressão em uma cláusula SELECT ou em uma expressão de chave de classificação de uma cláusula ORDER BY. Em cada caso, a referência especifica apenas um valor para cada grupo.
Por exemplo, se a expressão de agrupamento for col1+col2, uma expressão permitida na lista SELECT será col1+col2+3. As regras de associatividade para expressões não permitem a expressão semelhante, 3+col1+col2, a menos que parênteses sejam usados para garantir que a expressão correspondente seja avaliada na mesma ordem. Assim, 3+(col1+col2) também é permitido na lista SELECT. Se o operador de concatenação for usado, a expressão de agrupamento deve ser usada exatamente como a expressão foi especificada na lista SELECT.
Se a expressão de agrupamento contiver strings de comprimento variável com espaços em branco à direita, os valores no grupo podem diferir no número de espaços em branco à direita e podem não ter todos o mesmo comprimento. Nesse caso, uma referência à expressão de agrupamento ainda especifica apenas um valor para cada grupo. No entanto, o valor de um grupo é escolhido arbitrariamente a partir do conjunto de valores disponíveis ou de uma forma normalizada que pode ou não ser do conjunto de valores disponíveis. Assim, o comprimento real do valor do resultado é imprevisível.
Conforme observado, há alguns casos em que a cláusula GROUP BY não pode se referir diretamente a uma coluna especificada na cláusula SELECT como uma expressão (seleção completa escalar, funções de ação não determinísticas ou externas). Para agrupar usando tal expressão, use uma expressão de tabela aninhada ou uma expressão de tabela comum para primeiro fornecer uma tabela de resultados com a expressão como uma coluna do resultado.
Uma especificação de conjuntos de agrupamento pode ser usada para especificar várias cláusulas de agrupamento em uma única instrução. Isso pode ser pensado como a união de dois ou mais grupos de linhas em um único conjunto de resultados. É logicamente equivalente à união de várias subseleções com a cláusula group by em cada subseleção correspondente a um conjunto de agrupamento. Um conjunto de agrupamento pode ser um único elemento ou pode ser uma lista de elementos delimitados por parênteses, onde um elemento é uma expressão de agrupamento ou um supergrupo. Os grupos podem ser calculados com uma única passagem sobre a tabela base usando conjuntos de agrupamento .
Uma expressão de agrupamento simples ou as formas mais complexas de supergrupos são suportadas pela especificação de conjuntos de agrupamento.
Observe que os conjuntos de agrupamento são os blocos de construção fundamentais para as operações GROUP BY. Um simples GROUP BY com uma única coluna pode ser considerado um conjunto de agrupamento com um elemento. Por exemplo:
GROUP BY a
é o mesmo que
GROUP BY GROUPING SETS((a))
e
GROUP BY a,b,c
é o mesmo que
GROUP BY GROUPING SETS((a,b,c))
As colunas de não agregação da lista SELECT da subseleção que são excluídas de um conjunto de agrupamento retornarão um valor nulo para essas colunas para cada linha gerada para esse conjunto de agrupamento. Isso reflete o fato de que a agregação foi feita sem considerar os valores dessas colunas.
ROLLUP (grouping-expression-list)
Um agrupamento ROLLUP é uma extensão da cláusula GROUP BY que produz um conjunto de resultados contendo linhas de subtotal além das linhas agrupadas “ regulares ” . As linhas de subtotal são linhas “ superagregadas ” que contêm agregações adicionais cujos valores são derivados pela aplicação das mesmas funções de agregação que foram usadas para obter as linhas agrupadas. Essas linhas são chamadas de linhas de subtotais, porque esse é o uso mais comum; no entanto, qualquer função de agregação pode ser usada para a agregação.
A função agregada GROUPING pode ser usada para indicar se uma linha foi gerada pelo supergrupo.
Um agrupamento ROLLUP é uma série de conjuntos de agrupamento. A especificação geral de um ROLLUP com n elementos
GROUP BY ROLLUP(C1,C2,...,Cn-1,Cn)
é equivalente a
GROUP BY GROUPING SETS((C1,C2,...,Cn-1,Cn)
(C1,C2,...,Cn-1)
...
(C1,C2)
(C1)
() )
Observe que os n elementos do ROLLUP se traduzem em n +1 conjuntos de agrupamento.
Observe também que a ordem na qual as expressões de agrupamento são especificadas é significativa para ROLLUP. Por exemplo, a seguinte cláusula:
GROUP BY ROLLUP(a,b)
é equivalente a:
GROUP BY GROUPING SETS((a,b)
(a)
() )
Da mesma forma, a seguinte cláusula:
GROUP BY ROLLUP(b,a)
é equivalente a:
GROUP BY GROUPING SETS((b,a)
(b)
() )
A cláusula ORDER BY é a única maneira de garantir a ordem das linhas no conjunto de resultados.
CUBE (grouping-expression-list)
Um agrupamento CUBE é uma extensão da cláusula GROUP BY que produz um conjunto de resultados que contém todas as linhas de uma agregação ROLLUP e, além disso, contém linhas de "tabulação cruzada". As linhas de tabulação cruzada são linhas "superagregadas" adicionais que não fazem parte de uma agregação com subtotais. A função agregada GROUPING pode ser usada para indicar se uma linha foi gerada pelo supergrupo.
Semelhante a um ROLLUP, um agrupamento CUBE também pode ser pensado como uma série de conjuntos de agrupamento. No caso de um CUBO, todas as permutações da lista de expressões de agrupamento em cubos são computadas junto com o total geral. Portanto, os n elementos de um CUBO se traduzem em 2** n (2 elevado a n ) conjuntos de agrupamento. Por exemplo, uma especificação de:
GROUP BY CUBE(a,b,c)
é equivalente a:
GROUP BY GROUPING SETS((a,b,c)
(a,b)
(a,c)
(b,c)
(a)
(b)
(c)
() )
Observe que os três elementos do CUBO se traduzem em oito conjuntos de agrupamento.
A ordem de especificação dos elementos não importa para o CUBE. 'CUBE (DayOfYear, Sales_Person)' e 'CUBE (Sales_Person, DayOfYear)' geram os mesmos conjuntos de resultados. O uso da palavra 'mesmo' se aplica ao conteúdo do conjunto de resultados, não à sua ordem. A cláusula ORDER BY é a única maneira de garantir a ordem das linhas no conjunto de resultados.
grouping-expression-list
Uma lista de expressões de agrupamento é usada em uma cláusula CUBE ou ROLLUP para definir o número de elementos na operação CUBE ou ROLLUP. Isso é controlado usando parênteses para delimitar elementos com várias expressões de agrupamento s.
Por exemplo, suponha que uma consulta deva retornar as despesas totais para o ROLLUP de Cidade em uma Província, mas não em um Condado. No entanto, a cláusula:
GROUP BY ROLLUP(Province, County, City)
resulta em linhas de subtotais indesejadas para o Condado. Na cláusula:
GROUP BY ROLLUP(Province, (County, City))
o composto (County, City) forma um elemento no ROLLUP e, portanto, uma consulta que usa essa cláusula produzirá o resultado necessário. Em outras palavras, o ROLLUP de dois elementos:
GROUP BY ROLLUP(Province, (County, City))
gera:
GROUP BY GROUPING SETS((Province, County, City)
(Province)
() )
e o ROLLUP de três elementos gera:
GROUP BY GROUPING SETS((Province, County, City)
(Province, County)
(Province)
() )
grand-total
Tanto CUBE quanto ROLLUP retornam uma linha que é a agregação geral (total geral). Isso pode ser especificado separadamente com parênteses vazios na cláusula GROUPING SET. Também pode ser especificado diretamente na cláusula GROUP BY, embora não haja efeito no resultado da consulta.
Combinando conjuntos de agrupamento
Isso pode ser usado para combinar qualquer um dos tipos de cláusulas GROUP BY. Quando campos de expressão de agrupamento simples são combinados com outros grupos, eles são "anexados" ao início dos conjuntos de agrupamento resultantes. Quando as expressões ROLLUP ou CUBE são combinadas, elas operam de forma semelhante aos "multiplicadores" na expressão restante, formando entradas de conjunto de agrupamento adicionais de acordo com a definição de ROLLUP ou CUBE.
Por exemplo, combinar elementos de expressão de agrupamento age da seguinte forma:
GROUP BY a, ROLLUP(b,c)
é equivalente a
GROUP BY GROUPING SETS((a,b,c)
(a,b)
(a) )
Ou similarmente,
GROUP BY a, b, ROLLUP(c,d)
é equivalente a
GROUP BY GROUPING SETS((a,b,c,d)
(a,b,c)
(a,b) )
A combinação de elementos ROLLUP atua da seguinte forma:
GROUP BY ROLLUP(a), ROLLUP(b,c)
é equivalente a
GROUP BY GROUPING SETS((a,b,c)
(a,b)
(a)
(b,c)
(b)
() )
De forma similar,
GROUP BY ROLLUP(a), CUBE(b,c)
é equivalente a
GROUP BY GROUPING SETS((a,b,c)
(a,b)
(a,c)
(a)
(b,c)
(b)
(c)
() )
A combinação de elementos CUBE e ROLLUP atua da seguinte forma:
GROUP BY CUBE(a,b), ROLLUP(c,d)
é equivalente a
GROUP BY GROUPING SETS((a,b,c,d)
(a,b,c)
(a,b)
(a,c,d)
(a,c)
(a)
(b,c,d)
(b,c)
(b)
(c,d)
(c)
() )
Semelhante a uma expressão de agrupamento simples , a combinação de conjuntos de agrupamento também elimina duplicatas dentro de cada conjunto de agrupamento. Por exemplo,
GROUP BY a, ROLLUP(a,b)
é equivalente a
GROUP BY GROUPING SETS((a,b)
(a) )
Um exemplo mais completo de combinação de conjuntos de agrupamento é construir um conjunto de resultados que elimine certas linhas que podem ser retornadas para uma agregação CUBE completa.
Por exemplo, considere a seguinte cláusula GROUP BY:
GROUP BY Region,
ROLLUP(Sales_Person, WEEK(Sales_Date)),
CUBE(YEAR(Sales_Date), MONTH (Sales_Date))
A coluna listada imediatamente à direita de GROUP BY é agrupada, aquelas dentro dos parênteses após ROLLUP são acumuladas e aquelas dentro dos parênteses após CUBE são cúbicas. Assim, a cláusula GROUP BY resulta em um cubo de MONTH dentro de YEAR que é então acumulado em WEEK em Sales_Person dentro da agregação Region. Isso não resulta em nenhuma linha de total geral ou nenhuma linha de tabulação cruzada em Region, Sales_Person ou WEEK(Sales_Date), portanto, produz menos linhas do que a cláusula:
GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),
YEAR(Sales_Date), MONTH(Sales_Date) )