Desvendando a cláusula GROUP BY - Artigo sql magazine 47

TEAMNO PLAYERNO
------ --------
1 2
1 6
1 8
1 44
1 57
1 83
2 8
2 27
2 104
2 112

Neste ponto, é importante destacar que a seqüência de colunas na cláusula de GROUP BY não tem efeito no resultado final de um relatório. O seguinte relatório, portanto, é equivalente ao anterior:

SELECT TEAMNO, PLAYERNO
FROM MATCHES
GROUP BY PLAYERNO, TEAMNO

Como um exemplo, vamos adicionar algumas funções de agregação ao relatório SELECT anterior:

SELECT TEAMNO, PLAYERNO, SUM(WON),
COUNT(*), MIN(LOST)
FROM MATCHES
GROUP BY TEAMNO, PLAYERNO

Considerando este cenário, teríamos o resultado apresentado na Listagem 11.

TEAMNO PLAYERNO SUM(WON) COUNT(*) MIN(LOST)
------ -------- -------- -------- ---------
1 2 1 1 3
1 6 8 3 0
1 8 0 1 3
1 44 3 1 2
1 57 3 1 0
1 83 0 1 3
2 8 0 1 3
2 27 3 1 2
2 104 3 1 2
2 112 3 2 3

Neste exemplo, o agrupamento é igual a [TEAMNO, PLAYERNO] e o nível de agregação do resultado é a combinação do número de time com número de jogador.

Exemplo 7. Para cada jogador que algum dia tenha incorrido em pelo menos uma penalidade, obtenha o número do jogador, o nome e a quantidade total em penalidades incorridas:

SELECT P.PLAYERNO, NAME, SUM(AMOUNT)
FROM PLAYERS AS P INNER JOIN PENALTIES AS PEN
ON P.PLAYERNO = PEN.PLAYERNO
GROUP BY P.PLAYERNO, NAME

Podemos ver o resultado para este exemplo na Listagem 12.

P.PLAYERNO NAME SUM(AMOUNT)
---------- --------- -----------
6 Parmenter 100.00
8 Newcastle 25.00
27 Collins 175.00
44 Baker 130.00
104 Moorman 50.00

Explicação: este exemplo também tem um agrupamento composto de duas colunas. O relatório teria dado o mesmo resultado se a coluna PEN.PLAYERNO tivesse sido incluída no agrupamento. Descubra isto por você mesmo.

Agrupamento com expressões

Até agora, mostramos somente exemplos aonde o resultado foi agrupado em uma ou mais colunas, mas o que acontece quando efetuamos agrupamentos em expressões? Novamente, aqui estão dois exemplos.

Exemplo 8. Para cada ano presente na tabela PENALTIES, obtenha o número de penalidades pagas:

SELECT YEAR(PAYMENT_DATE), COUNT(*)
FROM PENALTIES
GROUP BY YEAR(PAYMENT_DATE)

O resultado intermediário da cláusula GROUP BY pode ser visto na Listagem 13 e o final na Listagem 14.

YEAR(PAYMENT_DATE) PAYMENTNO PLAYERNO PAYMENT_DATE AMOUNT
------------------ --------- ---------- ------------ --------
1980 {1, 5, 6} {6, 44, 8} {1980-12-08, {100.00,
1980-12-08, 25,00,
1980-12-08} 25,00}
1981 {2} {44} {1981-05-05} {75,00}
1982 {7} {44} {1982-12-30} {30,00}
1983 {3} {27} {1983-09-10} {100,00}
1984 {4, 8} {104, 27} {1984-12-08, {50,00,
1984-11-12} 75,00}

YEAR(PAYMENT_DATE) COUNT(*)
------------------ --------
1980 3
1981 1
1982 1
1983 1
1984 2

Explicação: o resultado agora é agrupado considerando os valores da expressão escalar YEAR(PAYMENT_DATE). Com isso, colunas para as quais o valor da expressão YEAR(PAYMENT_DATE) é igual formam um grupo.

