artigo sql magazine 48 - Desvendando a cláusula GROUP BY – Parte 2

Guia do artigo:

• Agrupamento através de WITH ROLLUP
• Agrupamento utilizando WITH CUBE
• Agrupando conjuntos
• Agrupamentos com ROLLUP e CUBE
• Conclusão

Daremos continuidade neste artigo ao estudo sobre a cláusula GROUP BY. Focaremos nesta matéria usos mais avançados deste recurso da SQL.

Agrupamento através de WITH ROLLUP

A cláusula GROUP BY possui muitas facilidades para agrupar e calcular informações agregadas, tais como o número total das penalidades ou a soma de todas as penalidades. Entretanto, até o que vimos na primeira parte desta matéria, todos os relatórios retornam resultados nos quais todas as informações estão no mesmo nível de agregação. Mas e se nós quisermos ver as informações pertencentes aos diferentes níveis de agregação contidos dentro de um relatório? Imagine que nós queremos ver, em um relatório, valor total das penalidades para cada jogador e, também, o valor total das penalidades para todos os jogadores. Isto não é possível com os formulários das cláusulas de GROUP BY que nós discutimos até aqui. Para esta finalidade, mais de dois agrupamentos dentro de uma cláusula de GROUP BY são requeridos. Adicionando a especificação WITH ROLLUP à cláusula de GROUP BY, isto se torna possível.

Exemplo 1. Para cada jogado, descubra a soma de todas as suas penalidades mais a soma de todas as penalidades.

Uma maneira de combinar esses dois agrupamentos em um relatório é usar o operador UNION (ver Listagem 1).

SELECT PLAYERNO, SUM(AMOUNT)
FROM PENALTIES
GROUP BY PLAYERNO
UNION
SELECT CAST(NULL AS SIGNED INTEGER), SUM(AMOUNT)
FROM PENALTIES

Para a consulta da Listagem 1 temos o resultado apresentado na Listagem 2.

PLAYERNO SUM(AMOUNT)
-------- -----------
6 100.00
8 25.00
27 175.00
44 130.00
104 50.00
? 480.00

Explicação: As linhas neste resultado intermediário, no qual a coluna PLAYERNO é preenchida, formam o resultado do primeiro bloco select. As linhas nas quais PLAYERNO é igual a NULL compõem o resultado do segundo bloco select. As primeiras cinco linhas contêm informações sobre o nível de agregação dos números dos jogadores e a última linha, contém dados sobre o nível de agregação de todas as linhas.

A especificação WITH ROLLUP foi introduzida para simplificar este tipo de relatório. WITH ROLLUP pode ser usado para requerer múltiplos agrupamentos com uma única cláusula de GROUP BY. Neste caso, podemos obter o mesmo resultado com a consulta:

SELECT PLAYERNO, SUM(AMOUNT)
 FROM PENALTIES
 GROUP BY PLAYERNO WITH ROLLUP

Explicação: O resultado deste relatório é o mesmo que o do anterior. A especificação WITH ROLLUP indica que, depois do resultado ser agrupado com [PLAYERNO], outro agrupamento se faz necessário - neste caso, em todas as linhas.

Vamos analisar uma explicação mais formal. Imagine que numa cláusula GROUP BY as expressões E1, E2, E3 e E4 são especificadas. O agrupando executado é [E1, E2, E3, E4]. Quando nós adicionarmos a especificação WITH ROLLUP a este GROUP BY, todo um conjunto de agrupamentos será executado: [E1, E2, E3, E4], [E1, E2, E3], [E1, E2], [E1] e, finalmente, []. A especificação [] significa que todas as linhas estão agrupadas em um grupo. O agrupamento especificado é visto como o nível mais elevado de agregação que é pedido e também indica que todos os níveis mais elevados de agregação devem ser calculados outra vez. Agregar para cima é chamado rollup na literatura. Assim, o resultado deste relatório contém informações em cinco níveis diferentes de agregação.

Exemplo 2. Para cada combinação de sexo-cidade, obtenha o número de jogadores, obtenha o número total de jogadores por sexo e o número total de jogadores em toda a tabela:

SELECT SEX, TOWN, COUNT(*)
FROM PLAYERS
GROUP BY SEX, TOWN WITH ROLLUP

Para esta consulta, temos o resultado apresentado na Listagem 3.

SEX TOWN COUNT(*)
--- --------- --------
1: M Stratford 7
2: M Inglewood 1
3: M Douglas 1
4: M ? 9
5: V Midhurst 1
6: V Inglewood 1
7: V Plymouth 1
8: V Eltham 2
9: V ? 5
10:? ? 14

Explicação: Este resultado possui três níveis de agregação. As linhas 1, 2, 3, 5, 6, 7 e 8 formam o nível mais baixo e foram adicionadas por causa do agrupamento [SEXO, CIDADE]; as linhas 4 e 9 foram adicionadas por causa do agrupamento [SEXO]; e a última linha forma o nível mais elevado de agregação e foi adicionada por causa do agrupamento []. Ele contém o número total dos jogadores.

Agrupamento utilizando WITH CUBE

Outra maneira de se obter múltiplos agrupamentos contidos numa cláusula GROUP BY é utilizar a especificação WITH CUBE.

Novamente, utilizaremos uma maneira mais formal para explicar esta nova especificação. Imagine que a especificação WITH CUBE é adicionada a uma cláusula GROUP BY que possui as expressões E1, E2 e E3. Como resultado, diversos agrupamentos são executados: [E1, E2, E3], [E1, E2], [E1, E3], [E2, E3], [E1], [E2], [E3] e, finalmente, []. A lista tem início com um agrupamento em todas as três expressões, seguido por três agrupamentos com duas expressões cada (um agrupamento para cada combinação possível de duas expressões) e seguido por um agrupamento para cada expressão separadamente; ele encerra com um agrupamento de todas as linhas.

Exemplo 3. Agrupe a tabela PLAYERS nas colunas SEX com TOWN e adicione uma especificação WITH CUBE (ver Listagem 4).

SELECT ROW_NUMBER() OVER () AS SEQNO,
SEX, TOWN, COUNT(*)
FROM PLAYERS
GROUP BY SEX, TOWN WITH CUBE
ORDER BY SEX, TOWN

Para a consulta da Listagem 4 temos o resultado apresentado na Listagem 5.

 
SEQNO SEX TOWN COUNT(*)
  ----- --- --------- --------
 1. 1 M Stratford 7
 2. 2 M Inglewood 1
 3. 3 M Douglas 1
 4. 4 M ? 9
 5. 5 F Midhurst 1
 6. 6 F Inglewood 1
 7. 7 F Plymouth 1
 8. 8 F Eltham 2
 9. 9 F ? 5
 10. 10 ? Stratford 7
 11. 11 ? Midhurst 1
 12. 12 ? Inglewood 2
 13. 13 ? Plymouth 1
 14. 14 ? Douglas 1
 15. 15 ? Eltham 2 
 16. 16 ? ? 14

Explicação: As linhas 1, 2, 3, 5, 6, 7 e 8 foram adicionadas por causa do agrupamento [SEX, TOWN]. As linhas 4 e 9 foram adicionadas por causa do agrupamento [SEX]. As linhas 10 até 15 foram adicionadas por conta do agrupamento em [TOWN] e a linha 16 foi adicionada por conta do agrupamento de todas as linhas.

Agrupando conjuntos

As cláusulas GROUP BY que foram descritas até aqui utilizam a notação curta para a especificação de agrupamentos. SQL também tem uma notação mais extensa. A especificação grouping sets indica em quais expressões devem ser executados agrupamentos. Vamos aos exemplos.

Exemplo 4. Para cada cidade, obtenha a data de nascimento mais recente.

Com a notação reduzida, a consulta para obter o resultado desejado pode ser obtida com:

SELECT TOWN, MIN(BIRTH_DATE)
FROM PLAYERS
GROUP BY TOWN

Utilizando a outra notação temos:

SELECT TOWN, MIN(BIRTH_DATE)
 FROM PLAYERS
 GROUP BY GROUPING SETS ((TOWN))

O resultado de ambas as consultas pode ser visto na Listagem 6.

TOWN MIN(BIRTH_DATE)
--------- ---------------
Stratford 1948-09-01
Midhurst 1963-06-22
Inglewood 1962-07-08
Plymouth 1963-10-01
Douglas 1963-05-14
Eltham 1964-12-28

Explicação: Dentro da especificação GROUPING SETS diversos agrupamentos podem ser especificados.

A vantagem do formato extensivo da notação é que ele oferece mais maneiras de se agrupar dados. Diversos agrupamentos podem ser especificados, entre outras coisas, e combinações de ROLLUP e CUBE podem ser usados.

Exemplo 5. Para cada cidade, obtenha o número de jogadores e, para cada sexo, obtenha o número de jogadores também.

Como um agrupamento é necessário em duas colunas diferentes, podemos trabalhar com o operador UNION (ver Listagem 7) (ver resultado na Listagem 8).

SELECT CAST(NULL AS CHAR), TOWN, COUNT(*)
FROM PLAYERS
GROUP BY TOWN
UNION
SELECT SEX, CAST(NULL AS CHAR), COUNT(*)
FROM PLAYERS
GROUP BY SEX
ORDER BY 2, 1

SEX TOWN COUNT(*)
--- --------- --------
? Stratford 7
? Midhurst 1
? Inglewood 2
? Plymouth 1
? Douglas 1
? Eltham 2
M ? 9
F ? 5

Explicação: As linhas deste resultado intermediário no qual a coluna TOWN foi preenchida vieram do primeiro bloco select. As linhas nas quais TOWN é igual a NULL formam o resultado intermediário do segundo bloco select. Na verdade, estas duas linhas formam subtotais para cada sexo.

Para simplificar a elaboração deste tipo de relatório, podemos utilizar a notação grouping sets:

SELECT SEX, TOWN, COUNT(*)
 FROM PLAYERS
 GROUP BY GROUPING SETS ((TOWN), (SEX))
 ORDER BY 2, 1

Explicação: Na especificação do GROUPING SETS, dois agrupamentos são definidos (TOWN) e (SEX).

Se um agrupamento consiste de uma expressão, os parênteses podem ser removidos. Então, GROUP BY GROUPING SETS ((TOWN), (SEX)) é equivalente a GROUP BY GROUPING SETS (TOWN, SEX).

A Tabela 1 contém diversos exemplos de formulações originais sem grouping sets e seus equivalentes com grouping sets.

Um caso especial do grouping sets é o uso de () - não há nenhuma expressão entre os parênteses. Neste caso, todas as linhas estão colocadas em um grupo. Com isto, podemos, por exemplo, calcular um total geral.

Exemplo 6. Descubra, para cada combinação de sexo-cidade, o número de jogadores; obtenha, para cada sexo, o número de jogadores e obtenha o número total de jogadores:

SELECT SEX, TOWN, COUNT(*)
 FROM PLAYERS
 GROUP BY GROUPING SETS ((SEX, TOWN), (SEX), ())
 ORDER BY 1, 2

Podemos observar o resultado na Listagem 9.

SEX TOWN COUNT(*)
--- --------- --------
M Stratford 7
M Inglewood 1
M Douglas 1
M ? 9
F Midhurst 1
F Inglewood 1
F Plymouth 1
F Eltham 2
F ? 5
? ? 14

Explicação: A última linha contém o número total de jogadores e é adicionada por causa do agrupamento ().

Exemplo 7. Obtenha, para cada time e para cada jogador individualmente, o número de partidas disputadas:

SELECT TEAMNO, PLAYERNO, COUNT(*)
FROM MATCHES
GROUP BY GROUPING SETS (TEAMNO, PLAYERNO)
ORDER BY 1, 2

Podemos observar o resultado na Listagem 10.

