Como usar a PARTITION BY Cláusula em SQL

Estaremos lidando com as funções de janela hoje. Especificamente, vamos nos concentrar no PARTITION BY cláusula e explicar o que ela faz.

PARTITION BY é uma das cláusulas usadas em funções de janela. Em SQL, as funções de janela são usadas para organizar os dados em grupos e calcular estatísticas para eles. Parece muito familiar, não é mesmo? Apesar de soarem semelhantes, as funções de janela e GROUP BY não são a mesma coisa; as funções de janela são mais como GROUP BY em esteróides. Por quê? Porque as funções de janela mantêm os detalhes das linhas individuais enquanto calculam as estatísticas para os grupos de linhas. GROUP BY não pode fazer isso!

PARTITION BY é crucial para essa distinção; esta é a cláusula que divide o resultado de uma função de janela em subconjuntos de dados ou partições. De certa forma, é GROUP BY para funções de janela. Você logo aprenderá como funciona.

Há uma versão muito mais abrangente (e interativa) deste artigo - nossa Funções de Janela (Window Functions) em SQL curso. Ele cobre tudo o que vamos falar e muito mais. Através de seus exercícios interativos, você aprenderá tudo o que precisa saber sobre funções de janela. Você passará pelas cláusulas OVER(), PARTITION BY e ORDER BY e aprenderá como usar as funções de classificação e de janela analítica. O curso também lhe dará 47 exercícios para praticar e um quiz final. Se você for indeciso, eis porque você deve aprender as funções de janela.

Agora, vamos falar sobre PARTITION BY!

PARTITION BY Sintaxe

A sintaxe para a cláusula PARTITION BY é:

SELECT column_name,
  	 window_function (expression) OVER (PARTITION BY column name)
FROM table;

Na parte window_function, você coloca a função de janela específica.

A cláusula OVER() é uma cláusula obrigatória que faz com que a função de janela funcione. Ela praticamente define a função de janela.

A subcláusula PARTITION BY é seguida pelo(s) nome(s) da(s) coluna(s). A(s) coluna(s) especificada(s) nesta cláusula serão as partições/grupos nos quais a função de janela será agrupada.

Os exemplos a seguir tornarão isto mais claro. Sabemos que você não pode memorizar tudo imediatamente, portanto, sinta-se à vontade para manter nossa folha de fraude SQL Funções de Janela (Window Functions) em SQL por perto enquanto analisamos os exemplos. É um lembrete prático de diferentes funções de janela e sua sintaxe.

PARTITION BY EXEMPLOS

O conjunto de dados do exemplo consiste em uma tabela, funcionários. Aqui estão suas colunas:

• id - A identificação do funcionário.
• first_name - O primeiro nome do funcionário.
• last_name - O sobrenome do funcionário.
• job_title - O cargo do funcionário.
• department - O departamento do empregado.
• date_of_employment - A data de início do emprego do empregado.
• salary - O salário do empregado.

Dê uma olhada nos dados da tabela antes de começar a escrever o código:

Se você deseja seguir escrevendo suas próprias consultas SQL, aqui está o código para criar este conjunto de dados.

Usando OVER (PARTITION BY)

Agora é hora de mostrarmos como PARTITION BY funciona em um ou dois exemplos.

Exemplo nº 1

No primeiro exemplo, o objetivo é mostrar os salários dos funcionários e o salário médio para cada departamento. Se você estava prestando atenção, já sabe como PARTITION BY pode nos ajudar aqui:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 AVG(salary) OVER (PARTITION BY department) AS average_salary_by_department
FROM employees;

Para calcular a média, você precisa usar a função agregada AVG(). Escreva a coluna salary entre parênteses. Esta é, por enquanto, uma função agregada comum. Para torná-la uma função de janela agregada, escreva a cláusula OVER().

Agora, lembre-se que não precisamos da média total (ou seja, para toda a empresa), mas da média por departamento. Para ter esta métrica, ponha o departamento de colunas na cláusula PARTITION BY.

Isto retorna a saída desejada? Vamos ver!

Você pode ver que a saída lista todos os funcionários e seus salários. Para o departamento de TI, o salário médio é de 7.636,59. Este valor é repetido para todos os funcionários do departamento de TI.

Quando chegamos a funcionários de outro departamento, a média muda. Neste caso, é de 6.418,12 em Marketing. Seguindo esta lógica, o salário médio em Gerenciamento de Risco é de 6.760,01.

Como você pode ver, PARTITION BY instruiu a função de janela para calcular a média do departamento.

Como isso difere do GROUP BY? Vamos ver o que acontece se calcularmos o salário médio por departamento usando GROUP BY.

Como você pode ver, você pode obter todos os mesmos salários médios por departamento. Entretanto, uma grande diferença é que você não recebe o salário individual do funcionário. Você pode ampliar esta diferença lendo um artigo sobre a diferença entre PARTITION BY e GROUP BY.

Exemplo nº 2

Agora queremos mostrar os salários de todos os funcionários junto com o salário mais alto por título de trabalho.

A pergunta é muito parecida com a anterior. As duas únicas mudanças são a função agregada e a coluna em PARTITION BY.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 MAX(salary) OVER (PARTITION BY job_title) AS max_salary_by_job_title
FROM employees;

Desta vez, usamos a função agregada em MAX() e dividimos a saída por cargo.

Aqui está o resultado:

