Qual é a Cláusula OVER() em SQL?

As funções de janela são um dos recursos mais poderosos de SQL, mas não são usadas com freqüência pelo desenvolvedor SQL médio. Neste artigo, explicaremos como você pode definir diferentes tipos de estruturas de janela usando o OVER cláusula.

A cláusula OVER é essencial para as funções da janela SQL. Assim como as funções de agregação, as funções de janela realizam cálculos com base em um conjunto de registros - por exemplo, encontrar o salário médio de um grupo de funcionários.

Em alguns casos, funções agregadas não podem ser usadas porque colapsam todos os registros individuais em um grupo; isto torna impossível a referência a valores específicos (como o salário de um funcionário fora do grupo). Nessas situações, as funções de janela são preferidas porque não colapsam linhas; é possível referir-se a um valor de coluna no nível da linha, bem como ao valor agregado.

Há outros cenários em que as funções de janela são úteis. Por exemplo, podemos precisar fazer aritmética envolvendo uma coluna individual e um cálculo baseado em um conjunto de linhas. Uma instância do mundo real disto é calcular a diferença entre o salário médio do departamento e o salário de cada funcionário do departamento.

Ao utilizar funções de janela, a definição do conjunto de registros onde a função será calculada é crítica. Este conjunto de registros é chamado de quadro de janela; nós o definimos usando a cláusula SQL OVER.

Ao longo deste artigo, vamos demonstrar as consultas SQL usando o banco de dados de uma pequena empresa de relógios de luxo. A empresa armazena suas informações de vendas em uma tabela chamada sales:

A moldura de janela é um conjunto de linhas que depende da linha atual; assim, o conjunto de linhas poderia mudar para cada linha processada pela consulta. Definimos a moldura de janela usando a cláusula OVER. A sintaxe é:

  OVER ([PARTITION BY columns] [ORDER BY columns])

A subcláusula PARTITION BY define os critérios que os registros devem satisfazer para fazer parte da moldura da janela. Em outras palavras, PARTITION BY define os grupos nos quais as linhas estão divididas; isto será mais claro em nossa próxima consulta de exemplo. Finalmente, a cláusula ORDER BY define a ordem dos registros na moldura da janela.

Vamos ver a cláusula SQL OVER em ação. Aqui está uma consulta simples que retorna a quantidade total de unidades vendidas para cada artigo.

SELECT sale_day, sale_time, 
       branch, article, quantity, revenue,
       SUM(quantity) OVER (PARTITION BY article) AS total_units_sold
FROM   sales

Esta consulta mostrará todos os registros do sales tabela com uma nova coluna exibindo o número total de unidades vendidas para o artigo relevante. Podemos obter a quantidade de unidades vendidas usando a função de agregação SUM, mas depois não pudemos mostrar os registros individuais.

Nesta consulta, a subcláusula OVER PARTITION BY indica que o quadro de janela é determinado pelos valores na coluna article; todos os registros com o mesmo valor article estarão em um grupo. Abaixo, temos o resultado desta consulta:

A coluna total_units_sold do relatório foi obtida através da expressão:

SUM(quantity) OVER (PARTITION BY article) total_units_sold

Para os leitores que desejam aprofundar o assunto, sugiro os dois artigos seguintes: Qual é a diferença entre GROUP BY e PARTITION BY e Funções de Janela (Window Functions) em SQL no SQL Server: Primeira Parte: A Cláusula OVER()

A Cláusula SQL OVER em Ação

Para cada artigo, suponhamos que queremos comparar a quantidade total deste artigo vendido em cada mês de 2021 com a quantidade total deste artigo vendido durante todo o ano. Para isso, criaremos um relatório simples com as colunas article, month, units_sold_month e units_sold_year. A consulta é:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year
FROM  sales 
WHERE EXTRACT('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Os resultados da consulta são:

Aqui, calculamos o total de unidades vendidas usando duas granularidades de agrupamento diferentes: mês e ano. A primeira cláusula OVER...

OVER (PARTITION BY article, sale_month) 

... nos permite obter o número de unidades de cada artigo vendido em um mês. A segunda cláusula OVER...

OVER (PARTITION BY article)

.... nos permite calcular o número total de unidades de um determinado artigo vendido durante todo o ano.

Na próxima consulta, vamos apenas adicionar a coluna month_percentage para mostrar a porcentagem que um mês específico ocupa dentro do total anual. Podemos calculá-la usando a seguinte consulta:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) as month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year,
       ( ( SUM(quantity) OVER (PARTITION BY article, sale_month)::decimal /
           SUM(quantity) OVER (PARTITION BY article)::decimal ) * 100
       ) AS month_percentage
FROM sales 
WHERE extract('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Note que na consulta anterior, não utilizamos diferentes cláusulas OVER; apenas reutilizamos as mesmas funções de janela e calculamos uma porcentagem. Você pode ver os resultados abaixo:

Vamos agora criar um relatório diferente que analisa o desempenho de vários ramos. Queremos ver as colunas branch e month. Também precisamos de cálculos para obter o:

• Receita total para aquele mês.
• Receita agrupada por filial e mês.
• Receita média mensal das filiais.
• Diferença entre a receita de cada filial e a receita média mensal.

SELECT DISTINCT branch,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(revenue) OVER (PARTITION BY sale_month) AS total_revenue_month,
       SUM(revenue) OVER (PARTITION BY branch, sale_month) AS branch_revenue_month,
      
 -- Next column is the branch average revenue in the current month
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS average_month_branch,
 
 -- Next column is the difference between branch revenue and average branch revenue

        SUM(revenue) OVER (PARTITION BY branch, sale_month) -
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS gap_branch_average

FROM sales 
WHERE extract('YEAR' from sale_day) = 2021
ORDER BY branch, month

Mais uma vez, usamos apenas duas cláusulas OVER, mas usamos expressões aritméticas diferentes para obter certos valores. Utilizamos ...

SUM(revenue) OVER (PARTITION BY sale_month) 

... para calcular a receita total do mês, mas também a utilizamos em uma expressão aritmética para obter a receita média mensal do ramo.

Utilizamos ...

SUM(revenue) OVER (PARTITION BY branch, sale_month) 

.... para calcular a receita mensal do ramo e a diferença entre a receita mensal desse ramo e a média.

A tabela seguinte é o resultado da consulta. Observe que a coluna gap_branch_average pode conter números positivos ou negativos. Um número negativo indica que a receita mensal desta filial foi menor que a receita média.

Para informações adicionais sobre funções de janela em SQL, sugiro um exemplo de função de janela SQL com explicações, um artigo de nível de entrada sobre funções de janela. Para leitores mais avançados, How to Rank Rows Within a Partition in SQL mostra como criar rankings em seus relatórios usando a função de janela RANK().

A Cláusula OVER e Analítica Funções de Janela (Window Functions) em SQL

Nas consultas anteriores, usamos funções de janela para comparar números mensais (receita e unidades vendidas, respectivamente) com números anuais. Nesta seção, utilizaremos esquadrias ordenadas, o que nos permite escolher um registro na esquadria com base em sua posição. Por exemplo, podemos escolher o primeiro registro na moldura da janela, ou o registro anterior ao registro atual, ou o registro posterior ao registro atual. Estas funções de janela analítica fornecem um grande poder expressivo ao SQL.

Na consulta seguinte, mostraremos o aumento/diminuição da receita para o mesmo ramo em dois meses contíguos. Para fazer isso, precisamos calcular a diferença entre a receita do mês atual e a receita do mês anterior. Isto requer a função de janela analítica LAG(), que pode obter um valor de coluna de uma linha anterior à linha atual.

WITH branch_month_sales AS (
	SELECT    DISTINCT
		    branch,
		    EXTRACT('MONTH' FROM sale_day) AS month,
		    SUM(revenue) OVER (PARTITION BY branch, sale_month ) AS revenue
	FROM sales
)
SELECT branch, 
	month,
	revenue AS revenue_current_month,
	LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_prev_month,
	revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_delta
FROM branch_month_sales
ORDER BY branch, month

Nesta consulta, usamos uma expressão de tabela comum (CTE) chamada branch_month_sales para armazenar a receita total de cada filial e mês. Depois escrevemos uma segunda consulta que usa a função de janela LAG() para obter a receita do mês anterior (usando a informação de branch_month_sales). Note que a estrutura da janela é ordenada por mês.

Aqui estão os resultados:

Em todas as consultas mostradas neste artigo, usamos apenas algumas funções de janela. Há muitas outras funções de janela em SQL. Aqui está uma lista de cada uma delas:

Saiba mais sobre a cláusula SQL OVER

Neste artigo, cobrimos a cláusula OVER e o PARTITION BY e ORDENAR POR subcláusulas.

Se você quiser continuar aprendendo sobre funções de janela, há um par de artigos que eu gostaria de compartilhar. O primeiro é o 8 Best SQL Window Function Articles, que lhe indicará outros grandes artigos. O segundo é uma folha de trapaça sobre funções de janela que inclui sintaxe, exemplos e imagens; é o meu artigo favorito sobre funções de janela.

Para aqueles que querem ir mais fundo, sugiro nosso curso interativo Funções de Janela (Window Functions) em SQL SQL. Se você quiser aumentar suas habilidades SQL em geral, tente a faixa SQL de A a Z. É um olhar abrangente sobre tudo o que você precisa saber para trabalhar efetivamente com SQL.