5 Exemplos práticos de utilização de ROWS BETWEEN em SQL

As funções da janela SQL são extremamente úteis para calcular agregações complexas como médias móveis ou totais em execução. A cláusula ROWS permite especificar linhas para seus cálculos, permitindo ainda mais sofisticadas molduras de janela. Aqui estão cinco exemplos práticos de alavancagem da cláusula ROWS BETWEEN em SQL.

As funções de janela (também chamadas funções OVER) calculam seu resultado com base em uma armação de janela deslizante (ou seja, um conjunto de linhas). Elas são semelhantes às funções agregadas na medida em que é possível calcular o valor médio, total ou mínimo/máximo em um grupo de linhas. Entretanto, há algumas diferenças importantes:

• As funções de janela não colapsam linhas como as funções agregadas fazem. Assim, você ainda pode misturar atributos de uma linha individual com os resultados de uma função de janela.
• As funções de janela permitem o deslizamento de esquadrias de janela, o que significa que o conjunto de linhas usadas para o cálculo de uma função de janela pode ser diferente para cada linha individual.

A sintaxe de uma função de janela é mostrada em texto azul abaixo:

SELECT , ,
  OVER (
 	PARTITION BY <...>
   	ORDER BY <...>
    	) 
FROM ;

Quando você usa uma função de janela na instrução SELECT, você basicamente calcula outra coluna com esta função:

• Você começa especificando uma função (por exemplo, AVG(), SUM(), ou COUNT()).
• Em seguida, você usa a palavra-chave OVER para definir um conjunto de linhas. Opcionalmente, você pode:
  • Agrupe as linhas com PARTITION BY para que as funções sejam calculadas dentro destes grupos, ao invés de todo o conjunto de linhas.
  • Ordene as linhas dentro de um quadro de janela usando ORDER BY se a ordem das linhas for importante (por exemplo, ao calcular totais em execução).
  • Especifique a relação do quadro de janela com a linha atual (por exemplo, o quadro deve ser a linha atual e duas anteriores, ou a linha atual e todas as linhas seguintes, etc.).

Um quadro de janela é definido usando ROWS, RANGE, e GROUPS cláusulas. Neste artigo, vamos nos concentrar na cláusula ROWS e suas opções. Para saber mais sobre funções de janela e definição de armações de janela, consulte este artigo com exemplos de funções de janela, este guia explicativo e, é claro, nossa folha de consulta SQL Funções de Janela (Window Functions) em SQL de duas páginas.

Cláusula ROWS: Sintaxe e Opções

O objetivo da cláusula ROWS é especificar a moldura da janela em relação à linha atual. A sintaxe é:

ROWS BETWEEN lower_bound AND upper_bound

Os limites podem ser qualquer uma destas cinco opções:

• UNBOUNDED PRECEDING - Todas as linhas antes da linha atual.
• n PRECEDING - n linhas antes da linha atual.
• CURRENT ROW - Apenas a linha atual.
• n FOLLOWING - n linhas após a linha atual.
• UNBOUNDED FOLLOWING - Todas as linhas após a linha atual.

Fonte: SQL Funções de Janela (Window Functions) em SQL Folha de fraude

Aqui estão algumas coisas para ter em mente ao definir as armações das janelas com a cláusula ROWS:

• A armação da janela é avaliada separadamente dentro de cada divisória.
• A opção padrão depende de se você usar ORDER BY:
  • Com ORDER BY, a moldura padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Sem ORDER BY, a moldura padrão é ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Se um de seus limites for uma linha atual, você pode pular a especificação deste limite e usar uma versão mais curta da definição da moldura da janela:
  • UNBOUNDED PRECEDING é o mesmo que BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • n PRECEDING é o mesmo que BETWEEN n PRECEDING AND CURRENT ROW.
  • n FOLLOWING é o mesmo que BETWEEN CURRENT ROW AND n FOLLOWING.
  • UNBOUNDED FOLLOWING é o mesmo que BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Passemos aos exemplos para ver como isto funciona na prática.

5 Exemplos práticos de utilização do ROWS em Funções de Janela (Window Functions) em SQL

Exemplo 1

Para começar com a cláusula ROWS, usaremos a seguinte tabela com dados de vendas de uma livraria.

