Artigo original: SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained
A instrução GROUP BY
é poderosa, mas, às vezes, pode causar confusão.
Mesmo após oito anos, sempre que utilizo o GROUP BY
, eu tenho de parar e pensar o que exatamente está ocorrendo.
Neste artigo, nós vamos ver como criar uma instrução GROUP BY
, o que ela faz em sua consulta e como você pode utilizá-la para gerar agregações e coletar insights sobre os seus dados.
O que vamos fazer:
- Configurar o banco de dados.
- Configurar os dados (criar vendas).
- Como funciona um
GROUP BY
?. - Escrever instruções
GROUP BY
. - Agregações (
COUNT
,SUM
,AVG
). - Trabalhar com diversos grupos.
- Utilizar funções dentro do
GROUP BY
. - Filtrar grupos com
HAVING
. - Agregar com agrupamentos implícitos.
Configurar o banco de dados
Antes de escrever nossas consultas, precisamos configurar nosso banco de dados.
Para os exemplos, vamos utilizar o PostgreSQL, mas as consultas e conceitos mostrados serão de fácil compreensão para qualquer sistema de banco de dados moderno (como o MySQL, o SQL Server e assim por diante).
Para trabalhar com nosso banco de dados PostgreSQL, podemos utilizar psql — o comando de linha interativo do PostgreSQL. Se você possui outro serviço de banco de dados de sua preferência, não há problema de trabalhar nele também.
Para começar, vamos criar nossa tabela de dados. Com o PostgreSQL instalado, podemos usar o comando createdb <database-name>
no console para criar um novo banco de dados. Chamei o meu de fcc
:
$ createdb fcc
Agora, vamos iniciar o nosso console interativo utilizando o comando psql
, e nos conectar ao banco de dados que acabamos de fazer \c <nome-do-banco>
:
$ psql
psql (11.5)
Type "help" for help.
john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#
Observação: eu limpei a saída do psql
nestes exemplos para facilitar a leitura, então não se preocupe se as saídas mostradas aqui não sejam exatamente iguais ao que você verá no seu terminal.
Eu encorajo você a acompanhar estes exemplos e a fazer as consultas por conta própria. Você vai aprender e lembrar bem mais trabalhando nos exemplos ao invés de apenas lê-los.
Configurar os dados (criar vendas).
Para os nossos exemplos, vamos utilizar um banco que guarde o histórico de vendas de vários produtos através de diferentes localizações.
Iremos chamar esta tabela de vendas
. Ela será uma representação simples de uma loja: nome do local, nome do produto, preço e a data em que o produto foi vendido.
Se fossemos construir este banco em uma aplicação real, configuraríamos as chaves estrangeiras para outras tabelas (como local
ou produtos
). No entanto, para ilustrar os conceitos de GROUP BY
, vamos utilizar apenas colunas do tipo TEXT
.
Vamos criar a tabela e inserir alguns dados de vendas:
CREATE TABLE vendas(
local TEXT,
produto TEXT,
preco DECIMAL,
vendido_em TIMESTAMP
);
INSERT INTO vendas(local, produto, preco, vendido_em) VALUES
('Central', 'Café', 2, NOW()),
('Central', 'Café', 2, NOW() - INTERVAL '1 hora'),
('Centro', 'Biscoito', 3, NOW() - INTERVAL '2 hora'),
('Centro', 'Café', 2, NOW() - INTERVAL '1 dia'),
('Central', 'Biscoito', 2, NOW() - INTERVAL '2 dias'),
('Rua 1', 'Biscoito', 3, NOW() - INTERVAL '2 dias' - INTERVAL '1 hora'),
('Rua 1', 'Café', 2, NOW() - INTERVAL '3 dias'),
('Central', 'Biscoito', 3, NOW() - INTERVAL '3 dias' - INTERVAL '1 hora');
Temos 3 localizações: Central, Centro e Rua 1.
Nós temos dois produtos, Café e Biscoito, e inserimos diferentes valores em vendido_em
para representar os itens sendo vendidos em dias e horas diferentes.
Existem algumas vendas na data de hoje, algumas na data de ontem, e algumas em dias anteriores a ontem.
Como funciona um GROUP BY
?
Para ilustrar como as instruções GROUP BY
funcionam, vamos primeiro falar através de um exemplo.
Imagine que temos um quarto cheio de pessoas que nasceram em países diferentes.
Se quiséssemos descobrir a média da altura das pessoas no quarto por país, primeiro, pediríamos para as pessoas se separarem em grupos baseados no país em que nasceram.
Uma vez separados em seus devidos grupos, poderíamos calcular a média de altura de qualquer um desses grupos.
É assim que funcionam as instruções GROUP BY
. Primeiro, definimos como queremos agrupar as informações – então, podemos realizar cálculos ou agregações nos grupos.
Diversos grupos
Podemos agrupar os dados em quantos grupos ou subgrupos quisermos.
Por exemplo, depois de pedirmos para as pessoas se separarem baseados no seu país de nascimento, podemos dizer para cada um desses grupos que se separem em grupos baseados na cor de seus olhos.
Ao fazer isso, temos grupos de pessoas baseados na combinação do seu país de nascimento e na cor de seus olhos.
Agora, podemos encontrar a média da altura dentro de cada um desses pequenos grupos. Teremos um resultado mais específico: média de altura por país e cor dos olhos.
As instruções GROUP BY
são frequentemente usadas para situações onde você utiliza a frase por alguma coisa ou para cada alguma coisa:
- Média de altura por país
- Número total de pessoas para cada combinação de cor de olhos e cor de cabelo
- Total de vendas por produto
Escrever instruções GROUP BY
Uma instrução GROUP BY
é muito fácil de escrever — apenas utilizamos a palavra chave GROUP BY
e então especificamos os campos que queremos agrupar:
SELECT ...
FROM vendas
GROUP BY local;
Esta consulta simples agrupa nossos dados de vendas
pela coluna do local
.
Fizemos o agrupamento — mas e agora? O que colocamos em nosso SELECT
?
A coisa mais clara para selecionar é o nosso local
— nós agrupamos para que ao menos vejamos o nome dos grupos que fizemos:
SELECT location
FROM sales
GROUP BY location;
O resultado é as nossas três localizações:
local
------------
Rua 1
Central
Centro
(3 rows)
Se olharmos para a tabela bruta de dados (SELECT * FROM vendas;
), veremos que temos 4 linhas com o local Central, duas linhas com o local Centro, e duas linhas com o local Rua 1:
produto | local | preco | vendido_em
---------+---------+------------+-------+----------------------------
Cafe | Central | 2 | 2020-09-01 09:42:33.085995
Cafe | Central | 2 | 2020-09-01 08:42:33.085995
Biscoito | Centro | 3 | 2020-09-01 07:42:33.085995
Cafe | Centro | 2 | 2020-08-31 09:42:33.085995
Biscoito | Central | 2 | 2020-08-30 09:42:33.085995
Biscoito | Rua 1 | 3 | 2020-08-30 08:42:33.085995
Cafe | Rua 1 | 2 | 2020-08-29 09:42:33.085995
Biscoito | Central | 3 | 2020-08-29 08:42:33.085995
(8 rows)
Agrupando na coluna local
, nosso banco de dados pega essas linhas de input e identifica locais únicos dentre eles — estes locais únicos servem como nosso "grupo".
E quanto às outras colunas da nossa tabela?
Se tentarmos selecionar a coluna produto
que não agrupamos...
SELECT
local,
produto
FROM vendas
GROUP BY local;
...receberemos o seguinte erro:
ERROR: column "vendas.produto" must appear in the GROUP BY clause or be used in an aggregate function
O problema aqui é que pegamos oito linhas e as esprememos ou as transformamos em três.
Não podemos apenas retornar o resto das colunas como normalmente fazíamos — nós possuíamos oito linhas, e agora temos três.
O que faremos com as cincos linhas restantes de dados? Quais das oito linhas de dados devem ser mostradas nestas três linhas de locais distintos?
Não há uma resposta clara e definitiva aqui.
Para utilizar o resto da nossa tabela de dados, nós também temos de analisar os dados do restante das colunas dentro dos nossos três grupos de local.
Isto significa que temos de agregar ou realizar um cálculo para produzir um tipo de resumo das informações sobre os dados restantes.
Agregações (COUNT
, SUM
, AVG
)
Uma vez que definimos como agrupar nossos dados, podemos realizar agregações com as colunas restantes.
São coisas como contar o número de linhas por grupo, somar um valor em específico através do grupo, ou calcular a média das informações dentro do grupo.
Para começar, vamos encontrar o número de vendas por local.
Como cada gravação em nossa tabela de vendas
é uma venda, o número de vendas por local seria o número de linhas dentro de cada local do grupo.
Para fazer isto, utilizaremos a função de agregação COUNT()
para contar o número de linhas de cada grupo:
SELECT
local,
COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local;
Nós usamos COUNT(*)
, que conta todas as linhas de input por grupo (COUNT()
também funciona com expressões, mas possui um comportamento levemente diferente).
Abaixo, vemos como o banco de dados executa esta consulta:
FROM vendas
— Primeiro, retornamos todos os dados da tabelavendas
GROUP BY local
— Em seguida, determinamos os grupos únicos delocal
SELECT ...
— Finalmente, selecionamos o nome do local e contamos o número de linhas daquele grupo
Nomearemos a contagem de linhas usando AS numero_de_vendas
para fazer uma saída de dados mais legível. Se parecerá com o seguinte:
local | numero_de_vendas
------------+-----------------
Rua 1 | 2
Central | 4
Centro | 2
(3 rows)
O local Rua 1 possui duas vendas, Central possui quatro, e Centro possui duas.
Aqui, podemos ver como pegamos as demais colunas de dados das nossas oito linhas independentes e as colocamos em um resumo das informações para cada local: o número de vendas.
De um jeito similar, ao invés de contar o número de linha em um grupo, podemos somar as informações dentro do grupo — como o dinheiro total ganho nesses locais.
Para fazer isto, utilizamos a função SUM()
:
SELECT
local,
SUM(preco) AS ganho_total
FROM vendas
GROUP BY local;
No lugar de contar o número de linhas em cada grupo, somamos o total de ganhos de cada venda. Isto mostra o ganho total por local:
local | ganho_total
------------+---------------
Rua 1 | 5
Central | 9
Centro | 5
(3 rows)
Média (AVG
)
Encontrar a média do valor de vendas por local significa apenas que trocaremos a função SUM()
para a função AVG()
:
SELECT
local,
AVG(preco) AS media_de_ganho
FROM vendas
GROUP BY local;
Trabalhar com diversos grupos
Até o momento, temos trabalho apenas com um grupo: local.
E se quisermos subdividir o grupo ainda mais?
É semelhante ao cenário "País de nascimento e cor dos olhos" com o qual começamos. E se quiséssemos encontrar o número de vendas por produto e por local?
Para fazermos tudo isso, precisamos adicionar uma segunda condição de agregação ao declararmos o GROUP BY
:
SELECT ...
FROM vendas
GROUP BY local, produto;
Ao adicionarmos uma segunda coluna GROUP BY
, nós subdividimos ainda mais o nosso grupo de locais em grupo de locais por produto.
Agora que estamos agrupando também pela coluna produto
, podemos retornar ao nosso SELECT
(adicionarei a instrução ORDER BY
nestas consultas para facilitar a leitura dos dados de saída.)
SELECT
local,
produto
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;
Olhando para o resultado de nosso novo agrupamento, podemos ver nossas combinações únicas de local/produto:
local | produto
------------+---------
Rua 1 | Biscoito
Rua 1 | Café
Centro | Biscoito
Centro | Café
Central | Biscoito
Central | Café
(6 rows)
Agora que temos nossos grupos, o que queremos fazer com o resto das nossas colunas de dados?
Bem, podemos encontrar o número de vendas por produto e por local utilizando a mesma função de agregação utilizada antes:
SELECT
local,
produto,
COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;
local | produto | numero_de_vendas
------------+---------+-----------------
Rua 1 | Biscoito | 1
Rua 1 | Café | 1
Centro | Biscoito | 1
Centro | Café | 1
Central | Biscoito | 2
Central | Café | 2
(6 rows)
Como exercício para o leitor™: encontre o total de ganhos (soma) de cada produto por local.
Utilizar funções dentro do GROUP BY
Agora, tentaremos achar o número total de vendas por dia.
Se seguirmos padrões similares como fizemos com os nossos locais e agrupamos pela nossa coluna vendido_em
...
SELECT
vendido_em,
COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em
ORDER BY vendido_em;
...esperamos que o resultado seja ter cada grupo em um único dia— no entanto, temos isso:
vendido_em | vendas_por_dia
----------------------------+---------------
2020-08-29 08:42:33.085995 | 1
2020-08-29 09:42:33.085995 | 1
2020-08-30 08:42:33.085995 | 1
2020-08-30 09:42:33.085995 | 1
2020-08-31 09:42:33.085995 | 1
2020-09-01 07:42:33.085995 | 1
2020-09-01 08:42:33.085995 | 1
2020-09-01 09:42:33.085995 | 1
(8 rows)
Parece que nossos dados não estão agrupados — temos cada linha individualmente.
Nossos dados, no entanto, estão agrupados! O problema é que cada linha de vendido_em
possui um valor único — então, nossas linhas são seus próprios grupos!
O GROUP BY
está funcionando corretamente, mas não é a saída que queremos.
O que ocasiona esta situação é a informação extra do carimbo de data e hora.
Cada um destes carimbos se diferenciam por horas, minutos ou segundos — assim, eles são colocados em seu devido lugar em seu próprio grupo.
Precisamos converter cada um dos valores de data e hora para apenas data:
2020-09-01 08:42:33.085995
=>2020-09-01
2020-09-01 09:42:33.085995
=>2020-09-01
Convertido para uma data, todos os carimbos do mesmo dia retornarão apenas a referência do valor da data em que foi efetuada a compra—e será colocado dentro do mesmo grupo.
Para fazermos isso, converteremos os dados de vendido_em
para data:
SELECT
vendido_em::DATE AS date,
COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em::DATE
ORDER BY vendido_em::DATE;
Em nossa instrução GROUP BY
, utilizamos ::DATE
para cortar uma parte do carimbo de data/hora para apenas dia. Isto corta efetivamente as horas/minutos/segundos do carimbo e retorna apenas o dia.
No nosso SELECT
, retornamos a mesma expressão e damos a ela um nome para deixar o resultado mais bonito.
Pela mesma razão, não podemos retornar o produto
sem agrupar ou realizar algum tipo de agrupamento. O banco de dados não deixará retornar apenas o vendido_em
— tudo que está no SELECT
deve estar dentro do GROUP BY
ou em algum tipo de agregação nos grupos resultantes.
O resultado de vendas por dia que originalmente queríamos ver:
date | vendas_por_dia
------------+---------------
2020-08-29 | 2
2020-08-30 | 2
2020-08-31 | 1
2020-09-01 | 3
(4 rows)
Filtrar grupos com HAVING
Dando continuidade, vamos verificar como filtrar nossas linhas agrupadas.
Para fazer isso, vamos tentar encontrar dias onde houve mais de uma venda.
Sem agrupar, normalmente filtraríamos nossas linhas utilizando a instrução. Por exemplo:
SELECT *
FROM vendas
WHERE produto = 'Café';
Com nossos grupos, utilizaremos algo parecido com isso para filtrá-los com base na quantidade de linhas...
SELECT
vendido_em::DATE AS date,
COUNT(*) AS vendas_por_dia
FROM vendas
WHERE COUNT(*) > 1 -- filtrar os grupos?
GROUP BY vendido_em::DATE;
Infelizmente, isso não funciona e recebemos este erro:
ERROR: aggregate functions are not allowed in WHERE
Funções agregadas não são permitidas dentro da instrução WHERE
, pois ela é avaliada antes da instrução GROUP BY
—não há nenhum grupo para realizar os cálculos.
Existe, contudo, um tipo de instrução que nos permite filtrar e realizar agregações. Ela é avaliada depois da instrução GROUP BY
: a instrução HAVING
.
A instrução HAVING
é como a instrução WHERE
para os seus grupos.
Para encontrar os dias em que houve mais de uma venda, adicionamos a instrução HAVING
que verificará a quantidade de linhas dentro do grupo:
SELECT
vendido_em::DATE AS date,
COUNT(*) AS vendas_por_dia
FROM sales
GROUP BY vendido_em::DATE
HAVING COUNT(*) > 1;
A instrução HAVING
filtra qualquer linha onde a contagem de linhas deste grupo não é maior que um. Vemos isso no nosso resultado:
date | vendas_por_dia
------------+---------------
2020-09-01 | 3
2020-08-29 | 2
2020-08-30 | 2
(3 rows)
Apenas para deixar completo, segue em ordem a execução de todas as partes de uma instrução SQL:
FROM
— Retorna todas as linhas da tabelaFROM
JOIN
— Realiza quaisquer junçõesWHERE
— Filtra linhasGROUP BY
— Forma gruposHAVING
— Filtra gruposSELECT
— Seleciona os dados a serem retornadosORDER BY
— Ordena a saída de linhasLIMIT
— Retorna uma certa quantidade de linhas
Agregações com agrupamentos implícitos
O último tópico que veremos é a agregação que pode ser realizada sem o GROUP BY
— ou, melhor dizendo, que possui um agrupamento implícito.
Estas agregações são úteis em cenários em que você quer encontrar um agrupamento específico dentro de uma tabela — como o total de ganhos ou o maior ou menor valor da coluna.
Por exemplo, podemos encontrar o total de ganhos de todos os locais selecionando a soma da tabela inteira:
SELECT SUM(preco)
FROM vendas;
sum
-----
19
(1 row)
Até o momento fizemos $19 de vendas em todos os locais (vivaaaaa!).
Outra coisa útil que podemos consultar é o primeiro ou o último valor de algo.
Por exemplo, qual a data da nossa primeira venda?
Para encontrá-la, basta utilizarmos a função MIN()
:
SELECT MIN(vendido_em)::DATE AS primeira_venda
FROM vendas;
primeira_venda
------------
2020-08-29
(1 row)
(Para encontrar a data da última venda, bastar substituir MIN()
por MAX()
.)
Como usar MIN
/ MAX
Embora essas consultas simples possam ser utilizadas em consultas independentes, elas são frequentemente parte de filtros de consultas maiores.
Por exemplo, vamos tentar encontrar o total de vendas do último dia em que tivemos vendas.
Um jeito de podermos escrever esta consulta seria o seguinte:
SELECT
SUM(preco)
FROM vendas
WHERE vendido_em::DATE = '2020-09-01';
Esta consulta funciona, mas obviamente codificando a data de 2020-09-01
.
09/01/2020 talvez seja o último dia em que tivemos uma venda, mas nem sempre será essa data. Precisamos de uma solução dinâmica.
Conseguimos isto através da combinação desta consulta com a função MAX()
em uma subconsulta:
SELECT
SUM(preco)
FROM venda
WHERE vendido_em::DATE = (
SELECT MAX(vendido_em::DATE)
FROM vendas
);
Na nossa instrução WHERE
, encontramos a data mais antiga dentro da tabela utilizando a subconsulta: SELECT MAX(vendido_em::DATE) FROM vendas
.
Então, utilizamos esta "data máxima" como um valor para filtrar a tabela, e somamos cada uma das vendas.
Agrupamento implícito
Chamamos de agrupamento implícito, pois, se tentarmos selecionar e agregar o valor com uma coluna não agregada dessa forma...
SELECT
SUM(preco),
local
FROM vendas;
...teremos um erro familiar:
ERROR: column "vendas.local" must appear in the GROUP BY clause or be used in an aggregate function
GROUP BY
é uma ferramenta
Como muitos dos tópicos em desenvolvimento de software, GROUP BY
é uma ferramenta.
Existem várias maneiras de escrever e reescrever estas consultas utilizando a combinação de GROUP BY
, funções agregadas ou outras ferramentas como DISTINCT
, ORDER BY
e LIMIT
.
Entender e trabalhar com o GROUP BY
requer um pouco de pratica, mas ao aprender, você descobrirá um conjunto inteiramente novo de problemas que você poderá resolver!
Se gostou deste post, você pode seguir o autor deste artigo no Twitter, onde ele fala sobre banco de dados e sobre como ter sucesso na carreira como desenvolvedor.
Obrigado pela leitura!