Dê uma olhada nas duas primeiras filas. Bob Mendelsohn e Frances Jackson são analistas de dados que trabalham em Gerenciamento de Risco e Marketing, respectivamente. A tabela mostra seus salários e o salário mais alto para este cargo. São 5.412,47, o salário de Bob Mendelsohn.

A mesma lógica se aplica ao restante dos resultados. É claro, quando há apenas um cargo, o salário do funcionário e o salário máximo para esse cargo será o mesmo. Esse é o caso do engenheiro de dados e do analista de sistemas.

Este exemplo também pode mostrar as limitações do GROUP BY.

O código abaixo mostrará o salário mais alto pelo cargo:

SELECT job_title,
	 MAX(salary) AS max_salary_by_job_title
FROM employees
GROUP BY job_title;

E aqui está a saída:

Sim, os salários são os mesmos que com PARTITION BY. Mas com este resultado, você não tem idéia do que é o salário de cada funcionário e quem tem o salário mais alto.

Usando OVER (ORDER BY)

A cláusula ORDER BY é outra subcláusula de função de janela. Ela ordena dados dentro de uma partição ou, se a partição não estiver definida, todo o conjunto de dados.

Quando dizemos ordem, não nos referimos à saída. Quando usada com funções de janela, a cláusula ORDER BY define a ordem na qual uma função de janela realizará seu cálculo.

ORDER BY pode ser usada com ou sem PARTITION BY.

Vamos ver primeiro como ela funciona sem PARTITION BY. Vamos usá-la para mostrar os dados dos funcionários e classificá-los por sua data de emprego. A classificação será feita desde a data mais antiga até a mais recente.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 date_of_employment,
	 RANK() OVER (ORDER BY date_of_employment ASC) AS employment_date_rank
FROM employees;

A função de janela que usamos agora é RANK(). É uma das funções usadas para classificar os dados. Novamente, a cláusula OVER() é obrigatória.

A cláusula ORDER BY diz à função de classificação para atribuir postos de acordo com a data de emprego, em ordem decrescente.

Execute a consulta e você terá esta saída:

Todos os funcionários são classificados de acordo com sua data de emprego. A primeira pessoa empregada ocupa o primeiro lugar e a última ocupa o décimo lugar.

Usando OVER (PARTITION BY ORDER BY)

Como já mencionamos, a PARTITION BY e ORDER BY também podem ser usadas simultaneamente. Vejamos alguns exemplos.

Exemplo nº 1

Imagine que você tem que classificar os funcionários de cada departamento de acordo com seu salário. Como você faria isso?

Aqui está a solução:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Usamos novamente a função da janela RANK(). Na cláusula OVER(), os dados precisam ser divididos por departamento. Para classificar os funcionários, use a coluna salário em ORDER BY e classifique os registros em ordem decrescente.

Vamos ver o que esta consulta faz:

No departamento de TI, Carolina Oliveira tem o salário mais alto. Depois vêm Ines Owen e Walter Tyson, enquanto o último é Sean Rice. Todos eles são classificados de acordo.

Quando a função de janela chega ao departamento seguinte, ela é reiniciada e começa o ranking desde o início. Assim, Franck Monteblanc é o mais bem pago, enquanto Simone Hill e Frances Jackson vêm em segundo e terceiro lugar, respectivamente.

O mesmo é feito com os funcionários do Gerenciamento de Riscos.

Exemplo nº 2

Vamos praticar isto em um exemplo um pouco diferente. Ainda queremos classificar os funcionários por salário. Desta vez, não pelo departamento, mas pelo cargo.

Eis como fazer isso.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY job_title ORDER BY salary DESC) AS salary_rank
FROM employees;

Os dados agora estão divididos por cargo. A cláusula ORDER BY permanece a mesma: ainda é ordenada em ordem decrescente por salário.

Esta é a saída da consulta:

A lógica é a mesma que no exemplo anterior. Neste exemplo, há um máximo de dois funcionários com o mesmo cargo, de modo que as fileiras não vão mais longe.

Bob Mendelsohn é o mais bem pago dos dois analistas de dados. Então há apenas o posto 1 para engenheiro de dados porque há apenas um funcionário com esse título de emprego. O restante dos dados é classificado com a mesma lógica.

Você pode encontrar mais exemplos neste artigo sobre funções de janela em SQL. E se conhecer as funções de janela o deixa faminto por uma carreira melhor, você ficará feliz por termos respondido às 10 principais perguntas de entrevista das funções de janela SQL para você.

Quando usar a PARTITION BY

Respondemos ao "como". A segunda pergunta importante que precisa ser respondida é quando você deve usar PARTITION BY.

Há dois usos principais. O primeiro uso é quando você quer agrupar dados e calcular algumas métricas, mas também manter as filas individuais com seus valores.

O segundo uso do PARTITION BY é quando você quer agregar dados em dois ou mais grupos e calcular estatísticas para estes grupos.

PARTITION BY TIPO DE Cócegas Sua Curiosidade

PARTITION BY é uma cláusula maravilhosa para se estar familiarizado. Não apenas significa que você conhece as funções de janela, mas também aumenta sua capacidade de calcular métricas, movendo-o para além das cláusulas obrigatórias usadas nas funções de janela.

Você quer satisfazer sua curiosidade sobre o que mais as funções de janela e PARTITION BY podem fazer? O Funções de Janela (Window Functions) em SQL curso está esperando por você!