Função SQL LAG()

A função LAG() - uma das funções de janela do SQL, é uma ferramenta importante para planejamento e análise de tendências. Neste artigo, demonstrarei como incluir a função SQL LAG() em suas consultas usando alguns exemplos do mundo real.

As funções de janelado SQL , também conhecidas como funções analíticas ou funções OVER, acrescentam uma nova dimensão à análise de dados. Elas permitem que você inclua agregados ou dados de outras linhas ao lado da linha atual.

A função LAG() permite que você olhe para trás "através da janela" em uma linha anterior e inclua seus dados junto com a linha atual. Uma função semelhante, LEAD(), permite que você olhe para frente, para as linhas subsequentes. Usando as funções LEAD() e LAG(), você pode incluir facilmente recursos úteis, como comparações ano a ano, em seus relatórios.

Se você leva a sério a análise de dados, talvez queira dar uma olhada no curso LearnSQL.com.br's Funções de Janela (Window Functions) em SQL. Você aprenderá resolvendo mais de 200 exercícios interativos guiados usando um banco de dados real que será acessado pelo navegador. O curso leva cerca de 20 horas para ser concluído; quando terminar, você poderá usar suas novas habilidades para turbinar seus próprios projetos de análise de dados.

O que a função LAG() faz?

Essa função permite incluir um valor de coluna de uma linha anterior junto com os dados da linha atual. Ela é particularmente útil para analisar tendências ao longo do tempo, como comparações mês a mês e ano a ano.

Também pode ser usado para responder a uma variedade de perguntas. Qual é a diferença entre os resultados médios da Escola A e da Escola B? Quanto o uso de uma matéria-prima diferente afeta a vida útil de um componente?

Sintaxe da função LAG()

Em sua forma mais simples, a sintaxe da função SQL LAG() é ...

LAG(column_1) OVER (ORDER BY column_2)

... onde:

• column_1 é o nome da coluna que você deseja incluir da linha anterior.
• OVER indica que você está usando uma função de janela; essa palavra-chave é obrigatória.
• ORDER BY também é obrigatória quando você estiver usando LAG(). A sequência de linhas deve ser previsível, caso contrário, a função não fará sentido. Entretanto, a ordem escolhida não precisa ser a mesma do relatório final.
• column_2 é a coluna que você está usando para sequenciar as linhas. Você pode especificar mais de uma coluna aqui.

O SQL primeiro sequencia seus dados usando valores na coluna_2. Em cada linha, ele volta para a linha anterior nessa sequência e recupera o valor da coluna_1. Esse valor é incluído junto com qualquer outro dado que você tenha solicitado na linha atual.

Exemplo básico da função LAG()

Vejamos uma comparação simples mês a mês. Uma tabela chamada monthly_sales contém os seguintes dados:

Se você quisesse ver as vendas do mês passado junto com as vendas deste mês, sua consulta seria semelhante a esta:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

O resultado é o seguinte:

Observe que o valor das vendas do mês anterior foi trazido para a linha do mês atual:

Exemplos mais complexos

Agora que você aprendeu a fazer uma consulta simples usando a função SQL LAG(), vamos ver algumas das outras maneiras de usá-la em sua análise.

Uso de LAG() em cálculos

Muitas vezes, você desejará usar os resultados de LAG() em cálculos. Por exemplo, talvez você queira ver a diferença e a alteração percentual entre os meses. Vamos alterar a consulta anterior para incluir esses cálculos.

Você pode usar o resultado de uma função em cálculos da mesma forma que usaria qualquer outra coluna.

A consulta tem a seguinte aparência:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  sales_value - LAG(sales_value) OVER (ORDER BY year, month) AS change,
  ((sales_value - LAG(sales_value) OVER (ORDER BY year, month)) * 100)
   / (LAG(sales_value) OVER (ORDER BY year, month)) AS percentage
FROM monthly_sales;

Os resultados são:

Usando LAG() com um deslocamento

Nos exemplos que vimos, a consulta pegou os dados da linha anterior do conjunto. Esse é o comportamento padrão. Entretanto, você pode olhar para trás em mais de uma linha especificando um deslocamento.

A sintaxe para especificar um deslocamento é a seguinte:

LAG(column_1, offset) OVER (ORDER BY column_2)

O offset é um número inteiro que indica quantas linhas a consulta deve olhar para trás para encontrar os dados. Se você não especificar um deslocamento, o banco de dados assumirá um deslocamento de 1.

Vamos supor que você queira ver as vendas deste mês, as vendas do mês passado e as vendas do mês anterior lado a lado. A consulta é:

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (ORDER BY year, month) AS last_month,
  LAG(sales_value,2) OVER (ORDER BY year, month) AS prev_month
FROM monthly_sales;

Os resultados são:

Incluindo um padrão

Nos resultados que examinamos, a função LAG() retornou NULL na primeira linha porque não havia nenhuma linha anterior. Talvez você nem sempre queira que ela faça isso.

