Como definir uma moldura de janela em funções de janela SQL

A função de janela é um recurso super poderoso da linguagem SQL. No núcleo de qualquer função de janela, há um conjunto de registros, chamado de quadro de janela, definido usando um Cláusula OVER. Saber que registros estão na moldura da janela, como eles são ordenados e quais são seus limites superior e inferior, é fundamental para entender como funciona a janela. Neste artigo, analisaremos e explicaremos com exemplos como você pode definir diferentes tipos de esquadrias de janela. Continue lendo para dar um passo importante no crescimento de suas habilidades SQL!

Usando PARTITION BY para definir uma moldura de janela

As funções da janela SQL realizam cálculos com base em um conjunto de registros. Por exemplo, você pode querer calcular o salário médio de um grupo específico de registros de funcionários. Este grupo de registros é chamado de quadro de janela, e sua definição é central para entender como funcionam as funções de janela e como podemos tirar proveito delas.

O quadro de janela é um conjunto de linhas relacionadas com a linha atual onde a função de janela é usada para o cálculo. O quadro de janela pode ser um conjunto diferente de linhas para a próxima linha no resultado da consulta, uma vez que depende da linha atual sendo processada. Cada linha no conjunto de resultados da consulta tem sua própria moldura de janela.

No restante deste artigo, mostraremos exemplos de consultas com base em um banco de dados de um grupo de concessionárias de automóveis. O grupo armazena as informações de vendas agrupadas por mês em uma tabela chamada monthly_car_sales. Abaixo está a tabela com alguns dados de exemplo:

monthly_car_sales

Uma maneira simples de criar uma estrutura de janela é usando uma cláusula OVER com uma subcláusula PARTITION BY. No seguinte exemplo SQL, geramos um relatório de receita por marca do carro para o ano 2021.

SELECT make,
       SUM(revenue) OVER (PARTITION BY make) AS total_revenue
FROM   monthly_car_sales
WHERE  year = 2021

Abaixo, as esquadrias geradas pela consulta anterior são mostradas em cores diferentes (vermelho para Ford e azul para Renault). Todos os registros com o mesmo valor na coluna make (as linhas com o código de cores abaixo) pertencem à mesma armação de janela. Como temos apenas dois valores diferentes na coluna make, temos duas armações de janela.

O resultado da consulta é:

Gostaria de sugerir 2 artigos onde você pode encontrar muitas informações introdutórias sobre as funções da janela SQL: "SQL Course of The Month - Funções de Janela (Window Functions) em SQL " e "When Do I Use SQL Funções de Janela (Window Functions) em SQL?".

Ordenando as linhas dentro de uma moldura de janela com ORDENAMENTO POR

Além de PARTITION BY, podemos usar uma subcláusula ORDER BY para encomendar as filas dentro de uma armação de janela. Ter a estrutura da janela ordenada por alguns critérios nos permite utilizar funções de janela analíticas como LEAD(), LAG(), e FIRST_VALUE(), entre outras.

Por exemplo, se quisermos obter a diferença de receita entre meses consecutivos, podemos encomendar a armação de janela por mês. Então, dada qualquer linha atual, a função de janela LAG() pode retornar qualquer coluna do mês anterior. Vejamos um exemplo que obtém a diferença de receita entre cada par de meses consecutivos.

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER ( ORDER BY month) AS previous_month_revenue,
	 revenue - LAG (revenue) OVER (ORDER BY month) AS delta_revenue
FROM monthly_car_sales
WHERE year = 2021
  AND model = 'Mustang'

O resultado da consulta anterior é:

A primeira linha do resultado tem valores NULL nas colunas previous_month_revenue e delta_revenue. Isto porque não há nenhum mês anterior para janeiro. A coluna delta_revenue é calculada entre a receita do mês atual e a receita do mês anterior, a última das quais é obtida com a função da janela LAG().

Quando usamos ORDER BY em uma cláusula OVER, há um novo elemento a ser considerado: os limites da moldura da janela. Se não especificarmos nenhuma cláusula ORDER BY, toda a divisória se torna a moldura da janela. Entretanto, quando usamos uma subcláusula ORDER BY, a linha atual se torna o limite superior da moldura da janela. Em outras palavras, as linhas que seguem a linha atual (com base no critério ORDER BY ) não estão incluídas na moldura da janela. Na próxima seção, abordaremos em detalhes o conceito de limites da moldura da janela.

Definindo os limites da moldura de janela com ROWS

Uma característica muito interessante da cláusula OVER é a capacidade de especificar os limites superior e inferior de uma moldura de janela. Estes limites podem ser especificados usando uma das duas subcláusulas da cláusula OVER: ROWS ou RANGE. Nesta seção, explicaremos como usar a subcláusula ROWS da cláusula OVER.

A moldura da janela é um conjunto de linhas que estão de alguma forma relacionadas com a linha atual. Seus limites podem ser definidos para cada linha no resultado da consulta com uma subcláusula ROWS, que tem a seguinte sintaxe:

ROWS BETWEEN lower_bound AND upper_bound

Como já mencionamos na seção anterior, é importante saber quais são os limites padrão da moldura da janela. Quando especificamos uma subcláusula ORDER BY, a linha atual é o limite superior da moldura da janela por padrão. Entretanto, em alguns casos, precisamos alterar este limite superior (ou o limite inferior), como veremos abaixo.

Vamos ver um exemplo onde precisamos especificar os limites de uma moldura de janela. Suponha que queremos um relatório com o total de vendas do mês atual, o total de vendas do mês anterior e o máximo de vendas em qualquer mês individual ao longo do ano, tudo por marca e modelo. A consulta para obter tal relatório é:

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER (PARTITION BY make, model ORDER BY month) AS    
                                                   prev_month,
	 MAX(revenue) OVER (PARTITION BY make, model ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_year_revenue
FROM monthly_car_sales
WHERE year = 2021

Se tivéssemos omitido a subcláusula ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING na função da janela MAX() na consulta anterior, teríamos obtido o máximo entre o primeiro mês e o mês atual. Isto está errado, já que queremos a receita mensal máxima considerando o ano inteiro (incluindo os meses após o mês atual). Portanto, precisamos incluir todos os meses disponíveis na tabela. Fazemos isso adicionando a subcláusula ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING à cláusula OVER.

A imagem seguinte mostra todos os limites possíveis que podemos especificar para definir os limites inferior e superior de uma moldura de janela:

As opções para os limites inferior e superior na cláusula OVER são:

• UNBOUNDED PRECEDING
• N PRECEDING
• CURRENT ROW
• N FOLLOWING
• UNBOUNDED FOLLOWING

Precisamos ter certeza de que o limite inferior seja menor do que o limite superior.

Finalmente, eu gostaria de sugerir o artigo Por que aprender SQL Funções de Janela (Window Functions) em SQL em 2021? onde você pode aprender como usar as funções de janela.

Definindo Limites de Janela com RANGE

Na seção anterior, definimos os limites de uma moldura de janela em termos de ROWS. Nesta seção, explicaremos como usar a subcláusula RANGE para especificar os limites de uma moldura de janela em intervalos de linhas. A sintaxe da subcláusula RANGE é a seguinte:

RANGE BETWEEN lower_bound AND upper_bound

Um intervalo é um conjunto de linhas com o mesmo valor para o critério PARTITION BY. Por exemplo, se tivermos um mês PARTITION BY, podemos ver a diferença na próxima imagem quando usamos ROWS ou RANGE para definir uma moldura de janela:

OVER ( PARTITION BY …... 

             ORDER BY month 

             ROWS BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

OVER ( PARTITION BY ….. 

             ORDER BY month 

             RANGE BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

Se quisermos um relatório de receitas por marca para o mês atual e para cada um dos últimos três meses, podemos usar a seguinte consulta:

SELECT make,
       model,
       month,
	 revenue AS model_revenue_current_month,
       SUM(revenue) OVER ( PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 0 PRECEDING AND CURRENT ROW
                         ) AS make_current_month,
	 SUM(revenue) OVER (PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                        ) AS make_last_2_months,
	   SUM(revenue) OVER (PARTITION BY make
                            ORDER BY month
                            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                        ) AS make_last_3_months
FROM monthly_car_sales
WHERE year = 2021
ORDER BY 1,3,2

A consulta anterior usa a subcláusula RANGE para especificar um quadro de janela com todos os registros da marca atual para uma faixa de N meses. Por exemplo:

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                  ) AS make_last_2_months

A subcláusula anterior RANGE BETWEEN 1 PRECEDING AND CURRENT ROW especifica um quadro de janela que inclui o mês anterior e o mês atual. Então, a função SUM() retornará a receita total nos dois últimos meses.

Da mesma forma, podemos usar a seguinte cláusula OVER para obter a receita total nos últimos três meses.

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                  ) AS make_last_3_months

Há várias abreviações disponíveis para facilitar a sintaxe destas cláusulas limitadoras:

Por último, mas não menos importante, aqui está a minha folha de consultas preferida nas funções de janela com muitos detalhes de sintaxe: folha de consultas com funções de janela SQL.

Aproveite a vantagem de Funções de Janela (Window Functions) em SQL!

Uma idéia central na função de janela é a estrutura da janela, ou seja, o grupo de registros sobre o qual a função de janela funciona. Neste artigo, explicamos que a moldura de janela depende da linha atual e é definida pela cláusula OVER. Também mostramos vários exemplos para definir quais registros devem ser incluídos no quadro de janela, ordenando as linhas dentro dele e definindo seus limites.

Para aqueles que querem ir mais fundo, sugiro o curso online Funções de Janela (Window Functions) em SQL, com muitos exemplos usando diferentes funções de janela. Desenvolva suas habilidades e aumente seus ativos!!