A Cláusula RANGE em Funções de Janela SQL: 5 Exemplos práticos

O que é uma RANGE cláusula nas funções da janela SQL? Cinco exemplos práticos lhe mostrarão como e quando utilizá-la.

A cláusula RANGE é utilizada muito raramente em funções de janela SQL. Não sei por que; talvez as pessoas não estejam acostumadas a ela. Isto é uma pena, porque está longe de ser uma cláusula sem sentido; pode ser muito útil, e eu vou mostrar isso em cinco exemplos.

Sintaxe da Cláusula RANGE

Quando estou falando da cláusula RANGE, estou falando da utilizada nas funções da janela SQL, que tem a seguinte sintaxe:

OVER (   
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
) 

Quando você olha para a sintaxe acima, você vê que tanto ROW quanto RANGE podem fazer parte da função de janela. Sua sintaxe é a seguinte:

A moldura padrão da janela sem o ORDER BY é a divisória completa. Mas quando você usa a ORDEM POR, a moldura de janela padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

É normal esquecer esta sintaxe, especialmente se ela é nova para você ou se você não a usa com muita freqüência. Sinta-se à vontade para ter sua Funções de Janela (Window Functions) em SQL Folha de Consulta aberta enquanto lê este artigo.

Pronto para mergulhar? Ótimo! Primeiro, vamos falar sobre RANGE e ROW.

As Cláusulas RANGE e ROW são as mesmas?

Não, não são. No entanto, elas têm o mesmo propósito: especificar os pontos de partida e de chegada dentro da divisória, com o objetivo de limitar as filas. Entretanto, cada cláusula o faz de forma diferente. A cláusula ROW o faz ao especificar um número fixo de linhas que precedem ou seguem a linha atual.

A cláusula RANGE, por outro lado, limita as linhas logicamente; especifica o intervalo de valores em relação ao valor da linha atual.

Que tipos de dados posso utilizar na cláusula RANGE?

Você pode usar dois tipos de dados com a cláusula RANGE: numéricos e tipos de data/hora.

Você pode usar os tipos de dados numéricos e a cláusula RANGE em quase todos os bancos de dados populares. Infelizmente, o Microsoft SQL Server não o suporta.

Com relação aos tipos de dados de data/hora, apenas alguns poucos bancos de dados populares suportam o uso deles com a cláusula RANGE. Esses são PostgreSQL, MySQL e Oracle DB.

Vamos agora aos exemplos e ver como RANGE funciona na prática! Se você é novo nas funções de janela SQL, talvez você deva verificar primeiro o que são funções de janela e como elas se comparam às funções agregadas. Volte quando terminar de ler e nós continuaremos.

Exemplo 1 - Calcular o Total em Execução

Os dados com os quais estarei trabalhando estão na tabela revenue. As colunas são:

• id - A identificação da data e a chave primária da tabela (PK).
• date - A data da receita.
• revenue_amount - O valor da receita.

Sua tarefa é calcular os totais da receita corrente usando a cláusula RANGE. Vamos fazer isso primeiro sem SQL. Se você tiver os seguintes dados, qual será o total em execução (também conhecido como soma cumulativa)?

O total em execução para 2021-05-01 é igual à receita para aquela data: 12.573,25. Isso porque não há linhas anteriores a serem incluídas no cálculo. Então vem 2021-05-02. O total corrente é a receita de hoje adicionada à receita do dia anterior: 11,348.22 + 12,573.25 = 23,921.47.

Note que há outra linha com um montante de receita diferente para 2021-05-02. Talvez isto seja para outra filial, país, produto ou o que quer que seja. Funciona da mesma forma: 14.895,13 + 23.921,47 = 38.816,60. (A cláusula RANGE funcionará mesmo que haja várias filas com a mesma data). A seguir vem 2021-05-03. O total corrente para esta data será 14.388,14 + 38.816,60 = 53.204,74. Finalmente, o total corrido para 2021-05-04 será 18.847,54 + 53.204 = 72.052,28.

Como você faz o mesmo usando a cláusula RANGE? Poderia ser feito desta forma:

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