Por exemplo, suponha que uma empresa tenha sido aberta em dezembro de 2023. Talvez você queira mostrar isso colocando zero nas vendas do último mês de dezembro para que fique óbvio que as vendas aumentaram de zero para 7380 durante esse mês.

O zero é chamado de padrão - um valor mostrado quando não há números.

Para incluir um padrão em sua consulta, a sintaxe é:

LAG(column_1, offset, default) OVER (ORDER BY column_2)

Observe que você sempre precisa especificar um deslocamento se incluir um padrão. O deslocamento seria 1 se você quiser examinar a linha imediatamente anterior à linha atual.

A consulta do exemplo acima é::

SELECT 
  year, 
  month, 
  sales_value, 
  LAG(sales_value, 1, 0) OVER (ORDER BY year, month) AS last_month
FROM monthly_sales;

Os resultados são:

Usando LAG() com PARTITION BY

Vamos supor que os dados originais tenham sido expandidos para incluir mais de um departamento:

Você provavelmente desejaria que cada departamento fosse mantido separado em sua comparação. Isso é conhecido como particionamento. A sintaxe para dividir seus resultados em partições é ...

LAG(column_1) OVER (PARTITION BY column_2 ORDER BY column_3)

... onde column_2 é a coluna que você deseja usar para o particionamento.

Vamos tentar uma consulta que dividirá o relatório por departamento, mas ainda ordenará as linhas por ano e mês dentro de cada departamento:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS last_month
FROM monthly_sales;

Os resultados são os seguintes:

Observe que as vendas do mês anterior são revertidas para NULL na primeira linha do novo departamento.

O SQL usa department como chave de classificação primária porque ela foi especificada na cláusula PARTITION BY. Em department, ele sequencia os dados por year e, em seguida, por month, de acordo com a cláusula ORDER BY.

Em cada linha, ele verifica se há uma linha anterior pertencente ao mesmo department. Em caso afirmativo, ele recupera o conteúdo da coluna sales_value da linha anterior. Isso é incluído na linha atual como last_month. Se não houver uma linha anterior, last_month será definido como um valor NULL.

Uso dos resultados de LAG() para ordenar um relatório

A ordem final do relatório não precisa ser a mesma que a sequência usada na cláusula OVER. Você pode usar a cláusula normal ORDER BY no final da consulta para especificar uma sequência diferente.

Suponha que você queira visualizar os resultados sequenciados pelo aumento no valor das vendas entre os meses.

Sua consulta poderia ter a seguinte aparência:

SELECT 
  department, 
  year, 
  month, 
  sales_value, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month) AS change
FROM monthly_sales
ORDER BY
  department, 
  sales_value - LAG(sales_value) OVER (PARTITION BY department ORDER BY year, month);

Os resultados são os seguintes:

Isso pode ser útil se você quiser ver quando a empresa estava indo mal e quando estava indo bem. Essas informações podem ajudá-lo a rastrear a origem de qualquer problema.

Onde obter mais informações sobre a função SQL LAG()

Se você quiser saber mais sobre a função LAG() - e sobre as funções de janela em geral -, aqui estão alguns artigos que oferecem informações adicionais:

• As funções LAG() e LEAD() no SQL
• Função SQL LEAD()
• Como calcular as diferenças ano a ano no SQL

Se você precisar se aprofundar nas funções de janela, estes recursos podem ajudar:

• Esta folha de dicas do siteFunções de Janela (Window Functions) em SQL é excelente para ser mantida com você enquanto estiver escrevendo consultas.
• Quando você é novato no assunto, ver esses exemplos de funções de janela é muito útil.
• Se estiver procurando emprego, aqui estão algumas perguntas da entrevistaFunções de Janela (Window Functions) em SQL . Estude-as e elas o ajudarão a vencer a entrevista sobre SQL.

Como mencionei anteriormente, se você realmente quiser se tornar um especialista, recomendo o cursoFunções de Janela (Window Functions) em SQL da LearnSQL.com.br. Você aprenderá exatamente como usar todo o poder das funções de janela do SQL. E você ganhará confiança resolvendo muitos exercícios práticos.

Não há nada como a prática para aumentar seu conhecimento e suas habilidades. Se desejar alguns exemplos guiados para trabalhar por conta própria, aqui estão alguns recursos:

• O artigo Funções de Janela (Window Functions) em SQL Practice Exercises tem 11 exercícios com soluções.
• Este Funções de Janela (Window Functions) em SQL Practice Set é uma trilha de aprendizado que contém mais de 100 exercícios práticos. Você trabalhará em três bancos de dados diferentes para aprender a resolver diferentes tipos de problemas.

A função LAG() do SQL é uma ferramenta incrível para analisar seus dados a fim de identificar rapidamente as tendências e manter sua organização competitiva. Como analista de dados, vale a pena dedicar seu tempo para se tornar um especialista na função de janela!