Aprenda a cláusula OVER() no SQL com 7 exemplos

Aprenda a usar a cláusula OVER() em suas consultas SQL e você dará um passo à frente em seu conhecimento de SQL.

A maioria dos analistas de dados e desenvolvedores de SQL tem um bom controle das cláusulas SELECT, FROM, WHERE, GROUP BY e ORDER BY. Entretanto, essas cláusulas são apenas um subconjunto limitado da linguagem SQL. Há outras cláusulas, como OVER(), que nos permitem criar relatórios SQL flexíveis e acrescentam um enorme poder expressivo à linguagem SQL.

Neste artigo, explicaremos a cláusula OVER() demonstrando vários exemplos de seu uso. Vamos começar!

A cláusula OVER() no SQL

O SQL tem uma curva de aprendizado bastante fácil; qualquer pessoa pode aprender a criar consultas simples em SQL. De fato, o profissional de TI ou desenvolvedor de SQL comum lida com as cláusulas básicas SELECT, FROM, WHERE, GROUP BY e ORDER BY com facilidade. Essas cláusulas estavam disponíveis no SQL-92, que tem 30 anos de idade! Entretanto, a linguagem SQL não termina aí; muitos elementos novos foram adicionados desde 1992. Um deles é a cláusula OVER(), que nos permite usar funções de janela em consultas SQL.

Em SQL, as funções de janela são semelhantes a GROUP BY, pois funcionam em um grupo de linhas. Entretanto, as funções de janela são baseadas em uma janela de dados ou em um conjunto de linhas relacionadas à linha atual. Ao contrário de GROUP BY, as funções de janela não comprimem as linhas; elas mantêm intactos os detalhes das linhas individuais.

Para aqueles que desejam se aprofundar em OVER() e nas funções de janela em SQL, sugiro nosso curso on-lineFunções de Janela (Window Functions) em SQL . Ele contém vários exemplos de uso de diferentes funções de janela.

Ok, agora vamos voltar à função OVER(). Primeiro, vamos dar uma olhada em nossos dados.

Apresentando os dados

Nossas consultas de exemplo serão baseadas nos seguintes dados.

Suponhamos que tenhamos um restaurante de frutos do mar na costa do Mediterrâneo. Também temos uma tabela de banco de dados onde armazenamos o histórico da atividade do restaurante. Nosso banco de dados tem uma tabela chamada restaurant_activity com estas colunas:

• table_number
• waiter_name
• start_date
• start_time
• served_time
• end_time
• total_diners
• amount_payment
• total_tips.

Abaixo está um exemplo dos dados:

Restaurant_activity

Tudo isso deve ser autoexplicativo, mas vamos examinar rapidamente algumas colunas. start_date e start_time são a data e a hora em que uma refeição foi pedida pelos clientes em table_number; served_time é quando a refeição foi servida e end_time é quando os clientes pediram a conta. O amount_payment é o custo da refeição, sem incluir a gorjeta (que é armazenada como total_tips).

Exemplo de consulta nº 1: um uso simples de OVER()

Vamos começar usando a cláusula OVER() para calcular a porcentagem de cada pedido da receita diária total em 5/5/2022. Podemos calcular a receita total do restaurante nesse dia (excluindo gorjetas) usando OVER() combinado com a função de janela SUM(). A cláusula OVER() está sempre associada a uma função de janela; as funções de janela calculam um valor com base em um conjunto de registros definidos pela cláusula OVER(). A consulta é:

SELECT 
  start_date AS date,
  SUM(amount_payment) OVER () AS daily_revenue,
  amount_payment AS total_order,
  (amount_payment / SUM(amount_payment) OVER ())*100 AS order_percentage
FROM   restaurant_activity
WHERE  start_date = ’5/5/2022’

A consulta anterior calcula o daily_revenue para 5/5/2022 somando cada valor de pagamento para esse dia. A cláusula OVER() está vazia, o que significa que a janela de registros usada para calcular a função SUM() é o conjunto completo de registros retornados pela consulta. Em outras palavras, a janela é composta de todos os registros para essa data.