Eu usei a função SUM() na coluna revenue_amount; esta é a operação necessária para obter o total de funcionamento. Para que a função SUM() se torne uma função de janela, você precisa da cláusula OVER(). O cálculo da função janela é feito em ordem ascendente; isso porque quero ter certeza de que a receita está sendo somada da data mais antiga para a mais nova. Em seguida, vem a cláusula RANGE. Ela limita a janela para as datas anteriores à data atual (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) e a data atual. Essas são as linhas que serão incluídas no cálculo do total corrente.

Aqui está o que você obtém quando executa o código:

Observe que quando houver múltiplos valores para uma data (2021-05-02), o código incluirá ambas as linhas no cálculo do total em execução para aquela data. É por isso que há 38.816,60 na coluna running_total para aquela data.

Exemplo 2 - Calcular a Média Móvel

Vamos agora ver como você usa a cláusula RANGE com tipos de dados de data/hora - e sem a coluna id para ajudar! Vamos usar uma tabela ligeiramente modificada, que agora é chamada revenue_per_shop. Ele contém os dados de receita de duas lojas. As colunas são:

• date - A data da receita.
• shop - O nome da loja.
• revenue_amount - O valor da receita para aquela loja naquela data.

Antes de mais nada, vamos fazer com que você entenda o que é uma média móvel. Uma média móvel de dois dias inclui o dia atual e o dia anterior. Aqui estão alguns exemplos de dados para mostrar como funciona uma média móvel:

A média móvel de dois dias para 2021-05-01 é a própria receita diária: 12,573.25. Isso porque não há outras linhas a serem incluídas no cálculo. O cálculo para 2021-05-02 inclui duas datas: (12.573,25 + 11.348,22)/2 = 11.960,74. As outras linhas seguem a mesma lógica de duas datas - a data atual e a data anterior.

Então, como se calcula a mesma métrica para cada loja separadamente? Desta forma:

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

O código primeiro seleciona algumas colunas da tabela. Depois vem a parte divertida. Estou usando a função AVG() na coluna revenue_amount porque eu quero a receita média. Novamente, esta é uma função de janela, portanto deve ter a cláusula OVER(). Uso a PARTITION BY para especificar a coluna na qual quero agregar dados; é a loja de colunas porque quero a média móvel de cada loja separadamente. A operação é novamente ordenada por data. Na cláusula RANGE, eu simplesmente especifico quais linhas devem ser incluídas no cálculo. Já que estou trabalhando com datas, obterei a data anterior declarando: BETWEEN INTERVAL '1' DAY PRECEDING.

Trabalhar com dados de data/hora difere entre bancos de dados. Talvez seja necessário escrevê-lo desta forma em algumas bases de dados: RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW. O resultado deve, no entanto, ser o mesmo:

Média móvel para bancos de dados que não suportam o uso de RANGE com tipos de dados de data/hora

O que você deve fazer se seu banco de dados não suportar o uso de RANGE com tipos de dados de data/hora? Há uma maneira de "enganar" seu banco de dados; na verdade, provavelmente há várias maneiras. Aqui está uma maneira de calcular a diferença entre cada data e 2021-05-01 (ou seja, a primeira data nos dados). Você obtém a diferença como um número inteiro, que você pode usar em vez da data. Este é o código:

SELECT
  shop,
  date,
  revenue_amount,
  date - '2021_05_01' AS day_difference,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY (date - '2021_05_01')
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Eu incluí intencionalmente a coluna day_difference no resultado para que você entenda a lógica. Eu usei a mesma diferença na cláusula ORDER BY para que eu possa usar um número inteiro com a cláusula RANGE. E a média móvel é a mesma que no exemplo acima; veja por si mesmo. (Estou mostrando apenas as cinco primeiras linhas para economizar espaço).

Exemplo 3 - Encontrar o último valor dentro de uma faixa

Este uso da cláusula RANGE permite encontrar o último valor dentro de um intervalo definido. Por exemplo, utilizando a tabela revenue_by_shopPosso obter o último valor para cada loja separadamente. O último valor, neste caso, significa os últimos dados disponíveis, que é a receita para 2021-05-15. Como você obtém esses dados?