TEAMNO PLAYERNO COUNT(*)
------ -------- --------
1 ? 8
2 ? 5
? 2 1
? 6 3
? 8 2
? 27 1
? 44 1
? 57 1
? 83 1
? 104 1
? 112 2

Explicação: As duas primeiras linhas no resultado foram incluídas por causa do agrupamento na coluna TEAMNO e as outras linhas por causa do agrupamento na coluna PLAYERNO.

Este exemplo mostra claramente que parênteses são importantes. A especificação GROUPING SETS (TEAMNO, PLAYERNO) retorna um resultado diferente do que aquele retornado por GROUPING SETS ((TEAMNO, PLAYERNO)). A segunda especificação grouping sets resulta em um agrupamento na combinação das duas colunas especificadas e a segunda especificação de grouping sets leva até dois agrupamentos.

Agrupamentos com ROLLUP e CUBE

A especificação WITH ROLLUP não pode ser utilizada se a cláusula de GROUP BY contiver grouping sets. Neste caso, uma especificação alternativa deve ser utilizada.

Vimos anteriormente que informações podem ser agregadas em níveis diferentes. O exemplo 6 é um cenário claro desta necessidade. Para tal situação, podemos trabalhar também com o ROLLUP. Imagine que E1 e E2 são duas expressões. Nesse caso, a especificação GROUP BY ROLLUP (E1, E2) é semelhante à especificação GROUP BY GROUPING SETS ((E1, E2), ((E1), ()). Assim, ROLLUP não oferece nenhuma funcionalidade extra, ele somente facilita a formulação de cláusulas GROUP BY. Isto significa que o relatório SELECT do exemplo 6 pode ser simplificado utilizando-se ROLLUP.

Exemplo 8. Obtenha, para cada combinação de sexo-cidade, o número de jogadores; obtenha, para cada sexo, o número de jogadores e obtenha o número total de jogadores:

SELECT SEX, TOWN, COUNT(*)
FROM PLAYERS
GROUP BY ROLLUP (SEX, TOWN)
ORDER BY 1, 2

O resultado (obviamente, igual àquele do exemplo 6) pode ser visto na Listagem 9.

Explicação: A expressão ROLLUP é um operador que é suportado por muitas ferramentas de OLAP. Ele indica que a informação deve ser agregada em diferentes níveis, começando no nível mais baixo. Este nível mais baixo é especificado em ROLLUP. Neste exemplo, ele é formado pela combinação das colunas de SEX e TOWN. Depois disto, as informações são agregadas baseadas em SEX e, então, o total.

Exemplo 9. Para cada combinação de sexo-cidade-ano de nascimento, obtenha o número de jogadores; para cada combinação de sexo-cidade, obtenha o número de jogadores; para cada sexo, obtenha o número de jogadores; e, finalmente, obtenha o número total de jogadores (ver Listagem 11).

SELECT ROW_NUMBER() OVER () AS SEQNO,
SEX, TOWN, YEAR(BIRTH_DATE), COUNT(*)
FROM PLAYERS
GROUP BY ROLLUP (SEX, TOWN, YEAR(BIRTH_DATE))
ORDER BY 2, 3, 4

Para a consulta da Listagem 11 temos o resultado apresentado na Listagem 12.

SEQNO SEX TOWN YEAR(BIRTH_DATE) COUNT(*)
----- --- --------- ---------------- --------
1. 1 M Stratford 1948 1
2. 2 M Stratford 1956 2
3. 3 M Stratford 1963 2
4. 4 M Stratford 1964 1
5. 5 M Stratford 1971 1
6. 6 M Stratford 7
7. 7 M Inglewood 1963 1
8. 8 M Inglewood 1
9. 9 M Douglas 1963 1
10. 10 M Douglas 1
11. 11 M 9
12. 12 F Midhurst 1963 1
13. 13 F Midhurst 1
14. 14 F Inglewood 1962 1
15. 15 F Inglewood 1
16. 16 F Plymouth 1963 1
17. 17 F Plymouth 1
18. 18 F Eltham 1964 1
19. 19 F Eltham 1970 1
20. 20 F Eltham 2
21. 21 F 5
22. 22 14

Explicação: O agrupamento [SEX, TOWN, YEAR(BIRTH_DATE)] retorna as linhas 1, 2, 3, 4, 5, 7, 9, 12, 14, 16, 18 e 19. O agrupamento [SEX, TOWN] resulta nas linhas 6, 8, 10, 13, 15, 17 e 20. O agrupamento [SEX] retorna as linhas 11 e 21, e, finalmente, o agrupamento [] retorna a última linha.

Veremos a partir de agora que adicionando mais parênteses, determinados níveis de agregação podem ser pulados.

Exemplo 10. Para cada combinação de sexo-cidade-ano de nascimento, obtenha o número de jogadores; Para cada sexo, obtenha o número de jogadores; e, finalmente, obtenha o número total de jogadores (ver Listagem 13).

SELECT ROW_NUMBER() OVER () AS SEQNO,
SEX, TOWN, YEAR(BIRTH_DATE), COUNT(*)
FROM PLAYERS
GROUP BY ROLLUP (SEX, (TOWN, YEAR(BIRTH_DATE)))
ORDER BY 2, 3, 4

Para a consulta da Listagem 13 temos o resultado apresentado na Listagem 14.

SEQNO SEX TOWN YEAR(BIRTH_DATE) COUNT(*)
----- --- --------- ---------------- --------
1 M Stratford 1948 1
2 M Stratford 1956 2
3 M Stratford 1963 2
4 M Stratford 1964 1
5 M Stratford 1971 1
6 M Inglewood 1963 1
7 M Douglas 1963 1
8 M 9
9 F Midhurst 1963 1
10 F Inglewood 1962 1
11 F Plymouth 1963 1
12 F Eltham 1964 1
13 F Eltham 1970 1
14 F 5 
15 14

Explicação: Como a coluna TOWN é colocada entre parênteses junto com a expressão YEAR(BIRTH_DATE), ela é considerada como sendo um grupo. Os agrupamentos que são executados por causa disso são, sucessivamente, [SEX, TOWN, YEAR(BIRTH_DATE)], [SEX], e []. O agrupamento [SEX, TOWN] é pulado.

Vamos agora ao nosso último exemplo.

Exemplo 11. Obtenha o número de jogadores para cada combinação de sexo-cidade; para cada sexo e para cada cidade e obtenha, também, o número total de jogadores em toda a mesa (ver Listagem 15).

SELECT ROW_NUMBER() OVER () AS SEQNO,
SEX, TOWN, COUNT(*)
FROM PLAYERS
GROUP BY CUBE (SEX, TOWN)
ORDER BY 2, 3

Para a consulta da Listagem 15 temos o resultado apresentado na Listagem 16.

SEQNO SEX TOWN COUNT(*)
----- --- --------- --------
1. 1 M Stratford 7
2. 2 M Inglewood 1
3. 3 M Douglas 1
4. 4 M 9
5. 5 F Midhurst 1
6. 6 F Inglewood 1
7. 7 F Plymouth 1
8. 8 F Eltham 2
9. 9 F 5
10. 10 Stratford 7
11. 11 Midhurst 1
12. 12 Inglewood 2
13. 13 Plymouth 1
14. 14 Douglas 1
15. 15 Eltham 2
16. 16 14

Explicação: As linhas 1, 2, 3, 5, 6, 7 e 8 foram incluídas por causa do agrupamento [SEX, TOWN]. As linhas 4 e 9 foram incluídas por causa do agrupamento [SEX]. As linhas 10 até, e inclusive, 15 formam o resultado do agrupamento [TOWN]. Finalmente, a linha 16 forma o resultado de um agrupamento total.

Conclusão

Finalizamos aqui nossa série de dois artigos sobre o uso da cláusula Group By. Foi realizado um estudo avançado de sua aplicabilidade através de exemplos práticos. Fica fácil perceber agora o poder que está cláusula da SQL nos traz.

Curso de SQL

Curso

Documentação Completa de SQL

Exemplo

Guia Completo de SQL

Guia