Em nosso primeiro exemplo, queremos adicionar outra coluna que mostre a receita total desde a primeira data até a data atual da linha (ou seja, o total em execução). Aqui está a consulta que podemos usar:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

Para calcular o total em execução usando uma função de janela, passamos pelos seguintes passos:

• Calculando a receita total usando a função agregada SUM().
• Ordenando os registros no quadro da janela por data (o padrão está em ordem ascendente), uma vez que a ordem das linhas é importante ao calcular um total em execução.
• Especificando o quadro de janela definindo o limite inferior como UNBOUNDED PRECEDING e o limite superior como CURRENT ROW. Isto incluirá todas as linhas até e incluindo a atual. Observe que o comportamento padrão sem a cláusula ROWS especificada seria o mesmo neste caso. A moldura padrão usa RANGE, e não ROWS. Como cada dia aparece apenas uma vez na tabela, o resultado será o mesmo para RANGE e ROWS. Assim, poderíamos também usar a seguinte consulta para obter os mesmos resultados:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

Como você pode ver, a consulta funcionou como pretendido e obtivemos o total em execução em nossa terceira coluna. No primeiro dia, é igual à soma das vendas deste dia - $1515,45; no segundo dia, é igual à soma das vendas do primeiro e segundo dias - $3860,80; na linha seguinte, obtemos a soma das vendas dos três primeiros dias - $4764,79, etc.

Em nossos próximos exemplos, veremos como funciona a cláusula ROWS quando os registros forem divididos em vários grupos.

Para praticar a definição de esquadrias, confira esta Funções de Janela (Window Functions) em SQL curso com mais de 200 desafios de codificação.

Exemplo 2

Para os próximos dois exemplos, usaremos a tabela abaixo. Ela contém dados fictícios sobre a temperatura média (em °C) e precipitação total (em mm) em duas cidades italianas (Roma e Florença) durante cinco dias consecutivos.

Queremos calcular a temperatura média móvel de três dias separadamente para cada cidade. Para separar os cálculos para as duas cidades, incluiremos a cláusula PARTITION BY. Depois, ao especificar a estrutura da janela, estaremos considerando o dia atual e os dois dias anteriores:

Note também que colocamos nossa função de janela dentro da função ROUND() para que a média móvel de três dias seja arredondada para uma casa decimal. Aqui está o resultado:

A média móvel foi calculada separadamente para Florença e Roma. Para 1º de setembro, a média móvel é igual à temperatura média diária, já que não temos nenhum registro anterior. Então, em 2 de setembro, a média móvel é calculada como a temperatura média para 1º e 2º (17,5 °C em Florença e 18,8 °C em Roma, respectivamente). Em 3 de setembro, finalmente, temos dados suficientes para calcular a temperatura média para três dias (os dois anteriores e o dia atual), que acaba sendo de 18,0 °C em Florença e 19,2 °C em Roma. Então, a média móvel de três dias para 4 de setembro é calculada como a temperatura média para 2, 3 e 4 de setembro, e assim por diante.

Mais uma coisa a ser observada: a ordem dos registros na moldura da janela tem um papel fundamental na especificação das linhas a serem consideradas .

Na consulta acima, ordenamos os registros na moldura da janela por data em ordem ascendente (usando a configuração padrão), ou seja, estamos começando com a data mais antiga. Então, para incluir dois dias antes do dia atual em nossos cálculos, definimos o limite inferior como 2 PRECEDING.

Entretanto, poderíamos obter exatamente o mesmo quadro de janela ordenando os registros em ordem decrescente, e depois mudando a opção ROWS para incluir 2 FOLLOWING ao invés de 2 PRECEDING:

SELECT city, date, temperature,
	ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

Esta consulta produz exatamente o mesmo resultado.

Exemplo 3

Neste exemplo, calcularemos a precipitação total para os últimos três dias (ou seja, um total de três dias corridos) separadamente para duas cidades.

SELECT city, date, precipitation,
	SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

Nesta consulta, dividimos novamente os dados por cidade. Utilizamos a função SUM() para calcular o nível total de precipitação para os últimos três dias, incluindo o dia atual. Observe também que usamos uma abreviação ao definir o quadro da janela, especificando apenas o limite inferior: 2 PRECEDING.

Aqui está a saída da consulta acima:

A partir de 3 de setembro, temos um total de três dias corridos de precipitação em Florença: 30 mm. Esta é a soma de 5 mm de precipitação a partir de 1º de setembro, 15 mm a partir de 2º, e 10 mm a partir de 3º.

Você sabe como conseguimos o total de 12 mm para Roma em 5 de setembro? Tente seguir os resultados em nossa tabela de saída para ter certeza de compreender como funciona o funcionamento de janelas com molduras de janela específicas.

Agora vamos passar a alguns novos dados e exemplos.

Exemplo 4

Para os próximos dois exemplos, estaremos utilizando os dados mostrados abaixo. Inclui informações diárias sobre o número de novos assinantes em três redes sociais: Instagram, Facebook, e LinkedIn.

Vamos começar calculando os totais em execução para o número de novos assinantes separadamente para cada rede. Basicamente, para cada dia, queremos ver quantas pessoas se inscreveram desde que começamos a coletar dados até a data da linha atual.

Aqui está uma consulta SQL que atende a esta solicitação:

SELECT social_network, date, new_subscribers,
	SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

Começamos calculando o número total de novos assinantes usando a função agregada SUM(). Em seguida, usamos a cláusula PARTITION BY para computar cálculos separados para cada rede. Também ordenamos os registros por data na ordem ascendente (por padrão). Finalmente, definimos o quadro de janela como PRECEDENTE NÃO FUNDO para incluir todos os registros até o atual, inclusive o atual.

A saída tem este aspecto:

Na tabela de resultados, você pode ver como o número de novos assinantes é adicionado ao total acumulado para cada novo registro. O total em execução é calculado separadamente para cada rede, como especificado na função de janela.

Exemplo 5

Em nosso exemplo final, quero demonstrar como podemos exibir o primeiro e o último valor de um conjunto específico de registros usando as funções de janela e a cláusula ROWS. Desta vez, vamos adicionar duas colunas à saída:

• O número de novos assinantes adicionados no primeiro dia, e
• O número de novos assinantes adicionados no último dia.

Com estas informações calculadas separadamente para cada rede social, podemos ver como o desempenho de cada dia se compara ao ponto onde começamos e onde estamos agora.

Aqui está a consulta SQL para obter a saída necessária:

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

Como você pode ver, estamos usando as funções FIRST_VALUE() e LAST_VALUE() para obter as informações no primeiro e no último dia, respectivamente. Observe também como especificamos a estrutura da janela para cada uma das funções:

• Não incluímos a cláusula ROWS com a função FIRST_VALUE() porque o comportamento padrão (ou seja, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) é bom para nossos propósitos.
• Entretanto, especificamos o quadro de janela com a função LAST_VALUE() porque a opção padrão usaria o valor atual da linha como último valor para cada registro; isto não é o que estamos procurando neste exemplo. Especificamos a moldura da janela como ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para garantir que todos os registros sejam considerados.

E aqui está o resultado definido:

Conforme solicitado, temos o número de novos assinantes no primeiro e no último dia calculados separadamente para cada rede social.

Prática usando ROWS em SQL Funções de Janela (Window Functions) em SQL

Depois de passar pelos exemplos acima, esperamos que você tenha a motivação de aprender mais profundamente as funções da janela SQL e as opções ROWS. Este conjunto de ferramentas permite especificar um quadro de janela deslizante e permite o cálculo de agregações complexas como médias móveis e totais em execução.

Se você quiser ficar realmente confortável com as funções de janela, recomendo o LearnSQL.com.br's interativo Funções de Janela (Window Functions) em SQL curso. Ele mostra como calcular totais e médias de execução, construir diferentes tipos de rankings, investigar tendências ao longo do tempo, e muito mais. Melhor ainda, você mesmo fará os exercícios, que é a melhor maneira de aprender.

Se você quer realmente dominar o uso de SQL para análise de dados, nossos SQL Avançado O percurso de aprendizagem também inclui Comandos GROUP BY em SQL e expressões comuns de tabela (CTEs). É uma ótima maneira de construir sobre seu conhecimento das funções de janela.

Quer começar com alguma leitura primeiro? Aqui estão os 8 principais artigos que cobrem as funções de janela SQL.

Obrigado pela leitura, e feliz aprendizado.