Usando a cláusula RANGE, é claro:

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

A função de janela que eu usei desta vez é LAST_VALUE(). Mais uma vez, estou usando-a na coluna revenue_amount. Particionei os dados por loja, da mesma forma que antes. E os pedi por data, novamente o mesmo de antes. Para obter o último valor, usei RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Lembre-se, a faixa padrão com a cláusula ORDER BY é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se você não alterá-la, obterá o resultado errado. O resultado certo é:

Exemplo 4 - Encontrar o número de itens dentro de uma faixa

Aqui está outra maneira divertida e útil de usar a cláusula RANGE. Como você encontraria o número de vezes que a receita diária de qualquer loja estava entre 1.000 (dólares, euros...) abaixo e acima de seu valor atual?

Este código pode ajudar:

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

Estou usando a função da janela COUNT(). Como não estou interessado em separar a receita por lojas, não há PARTITION BY. A contagem será feita em ordem ascendente, de acordo com o valor da receita. O intervalo é definido pelo RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING.

Aqui está o que o código retornará:

Deixe-me explicar o que este resultado lhe diz. Se você pegar a primeira linha, o resultado na coluna number_of_days é 2. Há dois casos em que a receita está entre 3.987,56 e 5.987,56. Por que esta faixa? A receita para 2021-05-13 é de 4.987,56. Portanto, 4.987,56 - 1.000 = 3.987,56 e 4.987,56 + 1.000 = 5.987,56. Você quer verificar o resultado? Quais são as duas instâncias que se situam entre esta faixa? Obviamente, as duas primeiras:

Você deseja verificar a segunda linha? Diz que há três instâncias entre 4.894,12 e 6.894,12 - estas três:

A mesma lógica se aplica ao resto dos dados.

Exemplo 5 - Encontrar o valor máximo

Isto não é tão simples quanto possa parecer; não estou falando do valor máximo comum. Para descobrir isso, você não precisaria da cláusula RANGE. Mas que tal encontrar o valor máximo (ou receita, neste caso) ao longo de cinco dias? Esses cinco dias incluirão a data atual, até três dias antes disso, e um dia depois da data atual. Você provavelmente já conhece a lógica depois de todos estes exemplos de uso do RANGE. Aqui está a minha solução:

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

Estou usando a função MAX() como uma função de janela. Mais uma vez, estou usando-a com a coluna revenue_amount. Não há PARTITION BY na cláusula OVER() porque não estou interessado em separar dados em nenhum nível. A definição do intervalo não é tão difícil: RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING. Isto incluirá a data atual, três dias antes dela e um dia depois dela. O resultado é o seguinte:

Vamos verificar o resultado para 2021-05-05 - marcado em rosa. A faixa está marcada em amarelo. Para obter a receita máxima nessa faixa, o SQL comparará os valores: 14,388.14, 18,847.54, 9,845.29, 14,574.56, 11,500.63, 16,897.21, 9,634.56, 14,255.87, 11,248.33, 21,489.22. Qual delas é a mais alta? É o 21.489,22.

Após aprender como encontrar o valor máximo usando a cláusula RANGE, alcancei o número máximo de exemplos destinados a este artigo. Se você quiser mais exemplos de funções de janela, você pode sempre ler este artigo.

RANGE Really Does Have Quite Range of Uses, Doesn't it?

Acho que estes cinco exemplos lhe mostram uma boa gama de possibilidades da cláusula RANGE. Não é tudo o que você pode fazer com ela. Seu uso depende dos dados que você tem e provavelmente um pouco de imaginação. Esta cláusula não é muito falada, o que é uma vergonha. Aconselho a todos a aprenderem-na. Ela poderia economizar seu tempo na criação de soluções de trabalho. A cláusula RANGE é elegante e realmente não muito complicada.

Onde você pode aprender sobre a cláusula RANGE? Em nosso curso Funções de Janela (Window Functions) em SQL , é claro. Você não somente aprenderá a cláusula RANGE, mas todos os aspectos das funções da janela. Ou você pode usar o curso para praticar suas habilidades em SQL. Se você quiser saber mais, este artigo lhe dirá tudo sobre o curso e o que ele oferece.