Qual é a diferença entre um GROUP BY e uma PARTITION BY?

As funções Window são uma grande adição ao SQL, e podem facilitar muito sua vida se você souber como usá-las corretamente. Hoje, vamos abordar as diferenças entre um GROUP BY e uma PARTITION BY. Começaremos com o básico e lentamente chegaremos a um ponto em que você poderá continuar pesquisando por conta própria.

PARTITION BY vs. GROUP BY

As cláusulas PARTITION BY e GROUP BY são usadas freqüentemente em SQL quando é necessário criar um relatório complexo. Embora o retorno dos próprios dados seja útil (e até mesmo necessário) em muitos casos, cálculos mais complexos são freqüentemente necessários. É aqui que entram GROUP BY e PARTITION BY. Embora sejam muito semelhantes, pois ambos agrupam, existem diferenças fundamentais. Vamos analisar estas diferenças neste artigo.

GROUP BY

A cláusula GROUP BY é utilizada nas consultas SQL para definir grupos com base em alguns critérios determinados. Estes critérios são o que normalmente encontramos como categorias nos relatórios. Exemplos de critérios para agrupamento são:

• agrupar todos os funcionários por seu nível salarial anual
• agrupam todos os trens em sua primeira estação
• receitas e despesas do grupo por mês
• alunos do grupo de acordo com a classe em que estão matriculados

A utilização da cláusula GROUP BY transforma os dados em um novo conjunto de resultados no qual os registros originais são colocados em diferentes grupos, utilizando os critérios que fornecemos. Você pode conferir mais detalhes sobre a cláusula GROUP BY neste artigo.

Podemos realizar algumas ações ou cálculos adicionais sobre estes grupos, a maioria dos quais está intimamente relacionada às funções agregadas. Como uma rápida revisão, as funções agregadas são usadas para agregar nossos dados e, portanto, no processo, perdemos os detalhes originais no resultado da consulta. Há muitas funções agregadas, mas as mais usadas são COUNT, SUM, AVG, MIN, e MAX.

Se você quiser praticar usando a cláusula GROUP BY, recomendamos nosso curso interativo Criando Relatórios em SQL. As funções agregadas e a cláusula GROUP BY são essenciais para a criação de relatórios em SQL.

Vamos considerar o seguinte exemplo. Aqui temos o train tabela com as informações sobre os trens, o journey tabela com as informações sobre as viagens feitas pelos trens, e o route tabela com as informações sobre as rotas para as viagens. Veja abaixo - dê uma olhada nos dados e como as tabelas estão relacionadas:

Vamos fazer a seguinte consulta que retorna as informações sobre trens e viagens relacionadas usando o train e o journey tabelas.

SELECT
        train.id,
        train.model,
        journey.*
FROM train
INNER JOIN journey ON journey.train_id = train.id
ORDER BY
        train.id ASC;

Aqui está o resultado:

Você pode ver que o trem com id = 1 tem 5 fileiras diferentes, o trem com id = 2 tem 4 fileiras diferentes, etc.

Agora, vamos fazer uma consulta com as mesmas duas tabelas usando um GROUP BY.

SELECT
  	train.id,
	train.model,
	COUNT(*) AS routes
FROM train
INNER JOIN journey ON journey.train_id = train.id
GROUP BY
  	train.id,
	train.model
ORDER BY
  	train.id ASC;

E o resultado é o seguinte:

A partir do resultado da consulta, você pode ver que temos informações agregadas, informando-nos o número de rotas para cada trem. No processo, perdemos os detalhes em nível de linha do journey mesa.

Você pode comparar este resultado definido com o anterior e verificar se o número de linhas retornadas da primeira consulta (número de rotas) corresponde à soma dos números na coluna agregada (routes) do resultado da segunda consulta.

Embora você possa usar funções agregadas em uma consulta sem uma cláusula GROUP BY, isso é necessário na maioria dos casos. As funções agregadas funcionam desta forma:

1. Você gera grupos usando uma declaração GROUP BY especificando uma ou mais colunas que têm o mesmo valor dentro de cada grupo.
2. A função agregada calcula o resultado.
3. As linhas originais são "colapsadas". Você pode acessar as colunas na declaração GROUP BY e os valores produzidos pelas funções agregadas, mas os detalhes originais em nível de linha não estão mais lá.

O "colapsamento" das linhas está bem na maioria dos casos. Algumas vezes, no entanto, é necessário combinar os detalhes originais em nível de linha com os valores retornados pelas funções agregadas. Isto pode ser feito com subconsultas ligando as linhas na tabela original com o conjunto resultante da consulta usando funções agregadas. Ou, você pode tentar uma aproximação diferente - veremos isso a seguir.

PARTITION BY