No cálculo da coluna order_percentage, dividimos o valor do pedido individual pela receita diária total para obter a porcentagem; esse é um ponto central na consulta, pois estamos combinando colunas de nível de linha com resultados de função de janela em uma única expressão.

Compreender quais registros fazem parte da janela é fundamental para entender como as funções de janela funcionam; voltaremos a esse ponto mais adiante neste artigo. Por enquanto, vamos dar uma olhada nos resultados:

Antes de entrar em exemplos mais complexos do site OVER(), gostaria de sugerir nosso Funções de Janela (Window Functions) em SQL Practice Set. Se você quiser realmente aprender a usar o OVER(), esses 100 exercícios interativos serão suficientes.

Exemplo de consulta nº 2: uso da subcláusula PARTITION BY

Neste exemplo, usaremos a subcláusula PARTITION BY; ela funciona com OVER() para definir janelas de dados.

Vamos supor que queremos um relatório semelhante ao anterior, mas expandido para qualquer dia de maio de 2022. Para cada dia, queremos ver a receita diária total, o valor do pedido individual para todos os pedidos e qual porcentagem da receita diária é atribuída a cada pedido. Aqui está a consulta:

SELECT start_date AS date,
  		SUM(amount_payment) OVER (PARTITION BY start_date) AS daily_revenue,
  		amount_payment AS total_order,
  		(amount_payment / SUM(amount_payment) OVER (PARTITION BY start_date)) * 100 AS
    order_percentage
FROM restaurant_activity
WHERE start_date BETWEEN ’5/1/2022’ AND ’5/31/2022’ 

Mencionamos que as funções de janela funcionam com base em uma janela de registros (ou um conjunto de registros) relacionados ao registro atual. A consulta anterior usa a subcláusula PARTITION BY start_date para definir quais registros pertencem a cada janela. No nosso exemplo, todos os registros com o mesmo valor start_date que a linha atual farão parte da janela. Como temos apenas dois valores exclusivos para start_date, temos apenas duas janelas diferentes. Elas são mostradas em verde e vermelho no resultado abaixo:

Exemplo de consulta nº 3: Usando a subcláusula ORDER BY

Agora vamos apresentar a subcláusula ORDER BY, que permite colocar as linhas em uma ordem específica dentro da janela.

Vamos supor que queremos um relatório simples com os cinco pedidos mais caros, juntamente com o garçom, a data e o número da mesa. A consulta é:

WITH ranking AS (
	SELECT 
table_number, 
amount_payment, 
waiter_name, 
start_date, 
	       RANK() OVER (ORDER BY amount_payment DESC) AS position
	FROM restaurant_activity
)
SELECT 
  amount_payment, 
  waiter_name, 
  start_date, 
  table_number, 
  position
FROM ranking
WHERE position <= 5
ORDER BY position

Aqui, usamos um CTE (ou Common Table Expression, expressão de tabela comum) para criar a classificação. No CTE, calculamos a posição de cada pedido usando a função de janela RANK() combinada com a seguinte cláusula OVER():

RANK() OVER (ORDER BY amount_payment DESC)

A cláusula acima define uma janela de registros que é formada por todos os registros da tabela, portanto, todos os pedidos do restaurante estão incluídos. Essa janela é classificada por amount_payment em ordem decrescente: o maior valor é o primeiro registro na janela, e assim por diante. A função RANK() retorna a posição da linha atual na janela ordenada, armazenando esse valor na coluna position do CTE.

Depois de criarmos o CTE de classificação, o restante da consulta usa ranking como qualquer outra tabela. Filtramos os registros para obter apenas as 5 primeiras classificações e, em seguida, ordenamos os resultados por position. É isso que obtemos:

Neste ponto, gostaria de sugerir o artigo SQL Funções de Janela (Window Functions) em SQL vs. GROUP BY: What's the Difference? Ele explica as diferenças entre a cláusula GROUP BY e as funções de janela por meio de vários exemplos de consultas SQL. Outro artigo interessante com detalhes adicionais é What Is the OVER() Clause in SQL?

