GROUP BY Cláusula: Você o conhece bem?

O banco de dados pode fazer diferentes tipos de cálculos: pode adicionar e subtrair, multiplicar e dividir, pode fazer cálculos em datas, pode contar linhas e valores de linhas de soma, e assim por diante. Também pode fazer estatísticas bastante sofisticadas. A cláusula GROUP BY é a forma mais básica de calcular estatísticas em SQL. Pode ser bastante difícil para iniciantes, mas é realmente poderosa. Vamos ver os detalhes da cláusula GROUP BY, começando com o básico.

O básico: Como funciona a cláusula GROUP BY

Aqui temos medalhas de mesa com medalhistas da Copa do Mundo de Saltos de Esqui para as últimas quatro temporadas.

 country |         person        | season  | place
---------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2011-12 |   1
Austria  | Gregor Schlierenzauer | 2011-12 |   2
Austria  | Andreas Kofler        | 2011-12 |   3
Austria  | Gregor Schlierenzauer | 2012-13 |   1
Norway   | Anders Bardal         | 2012-13 |   2
Poland   | Kamil Stoch           | 2012-13 |   3
Poland   | Kamil Stoch           | 2013-14 |   1
Slovenia | Peter Prevc           | 2013-14 |   2
Germany  | Severin Freund        | 2013-14 |   3
Germany  | Severin Freund        | 2014-15 |   1
Slovenia | Peter Prevc           | 2014-15 |   2
Austria  | Stefan Kraft          | 2014-15 |   3

Quero saber quantas medalhas a Polônia conquistou:

SELECT count(*)
FROM medals
WHERE country = 'Poland';

Se eu quisesse saber o número de medalhas para a Alemanha, teria que emitir esta pergunta:

SELECT count(*)
FROM medals
WHERE country = 'Germany';

Se eu quisesse saber o número de medalhas para cada país, eu poderia fazer seis perguntas similares. Ou poderia usar um GROUP BY.

SELECT country, count(*)
FROM medals
GROUP BY country;

A cláusula GROUP BY vem logo após a cláusula WHERE na consulta SQL. Aqui, a cláusula WHERE está faltando, portanto é logo depois de FROM.

O resultado:

 country | count
---------+-------
Poland   |   2
Germany  |   2
Austria  |   4
Norway   |   2
Slovenia |   2

Com a consulta GROUP BY, o banco de dados divide os dados em grupos. Linhas com a mesma coluna GROUP BY (país no exemplo) são colocadas em um grupo. Assim, usando nosso exemplo, medalhistas da Polônia são colocados em um grupo, medalhistas da Alemanha são colocados em outro grupo e assim por diante. Aqui está o agrupamento que obtemos para esta consulta:

 country |        person         | season  | place
–--------+-----------------------+---------+-------
Poland   | Kamil Stoch           | 2012-13 |   3
         | Kamil Stoch           | 2013-14 |   1
–--------+-----------------------+---------+-------
Germany  | Severin Freund        | 2013-14 |   3
         | Severin Freund        | 2014-15 |   1
–--------+-----------------------+---------+-------
Austria  | Gregor Schlierenzauer | 2012-13 |   1
         | Stefan Kraft          | 2014-15 |   3
         | Gregor Schlierenzauer | 2011-12 |   2
         | Andreas Kofler        | 2011-12 |   3
–--------+-----------------------+---------+-------
Norway   | Anders Bardal         | 2012-13 |   2
         | Anders Bardal         | 2011-12 |   1
–--------+-----------------------+---------+-------
Slovenia | Peter Prevc           | 2013-14 |   2
         | Peter Prevc           | 2014-15 |   2
–--------+-----------------------+---------+-------

Com GROUP BY os agregados (contagem, soma, avg, avg, min, max, e outros) são computados para cada um separadamente. No exemplo, o banco de dados conta o número de linhas em cada grupo.

Agrupamento com Múltiplas Colunas

Você pode agrupar as linhas por mais de uma coluna.

Por exemplo, se você quiser saber quantas medalhas cada país recebeu em cada temporada, sua consulta ficaria assim:

SELECT country, season, count(*)
FROM medals
GROUP BY country, season;

Linhas com o mesmo país e a mesma estação são colocadas em um grupo. O agrupamento se parece com isto:

 country | season  |        person         | place
–--------+---------+-----------------------+-------
Poland   | 2012-13 | Kamil Stoch           |   3
–--------+---------+-----------------------+-------
Poland   | 2013-14 | Kamil Stoch           |   1
–--------+---------+-----------------------+-------
...
–--------+---------+-----------------------+-------
Austria  | 2011-12 | Gregor Schlierenzauer |   2
         | 2011-12 | Andreas Kofler        |   3
–--------+---------+-----------------------+------

O resultado final:

 country | season  | count
---------+---------+-------
 Poland  | 2012-13 |   1
 Austria | 2011-12 |   2
 ...
 Poland  | 2013-14 |   1

