Como usar a SQL PARTITION BY com OVER

No coração de cada chamada de função de janela está uma cláusula OVER que define como as janelas dos registros são construídas. Dentro da cláusula OVER, pode haver uma cláusula opcional PARTITION BY subclause que define os critérios para identificar quais registros devem ser incluídos em cada janela. Continue lendo e dê um passo importante no crescimento de suas habilidades SQL!

O que é a PARTITION BY Cláusula em SQL?

A expressão SQL PARTITION BY é uma subcláusula da cláusula OVER, que é usada em quase todas as invocações de funções de janela como AVG(), MAX(), e RANK(). Como muitos leitores provavelmente sabem, as funções de janela operam em molduras de janela que são conjuntos de linhas que podem ser diferentes para cada registro no resultado da consulta. É aqui que entra a subcláusula SQL PARTITION BY: ela é usada para definir quais registros fazem parte da moldura da janela associada a cada registro do resultado.

Este artigo explica a SQL PARTITION BY e seus usos com exemplos. Como está profundamente relacionado às funções de janela, você pode primeiro querer ler alguns artigos sobre funções de janela, como "Exemplo de função de janela SQL com explicações", onde você encontra muitos exemplos. Se você quiser aprender mais sobre funções de janela, há também um artigo interessante com muitas dicas para outros artigos sobre funções de janela.

A primeira coisa a se focar é a sintaxe. Veja aqui como usar a cláusula SQL PARTITION BY:

SELECT
    ,
     OVER(PARTITION BY  [ORDER BY ])
FROM table;

Vejamos um exemplo que usa uma cláusula PARTITION BY. Vamos usar a seguinte tabela chamada car_list_prices:

Para cada carro, queremos obter a marca, o modelo, o preço, o preço médio em todos os carros, e o preço médio sobre o mesmo tipo de carro (para ter uma idéia melhor de como o preço de um determinado carro se compara a outros carros). Aqui está a pergunta:

SELECT
    car_make,
    car_model,
    car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

O resultado da consulta é o seguinte:

A consulta acima usa duas funções de janela. A primeira é usada para calcular o preço médio em todos os carros da lista de preços. Ela usa a função de janela AVG() com uma cláusula OVER vazia, como vemos na expressão a seguir:

AVG(car_price) OVER() AS "overall average price"

A segunda função de janela é usada para calcular o preço médio de um car_type específico, como padrão, premium, esporte, etc. É aqui que usamos uma cláusula OVER com uma subcláusula PARTITION BY, como vemos nesta expressão:

AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"

As funções de janela são bastante poderosas, certo? Se você gostaria de aprender mais, fazendo exercícios bem preparados, sugiro o curso Funções de Janela (Window Functions) em SQLonde você pode aprender e se tornar confortável com o uso de funções de janela em bancos de dados SQL.

Indo fundo com a cláusula SQL PARTITION BY

A cláusula GROUP BY agrupa um conjunto de registros com base em critérios. Isto nos permite aplicar uma função (por exemplo, AVG() ou MAX()) a grupos de registros para produzir um resultado por grupo.

Como exemplo, digamos que queremos obter o preço médio e o preço máximo para cada marca. Use a seguinte consulta:

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

Aqui está o resultado desta consulta:

Em comparação com as funções de janela, GROUP BY colapsa os registros individuais em um grupo. Como conseqüência, não se pode fazer referência a nenhum campo de registro individual; ou seja, somente as colunas da cláusula GROUP BY podem ser referenciadas.

Por exemplo, digamos que você queira criar um relatório com o modelo, o preço, e o preço médio da marca. Você não pode fazer isso usando GROUP BY, pois os registros individuais de cada modelo são colapsados devido à cláusula GROUP BY car_make. Para algo assim, você precisa usar as funções de janela, como vemos no exemplo a seguir:

SELECT car_make,
       car_model,
       car_price,
       AVG(car_price) OVER (PARTITION BY car_make) AS average_make
FROM   car_list_prices

O resultado desta consulta é o seguinte:

Para aqueles que querem ir mais fundo, sugiro o artigo "Qual é a diferença entre um GROUP BY e uma PARTITION BY..." com muitos exemplos usando funções agregadas e de janela.

Além da cláusula PARTITION BY, há outra cláusula chamada ORDER BY que estabelece a ordem dos registros dentro da moldura da janela. Algumas funções de janela requerem um ORDER BY. Por exemplo, as funções de janela LEAD() e LAG() precisam que a janela de registro seja ordenada, pois acessam o registro anterior ou o próximo registro do registro atual.

Uma moldura de janela é composta de várias linhas definidas pelos critérios da cláusula PARTITION BY. Entretanto, podemos especificar limites ou limites para a moldura da janela como vemos na imagem a seguir:

Os limites inferior e superior na cláusula OVER podem ser:

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Quando não especificamos nenhum limite em uma cláusula OVER, sua moldura de janela é construída com base em alguns valores de limite padrão. Eles dependem da sintaxe usada para chamar a função de janela. A tabela a seguir mostra os limites padrão da moldura de janela.

Há um artigo detalhado chamado "SQL Funções de Janela (Window Functions) em SQL Cheat Sheet" onde você pode encontrar muitos detalhes de sintaxe e exemplos sobre os diferentes limites do quadro de janela.

A PARTICIPAÇÃO SQL POR Cláusula em Ação

Nesta seção, mostramos alguns exemplos da cláusula SQL PARTITION BY. Todos são baseados na tabela paris_london_flightsA companhia aérea é utilizada para analisar os resultados comerciais desta rota para os anos de 2018 e 2019. Aqui está um subconjunto dos dados:

Exemplo 1

