Guia de funções de janela do SQL

Todo analista de dados pode se beneficiar do conhecimento das funções de janela do SQL. Elas aprimoram seu jogo de análise de dados e abrem as portas para a análise de dados em um nível totalmente novo. Este artigo é seu ponto de partida para o mundo das funções de janela do SQL.

As funções de janela do SQL permitem que você mostre todas as linhas de dados e seus valores agregados ao mesmo tempo. Parece um pouco com GROUP BY com esteroides, não é? E não para por aí. Como as funções de janela executam vários cálculos no conjunto de linhas, elas também permitem que os conjuntos de dados sejam divididos em subconjuntos. Elas o ajudam a classificar facilmente os dados, agregá-los, calcular diferenças entre períodos diferentes (por exemplo, aumento/diminuição de lucro entre dois meses/trimestres/anos) e encontrar somas cumulativas, totais em execução, médias móveis etc.

As funções de janela são um tópico complexo, mas aprendê-las é muito mais fácil com nosso Funções de Janela (Window Functions) em SQL curso. Trata-se de um curso interativo com 218 exercícios que proporcionam um aprendizado sistemático com bastante codificação. Você aprenderá tudo sobre as cláusulas essenciais da função de janela - por exemplo, OVER(), ORDER BY e PARTITION BY - e o que é um quadro de janela. Em seguida, você aprenderá a usar tudo isso no contexto de diferentes funções de janela.

Depois de aprender, é hora de praticar (o que também é aprender). Nosso Funções de Janela (Window Functions) em SQL Practice Set oferece 100 exercícios interativos adicionais para que você possa realmente se sentir à vontade para colocar seu conhecimento em prática.

A sintaxe do SQL Funções de Janela (Window Functions) em SQL

As funções de janela recebem seu nome de um quadro de janela, que é um conjunto de linhas relacionadas à linha atual.

Para executar uma operação de função de janela no quadro de janela, você precisa conhecer a sintaxe geral da função de janela:

SELECT column_1,
       column_2,
	<window_function> OVER(PARTITION BY … ORDER BY … <window_frame>) AS column_alias
FROM table;

Há várias partes essenciais dessa sintaxe que precisam ser explicadas:

• Cláusula OVER: Essa é a cláusula obrigatória necessária para definir um quadro de janela. Por exemplo, você reconheceria a função de janela SUM(order_value) OVER() as a SUM(). Sem OVER(), essa é apenas uma função agregada SUM() comum.
• PARTITION BY: Essa é uma cláusula opcional para particionar o conjunto de dados, ou seja, dividi-lo em subconjuntos. Isso permite que você aplique uma função de janela a cada partição separadamente. Se essa cláusula for omitida, todo o conjunto de resultados será uma partição.
• ORDER BY: Essa cláusula opcional (para algumas funções de janela) é usada para especificar a ordem das linhas em um quadro de janela. Se você omitir essa cláusula, a ordem das linhas na moldura da janela será arbitrária.
• <window_frame>: Define os limites superior e inferior de um quadro de janela. Duas cláusulas importantes usadas para isso são ROWS e RANGE. ROWS define o número de linhas que precedem e seguem a linha atual. A cláusula RANGE define o intervalo de linhas com base em seu valor em comparação com a linha atual. Você pode saber mais em nosso artigo sobre as diferenças entre ROWS e RANGE. Essa parte da sintaxe geralmente é omitida, pois o quadro de janela padrão é o que os usuários mais precisam.

O quadro de janela padrão, nesse caso, depende do uso ou não da cláusula ORDER BY em OVER(). Se você o fizer, o quadro será toda a linha atual e todas as linhas anteriores a ela na partição atual. Se você não especificar ORDER BY, o quadro da janela será a linha atual e todas as linhas anteriores e posteriores a ela na partição atual. No segundo caso, o quadro da janela é basicamente todo o conjunto de dados - ou toda a partição, se você também estiver usando PARTITION BY.

SQL comum Funções de Janela (Window Functions) em SQL

Há muitas funções de janela diferentes. Aqui está uma visão geral de algumas das mais comuns:

SQL Funções de Janela (Window Functions) em SQL Exemplos

Agora que já vimos os conceitos básicos, é hora de mostrar vários exemplos práticos de funções de janela.

Em todos os exemplos, usarei a mesma tabela. Ela é intitulada exchange_rates e contém as taxas de câmbio do Banco Central Europeu (BCE) de abril de 2024 para três pares de moedas: EUR vs. USD, EUR vs. CHF e EUR vs. JPY.

Aqui está um instantâneo parcial dos dados.

Exemplo de função de janela de classificação

Este exemplo mostrará como funciona o DENSE_RANK(). As outras duas funções de janela de classificação podem ser usadas da mesma maneira; elas podem (dependendo dos dados) retornar resultados ligeiramente diferentes.

O código abaixo classifica os dados na tabela da taxa de câmbio mais alta para a mais baixa:

SELECT date,
	 currency_pair,
	 ecb_rate, 
	 DENSE_RANK() OVER (ORDER BY ecb_rate DESC) AS rank_ecb_rate
FROM exchange_rates;

Seleciono a data, o par de moedas e a taxa. Agora, escolhi DENSE_RANK() para classificar os dados. Isso é apenas para o caso de haver as mesmas taxas de câmbio (altamente improvável, mas ainda assim...); quero que elas sejam classificadas da mesma forma e não quero lacunas na classificação.

A função DENSE_RANK() é seguida pela cláusula OVER() que define a função de janela. Nos parênteses da cláusula, uso outra cláusula de função de janela -ORDER BY. Dessa forma, estou dizendo à função de janela DENSE_RANK() para classificar os dados pela taxa do BCE em ordem decrescente.

É isso que obtenho como resultado:

Como você pode ver, cada linha foi classificada de acordo com seu valor de taxa. No caso de taxas iguais, DENSE_RANK() atribuiria a mesma classificação e não pularia a classificação. RANK() faria o mesmo, só que pularia a sequência de classificação. ROW_NUMBER() atribuiria uma classificação consecutiva, mesmo que algumas linhas tivessem a mesma taxa de câmbio.

Exemplo de função de janela agregada

Aqui está um bom exemplo de como você pode usar a função de janela AVG() para calcular a taxa média para cada par de moedas:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 AVG(ecb_rate) OVER (PARTITION BY currency_pair) AS avg_rate_by_currency
FROM exchange_rates
ORDER BY currency_pair, date;

Como na função agregada regular AVG(), você deve escrever a coluna da qual deseja obter a média. Para transformá-la em uma função de janela, use a cláusula OVER(). Desta vez, utilizo PARTITION BY na coluna currency_pair. Ao fazer isso, estou dividindo os dados em subconjuntos de acordo com o par de moedas.

Em outras palavras, estou calculando a taxa média para cada par de moedas separadamente.

Dê uma olhada na saída do código. Ele foi classificado por pares de moedas e data:

A taxa média de EUR vs. CHF é 0,9793, e esse valor é repetido para cada linha de EUR vs. CHF. Quando a função de janela atinge o próximo par de moedas, a média é redefinida e calculada novamente; para EUR vs. JPY, é 164,1211. Finalmente, a média para EUR vs. USD é 1,0795.

Essa função de janela permitiu que eu calculasse as médias separadamente e mostrasse os valores médios sem recolher as linhas individuais. Em outras palavras, posso ver cada taxa diária junto com a média desse par de moedas.

Exemplos da função Analytic Window

Nesta seção, mostrarei três exemplos de diferentes funções de janela do SQL analítico.

LAG()

O exemplo LAG() demonstrará como calcular a alteração diária. Essa função é usada para acessar o valor das linhas anteriores. Outra função de janela analítica é LEAD(), que faz exatamente o oposto: busca os dados das linhas seguintes. Ambas as funções têm basicamente a mesma sintaxe; basta alterar o nome da função.