Dependendo do que você precisa fazer, você pode usar um PARTITION BY em nossas consultas para calcular valores agregados sobre os grupos definidos. O PARTITION BY é combinado com OVER() e funções windows para calcular os valores agregados. Isto é muito semelhante ao GROUP BY e funções agregadas, mas com uma diferença importante: quando você usa um PARTITION BY, os detalhes em nível de linha são preservados e não colapsados. Ou seja, você ainda tem à sua disposição os detalhes originais em nível de linha, bem como os valores agregados. Todas as funções agregadas podem ser usadas como funções de janela.

Vejamos a seguinte pergunta. Além de train e journeyAgora, incorporamos também a tabela de rotas.

SELECT
  	train.id,
	train.model,
	route.name,
	route.from_city,
	route.to_city,
	COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes,
	COUNT(*) OVER () AS routes_total
FROM train
INNER JOIN journey ON journey.train_id = train.id
INNER JOIN route ON journey.route_id = route.id;

Aqui está o resultado da consulta:

A partir do conjunto de resultados, observamos vários pontos importantes:

• Não utilizamos um GROUP BY mas ainda assim obtivemos valores agregados (routes e routes_total).
• Temos as mesmas colunas (id e model) do GROUP BY na consulta anterior, mas os detalhes originais em nível de linha foram preservados. Os valores agregados são repetidos em todas as filas com os mesmos valores de id e modelo. Isto é esperado; como exemplo, temos 5 registros de viagem para id = 1, todos com valores idênticos para estas colunas.
• Também temos valores no nome das colunas, from_city, e to_city que são diferentes dentro de um determinado valor de id. Se tivéssemos usado um GROUP BY nas colunas id e model, estes detalhes em nível de linha estariam perdidos.
• COUNT(*) OVER () AS routes_total produzissem a mesma contagem agregada, 30, como fariam COUNT e GROUP BY. Neste conjunto de resultados, no entanto, este valor está incluído em cada linha.
• A parte COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes é muito interessante. Definimos o grupo sobre o qual esta função de janela deve ser usada com a cláusula PARTITION BY. Portanto, na coluna routes, temos uma contagem de linhas apenas para esse grupo. As funções de janela são aplicadas depois que as linhas são filtradas, mantendo assim os detalhes em nível de linha enquanto ainda definimos os grupos através de PARTITION BY.

Usar funções agregadas padrão como funções de janela com a palavra-chave OVER() nos permite combinar valores agregados e manter os valores das linhas originais. Podemos fazer o mesmo usando funções agregadas, mas isso requer subconsultas para cada grupo ou partição.

É importante observar que todas as funções agregadas padrão podem ser usadas como funções de janela como esta.

Funções de Janela (Window Functions) em SQL

Além das funções agregadas, existem algumas outras funções de janela importantes, como por exemplo:

• ROW_NUMBER(). Retorna o número seqüencial da linha no conjunto de resultados.
• RANK(). Similar a ROW_NUMBER(), mas pode tomar uma coluna como argumento. A ordem de classificação é determinada sobre o valor desta coluna. Se duas ou mais linhas têm o mesmo valor nesta coluna, todas estas linhas recebem a mesma classificação. A próxima fileira continuará a partir do número equivalente de fileiras; por exemplo, se duas fileiras compartilham uma fileira de 10, a próxima fileira será 12.
• DENSE_RANK(). Muito semelhante ao RANK(), exceto que não tem "lacunas". No exemplo anterior, se duas filas compartilham uma posição de 10, a próxima posição será 11.
• NTILE. Usado para calcular quartis, deciles ou qualquer outro percentil.
• LAG & LEAD. Usado para puxar valores da linha anterior (LAG) ou da seguinte (LEAD).

Não há uma regra geral sobre quando se deve usar funções de janela, mas você pode desenvolver uma sensação para elas. Eu definitivamente recomendo passar pelo cursoFunções de Janela (Window Functions) em SQL ; lá, você encontrará todos os detalhes que deseja saber!

PARTITION BY e GROUP BY: Similitudes e diferenças

Embora utilizemos um GROUP BY na maioria das vezes, há inúmeros casos em que um PARTITION BY seria uma escolha melhor. Em alguns casos, você poderia usar um GROUP BY usando subconsultas para simular um PARTITION BY, mas estas podem terminar com consultas muito complexas.

Vamos encerrar tudo com as semelhanças e diferenças mais importantes:

• Semelhança: Ambas são usadas para retornar valores agregados.
• Diferença: O uso de uma cláusula GROUP BY faz cair as linhas originais; por esse motivo, não é possível acessar os valores originais mais tarde na consulta. Por outro lado, o uso de uma cláusula PARTITION BY mantém os valores originais, ao mesmo tempo em que nos permite produzir valores agregados.
• Diferença: O PARTITION BY é combinado com OVER() e funções do windows para adicionar muito mais funcionalidades.