A primeira consulta gera um relatório incluindo o flight_number, aircraft_model com a quantidade de passageiros transportados, e a receita total. A consulta está abaixo:

SELECT DISTINCT
       flight_number,
       aircraft_model,
	SUM(num_of_passengers) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_passengers,
	SUM(total_revenue) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_revenue
FROM paris_london_flights
ORDER BY flight_number, aircraft_model;

Como o total de passageiros transportados e a receita total são gerados para cada combinação possível de flight_number e aircraft_model, usamos a seguinte cláusula PARTITION BY para gerar um conjunto de registros com o mesmo número de vôo e modelo de aeronave:

OVER (PARTITION BY flight_number, aircraft_model)

Em seguida, para cada conjunto de registros, aplicamos as funções de janela SUM(num_of_passengers) e SUM(total_revenue) para obter as métricas total_passengers e total_revenue mostradas no próximo conjunto de resultados.

Exemplo 2

Na consulta seguinte, mostramos como o negócio evolui, comparando as métricas de um mês com as do mês anterior. Criamos um relatório usando funções de janela para mostrar a variação mensal em passageiros e receita.

WITH year_month_data AS (
  SELECT DISTINCT
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   SUM(number_of_passengers)
              OVER (PARTITION BY EXTRACT(YEAR FROM scheduled_departure),
                                  EXTRACT(MONTH FROM scheduled_departure)
                   ) AS passengers
   FROM  paris_london_flights
  ORDER BY 1, 2
)
SELECT  year,
        month,
	 passengers,
	 LAG(passengers) OVER (ORDER BY year, month) passengers_previous_month,
	 passengers - LAG(passengers) OVER (ORDER BY year, month) AS passengers_delta
FROM year_month_data;

Na consulta acima, usamos uma cláusula WITH para gerar um CTE (CTE significa expressões comuns de tabela e é um tipo de consulta para gerar uma tabela virtual que pode ser usada no resto da consulta). Nós inserimos os dados em uma tabela virtual chamada year_month_dataque tem 3 colunas: year, month, e passengers com o total de passageiros transportados durante o mês.

Em seguida, a segunda consulta (que leva o CTE year_month_data como um input) gera o resultado da consulta. A coluna passageiros contém o total de passageiros transportados associado ao registro atual. Com a função da janela LAG(passenger), obtemos o valor dos passageiros da coluna de passageiros do registro anterior ao registro atual. Nós ORDER BY year and month:

LAG(passengers) OVER (ORDER BY year, month)
passengers_previous_month

Ele obtém o número de passageiros do registro anterior, correspondente ao mês anterior. Em seguida, temos o número de passageiros do mês atual e dos meses anteriores. Finalmente, na última coluna, calculamos a diferença entre os dois valores para obter a variação mensal de passageiros.

Exemplo 3

Para nosso último exemplo, vejamos os atrasos dos vôos. Queremos obter diferentes médias de atraso para explicar os motivos por trás dos atrasos.

Usamos um CTE para calcular uma coluna chamada month_delay com o atraso médio para cada mês e obter o modelo da aeronave. Então, na consulta principal, obtemos as diferentes médias, como vemos abaixo:

WITH paris_london_delays AS (
  SELECT DISTINCT
	   aircraft_model,
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   AVG(real_departure - scheduled_departure) AS month_delay
   FROM  paris_london_flights
   GROUP BY 1, 2, 3
)
SELECT  DISTINCT
     aircraft_model,
     year,
     month,
     month_delay AS monthly_avg_delay,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year) AS year_avg_delay,
     AVG(month_delay) OVER (PARTITION BY year) AS year_avg_delay_all_models,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year 
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                            ) AS rolling_average_last_4_months

FROM paris_london_delays
ORDER BY 1,2,3

Esta consulta calcula várias médias. A primeira é a média por modelo de aeronave e ano, o que é muito claro. A segunda é a média por ano em todos os modelos de aeronaves. Note que usamos apenas o ano da coluna na cláusula PARTITION BY. A terceira e última média é a média móvel, onde usamos os 3 meses mais recentes e o mês atual (ou seja, linha) para calcular a média com a seguinte expressão:

AVG(month_delay) OVER (PARTITION BY aircraft_model, year
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                           ) AS rolling_average_last_4_months

A cláusula ROWS BETWEEN 3 PRECEDING AND CURRENT ROW no PARTITION BY restringe o número de linhas (ou seja, meses) a serem incluídas na média: os 3 meses anteriores e o mês atual. Você pode ver um resultado parcial desta consulta abaixo:

O artigo "A Cláusula RANGE em SQL Funções de Janela (Window Functions) em SQL: 5 Exemplos Práticos" explica como definir um subconjunto de linhas na moldura da janela usando RANGE ao invés de ROWS, com vários exemplos. Outro artigo interessante é "Common SQL Funções de Janela (Window Functions) em SQL: Usando Partições com Funções de Ranking" no qual a cláusula PARTITION BY é coberta em detalhes.

O Poder de Funções de Janela (Window Functions) em SQL e a PARTITION BY SQL PARTITION BY

As funções Window são um recurso muito poderoso da linguagem SQL, e a cláusula SQL PARTITION BY desempenha um papel central em seu uso. Neste artigo, abordamos como esta cláusula funciona e mostramos vários exemplos usando diferentes sintaxes.

Antes de fechar, eu sugiro um SQL Avançado curso, onde você pode ir além do básico e se tornar um mestre SQL. Se você quiser ler sobre a cláusula OVER, há um artigo completo sobre o tópico: "Como Definir uma Estrutura de Janela em SQL Funções de Janela (Window Functions) em SQL". Melhore suas habilidades e aumente seus ativos!