Quando uso as funções de janela SQL?

Você já ouviu um de seus colegas se gabando de usar as funções de janela SQL? Você conhece SQL básico mas não sabe muito, ou nada, sobre funções de janela? Se você quer aprender sobre funções de janela e como utilizá-las em um contexto empresarial, você está no lugar certo!

Isto acontece com freqüência. Você alcança um certo nível de conhecimento e se sente como o rei (ou rainha) do mundo. Então, você ouve seus colegas falando sobre algo que você nunca ouviu falar (neste caso, funções de janela). Você imediatamente sente um pouco de vergonha de não conhecer já as funções de janela. Isto lhe soa familiar?

Esta sensação não é agradável. Mas, pode ser uma coisa boa se isso o leva a perguntar o que são funções de janela. Então, você pode procurar no Google e encontrar alguns artigos úteis (como este aqui). De repente, o embaraço se transformará em empoderamento quando você perceber que as funções de janela não são misteriosas ou desaprendidas. Mais uma vez, você se sente como o rei do mundo. Ou rainha. Talvez até as duas coisas.

Vamos em direção a este sentimento real! Tentarei não bombardear você com SQL. Em vez disso, vou dar a volta por cima. Darei vários exemplos comerciais do mundo real que lhe mostrarão o uso prático das funções de janela.

O que são Funções de Janela (Window Functions) em SQL?

Uma função de janela é, simplesmente, uma função que realiza cálculos através de um conjunto de linhas de tabela. O nome vem do fato de que o conjunto de linhas é chamado de janela ou moldura de janela.

Aqui está um exemplo de como é uma janela. Na tabela abaixo, as janelas são marcadas por cores diferentes. A soma cumulativa é calculada para cada região, portanto, neste caso, as janelas são definidas por região.

A sintaxe da função de janela é:

window_function ([ALL] expression)
OVER ([PARTITION BY partition_list] [ORDER BY order_list] [window_frame_clause])

A função de janela parte da sintaxe pode ser uma função agregada, como SUM() ou AVG(). Também pode ser outro tipo de função, como uma função analítica ou de ranking.

A janela, ou o número de linhas sobre as quais determinados cálculos serão efetuados, é definida pela cláusula OVER().

PARTITION BY é uma cláusula opcional nas funções de janela. Ela define uma partição sobre a qual a função de janela realizará a operação. Se PARTITION BY não estiver definida, a função de janela executará a operação em toda a tabela. Você pode saber mais neste artigo.

ORDER BY ordenará as linhas dentro de cada partição na ordem desejada. Se esta cláusula não estiver definida, então a cláusula utilizará a tabela inteira.

A cláusula de quadro de janela define o número de linhas sobre as quais a função de janela irá operar usando duas palavras-chave. Uma é ROWS, usada para limitar o número de linhas, especificando o número de linhas que precedem ou seguem a linha atual. A outra é RANGE, usada para limitar o número de linhas, especificando um intervalo de valores com relação ao valor atual da linha. É por isso que a cláusula da janela também é chamada de cláusula ROW ou RANGE.

Para aprender como usar esta sintaxe e praticá-la, o curso LearnSQL.com.br sobre funções de janela é o lugar para se ir. Além disso, se você estiver interessado em mais explicações sobre funções de janela, você pode encontrá-las aqui, juntamente com alguns exemplos.

Passemos agora à parte divertida, resolvendo problemas!

Exemplo 1: Calcular o salário médio e comparar Individual Salário com a média

Este é um exemplo simples de um problema comum no mundo dos negócios. Digamos que há uma tabela employeeque contém dados sobre os salários dos funcionários. Ele consiste nas seguintes linhas:

• id - identificação única
• first_name - nome do funcionário
• last_name - sobrenome do funcionário
• department - departamento do funcionário
• salary - salário mensal do funcionário

Usando estes dados, primeiro é preciso calcular o salário médio para toda a empresa. Em seguida, é preciso calcular quanto está acima ou abaixo do salário médio de cada funcionário. O seguinte código fará isso rapidamente:

SELECT	first_name,
		last_name,
		department,
		salary,
		AVG(salary) OVER() AS avg_salary,
		salary - AVG(salary) OVER() AS diff_salary
FROM employee;

Como você está familiarizado com SQL, você provavelmente reconhece pelo menos parte deste código. Ele seleciona first_name, last_name, department, e salary a partir da tabela employee. Nada de novo aqui.

