Como Calcular a Diferença Entre Duas Linhas em SQL

Calcular a diferença entre duas linhas em SQL pode ser uma tarefa desafiadora. É algo possível de ser feito, e existe mais de uma maneira de fazê-lo. Neste artigo, vou explicar como usar as funções de janela SQL LEAD() e LAG() para encontrar a diferença entre duas linhas na mesma tabela.

Gosto de pensar nisso como um artigo de "happy hour" - você pode aprender sobre dois tópicos (calcular a diferença entre duas linhas e as funções da janela SQL) em apenas um artigo. Vamos direto ao assunto!

Se você quiser aprender sobre funções de janela, confira nosso curso interativo Funções de Janela (Window Functions) em SQL. Se você já o conhece e deseja praticar a escrita de consultas utilizando funções de janela, recomendo nosso conjunto de práticas Funções de Janela (Window Functions) em SQL, com 100 exercícios práticos.

Calculando a Diferença Entre Dois Valores na Mesma Linha

Para calcular qualquer diferença, você precisa de dois elementos. Para calcular uma diferença em SQL, você precisa de dois registros. É possível calcular a diferença entre duas colunas no mesmo registro, como mostrarei daqui a pouco. É muito fácil. Entretanto, focarei principalmente em encontrar a diferença entre dois valores da mesma coluna em registros diferentes.

Primeiro, vamos falar sobre nossos dados. Usaremos um banco de dados com duas tabelas, usado pelo governo de um estado fictício para definir alguns programas sociais. A primeira delas é a tabela moradia, com dados sobre o número de pessoas que alugam, possuem ou precisam de uma casa. Confira:

Tabela moradia

Para projetar um plano de construção de casas para pessoas que precisam de moradia, o governo deseja obter algumas métricas sobre os problemas de moradia em diferentes cidades. Suponha que o governo deseja saber quantas pessoas em cada cidade não possuem uma casa. Nesse caso, a consulta será:

SELECT 	
    Cidade,
    populacao_total, 
    populacao_total - populacao_com_casa_propria AS pessoas_sem_casa_propria
FROM moradia

Nesta consulta, é claro que você está calculando uma diferença usando duas colunas diferentes no mesmo registro. A seguir, você calculará as diferenças usando dois registros.

Calculando a Diferença Entre Dois Valores na Mesma Coluna

Normalmente, todas as operações que fazemos em uma consulta SQL estão relacionadas com o registro atual. Neste caso, porém, precisamos de um segundo registro. É aqui que a coisa pode ficar complicada. Vamos usar uma técnica que emprega as funções de janela (window functions) LAG() e LEAD() para obter dados de outro registro. Se você quiser ir mais fundo, sugiro ler este artigo sobre funções de janela que dá explicações claras sobre essas funções, incluindo muitos exemplos.

Vamos supor que, para uma cidade específica ('Bronzelândia'), você queira saber a variação de pessoas que precisam de uma casa em comparação com o ano anterior. Vamos ver a consulta:

SELECT 	
  Cidade,
  Ano,
  populacao_precisando_de_moradia,
  LAG(populacao_precisando_de_moradia) AS ano_anterior,
  populacao_precisando_de_moradia - LAG(populacao_precisando_de_moradia)
    OVER (ORDER BY Ano ) AS diferenca_ano_anterior
FROM moradia
WHERE Cidade = ‘Bronzelândia’
ORDER BY Ano