Exemplo de consulta nº 4: Cálculo do tempo livre da tabela com OVER() e LAG()

As funções do Windows incluem muitas maneiras de facilitar cálculos complexos. Uma delas é a função LAG(), que retorna um valor de qualquer coluna da linha anterior relacionada à linha atual da janela.

O proprietário do restaurante quer saber quanto tempo as mesas estão livres, por exemplo, o tempo que passa entre os clientes. Podemos fazer isso criando um relatório com as colunas table_number, date, free_start, free_end, e free_time_duration.

Para calcular o tempo livre, precisamos acessar duas linhas. Precisamos do end_time da ocupação anterior e do start_time da ocupação seguinte; depois, podemos calcular o tempo decorrido entre as duas. É aqui que entra a função de janela LAG(), pois LAG() permite o acesso a qualquer coluna do registro anterior. Esta é a consulta que usaríamos:

    SELECT 
start_date AS date,
table_number,
-- ending time of the previous occupation 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS start_free_time,
-- starting time of current occupation
start_time AS end_free_time,
-- calculating the free time when the table was unoccupied
start_time - 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS free_time_duration
    FROM   restaurant_activity

A chave na consulta anterior é a função de janela LAG(). Nós a usamos para obter o end_time da ocupação anterior. A cláusula ...

OVER (PARTITION BY start_date, table_number ORDER BY start_time)

... define uma janela (ou conjunto de linhas) para cada par distinto de <start_date, table_number>, e cada uma dessas janelas é ordenada por start_time. Em seguida, LAG(end_time) retorna a hora final da ocupação anterior da tabela.

Você provavelmente notou que usamos LAG() duas vezes. A primeira é usada para obter o horário de início do período livre, e a segunda é para calcular a duração do tempo livre usando a seguinte expressão:

start_time - 
coalesce(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00')

Os resultados da consulta são:

Antes de ir para a próxima seção, sugiro os seguintes artigos para obter mais detalhes:

• Exemplo de função SQL Window com explicações
• Como classificar linhas no SQL: Um guia completo
• A função LAG e a função LEAD no SQL
• Como calcular o comprimento de uma série com o SQL

Exemplo de consulta nº 5: Cálculo de classificações com a cláusula OVER()

Nesta seção, abordaremos uma situação de negócios em que a cláusula SQL OVER() pode ser aplicada para criar uma classificação, como os 10 principais vendedores ou os 5 produtos mais vendidos. Você pode usar a cláusula OVER() combinada com a função de janela RANK() para obter esse tipo de relatório. Vejamos um exemplo de consulta que retornará as duas maiores gorjetas do dia e o garçom que as recebeu:

SELECT  *
FROM (
  		SELECT 	waiter_name,
  			start_date AS date,
			total_tips AS tip_amount,
			RANK() OVER(PARTITION BY start_date ORDER BY total_tips DESC) AS ranking
  	  	FROM restaurant_activity
     	     ) AS ranking_table
WHERE ranking <= 2;

Usamos uma subconsulta na cláusula FROM para criar uma "tabela" temporária chamada ranking_table. Ela usa a coluna ranking para armazenar a posição da gorjeta na classificação diária de gorjetas. A posição na classificação é calculada com a função de janela RANK(). Outras colunas da tabela temporária são waiter_name, date e tip_amount.

Na consulta externa, filtramos apenas as dicas classificadas como 1 e 2. O resultado da consulta é mostrado abaixo:

Exemplo de consulta nº 6: Cálculo de diferenças em relação a um período anterior

Outra possibilidade interessante é mostrar a diferença de valor entre o período de tempo anterior e o atual. A cláusula OVER() combinada com funções de janela como LEAD() e LAG() são usadas para criar esse tipo de relatório. Para obter mais informações, consulte Como calcular a diferença entre duas linhas no SQL.

Vamos supor que queremos ver a receita diária de cada garçom juntamente com a diferença do dia anterior. Na mesma linha, também queremos ver a diferença expressa como uma porcentagem. Aqui está a consulta:

SELECT	
  waiter_name,
  date,
  today_revenue,
  -- revenue variation ----------------------------------------------
  LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) yesterday_revenue,
  today_revenue - LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) AS revenue_variation,
  -- -----------------------------------------------------------------
  -- revenue variation percentage ------------------------------------------------
  round((today_revenue - 
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date)) /
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date),2) * 100 
AS revenue_variation_percentage
  -- -----------------------------------------------------------------------------		
