Resumo : neste tutorial, você aprenderá como usar a LAG()
função SQLite para obter o valor da linha anterior da linha atual na partição.
Introdução à LAG()
função SQLite
A função SQLite LAG()
é uma função de janela que permite obter os dados da linha anterior em um determinado deslocamento físico da linha atual na partição.
O seguinte mostra a sintaxe da LAG()
função:
LAG(expression [,offset[, default ]]) OVER (
PARTITION BY expression1, expression2,...
ORDER BY expression1 [ASC | DESC], expression2,...
)
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe:
expression
É uma expressão avaliada em relação ao valor da linha anterior com base no arquivo offset
. A expressão deve retornar um único valor.
offset
É o número de linhas anteriores à linha atual da qual o valor será obtido. O valor padrão de offset
é 1 se você não especificá-lo explicitamente.
default
É o valor padrão a ser retornado se expression
at offset
for NULL
. Se você pular o default
, a LAG()
função retornará NULL
se for expression
avaliado como NULL
.
PARTITION BY
cláusula
A PARTITION BY
cláusula divide as linhas do conjunto de resultados em partições às quais a LAG()
função se aplica. Se você não especificar a PARTITION BY
cláusula explicitamente, a LAG()
função tratará todo o conjunto de resultados como uma única partição.
ORDER BY
cláusula
A ORDER BY
cláusula classifica as linhas de cada partição à qual a LAG()
função se aplica.
A LAG()
função é frequentemente usada para calcular a diferença entre os valores da linha atual e da linha anterior em um determinado deslocamento.
LAG()
Exemplos de funções SQLite
Usaremos a CustomerInvoices
visualização criada no LEAD()
tutorial da função para demonstração.
A consulta a seguir retorna dados da CustomerInvoices
visualização:
SELECT
*
FROM
CustomerInvoices
ORDER BY
CustomerId,
Year,
Total;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A imagem a seguir mostra a saída parcial:
1) Usando LAG()
a função SQLite no exemplo do conjunto de resultados
A consulta a seguir usa a LAG()
função para retornar a diferença nos valores da fatura do ID de cliente 4 nos anos subsequentes:
SELECT
CustomerId,
Year,
Total,
LAG ( Total, 1, 0 ) OVER (
ORDER BY Year
) PreviousYearTotal
FROM
CustomerInvoices
WHERE
CustomerId = 4;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Aqui está a saída:
Neste exemplo:
- Primeiro, ignoramos a
PARTITION BY
cláusula para que aLAG()
função tratasse todo o conjunto de resultados como uma única partição. - Segundo, como não há valor anterior disponível para a primeira linha, a
LAG()
função retornou o valor padrão zero.
2) Usando LAG()
a função SQL sobre partição por exemplo
A instrução a seguir usa a LAG()
função para retornar a diferença nos valores das faturas de cada cliente nos anos subsequentes:
SELECT
CustomerId,
Year,
Total,
LAG ( Total,1,0) OVER (
PARTITION BY CustomerId
ORDER BY Year ) PreviousYearTotal
FROM
CustomerInvoices;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo:
- Primeiro, a
PARTITION BY
cláusula dividiu as linhas do conjunto de resultados por ID do cliente em partições. - Em segundo lugar, a
ORDER BY
cláusula especificada naOVER
cláusula especificada ordena as linhas em cada partição por ano antes daLAG()
aplicação da função. - A
LAG()
função é aplicada a cada partição separadamente e o cálculo foi reiniciado para cada partição.
A imagem a seguir mostra a saída parcial:
Observe que a primeira linha de cada partição tem um valor zero (0) porque não tinha valor LAG.
Neste tutorial, você aprendeu como usar a LAG()
função SQLite para obter dados da linha anterior da linha atual na partição.