Exemplo 9. Agrupe os jogadores com base nos seus números. O Grupo 1 deve conter os jogadores com o número 1 até, e inclusive, 24. O Grupo 2 deverá incluir os jogadores com os números 25 até, e incluindo, 49, e assim sucessivamente. Para cada grupo, obtenha o número de jogadores e o número mais alto de jogador:

SELECT TRUNCATE(PLAYERNO/25,0), COUNT(*), MAX(PLAYERNO)
FROM PLAYERS
GROUP BY TRUNCATE(PLAYERNO/25,0)

Neste caso, temos o resultado apresentado na Listagem 15.

TRUNCATE(PLAYERNO/25,0) COUNT(*) MAX(PLAYERNO)
----------------------- -------- -------------
0 4 8
1 4 44
2 1 57
3 2 95
4 3 112

Aqui é importante destacar que a expressão escalar sobre a qual é realizado o agrupamento pode ser um tanto complexa. Pode ser composta, por exemplo, de variáveis, funções e cálculos de sistema.

Agrupamento de valores NULL

Caso seja necessário o uso de agrupamento em uma coluna que contenha valores NULL, todos estes valores NULL formarão um grupo. Quando as linhas forem agrupadas, os valores NULL também são considerados como sendo iguais. Vamos a um exemplo.

SELECT LEAGUENO
FROM PLAYERS
GROUP BY LEAGUENO
Neste caso, temos o resultado:
LEAGUENO
--------
1124
1319
1608
2411
2513
2983
6409
6524
7060
8467
?

Explicação: os jogadores 7, 28, 39 e 95 não têm um número de liga e, portanto, formam um grupo (a última linha) no resultado final.

Regras gerais para uso da cláusula GROUP BY

Esta seção apresenta algumas regras importantes sobre o uso do GROUP BY.

Regra 1: Se um determinado bloco tem uma cláusula GROUP BY, qualquer definição de coluna especificada na cláusula SELECT deve ocorrer exclusivamente como um parâmetro de uma função agregada ou na lista de colunas dada na cláusula de GROUP BY ou em ambas. Conseqüentemente, o seguinte relatório está incorreto porque a coluna TOWN aparece na cláusula SELECT, contudo ela não é o parâmetro de uma função de agregação e não ocorre na lista das colunas pela qual o resultado é agrupado:

SELECT TOWN, COUNT(*)
FROM PLAYERS
GROUP BY PLAYERNO

Regra 2: Na maioria dos exemplos, as expressões que são usadas para formar grupos ocorrem também na cláusula SELECT. Entretanto, isso não é necessário. Uma expressão que ocorra na cláusula GROUP BY pode ou não aparecer na cláusula SELECT.

Regra 3: Uma expressão que seja usada para formar grupos pode também ocorrer na cláusula SELECT dentro de uma expressão composta (veja o exemplo 11).

Exemplo 11. Obtenha a lista com as diferentes quantias de penalidades em centavos:

SELECT CAST(AMOUNT * 100 AS SIGNED INTEGER)
  AS AMOUNT_IN_CENTS
 FROM PENALTIES
 GROUP BY AMOUNT
 Para esta consulta, temos o resultado:
 AMOUNT_IN_CENTS
 ---------------
  2500
  3000
  5000
  7500
  10000

Esta regra é complementada pelo fato de que, não importa o quão complexa uma expressão composta é, se ela ocorrer numa cláusula GROUP BY, ela pode ser incluída em sua totalidade somente na cláusula SELECT. Por exemplo, se a expressão composta PLAYERNO * 2 ocorre numa cláusula de GROUP BY, as expressões PLAYERNO * 2, (PLAYERNO * 2) – 100 e MOD (PLAYERNO * 2, 3) - 100 podem ocorrer na cláusula SELECT. Por outro lado, as expressões PLAYERNO, 2 * PLAYERNO, PLAYERNO * 100 e 8 * PLAYERNO * 2 não são permitidas.