FROM (
  SELECT  DISTINCT 
    start_date::date AS "date",
    waiter_name,
    SUM(total_tips) OVER ( 
      PARTITION BY waiter_name, start_date::date ORDER BY start_date)
AS today_revenue
  FROM 	restaurant_activity
) AS daily_revenue_per_waiter;

Criamos uma subconsulta na cláusula FROM chamada daily_revenue_per_waiter que tem os campos waiter_name, date e a receita total obtida por esse garçom nesse dia. Na consulta externa, usamos a função de janela LAG() para obter a receita no dia anterior e, em seguida, obtemos a variação da receita entre ontem e hoje e a variação percentual. O resultado é:

Exemplo de consulta nº 7: média móvel

As médias móveis são uma métrica frequentemente usada para suavizar as flutuações de curto prazo. Você pode saber mais no artigo O que é uma média móvel e como calculá-la no SQL; vamos ver como podemos criar uma usando a cláusula OVER().

Como exemplo, vamos supor que o proprietário de um restaurante queira saber o valor médio pago por pessoa na última mesa servida em um determinado momento. Ele usa essa média como uma métrica para saber quanto dinheiro os clientes estão pagando e para ativar algumas promoções ou descontos. Podemos calcular facilmente essa média com a expressão amount_payment/total_diners; no entanto, o proprietário percebeu que essa métrica apresenta grandes flutuações, por isso decidiu usar o valor médio pago por pessoa nas últimas 3 e 6 mesas servidas. A consulta para calcular esse relatório é:

SELECT start_date AS "date",
start_time AS "time",
table_number,
amount_payment AS total_amount,
total_diners,
       	ROUND(amount_payment/total_diners,2) AS diner_avg,
ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_3_tables_served,
	ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_6_tables_served
FROM   restaurant_activity

A consulta anterior calcula três médias diferentes. A primeira é uma média simples baseada na expressão:

ROUND(amount_payment/total_diners,2)

A segunda média é a média móvel para as últimas 3 mesas servidas; a terceira média é exatamente a mesma, mas para as últimas 6 mesas servidas:

ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2)
…
OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 

O termo "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" especifica que a média é calculada com base em 3 linhas: a linha atual e as 2 linhas imediatamente anteriores a ela. A janela é ordenada pelo horário de início da mesa. Aqui está o resultado:

Outros usos comerciais da cláusula OVER() incluem o cálculo de totais em execução (útil em todos os tipos de cenários de análise financeira) e o cálculo do comprimento de uma série de dados.

Pronto para praticar a cláusula SQL OVER()?

Demonstramos várias consultas que apresentam a cláusula SQL OVER(). Como OVER() deve ser usada combinada com uma função de janela, também abordamos algumas delas: SUM(), AVG(), LAG() e RANK().

Se quiser colocar em prática seus novos conhecimentos sobre a cláusula SQL OVER(), recomendo nosso curso interativo Funções de Janela (Window Functions) em SQL, seguido de nosso conjunto de práticas Funções de Janela (Window Functions) em SQL. Você pode ler sobre o curso no artigo SQL Course of the Month - Funções de Janela (Window Functions) em SQL. Você também pode obter uma cópia da nossa Folha de Consulta SQL Funções de Janela (Window Functions) em SQL gratuita para ajudá-lo em seu caminho de aprendizado. Desenvolva suas habilidades e aumente seus ativos!