Valores NULL em GROUP BY

Um lembrete: Na condição WHERE, não são considerados dois NULLs iguais. Por mais estranho que isto possa parecer, a pergunta

SELECT *
FROM medals
WHERE place = place;

selecionará todas as filas , exceto aquelas com lugar NULL. Para SQL o valor NULL significa "Desconhecido" e se for desconhecido, SQL não pode assumir que sabe ao certo qual será seu resultado. (Em particular, não pode ter certeza de que o resultado é VERDADEIRO).

Com GROUP BY, é diferente. Linhas com valores NULL vão todas para um grupo, e os agregados são computados para este grupo, como para qualquer outro. Também funciona para GROUP BYs de várias colunas.

Para esta tabela:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
 United States | NULL    | Maria Jane   |   1000
 Germany       | Berlin  | Hans Schmitt |   2430
 United States | NULL    | Bill Noir    |   1000
 United States | Chicago | Rob Smith    |   3000
 NULL          | Warsaw  | Sophie Doe   |   2000
 Germany       | Berlin  | Jane Dahl    |   1500

a consulta

SELECT country, city, sum(earnings)
FROM employees
GROUP BY country, city;

torna estes grupos:

    country    |   city  |    person    | earnings
---------------+---------+--------------+----------
 NULL          | Warsaw  | John Doe     |   1000
               |         | Sophie Doe   |   2000
–--------------------------------------------------
 United States | NULL    | Maria Jane   |   1000
               |         | Bill Noir    |   1000
–--------------------------------------------------
 United States | Chicago | Rob Smith    |   3000
–--------------------------------------------------
 Germany       | Berlin  | Hans Schmitt |   2430
               |         | Jane Dahl    |   1500

e este resultado

    country    |   city  |  sum
---------------+---------+-------
 NULL          | Warsaw  |  3000
 United States | NULL    |  2000
 United States | Chicago |  3000
 Germany       | Berlin  |  3930

Mito: Selecione as colunas devem aparecer no GROUP BY cláusula ou sob a função agregada?

A sabedoria comum diz que colunas selecionadas em uma consulta de GROUP BY devem aparecer na cláusula GROUP BY ou sob uma função agregada. Portanto, esta consulta é incorreta:

SELECT user_account.id, email, count(*)
FROM user_account JOIN address
ON user_account.id = address.user_id
GROUP BY email;

A consulta agrupa os resultados por e-mail, mas seleciona a coluna id, que não está na cláusula GROUP BY.

Esta sabedoria foi a regra no padrão SQL92. É assim que muitos bancos de dados implementam hoje o comportamento GROUP BY. Você receberá um erro semelhante a este:

ERROR:  column "user_account.id" must appear in the GROUP BY clause or be used 
in an aggregate function

De onde vem esta regra? Vejamos os dados de exemplo.

|       user_account      |   |         address         |
+----+--------------------+   +----+----------+---------+
| id |        Email       |   | id |   city   | user_id |
+----+--------------------+   +----+----------+---------+
| 1  | john@example.com   |   | 1  | London   | 1       |
| 2  | mary@example.co.uk |   | 2  | Brussels | 2       |
| 3  | john@example.com   |   | 3  | Cairo    | 3       |
|    |                    |   | 4  | Dublin   | 1       |

Nós agrupamos os dados por e-mail

 
user_account.email |user_account.id |address.id|address.city|address.user_id|
-------------------+----------------+----------+------------+---------------+
john@example.com   | 1              | 1        | A          | 1             |
                   +----------------+----------+------------+---------------+
                   | 1              | 4        | D          | 1             |
                   +----------------+----------+------------+---------------+
                   | 3              | 3        | C          | 3             |
-------------------+----------------+----------+------------+---------------+
mary@example.com   | 2              | 2        | B          | 2             |

O banco de dados cria um grupo para cada e-mail. Mas há múltiplas identidades de contas de usuário em cada grupo. O banco de dados não sabe qual id ele deve retornar. O padrão SQL quer que o resultado SQL seja determinístico, portanto proíbe que você execute uma consulta como esta.

O padrão SQL99 modificou a redação da regra. Agora diz que qualquer coluna que aparece sob SELECT tem que aparecer sob a função agregada ou ser funcionalmente dependente de colunas na cláusula GROUP BY. A regra não espera mais repetir todas as colunas não agregadas a serem repetidas na cláusula GROUP BY.

O que significam as colunas funcionalmente dependentes na cláusula BY by clause? Significa: se eu fixar valores para colunas na cláusula GROUP BY, tem que haver apenas um valor para a outra coluna. Por exemplo, o endereço de e-mail determina o valor do nome de seu proprietário. Mas há um senão: o banco de dados tem que saber sobre esta dependência. No contexto de bancos de dados, a dependência significa chaves primárias e chaves únicas. Se eu agrupar por uma chave primária, então sei que outras colunas desta tabela têm valores fixos.