Regra 4: Se uma expressão ocorrer duas ou mais vezes numa cláusula GROUP BY, as expressões duplicadas são simplesmente removidas. A cláusula GROUP BY GROUP BY TOWN, TOWN é convertida para GROUP BY TOWN. Outro exemplo: GROUP BY SUBSTR (TOWN, 1.1), SEX, SUBSTR (TOWN, 1.1) é convertida para GROUP BY SUBSTR (TOWN, 1.1), SEX.

Regra 5: a cláusula GROUP BY agrupa as linhas de tal maneira que as colunas nas quais elas são agrupadas já não contêm valores duplicados.

Exemplos complexos com GROUP BY

A partir de agora veremos diversos exemplos para ilustrar as extensas possibilidades de uso da cláusula GROUP BY.

Exemplo 12. Qual é a média do total de penalidades para jogadores que moram em Stratford e Inglewood (ver Listagem 16)?

SELECT AVG(TOTAL)
 FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL
  FROM PENALTIES
  GROUP BY PLAYERNO) AS TOTALS
 WHERE PLAYERNO IN
  (SELECT PLAYERNO
  FROM PLAYERS
  WHERE TOWN = 'Stratford' OR TOWN = 'Inglewood')
 O resultado da consulta para o exemplo 12 é:
 AVG(TOTAL)
 ----------
 85

Explicação: o resultado intermediário da subquery na cláusula FROM composta de duas colunas, PLAYERNO e TOTAL, contém cinco linhas (jogadores 6, 8, 27, 44, e 104). Esta tabela é passada adiante até a cláusula WHERE, onde uma subquery seleciona jogadores de Stratford e Inglewood (jogadores 6, 8 e 44). Finalmente, a média é calculada na cláusula SELECT da coluna TOTAL.

Exemplo 13. Para cada jogador que incorreu em penalidades e é capitão, obtenha o número do jogador, o nome, o número de penalidades nas quais ele (ou ela) incorreu e o número de times que ele (ou ela) é capitão (ver Listagem 17).

SELECT PLAYERS.PLAYERNO, NAME, NUMBER_OF_PENALTIES,
  NUMBER_OF_TEAMS
 FROM PLAYERS,
  (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_PENALTIES
  FROM PENALTIES
  GROUP BY PLAYERNO) AS NUMBER_PENALTIES,
  (SELECT PLAYERNO, COUNT(*) AS NUMBER_OF_TEAMS
  FROM TEAMS
  GROUP BY PLAYERNO) AS NUMBER_TEAMS
 WHERE PLAYERS.PLAYERNO = NUMBER_PENALTIES.PLAYERNO
 AND PLAYERS.PLAYERNO = NUMBER_TEAMS.PLAYERNO

Para a consulta da Listagem 17 temos o resultado apresentado na Listagem 18.

PLAYERNO NAME NUMBER_OF_PENALTIES NUMBER_OF_TEAMS
 -------- --------- ------------------- ---------------
  6 Parmenter 1 1
  27 Collins 2 1

O relatório da Listagem 18 poderia ter sido formulado mais facilmente incluindo subqueries na cláusula SELECT, que torna as cláusulas GROUP BY não mais necessárias. Veja o exemplo da Listagem 19. Neste caso, a única diferença é que todos os jogadores aparecem no resultado.

SELECT PLAYERS.PLAYERNO, NAME,
  (SELECT COUNT(*)
  FROM PENALTIES
  WHERE PLAYERS.PLAYERNO =
  PENALTIES.PLAYERNO) AS NUMBER_OF_PENALTIES,
  (SELECT COUNT(*)
  FROM TEAMS
  WHERE PLAYERS.PLAYERNO =
  TEAMS.PLAYERNO) AS NUMBER_OF_TEAMS
 FROM PLAYERS

Exemplo 14. Obtenha o número do jogador e o número total de penalidades para cada jogador que disputou uma partida (ver Listagem 20).

SELECT DISTINCT M.PLAYERNO, NUMBERP
 FROM MATCHES AS M LEFT OUTER JOIN
  (SELECT PLAYERNO, COUNT(*) AS NUMBERP
  FROM PENALTIES
  GROUP BY PLAYERNO) AS NP
  ON M.PLAYERNO = NP.PLAYERNO

Explicação: Neste relatório, a subquery cria o resultado intermediário apresentado na Listagem 21.

PLAYERNO NUMBERP
 -------- -------
  6 1
  8 1
  27 2
  44 3
  104 1

Dando continuidade ao raciocínio, é efetuado um join (left outer) com a tabela MATCHES. O resultado final pode ser visto na Listagem 22.

PLAYERNO NUMBERP
 -------- -------
  2 ?
  6 1
  8 1
  27 2
  44 3
  57 ?
  83 ?
  104 1
  112 ?

Exemplo 15. Agrupar as penalidades com base na data de pagamento. O Grupo 1 deverá conter todas as penalidades entre 1º de janeiro de 1980 e 30 de junho de 1982; o grupo 2 deverá conter todas as penalidades entre 1º de julho de 1981 e 31 de dezembro de 1982; e o grupo 3 deverá conter todas as penalidades entre 1º de janeiro de 1983 e 31 de dezembro de 1984. Obtenha, para cada grupo, a somatória das penalidades (ver Listagem 23).

SELECT GROUPS.PGROUP, SUM(P.AMOUNT)
FROM PENALTIES AS P,
(SELECT 1 AS PGROUP, '1980-01-01' AS START,
'1981-06-30' AS END
UNION
SELECT 2, '1981-07-01', '1982-12-31'
UNION
SELECT 3, '1983-01-01', '1984-12-31') AS GROUPS
WHERE P.PAYMENT_DATE BETWEEN START AND END
GROUP BY GROUPS.PGROUP
ORDER BY 1
O resultado desta consulta é:
GROUP SUM(P.AMOUNT)
----- -------------
1 225.00
2 30.00
3 225.00

Explicação: Na cláusula FROM, é criada uma nova tabela, virtual, na qual três grupos foram definidos. Esta tabela GROUPS é somada à tabela PENALTIES. Um operador BETWEEN é utilizado para juntar as duas tabelas. Caso existam penalidades que recaiam fora destes grupos no que diz respeito à data de pagamento, estas não serão incluídas no resultado.

Exemplo 16. Para cada penalidade, obtenha o número do pagamento, a quantidade de penalidades e o percentual que a quantidade forma considerando a soma de todas as penalidades (novamente, utilizamos a mesma tabela PENALTIES) (ver Listagem 24).

SELECT P1.PAYMENTNO, P1.AMOUNT,
(P1.AMOUNT * 100) / SUM(P2.AMOUNT)
FROM PENALTIES AS P1, PENALTIES AS P2
GROUP BY P1.PAYMENTNO, P1.AMOUNT
ORDER BY P1.PAYMENTNO

O resultado intermediário da cláusula GROUP BY pode ser visto na Listagem 25.

P1.PAYNO P1.AMOUNT P2.PAYNO P2.AMOUNT
-------- --------- -------- --------------
1 100 {1, 2, 3} {100, 75, 100}
2 75 {1, 2, 3} {100, 75, 100}
3 100 {1, 2, 3} {100, 75, 100}

Por fim, o resultado intermediário da cláusula SELECT pode ser visto na Listagem 26.

P1.PAYNO P1.AMOUNT (P1.AMOUNT * 100) / SUM(P2.AMOUNT)
-------- --------- ----------------------------------
1 100 36.36
2 75 27.27
3 100 36.36

Descubra você mesmo se este é o resultado final também.

Conclusão

Analisamos nesta matéria de forma pratica e em detalhes a cláusula GROUP BY. ‘E um elemento bastante rico da SQL, trazendo grandes possibilidades em termos de consulta. Na próxima edição daremos continuidade a esta matéria analisando outros aspectos do GROUP BY.

Confira também

Curso de SQL

Curso

Documentação Completa de SQL

Exemplo

Guia Completo de SQL

Guia