A linha AVG(salary) OVER() AS avg_salary calcula o salário médio na janela definida por OVER(). O resultado será mostrado na coluna avg_salary.

A linha salary - AVG(salary) OVER() AS diff_salary calcula a diferença entre o salário de cada funcionário e o salário médio, O resultado será mostrado na coluna diff_salary.

A execução deste código dará uma tabela útil. Veja um trecho dela abaixo:

Exemplo 2: Calcular o número médio de produtos vendidos por data e região

Imagine que você está trabalhando em uma empresa que opera em três regiões. A gerência quer saber o número médio de produtos vendidos em cada região. Além disso, eles querem saber o número médio de produtos vendidos em geral para cada data. As funções de janela permitirão que você faça isso facilmente.

Neste exemplo, os dados são armazenados na tabela sales que tem três colunas:

• date - data da venda do produto
• region - nome da região
• products_sold - número de produtos vendidos

Aqui está o código necessário para criar esse relatório:

SELECT	date,
		region,
		products_sold,
		AVG(products_sold) OVER(PARTITION BY date) AS avg_date,
		AVG(products_sold) OVER(PARTITION BY region) AS avg_region
FROM sales
ORDER BY region, date;

Este código seleciona as colunas date, region, e products_sold. Em seguida, ele calcula o número médio de produtos vendidos em cada data. Isto é definido pela cláusula PARTITION BY(). O resultado será mostrado na coluna avg_date.

A linha seguinte também calcula o número médio de produtos vendidos, desta vez em cada região. O resultado será mostrado na coluna avg_region. Em seguida, o resultado é ordenado pela região e a data usando a cláusula ORDER BY.

Veja os resultados abaixo:

Exemplo 3: Cálculo da Soma Cumulativa de Produtos Vendidos por Região

A gerência ficou feliz com seu relatório anterior! Agora, eles querem que você calcule a soma acumulada (ou o total em funcionamento) dos produtos vendidos em cada região. As funções de janela são úteis para fazer tais cálculos.

O cálculo será realizado sobre a mesa sales usado no Exemplo 2. Este código permitirá que você entregue rapidamente os números necessários:

SELECT	date,
		region,
		products_sold,
		SUM(products_sold) OVER(PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_region
FROM sales
ORDER BY region, date;

A primeira parte do código seleciona as mesmas colunas que o código do Exemplo 2. Em seguida, calcula a soma dos produtos por região. Isto é definido pela cláusula PARTITION BY(). Naturalmente, você precisa da soma acumulada, não da soma total. É por isso que a janela é ordenada pela data usando o comando ORDER BY.

Agora que tudo está definido, você precisa dizer a SQL para adicionar o valor da linha atual à soma das linhas anteriores dentro da janela. Isto é feito por ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

A função de janela somará todas as linhas antes da linha atual (é por isso que UNBOUNDED PRECEDING) e nenhuma linha depois da linha atual (é por isso que CURRENT ROW) dentro da janela especificada. O resultado será mostrado na coluna cumulative_region.

O resultado será ordenado pela região e pela data, como no exemplo anterior.

Após executar o código, você obterá a tabela abaixo:

Exemplo 4: Compare os preços das ações com os preços mínimo e máximo no mercado

Sua empresa está monitorando os preços das ações na NASDAQ e NYSE. A administração quer que você calcule os preços mínimos e máximos das ações em ambos os mercados nos últimos 365 dias. Eles também querem ver como o preço de cada ação negociada em 2020 difere do preço mínimo e máximo no mercado.

A tabela stockprice contém dados de 15/03/2019 a 14/03/2020 e consiste nas seguintes colunas:

• date - data da negociação
• stock_name - nome do estoque
• stock_price - preço das ações
• stock_market - mercado em que as ações estão sendo negociadas
• max_price - preço máximo no mercado nos últimos 365 dias
• min_price - preço mínimo no mercado nos últimos 365 dias
• diff_max - preço das ações menos o preço máximo no mercado nos últimos 365 dias
• diff_min - preço das ações menos o preço mínimo no mercado nos últimos 365 dias

O código que criará rapidamente o relatório solicitado se assemelha a este:

SELECT	date,
		stock_name,
		stock_price,
		stock_market,
		MAX(stock_price) OVER(PARTITION BY stock_market) as max_price,
		MIN(stock_price) OVER(PARTITION BY stock_market) as min_price,
		stock_price - MAX(stock_price) OVER(PARTITION BY stock_market) AS diff_max,
		stock_price - MIN(stock_price) OVER(PARTITION BY stock_market) AS diff_min
FROM stockprice
WHERE date > '2019-12-31'
ORDER BY date;

A primeira parte do código seleciona as colunas originais na tabela: date, stock_name, stock_price, e stock_market. Em seguida, a função de janela MAX() com a cláusula PARTITION BY calcula o preço máximo para cada mercado, NASDAQ e NYSE, separadamente. O resultado será mostrado na coluna max_price.

A próxima linha de código funciona da mesma forma, exceto que agora ele calcula o preço mínimo. O resultado será mostrado na coluna min_price.

As seguintes linhas de código calculam a diferença entre o preço da ação e o preço máximo e mínimo, respectivamente, para cada mercado. Os resultados serão mostrados nas colunas diff_max e diff_min.

Como o relatório só precisa mostrar dados a partir de 2020, usei a cláusula WHERE. Finalmente, a tabela resultante é ordenada pela data, o que é lógico para tal relatório.

Aqui está como são as primeiras várias linhas do relatório:

Exemplo 5: Calcular a porcentagem de mudança de preço e as médias móveis

A gerência agora sabe que você pode entregar relatórios com grande precisão e rapidez. Eles estão impressionados! Eles não percebem que você tem aprendido as funções da janela SQL e que o que eles pediram é fácil para você.

Em seguida, eles pedem algo que deve levar muito mais tempo. Mas não vai demorar! Você tem uma tabela de preços que contém todos os price mudanças de um estoque em 2020. Às vezes há apenas uma mudança diária, às vezes há mais. A tabela é composta pelas seguintes colunas:

• date - data do preço
• stock_price - preço das ações

A gerência lhe pediu que lhes enviasse um relatório que tomaria cada preço e o compararia com o preço anterior. Além disso, eles pediram que você calcule a média móvel do preço da ação. É assim que você vai fazer:

SELECT	date,
		stock_price,
		(stock_price/LAG(stock_price) OVER(ORDER BY date)) - 1 AS percent_change,
AVG(stock_price) OVER(ORDER BY date ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) AS moving_avg
FROM price;

Então, o que este código faz? Primeiro, ele seleciona as colunas originais na tabela: date e stock_price.

A próxima linha introduz uma nova função de janela, LAG(). Esta função acessa os dados da linha anterior, o que é ideal para esta tarefa. O stock_price é dividido pelo preço anterior (daí, a função LAG() ). Em seguida, 1 é subtraído do resultado para obter uma porcentagem. O resultado será mostrado na coluna percent_change.

A linha seguinte calcula a média móvel. Ela usa a função da janela AVG(), que você já conhece. Na cláusula OVER(), os dados são ordenados pela data.

A gerência não especificou como eles querem que a média móvel seja calculada. Portanto, decidi calculá-la usando cinco mudanças de preço, o que está especificado em ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING. Ao calcular a média móvel, o código levará em conta a linha atual e as quatro linhas que se seguem, que são cinco no total.

Você pode jogar com essa parte do código, uma vez que não há um número definido de dados que devem ser levados em conta no cálculo das médias móveis. Depende da quantidade e do tipo de dados, assim como da preferência individual. Ao alterar os números que vêm antes de PRECEDING e FOLLOWING, você pode mudar fácil e rapidamente o cálculo, dependendo da metodologia que você decidir utilizar.

Veja os resultados abaixo:

Você acha que o aprendizado Funções de Janela (Window Functions) em SQL pode ajudá-lo em seu trabalho?

Eu dei exemplos comerciais do mundo real como ponto de partida em vez de análise de código de funções de janela pura. Todos os cinco exemplos são algo com que já lidei em minha carreira.

Foi assim que eu aprendi SQL. Primeiro, eu tenho um problema para resolver. Depois, tentei descobrir como fazer isso usando SQL. Se você acha que as funções de janela serão úteis para seu trabalho, este curso LearnSQL.com.br é uma ótima maneira de aprender mais.

Se você achou estes exemplos interessantes ou quiser compartilhar alguns de seus exemplos do mundo real, sinta-se à vontade para comentar abaixo!