Neste exemplo, quero calcular a variação diária das taxas de câmbio:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - LAG(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS daily_rate_change
FROM exchange_rates;

LAG() é uma função para acessar o(s) valor(es) na(s) linha(s) anterior(es) à linha atual. No exemplo acima, especifico a coluna ecb_rate em LAG(). Isso significa que quero acessar o valor da taxa do BCE. Não especifico explicitamente o número de linhas para as quais quero voltar, portanto, será o valor padrão de uma linha.

OVER() vem após o nome da função. Aqui, particiono o conjunto de dados por par de moedas, pois quero calcular a alteração diária da taxa para cada par de moedas separadamente.

Também uso ORDER BY para classificar os dados dentro das partições. Como a lógica é retroceder um dia, os dados devem ser ordenados de forma ascendente por data.

Portanto, a parte LAG() do código representa a taxa de câmbio do dia anterior. Para obter a diferença diária, basta subtrair esse valor da taxa de câmbio atual (ecb_rate - LAG(ecb_rate)).

Aqui está o resultado:

A primeira linha é NULL porque não há data anterior, portanto, a diferença não pode ser calculada. Na próxima linha, a alteração da taxa diária é 0,9792-0,9765 = 0,0027. O mesmo princípio de pegar o valor da linha anterior e subtraí-lo do atual continua em todas as linhas.

Como o conjunto de dados é particionado por par de moedas, o cálculo é redefinido quando atinge outros pares de moedas, ou seja, EUR vs. JPY e EUR vs. USD.

FIRST_VALUE()

A função de janela FIRST_VALUE() pode ser usada em nossos dados para calcular as diferenças entre a taxa de câmbio atual e a primeira do mês.

Posso fazer isso porque FIRST_VALUE() retorna o primeiro valor dentro da partição. Seu oposto é LAST_VALUE(), que retorna o último valor na partição. Ambas as funções têm basicamente a mesma sintaxe; somente o nome da função é diferente (e o resultado, é claro!).

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - FIRST_VALUE(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS difference_current_first
	  FROM exchange_rates
ORDER BY currency_pair, date;

Eu quero o valor da taxa; é por isso que existe a coluna ecb_rate em FIRST_VALUE(). Os dados são novamente particionados por par de moedas, pois quero um cálculo para cada par.

OK, então FIRST_VALUE() é usado para buscar o valor da primeira linha na partição. Mas o que acontece se eu ordenar os dados na partição de forma ascendente por data? É isso mesmo; a primeira linha é a que tem a taxa do primeiro dia do mês. Em nosso caso, é a primeira taxa de câmbio de abril.

Agora, subtraia isso da taxa de câmbio atual. Como sabemos que nossos dados são apenas para abril, obtemos a diferença entre a taxa atual e a primeira taxa para aquele mês.

Quando a diferença é 0, a taxa atual e a mais antiga são iguais. Para EUR vs. CHF, a primeira taxa é 0,9765. Vamos verificar as duas primeiras linhas: 0.9765 - 0.9765 = 0.0000; 0.9792 - 0.9765 = 0.0027.

O mesmo princípio é aplicado aos outros dois pares de moedas.

NTILE()

O último exemplo que mostrarei é a função NTILE(), que divide a janela (ou partição) em grupos. O argumento entre parênteses da função NTILE() especifica o número de grupos em que você deseja que o conjunto de dados seja dividido.

A divisão será feita de forma cronológica, ordenando os dados de forma ascendente por data:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 NTILE(3) OVER (ORDER BY date) AS group_number
FROM exchange_rates;

E aqui está o resultado:

Os dados são divididos em três grupos. Como há 27 linhas de dados no total, eles poderiam ser divididos em grupos iguais de nove linhas.

Funções de Janela (Window Functions) em SQL vs. GROUP BY e funções agregadas

As funções de janela de agregação, como você pode presumir pelo nome, são usadas para agregar dados. Mas e as funções de agregação "regulares" e GROUP BY? Elas também são usadas para agregar dados. Então, qual é a diferença entre as funções de janela?

A principal diferença é que as funções de janela de agregação (e as funções de janela em geral) não comprimem as linhas individuais enquanto mostram o valor agregado. Por outro lado, as funções GROUP BY e aggregate só podem mostrar os valores agregados; elas comprimem as linhas individuais.

Em palavras simples, as funções de janela permitem que você mostre os dados analíticos e agregados ao mesmo tempo.

SQL Avançado Uso da função de janela

As funções de janela têm uso extensivo na análise de dados, pois podem resolver muitos requisitos de relatórios comerciais.

Aqui está uma visão geral de alguns dos usos mais complexos das funções de janela.

Classificação

Sim, já falei sobre classificação e mostrei um exemplo de como fazê-la. Há três funções de janela para classificar dados: ROW_NUMBER(), RANK(), e DENSE_RANK(). Elas não retornam necessariamente o mesmo resultado, pois todas têm maneiras ligeiramente diferentes de classificar os dados. Qual delas você usará depende de seus dados e do que deseja alcançar.

Execução de totais e médias móveis

Esses dois cálculos são normalmente usados na análise de séries temporais. Séries temporais são dados que mostram valores em determinados pontos do tempo. Analisar esses dados é, bem, uma análise de série temporal. Seu objetivo é revelar tendências nos dados e encontrar possíveis causas de desvios significativos da tendência.

O total em execução (ou soma cumulativa) é a soma dos valores da linha atual e de todas as linhas anteriores. À medida que você avança para o futuro, o tamanho do período de tempo aumenta em uma linha/ponto de dados e o valor é adicionado ao total acumulado da linha anterior.

As médias móveis são o valor médio dos últimos n períodos. À medida que você se move para o futuro, o período de tempo se move, mas seu tamanho permanece o mesmo. Isso é muito usado no setor financeiro, por exemplo, uma média móvel de 5 dias na análise de preços de ações. Dessa forma, o preço médio é continuamente atualizado e o impacto de mudanças significativas de curto prazo no preço das ações é neutralizado.

Diferença entre duas linhas ou períodos de tempo

A função de janela usada para calcular uma diferença entre duas linhas é LAG(), que permite acessar os valores das linhas anteriores. A diferença entre dois períodos de tempo é basicamente a mesma coisa; refere-se apenas a encontrar diferenças ao trabalhar com séries temporais. Na seção de exemplos, mostrei como fazer isso.

Análise de séries temporais

As funções de janela funcionam muito bem quando você precisa analisar séries temporais. Não há apenas a função LAG() para fazer isso, mas muitas outras.

Problemas comuns com SQL Funções de Janela (Window Functions) em SQL

Há vários problemas com os quais todo mundo que usa funções de janela se depara, mais cedo ou mais tarde:

1. Confundir funções de janela com funções agregadas e GROUP BY, que já discutimos.
2. Tentar usar funções de janela em WHERE. Isso não pode ser feito porque o SQL processa as condições WHERE antes das funções de janela.
3. Tentar usar funções de janela no GROUP BY, o que também não é permitido devido à ordem das operações do SQL: as funções de janela são executadas depois do GROUP BY.

Recursos adicionais para a prática de SQL Funções de Janela (Window Functions) em SQL

As funções de janela do SQL estão entre as ferramentas SQL mais úteis que os analistas de dados têm. Isso é especialmente verdadeiro quando você vai além dos relatórios básicos e exige cálculos sofisticados e a capacidade de mostrar dados analíticos e agregados simultaneamente.

Todos os tópicos abordados neste artigo exigem mais prática em exemplos práticos, que você pode encontrar nos artigos e cursos a seguir:

1. 11 exercícios de SQL Funções de Janela (Window Functions) em SQL com soluções
2. As 10 principais perguntas da entrevista sobre o SQL Funções de Janela (Window Functions) em SQL
3. Folha de consulta do SQL Funções de Janela (Window Functions) em SQL
4. Funções de Janela (Window Functions) em SQL Curso
5. Funções de Janela (Window Functions) em SQL Conjunto de práticas

Lembre-se: a prática leva à perfeição! Portanto, não se limite a ler os artigos; certifique-se também de praticar a codificação. Bom aprendizado!