LAG¶

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 valor expr 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.

Notas de uso¶

• Quando a cláusula IGNORE NULLS é definida, qualquer linha cuja expressão seja avaliada para NULL não é inclusa quando as linhas de deslocamento são contadas. Se { IGNORE | RESPECT } NULLS não for especificado, o padrão é RESPECT NULLS.

• 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 sobre isto e a cláusula ORDER BY, consulte Funções 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));

Copy

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);

Copy

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 |
+--------+------+----------+--------------+

Copy

Criar outra tabela e carregar os dados:

CREATE OR REPLACE TABLE t1 (col_1 NUMBER, col_2 NUMBER);

Copy

INSERT INTO t1 VALUES 
    (1, 5),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, NULL),
    (7, 6);

Copy

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 |
+-------+-------+-----------------------------------------------+

Copy