Aprenda tudo sobre análise de dados com funções de janela na nossa Série Análise de Dados no SQL com funções de janela.
Vamos dar início à Série Análise de Dados no SQL com funções de janela! Hoje, eu vou te apresentar o conceito de funções de janela e como usá-las para análise de dados.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Se você trabalha como analista de dados ou tem interesse em atuar nessa área, essa série será extremamente útil para você.
Na nossa primeira aula, vamos explorar o que são as funções de janela no SQL e as possíveis análises de dados que podemos construir com elas. Para compreendermos bem o assunto, esta será uma aula mais teórica, no entanto, as próximas serão voltadas para as práticas!
Não se esqueça de fazer o download do material desta aula para acompanhar e poder estudar melhor!
O SQL oferece uma variedade de recursos, funções e comandos que nos permitem realizar análises de dados, como o WHERE, GROUPBY, JOIN, entre outros, que nos permitem filtrar as informações ou estabelecer relacionamentos entre tabelas.
No entanto, quando lidamos com análises de dados mais complexas e avançadas, pode ser necessário recorrer a funções especiais para nos ajudar. É aí que entram as funções de janela no SQL.
Vamos pegar um exemplo prático para conseguir entender como usar as funções de janela para análise de dados. Na tabela abaixo, temos o registro de 10 lojas de uma determinada empresa e suas respectivas vendas.
Como poderíamos calcular o percentual vendido por cada loja?
Para fazer isso, a primeira coisa que precisamos saber é o valor total vendido por TODAS as lojas. Para calcular esse valor, podemos aplicar a função SUM.
SELECT SUM(Qtd_Vendida) AS 'Total Vendido' FROM LojasO resultado será de 22090.
Agora, com esse valor em mãos, o próximo passo seria adicionar essa informação à nossa tabela e calcular o percentual de participação nas vendas de cada loja. Esse percentual seria calculado pela divisão da Qtd_Vendida pelo Total Vendido.
Uma das abordagens que poderíamos pensar para adicionar essa coluna Total Vendido à nossa tabela é através de um SELECT em conjunto com a função SUM.
SELECT
ID_Loja,
Nome_Loja,
Regiao,
Qtd_Vendida,
SUM(Qtd_Vendida) AS 'Total Vendido'
FROM LojasNessa consulta, selecionamos as colunas existentes na tabela e adicionamos uma nova coluna chamada Total Vendido, que seria a soma das quantidades totais vendidas por todas as lojas.
Mas será que essa abordagem funcionaria?
Como percebemos na mensagem de erro, esse método não funcionará. O SQL espera que, quando incluímos uma soma (SUM) dentro do SELECT, também façamos algum agrupamento utilizando o GROUP BY.
No entanto, mesmo que a gente faça um agrupamento, o resultado não será o esperado.
SELECT
ID_Loja,
Nome_Loja,
Regiao,
Qtd_Vendida,
SUM(Qtd_Vendida) AS 'Total Vendido'
FROM Lojas
GROUP BY ID_Loja, Nome_Loja, Regiao, Qtd_Vendida
A solução para esse problema é usar as funções de janela e a instrução OVER.
Basicamente, precisaremos adicionar a instrução OVER() logo após a função de soma (SUM). Dessa forma, o Total Vendido será calculado igualmente para todas as linhas, permitindo que façamos o cálculo do percentual.
SELECT
ID_Loja,
Nome_Loja,
Regiao,
Qtd_Vendida,
SUM(Qtd_Vendida) OVER() AS 'Total Vendido'
FROM Lojas
A instrução OVER() permite definirmos qual será a “janela” (conjunto de linhas) que deve ser considerado no cálculo.
Se quisermos, ao invés de obter a soma total da quantidade vendida, informar apenas a soma total para uma região específica, podemos usar o PARTITION BY como argumento.
SELECT
ID_Loja,
Nome_Loja,
Regiao,
Qtd_Vendida,
SUM(Qtd_Vendida) OVER(PARTITION BY Regiao) AS 'Total Vendido'
FROM Lojas
ORDER BY ID_LojasAssim sendo, a janela considerará o agrupamento por região. Como as regiões podem se repetir para diferentes lojas, o cálculo da soma levará isso em consideração. Por exemplo, na linha de uma loja do Sudeste, o Total Vendido será calculado de acordo com aquela região.
Ao utilizar as funções de janela, trabalhamos com as instruções OVER e PARTITION BY. Combinadas com outras funções que veremos nas próximas aulas, podemos realizar análises mais avançadas no SQL.
Apesar de ainda não termos concluído nessa aula (concluiremos nas próximas), uma das análises muito utilizadas com funções de janela é o cálculo do percentual de participação. Essa análise nos mostra quanto um determinado valor representa em relação ao total.
Além da análise de participação, as funções de janela são muito úteis para análises de rankings, soma móvel e média móvel, análise MoM (Month over Month) ou YoY (Year over Year) e, por fim, para análises de acumulado no tempo.
Todos esses tipos de análises serão abordados e explorados ao longo da nossa série Análise de Dados no SQL com funções de janela.
Para que você já possa se preparar para o conteúdo que virá, vou compartilhar o calendário de aulas da nossa série Análise de Dados no SQL.
Lembrando que essa foi uma aula mais introdutória e teórica. Porém, a partir da próxima aula, entraremos mais na prática, exercitando todas as possíveis análises com funções de janela que podemos fazer dentro do SQL.
Vamos para a segunda aula da Série de Análise de Dados no SQL com funções de janela. Nesta aula, vamos partir para a prática e vou te ensinar como utilizar as funções de janela em análise de dados para calcular o percentual do total no SQL.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
O cálculo do percentual é extremamente importante em diversas áreas. No exemplo desta aula, vamos analisar o percentual de vendas por lojas ou regiões, a fim de identificar quais estão vendendo mais e quais apresentam um faturamento menor.
A partir dessa análise é possível buscar compreender e identificar o que causa a diferença de desempenho nas vendas de cada loja ou região. Com base nisso, podemos implementar o mesmo método das lojas que mais faturam, nas que possuem um menor número de vendas.
Para acompanhar mais essa aula da nossa Série Análise de Dados no SQL com funções de janela, não se esqueça de fazer o download do material, assim você poderá praticar comigo e realizar os exercícios propostos.
Além disso, para esta aula utilizarei o SQL Server. Caso queira seguir exatamente como demonstrarei, você pode aprender a instalá-lo a partir desta aula.
O cálculo do percentual em relação ao total é uma análise comum para entender a participação de um valor específico em relação ao valor total.
Por exemplo, imagine que você tenha o faturamento total de uma empresa para o ano de 2022, dividido por meses. Se você quiser saber qual foi a participação de cada mês em relação ao faturamento anual, será necessário calcular o percentual daquele mês em relação ao total faturado no ano.
Nosso primeiro passo será criar um banco de dados no qual teremos os valores para trabalhar. Dentro desse banco de dados, vamos criar uma tabela com os campos Nome_Loja, Região e Qtd_Vendida.
Para realizar isso, você deve seguir e executar os códigos na seguinte sequência:
CREATE DATABASE WF;USE WF;CREATE TABLE Lojas(
ID_Loja INT,
Nome_Loja VARCHAR(100),
Regiao VARCHAR(100),
Qtd_Vendida FLOAT);INSERT INTO Lojas(ID_Loja, Nome_Loja, Regiao, Qtd_Vendida)
VALUES
(1, 'Botafogo Praia&Mar', 'Sudeste', 1800),
(2, 'Lojas Vitoria', 'Sudeste', 800),
(3, 'Emporio Mineirinho', 'Sudeste', 2300),
(4, 'Central Paulista', 'Sudeste', 1800),
(5, 'Rio 90 graus', 'Sudeste', 700),
(6, 'Casa Flor & Anópolis', 'Sul', 2100),
(7, 'Pampas & Co', 'Sul', 990),
(8, 'Paraná Papéis', 'Sul', 2800),
(9, 'Amazonas Prime', 'Norte', 4200),
(10, 'Pará Bens', 'Norte', 3200),
(11, 'Tintas Rio Branco', 'Norte', 1500),
(12, 'Nordestemido Hall', 'Nordeste', 1910),
(13, 'Cachoerinha Loft', 'Nordeste', 2380);Pra cada etapa executada você deve receber uma mensagem de êxito.
Agora vamos visualizar a tabela que acabamos de criar. Basta utilizar o comando:
SELECT * FROM Lojas;Dessa forma, selecionaremos todas as colunas da nossa tabela e exibiremos os resultados.
Antes de calcular o percentual do total, precisamos primeiro calcular o total de vendas da nossa tabela. Para descobrir esse valor, podemos fazer uma nova consulta à nossa tabela, calculando a soma da coluna Qtd_Vendida.
SELECT SUM(Qtd_Vendida) FROM Lojas;
Temos então, que nosso total de vendas foi de 26480.
Agora que temos a nossa tabela e o total vendido, vamos calcular o percentual do total. Farei isso em uma nova janela de consulta, então verifique se o seu banco de dados está selecionado.
Vamos fazer isso para dois exemplos diferentes: o primeiro para calcular o percentual de participação de cada loja em relação ao total de vendas de todas as lojas; e o segundo para calcular o percentual de participação de cada loja em relação ao total de vendas por região.
Percentual de cada loja em relação ao total:
Para calcular o percentual do total, precisamos adicionar ao lado da coluna Qtd_Vendida a coluna Total Vendido. Podemos fazer isso usando a função de soma (SUM) seguida da instrução OVER(). Assim como vimos na aula 1.
SELECT
*,
SUM(Qtd_Vendida) OVER() AS 'Total Vendido'
FROM Lojas;
A partir disso, podemos adicionar ao nosso código o cálculo do percentual do total para cada loja. Ele será calculado pegando a quantidade vendida (Qtd_Vendida) de cada loja e dividindo pelo Total Vendido. Podemos multiplicar esse resultado por 100 para melhor visualização.
SELECT
*,
SUM(Qtd_Vendida) OVER() AS 'Total Vendido',
100.0 * Qtd_Vendida / SUM(Qtd_Vendida) OVER() AS '% Total'
FROM Lojas;
Com essa tabela, podemos analisar a participação de cada loja no total vendido e procurar entender, por exemplo, o que fez a Amazonas Prime ter uma participação alta de 15.86% e replicar isso em lojas com um percentual menor, como a Rio 90 Graus com apenas 2.64%.
Percentual de cada loja por região:
Agora iremos calcular o percentual de participação de cada loja em relação ao total vendido de sua respectiva região.
Para isso, precisamos criar um agrupamento em nosso cálculo por região, semelhante ao que aprendemos na Aula 1. Vamos adaptar o nosso código para esse cenário.
SELECT
*,
SUM(Qtd_Vendida) OVER(PARTITION BY Regiao) AS 'Total Vendido',
100.0 * Qtd_Vendida / SUM(Qtd_Vendida) OVER(PARTITION BY Regiao) AS '% Total'
FROM Lojas
ORDER BY ID_Loja;
Com o PARTITION BY, a janela de cálculo considerará o agrupamento por região. Dessa forma, cada linha de uma loja terá o Total Vendido calculado de acordo com a região a qual ela pertence, e o seu percentual será calculado com base nessa região também.
Na nossa terceira aula da série sobre Análise de Dados no SQL com Funções de Janela, vou ensinar a você como criar rankings usando as funções RANK e DENSE_RANK.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Nesta aula, vou mostrar como utilizar a função RANK e a função DENSE_RANK para criar rankings no SQL.
Ambas as funções funcionam de forma semelhante, mas a função RANK, em caso de empate, pula a próxima posição. Por exemplo, se houver um empate em segundo lugar, o SQL classificará a próxima posição como 4º em vez de 3º.
Já a função DENSE_RANK faz o ajuste necessário para manter a ordem, sem pular nenhuma posição quando há um empate.
Além disso, vou explicar como utilizar a função PARTITION BY no SQL para criar rankings entre as mesmas categorias, permitindo um ranqueamento completo de cada categoria dos seus dados!
Vamos começar selecionando nossa tabela de lojas a partir do banco de dados WF que criamos na aula 2.
SELECT * FROM Lojas;
A partir dessa tabela, iremos criar um ranking para determinar a posição de cada loja em relação à quantidade vendida.
Por exemplo, vamos considerar a classificação das lojas da região Sudeste com base nas vendas, das maiores para as menores. Podemos fazer isso da seguinte maneira:
SELECT
*,
RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'rank'
FROM
Lojas
WHERE Regiao = 'Sudeste';Com esse comando, estamos selecionando todas as informações da tabela Lojas, onde a coluna Regiao é igual a Sudeste, e criando uma nova coluna chamada ‘rank’ que exibe a classificação em ordem decrescente, da loja que mais vendeu para a que menos vendeu.
Explicando detalhadamente o código:
SELECT
*,Aqui, estamos selecionando todas as colunas da tabela Lojas.
RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'rank'Essa é a parte principal do nosso código. A função RANK() está calculando a classificação das lojas. A instrução OVER() define a janela pela qual o ranking será calculado. Nesse caso, estamos ordenando (ORDER BY) pela quantidade vendida (Qtd_Vendida) em ordem decrescente. O resultado dessa classificação é o que será exibido na coluna rank.
FROM Lojas
WHERE Regiao = 'Sudeste'Aqui é onde estamos especificando a tabela da qual estamos obtendo os dados e o filtro que estamos utilizando para pegar apenas as lojas da região Sudeste.
O resultado deve ser a tabela abaixo.
Repare que as lojas Central Paulista e Botafogo Praia&Mar estão empatadas em segundo lugar. Nesses casos, a função RANK() atribui a mesma posição e pula para a quarta posição, resultando na ausência de um terceiro colocado no ranking.
Para resolver essa questão e garantir que a classificação continue de forma consecutiva após as lojas empatadas, podemos utilizar a função DENSE_RANK().
SELECT
*,
RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'rank',
DENSE_RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'dense_rank'
FROM
Lojas
WHERE Regiao = 'Sudeste';
Se quisermos ranquear todas as lojas entre si, podemos remover o filtro WHERE e obter o resultado geral.
SELECT
*,
RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'rank',
DENSE_RANK() OVER(ORDER BY Qtd_Vendida DESC) AS 'dense_rank'
FROM
Lojas;
Agora, se quisermos exibir todas as lojas e, ao mesmo tempo, ranqueá-las de acordo com suas respectivas regiões, podemos usar a cláusula PARTITION BY junto com a função RANK() ou DENSE_RANK(). Isso criará partições separadas para cada região e calculará as classificações dentro de cada uma delas.
Essas funções de ranking no SQL são extremamente úteis para a análise de dados, permitindo identificar facilmente os melhores resultados, os piores resultados, os maiores valores e os menores valores.
Na quarta aula da série sobre Análise de Dados no SQL com funções de Janela, vou mostrar a você como calcular a soma móvel e a média móvel no SQL.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Nesta aula, vou ensinar como calcular a soma móvel e a média móvel no SQL. Esses são cálculos baseados em intervalos e são duas das principais análises possíveis com as funções de janela.
Para acompanhar esta aula da nossa Série Análise de Dados no SQL com funções de janela, não se esqueça de fazer o download do material. Assim, você poderá praticar comigo para fixar melhor o conteúdo.
Além disso, nesta aula, utilizarei o SQL Server. Se você deseja seguir exatamente como demonstrarei, poderá aprender a instalá-lo a partir desta aula.
Para a aula de hoje, precisaremos criar uma tabela com valores que tenham as informações de datas. Isso é necessário para realizar as análises de Soma Móvel e Média Móvel.
Vamos criar uma tabela simples adicionando-a ao nosso banco de dados WF, que já foi criado na aula 1.
CREATE TABLE Resultado(
DataFechamento DATE,
FaturamentoMM FLOAT);Nessa tabela, adicionaremos as informações referentes às datas de fechamento e ao faturamento em milhões.
INSERT INTO Resultado(DataFechamento, FaturamentoMM)
VALUES
('01/01/2020', 8),
('01/02/2020', 10),
('01/03/2020', 6),
('01/04/2020', 9),
('01/05/2020', 5),
('01/06/2020', 4),
('01/07/2020', 7),
('01/08/2020', 11),
('01/09/2020', 9),
('01/10/2020', 12),
('01/11/2020', 11),
('01/12/2020', 10);Vamos selecionar os dados para visualizar as informações:
SELECT * FROM Resultado;
A Média Móvel é uma média que vai mudando ao longo do tempo. A cada período, calculamos a média considerando valores que são modificados.
Por exemplo, podemos calcular a média do faturamento utilizando o mês atual, o mês anterior e o mês posterior. Em março, utilizaríamos os valores de fevereiro, março e abril. Já em abril, consideraríamos março, abril e maio. Ou seja, a média é recalculada a cada mês.
A Soma Móvel é semelhante, porém, em vez de calcular a média, calculamos a soma dos valores em diferentes períodos, considerando uma janela de intervalo.
Essas análises são amplamente utilizadas quando queremos identificar padrões ou comportamentos de uma determinada variável ao longo do tempo, como o preço de uma ação na bolsa de valores.
Vamos começar calculando a Média Móvel na nossa tabela. Para isso, criaremos uma coluna que apresentará a média móvel de um período de 3 meses (mês atual, anterior e o próximo).
SELECT
DataFechamento,
FaturamentoMM,
AVG(FaturamentoMM) OVER(
ORDER BY DataFechamento
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 'Fat. Acum.'
FROM Resultado
ORDER BY DataFechamento;A partir desse código, selecionamos as colunas DataFechamento e FaturamentoMM da tabela Resultado e calculamos a média acumulada dos valores da coluna FaturamentoMM para cada linha, considerando a linha atual, a anterior e a seguinte. Ordenamos os resultados pela DataFechamento em ordem crescente de data.
Detalhando o código:
SELECT
DataFechamento,
FaturamentoMM,Aqui selecionamos as colunas DataFechamento e FaturamentoMM da nossa tabela.
AVG(FaturamentoMM) OVER(
ORDER BY DataFechamento
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 'Fat. Acum.'Nessa parte do código, estamos calculando a média (AVG) da coluna FaturamentoMM. Como queremos fazer essa média de um período específico, um conjunto de linhas relacionadas, precisamos utilizar a função de janela OVER.
Dentro da função janela definimos que as linhas devem ser ordenadas pela coluna DataFechamento para organizar por ordem crescente de data antes de calcularmos a média.
Também determinamos o intervalo de linhas sobre o qual a média deve ser calculada. Nesse caso, estamos calculando a média entre a linha atual, a anterior (1 PRECEDING) e a próxima linha (1 FOLLOWING). E atribuímos à coluna Fat. Acum. a média resultante.
FROM Resultado
ORDER BY DataFechamento;Aqui estamos indicando de onde estamos obtendo os dados, da tabela Resultado e que os resultados também sejam ordenados pela coluna DataFechamento em ordem crescente.
Se executarmos nosso código, teremos como resultado a média móvel que estamos buscando.
Podemos adaptar esse código de acordo com a análise que desejamos construir. Por exemplo, é possível alterar o tamanho da janela que estamos analisando, substituindo os valores antes de PRECEDING e FOLLOWING. Também podemos usar CURRENT ROW para pegar os valores apenas até a linha atual, a linha analisada.
SELECT
DataFechamento,
FaturamentoMM,
AVG(FaturamentoMM) OVER(
ORDER BY DataFechamento
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 'Fat. Acum.'
FROM Resultado
ORDER BY DataFechamento;Nesse exemplo, estamos pegando os 2 meses anteriores (2 PRECEDING) até a linha atual (CURRENT ROW).
Para fazer a Soma Móvel,basta alterar a função que estamos trabalhando. Ao invés de usar a AVG para média, usamos a SUM para soma.
SELECT
DataFechamento,
FaturamentoMM,
SUM(FaturamentoMM) OVER(
ORDER BY DataFechamento
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 'Fat. Acum.'
FROM Resultado
ORDER BY DataFechamento;
Vamos para a nossa quinta aula da série de Análise de Dados no SQL com Funções de Janela. Hoje, vamos explorar duas análises amplamente utilizadas para comparar períodos: MoM (Month over Month) e YoY (Year over Year).
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Nesta aula, vou mostrar como realizar comparações entre períodos usando as Funções de Janela do SQL. Vamos analisar o crescimento ou decréscimo da empresa ao comparar tanto meses quanto anos.
Essas análises são cruciais para ajudar a empresa a entender o que está ocorrendo, se há realmente uma queda de faturamento em um determinado período ou se houve algum problema específico nesse intervalo.
Na aula de hoje, utilizaremos a mesma tabela que criamos na aula anterior, a aula 4. Caso você não tenha o material em mãos, pode fazer o download novamente no nosso material disponível para download.
As análises Month over Month (mês a mês) e Year over Year (ano a ano) são técnicas utilizadas para comparar períodos. São comumente aplicadas em empresas para determinar se houve crescimento ou decréscimo no faturamento, por exemplo.
A análise Month over Month (análise mês a mês) compara dados ou métricas de um mês em relação a outro. Enquanto a Year over Year (análise ano a ano), faz esse comparativo de um ano com outro.
Para realizar uma análise Month over Month no SQL, o ideal é ter uma coluna ao lado da coluna FaturamentoMM, contendo o faturamento do mês anterior. Assim, podemos fazer a comparação linha por linha.
Para criar essa coluna com os valores referentes ao mês anterior, ou seja, um deslocamento de valores onde os valores do mês anterior são colocados como valores no mês atual, precisamos utilizar funções de janela e a função LAG.
SELECT
DataFechamento,
FaturamentoMM,
LAG(FaturamentoMM, 1, 0) OVER(ORDER BY DataFechamento) AS 'Desloc'
FROM Resultado
ORDER BY DataFechamento;Primeiro, selecionamos as colunas DataFechamento e FaturamentoMM da tabela Resultado.
Em seguida, utilizamos a função LAG para recuperar o valor da coluna FaturamentoMM (primeiro argumento que passamos para ela), presente na linha anterior (1, uma linha atrás, segundo argumento) e caso essa função não encontre nenhum valor anterior, atribui o valor padrão de 0 (terceiro argumento).
Feito isso, aplicamos a cláusula OVER para especificar que estamos trabalhando com funções de janela, ordenando as linhas pela data usando o ORDER BY DataFechamento. Isso especifica que as linhas analisadas devem ser ordenadas pela coluna de datas.
Nomeamos essa coluna como “Desloc”, passamos a tabela na qual estamos realizando essas ações e determinamos novamente que seja ordenado pelas datas na coluna DataFechamento. Executando essa consulta obteremos a tabela abaixo:
Repare que o resultado da consulta trouxe uma tabela com três colunas: DataFechamento, FaturamentoMM e Desloc. A coluna Desloc contém os valores da coluna FaturamentoMM na linha anterior, ou 0 caso não haja uma linha anterior.
Para calcular o Month over Month (Mês a Mês), ou seja, a variação de um mês para o outro, precisamos aplicar um cálculo que subtraia o valor atual pelo valor anterior e divida o resultado pelo valor anterior. Ou, de forma mais simples, dividindo o valor atual pelo valor anterior menos 1 ((valor atual/valor anterior) – 1).
Na nossa análise, o valor atual é o valor na coluna FaturamentoMM, enquanto o valor anterior é o resultado da função LAG. Aplicando esse cálculo à nossa fórmula, temos:
SELECT
DataFechamento,
FaturamentoMM,
LAG(FaturamentoMM, 1, FaturamentoMM) OVER(ORDER BY DataFechamento) AS 'Desloc',
(FaturamentoMM/LAG(FaturamentoMM, 1, FaturamentoMM) OVER(ORDER BY DataFechamento)) - 1 AS '%MoM'
FROM Resultado
ORDER BY DataFechamento;Para evitar erros de divisão por zero, em vez de passar 0 como terceiro argumento da função LAG, vamos passar o próprio valor de FaturamentoMM como valor padrão.
Faremos isso porque, se não houver um período anterior a ser comparado, significa que a variação percentual daquele período foi de 0%. Uma das formas de chegarmos a esse resultado é tornando o valor de deslocamento (Desloc) igual ao valor de FaturamentoMM.
Nessa nova parte do código, calculamos a variação percentual mensal, ou seja, o Month over Month (MoM), e atribuímos o resultado a uma nova coluna chamada “%MoM”.
Para calcular a variação anual, primeiro precisamos adicionar um novo valor à tabela para termos uma janela de um ano que será comparada.
INSERT INTO Resultado(DataFechamento, FaturamentoMM)
VALUES
('01/01/2021', 20);Neste exemplo, estamos inserindo um novo mês e um novo valor referente a janeiro de 2021. Após isso, podemos replicar nossa fórmula para calcular a variação percentual mensal, substituindo o segundo argumento da função LAG por 12 em vez de 1. Ficaria assim:
SELECT
DataFechamento,
FaturamentoMM,
LAG(FaturamentoMM, 12, FaturamentoMM) OVER(ORDER BY DataFechamento) AS 'Desloc',
(FaturamentoMM/LAG(FaturamentoMM, 12, FaturamentoMM) OVER(ORDER BY DataFechamento)) - 1 AS '%YoY'
FROM Resultado
ORDER BY DataFechamento;
Observe que só conseguimos realizar a comparação Year over Year quando comparamos janeiro de 2021 com janeiro de 2020, pois é o único período de um ano que temos na nossa base de dados.
Na nossa sexta e última aula da série Análise de Dados no SQL com Funções de Janela, vou te mostrar como fazer o cálculo de acumulado no SQL.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Nesta aula, você irá aprender como fazer o cálculo de acumulado no SQL, que é um cálculo muito importante e útil para diversas análises de dados.
Para isso, iremos utilizar conceitos e abordagens semelhantes aos que vimos na aula 4 de Média Móvel. A diferença neste caso é que não teremos um limite anterior para pegar os dados; vamos sempre querer todos os dados até a linha atual para fazer a nossa soma.
Dessa maneira, em cada linha da tabela, teremos o cálculo acumulado de todos os meses anteriores até aquele mês em específico.
Para acompanhar nossa última aula da Série Análise de Dados no SQL com funções de janela, não se esqueça de fazer o download do material, assim você poderá praticar comigo e realizar o exercício proposto.
Vamos utilizar a mesma tabela que utilizamos ao longo das aulas 4 e 5, que é nossa tabela de Resultado.
Nela, temos as datas de fechamento e o faturamento em milhões.
O que nós vamos fazer nessa aula é adicionar uma coluna que trará a soma dos valores até a linha atual. Ou seja, na linha 2, nossa soma acumulada será de 18, na linha 3 será de 24 e assim sucessivamente.
Esse é um cálculo bem parecido com o que fizemos para a soma móvel, com a diferença que a soma móvel deixa algumas informações para trás, ela pega um intervalo e vai deslocando pela tabela.
Para fazer a soma acumulada, utilizaremos o seguinte código:
SELECT
DataFechamento,
FaturamentoMM,
SUM(FaturamentoMM) OVER (ORDER BY DataFechamento ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Soma Acumulada'
FROM Resultado
ORDER BY DataFechamento;Repare que é um código bem semelhante ao de soma móvel, mas com algumas pequenas modificações.
Essa consulta SQL seleciona as colunas DataFechamento e FaturamentoMM da tabela Resultado e calcula a soma acumulada dos valores na coluna FaturamentoMM para cada linha.
A janela para a soma acumulada começa na primeira linha da tabela e se estende até a linha atual. Ou seja, soma todos os valores da coluna FaturamentoMM, da primeira linha da tabela, até a linha atual analisada.
O resultado é exibido na coluna Soma Acumulada e ordenado com base na coluna DataFechamento.
Detalhando o código:
SELECT
DataFechamento,
FaturamentoMM,Aqui selecionamos as colunas DataFechamento e FaturamentoMM da nossa tabela.
SUM(FaturamentoMM) OVER (ORDER BY DataFechamento ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Soma Acumulada'Nessa parte do código estamos calculando a soma dos valores na coluna FaturamentoMM (SUM(FaturamentoMM)), no entanto, essa soma é feita para uma janela específica que definimos através da cláusula OVER.
Dentro da cláusula OVER, estamos definindo que a soma acumulada deve ser calculada ordenando os dados pela coluna DataFechamento em uma janela que começa na primeira linha da tabela (ROWS BETWEEN UNBOUNDED PRECEDING) e se estende até a linha atual (AND CURRENT ROW).
FROM Resultado
ORDER BY DataFechamento;Aqui, estamos indicando de onde estamos obtendo os dados, da tabela Resultado, e que os resultados também sejam ordenados pela coluna DataFechamento em ordem crescente.
Assim, concluímos o cálculo da nossa soma acumulada com funções de janela no SQL e também encerramos a nossa Série Análise de Dados no SQL com mais esse cálculo bastante importante para suas análises.
O objetivo dessa série Analise de Dados no SQL com funções de janela é te ensinar, de forma prática, fácil e direta, o que são as funções de janela no SQL e como usá-las para realizar análises de dados.
Se você já trabalha como analista de dados ou tem interesse em atuar nessa área, os conhecimentos adquiridos ao longo dessa série serão muito úteis para você construir análises mais completas.
Não deixe de baixar os arquivos disponíveis em cada aula, estudar e praticar. Dessa forma, você será capaz de dominar as funções de janela no SQL e realizar análises mais complexas e avançadas com facilidade.
Para acessar outras publicações de SQL, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador