- Categorias:
Sintaxe e uso da função de janela (classificação)
LAG¶
Acessa dados em uma linha anterior no mesmo conjunto de resultados sem precisar unir a tabela a si mesma.
- Consulte também:
Sintaxe¶
LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] )
Argumentos¶
expr
A expressão a ser retornada com base no offset especificado.
offset
O número de linhas para trás a partir da linha atual da qual se obtém um valor. Por exemplo, um
offset
de 2 retorna o valorexpr
com um intervalo de 2 linhas.Observe que a definição de um offset negativo tem o mesmo efeito que a utilização da função LEAD.
O padrão é 1.
default
A expressão a retornar quando o offset sai dos limites da janela. Suporta qualquer expressão cujo tipo seja compatível com
expr
.O padrão é NULL.
{ IGNORE | RESPECT } NULLS
Se você deve ignorar ou respeitar os valores de NULL quando um
expr
contiver valores de NULL:IGNORE NULLS
exclui qualquer linha cuja expressão seja avaliada como NULL quando as linhas de deslocamento são contadas.RESPECT NULLS
inclui qualquer linha cuja expressão seja avaliada como NULL quando as linhas de deslocamento são contadas.
Padrão:
RESPECT NULLS
Notas de uso¶
A cláusula PARTITION BY divide o conjunto de resultados produzido pela cláusula FROM em partições às quais a função é aplicada. Para obter mais informações, consulte Sintaxe e uso da função de janela.
A cláusula ORDER BY ordena os dados dentro de cada partição.
Exemplos¶
Criar a tabela e carregar os dados:
CREATE OR REPLACE TABLE sales(
emp_id INTEGER,
year INTEGER,
revenue DECIMAL(10,2));
INSERT INTO sales VALUES
(0, 2010, 1000),
(0, 2011, 1500),
(0, 2012, 500),
(0, 2013, 750);
INSERT INTO sales VALUES
(1, 2010, 10000),
(1, 2011, 12500),
(1, 2012, 15000),
(1, 2013, 20000);
INSERT INTO sales VALUES
(2, 2012, 500),
(2, 2013, 800);
Esta consulta mostra a diferença entre a receita deste ano e a receita do ano anterior:
SELECT emp_id, year, revenue,
revenue - LAG(revenue, 1, 0) OVER (PARTITION BY emp_id ORDER BY year) AS diff_to_prev
FROM sales
ORDER BY emp_id, year;
+--------+------+----------+--------------+
| EMP_ID | YEAR | REVENUE | DIFF_TO_PREV |
|--------+------+----------+--------------|
| 0 | 2010 | 1000.00 | 1000.00 |
| 0 | 2011 | 1500.00 | 500.00 |
| 0 | 2012 | 500.00 | -1000.00 |
| 0 | 2013 | 750.00 | 250.00 |
| 1 | 2010 | 10000.00 | 10000.00 |
| 1 | 2011 | 12500.00 | 2500.00 |
| 1 | 2012 | 15000.00 | 2500.00 |
| 1 | 2013 | 20000.00 | 5000.00 |
| 2 | 2012 | 500.00 | 500.00 |
| 2 | 2013 | 800.00 | 300.00 |
+--------+------+----------+--------------+
Criar outra tabela e carregar os dados:
CREATE OR REPLACE TABLE t1 (
col_1 NUMBER,
col_2 NUMBER);
INSERT INTO t1 VALUES
(1, 5),
(2, 4),
(3, NULL),
(4, 2),
(5, NULL),
(6, NULL),
(7, 6);
Esta consulta mostra como a cláusula IGNORE NULLS afeta a saída. Todas as linhas (exceto a primeira) contêm valores diferentes de NULL mesmo se a linha anterior contivesse NULL. Se a linha anterior continha NULL, então a linha atual usará o valor mais recente diferente de NULL.
SELECT col_1,
col_2,
LAG(col_2) IGNORE NULLS OVER (ORDER BY col_1)
FROM t1
ORDER BY col_1;
+-------+-------+-----------------------------------------------+
| COL_1 | COL_2 | LAG(COL_2) IGNORE NULLS OVER (ORDER BY COL_1) |
|-------+-------+-----------------------------------------------|
| 1 | 5 | NULL |
| 2 | 4 | 5 |
| 3 | NULL | 4 |
| 4 | 2 | 4 |
| 5 | NULL | 2 |
| 6 | NULL | 2 |
| 7 | 6 | 2 |
+-------+-------+-----------------------------------------------+