No texto em azul, você pode ver o cálculo do delta SQL entre duas linhas. Para calcular uma diferença, você precisa de um par de registros; esses dois registros são "o registro atual" e "o registro do ano anterior". Você obtém este registro usando a função de janela LAG(). Esta função permite obter dados do registro anterior (com base em um critério de ordenação, que neste caso é "ORDER BY Ano”).

O resultado desta consulta é mostrado a seguir. As setas vermelhas mostram que o valor da função LAG() retorna o mesmo valor de população_precisando_de_moradia que o registro do ano anterior. A coluna à direita mostra o resultado da diferença entre o ano atual e o ano anterior.

Para a próxima consulta, vamos ampliar a análise das questões de moradia para todas as cidades. Para cada cidade, queremos saber a diferença entre o ano atual e os anos anteriores para a coluna populacao_precisando_de_moradia.

Primeiro, você removerá a condição Cidade = ‘Bronzelândia’. Você quer calcular valores para cada cidade, portanto, precisa de uma maneira de separar os dados em grupos. É por isso que você adiciona a cláusula da cidade PARTITION BY à função LAG(). PARTITION BY Cidade permite processar todos os registros para a mesma cidade na mesma janela.

Você usará a função LAG() novamente para calcular a diferença entre o número de pessoas que precisam de uma casa entre este ano e o ano anterior. Vamos ver a consulta:

SELECT 	
  Cidade,
  Ano,
  populacao_precisando_de_moradia,
  LAG(populacao_precisando_de_moradia)
    OVER (PARTITION BY Cidade ORDER BY Ano ) AS ano_anterior,
  populacao_precisando_de_moradia - LAG(populacao_precisando_de_moradia)
   OVER (PARTITION BY Cidade ORDER BY Ano ) AS diferenca_ano_anterior
FROM moradia
ORDER BY Cidade, Ano

No texto em azul, você pode ver como a diferença é calculada. Aqui está uma versão em português simples do que acontece nos bastidores da consulta:

diferenca_ano_anterior = 
  populacao_precisando_de_moradia no registro atual
  - populacao_precisando_de_moradia no registro do ano anterior

Onde:

Populacao_precisando_de_moradia no registro atual é a coluna populacao_precisando_de_moradia

e

Populacao_precisando_de_moradia no registro do ano anterior é obtida pela função LAG(populacao_precisando_de_moradia) OVER (PARTITION BY Cidade ORDER BY Ano )

Primeiro, você usou a cláusula PARTITION para criar um conjunto com todos os registros para uma mesma cidade. Em seguida, a cláusula ORDER BY é usada para organizar todos esses registros por ano. Finalmente, a função LAG() é usada para obter o valor populacao_precisando_de_moradia do registro anterior.

Outra função da janela SQL, LEAD(), é similar a LAG(). Entretanto, ela retorna o próximo registro no conjunto (no nosso exemplo, este seria o registro do ano seguinte).

Se você revisar as duas consultas anteriores, verá que usamos o mesmo padrão para calcular a diferença: subtraindo o valor da coluna anterior do valor atual usando LAG() (ou do próximo registro usando LEAD()).

As funções da janela SQL são muito poderosas. Para exemplos de diferentes áreas, como finanças, vendas e comércio, confira este artigo sobre quando usar as funções de janela em SQL.

Calculando a Diferença Entre Valores de Datas em SQL

Nos exemplos anteriores, você calculou o delta entre duas linhas usando valores de colunas numéricas. Agora vou demonstrar como calcular a diferença entre dois valores para dados do tipo data.

Primeiro, vamos considerar o tipo de dado do resultado. Quando você calcula a diferença entre dois valores de data, o resultado não é uma data, e sim um intervalo que representa o número de dias entre as duas datas.

Imagine que existe uma tabela chamada estatisticas_hospital que armazena estatísticas para os três hospitais do estado. Abaixo está uma amostra dos dados da tabela:

Tabela estatisticas_hospital

Vamos supor que existe uma doença rara chamada MDLR. O governo quer investigar com que freqüência um paciente com MDLR é admitido em qualquer um dos hospitais. Eles pediram um relatório com as colunas dia, nome_hospital, numero_de_pacientes e dias_desde_ultimo_caso. Usaremos o SQL para construir o relatório:

SELECT
  dia,
  nome_hospital,
  numero_de_pacientes,
  dia - LAG(dia) OVER (ORDER BY dia) 
     AS dias_desde_ultimo_caso
FROM estatisticas_hospital
WHERE nome_doenca = 'MDLR'
ORDER BY dia

Você pode ver o mesmo padrão que usamos antes. Agora, ele está sendo usado para calcular os dias desde o último caso. A única diferença é que você está calculando uma diferença entre duas datas ao invés de valores numéricos.

Como você vê nos resultados, a coluna dias_desde_ultimo_caso é um valor inteiro, representando um número de dias.

Com o SQL, também é possível calcular diferenças entre valores data. Existem ainda outras operações aritméticas interessantes com datas que você pode usar em tipos de dados relacionados a datas. Se você quiser se aprofundar no assunto, recomendo a leitura do artigo Como Analisar os Dados da Série Temporal de COVID-19 com Funções de Janela (Window Functions) em SQL.

Como Calcular Diferenças Entre Registros Não Consecutivos

Até então, você calculou as diferenças entre registros consecutivos com base em critérios específicos de ordenação. Em alguns casos, porém, você precisará calcular a diferença entre registros não consecutivos.

Como exemplo, vamos voltar à primeira consulta. Suponha que queiramos adicionar outra coluna mostrando o número de casos nos últimos dois anos. Felizmente, LAG() e LEAD() têm um parâmetro opcional que especifica quantos registros devem ser pulados antes/depois do registro atual. Por padrão, este parâmetro é 1 (ou seja, "use o próximo registro/registro anterior"), mas você pode definir outro número para o parâmetro. Portanto, com este novo parâmetro, a consulta será:

SELECT 	
  Cidade,
  Ano,
  populacao_precisando_de_moradia,
  LAG(populacao_precisando_de_moradia)
    OVER (PARTITION BY Cidade ORDER BY Ano ) AS um_ano_antes,
  LAG(populacao_precisando_de_moradia,2)
    OVER (PARTITION BY Cidade ORDER BY Ano ) AS dois_anos_antes,
  populacao_precisando_de_moradia - LAG(populacao_precisando_de_moradia)
    OVER (PARTITION BY Cidade ORDER BY Ano ) AS diferenca_ano_anterior,
  populacao_precisando_de_moradia - LAG(populacao_precisando_de_moradia,2)
    OVER (PARTITION BY Cidade ORDER BY Ano ) AS diferenca_dois_ultimos_anos
FROM moradia
ORDER BY 1, 2 DESC

Em azul, você pode ver o cálculo da diferença entre dois registros não consecutivos. Na imagem seguinte, podemos ver a nova coluna diferenca dois ultimos anos apenas para os valores de 2019; isto porque 2019 é o único ano que tem dados de dois anos anteriores. As linhas vermelhas conectam os resultados da função LAG() (obtendo dados para 2017 e mostrando os mesmos dados no registro de 2017). Você pode ver que ambas as linhas têm o mesmo valor.

Mostramos como as funções LAG() e LEAD() são importantes, mas há muito mais funções de janela em SQL. Leia este artigo sobre funções de janela para saber mais sobre elas.

Mais sobre Cálculos e Funções de Janela (Window Functions) em SQL

Neste artigo, você aprendeu como calcular a diferença entre linhas consecutivas aplicando SQL. Você também aprendeu como usar LAG() e LEAD() para encontrar a diferença entre linhas não consecutivas. Embora existam outras maneiras de calcular esta diferença, como o uso de um Self Join (auto-junção), os métodos que explicamos aqui são muito úteis.

Se você estiver usando as funções de janela (ou mesmo SQL) com frequência - ou se você quiser apenas melhorar seus conhecimentos - este resumo completo sobre Funções de Janela (Window Functions) em SQL é um excelente recurso. Para aprender a usar funções de janela de forma prática, experimente nosso curso Funções de Janela (Window Functions) em SQL. Você pode ler sobre o curso neste artigo.