Nosso exemplo inicial ainda não é válido sob a nova regra. MAS: se eu aplicar a restrição única na coluna de e-mail, a consulta torna-se válida sob a nova regra. Se a coluna de e-mail for única na tabela user_account, então a fixação do valor do e-mail determina todas as outras colunas na tabela user_account. Naturalmente, se eu adicionar a restrição única, meus dados de exemplo são inválidos também. Eu não posso ter duas linhas diferentes com o mesmo e-mail.

Os bancos de dados suportam a nova regra GROUP BY?

Alguns aceitam, outros não. A nova regra está no padrão SQL99. O MySQL da versão 5.7.4 suporta o novo comportamento. Assim como o Postgres a partir da versão 9.1. O Postgres trata a coluna como funcionalmente dependente das colunas agrupadas se um subconjunto das colunas agrupadas for uma chave primária da tabela da qual a coluna vem.

Até onde eu sei, Oracle e SQL Server ainda se mantêm com a versão antiga.

Você deve usar a nova versão ou a antiga versão da regra em suas consultas? Na prática, esta modificação não muda realmente nada. Ambas as regras garantem que sempre que você seleciona uma coluna não agregada em uma consulta de GROUP BY, o seu valor é inequívoco em cada grupo. A antiga regra obriga você a adicionar esta coluna na cláusula GROUP BY, mas esta GROUP BY não muda a semântica da consulta. O resultado é o mesmo, basta digitar um pouco mais com a nova regra.

Em geral, você está melhor com o cumprimento da regra antiga. Suas consultas serão feitas na maioria dos bancos de dados. Mas é bom saber que você não precisa fazer isso.

Extensões para GROUP BY

SQL-99 adicionou ROLLUP, e CUBE e GROUPING SETS como opções para instruções SELECT.

AGRUPAR POR ROLLUP

A sintaxe para o ROLLUP é

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY ROLLUP (<group-by columns>);

A utilização do ROLLUP (a,b,c) gerará cláusulas GROUP BY: (a, b, c), (a, b), (a) e uma linha para uma agregação de todas as linhas selecionadas. É equivalente a quatro consultas SELECT com várias cláusulas GROUP BY.

Para esta tabela

department | year | sales
–----------+--------------
 IT        | 2012 |  25000
 IT        | 2013 |  26000
 Retail    | 2012 |  35000
 Retail    | 2013 |  15000
 IT        | 2014 |  18000

e esta pergunta

SELECT department, year, sum(sales)
FROM sales
GROUP BY ROLLUP (department, year);

obtemos o resultado:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000 <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000 <-  group by department
 NULL       | NULL |  119000 <-  group by (), i.e. all rows selected

As filas adicionais são às vezes chamadas de superagregados.

O ROLLUP é suportado pelo SQL Server, Oracle, DB2.

No MySQL você pode usar a sintaxe WITH ROLLUP:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY a,b,c WITH ROLLUP;

O PostgreSQL não suporta o ROLLUP.

GROUP BY CUBE

A sintaxe da CUBE é

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY CUBE (a, b, c);

Funciona de forma semelhante ao ROLLUP, mas gera todas as combinações possíveis de colunas: (a,b,c), (a,b), (a,c), (b,c), (a), (b), (c) e uma linha para todas as linhas selecionadas.

A consulta

SELECT department, year, sum(sales)
FROM sales
GROUP BY CUBE (department, year);

renderá este resultado:

 department | year |   sum   
------------+------+--------
 IT         | 2012 |   25000
 IT         | 2014 |   18000
 IT         | 2013 |   26000
 IT         | NULL |   69000  <-  group by department
 Retail     | 2013 |   15000
 Retail     | 2012 |   35000
 Retail     | NULL |   50000  <-  group by department
 NULL       | NULL |  119000  <-  group by ()
 NULL       | 2014 |   18000 |
 NULL       | 2012 |   60000 | <= three new rows added by CUBE
 NULL       | 2013 |   41000 |    

O CUBE é suportado pelo SQL Server e Oracle, e o DB2. O MySQL e o Postgres não o suportam.

AGRUPAMENTO POR CONJUNTOS DE AGRUPAMENTO

O GROUPING SETS funciona de forma semelhante, mas permite especificar quais combinações de colunas devem ser usadas no resultado. Os conjuntos de agrupamento têm que ser separados por vírgulas. Se houver mais de uma coluna em um conjunto de agrupamento, este conjunto de agrupamento deve ser colocado entre parênteses. Parênteses vazios significam o registro geral com agregados para todo o conjunto.

Exemplo de consulta:

SELECT <columns>
FROM <tables>
WHERE <condition>
GROUP BY GROUPING SETS ((a, b), c, ());

O GROUPING SETS é suportado pelo SQL Server e Oracle, e DB2. MySQL e Postgres não o suportam.