Categories:
Analytic / Window Functions

LAG

Accesses data in a previous row in the same result set without having to join the table to itself.

See also:
LEAD

Syntax

LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> ] )

Arguments

expr
The string expression to be returned.
offset

The number of rows backward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.

Note that setting a negative offset has the same effect as using the LEAD function.

Default is 1.

default

The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr.

Default is NULL.

Usage Notes

  • When the IGNORE NULLS clause is set, any row whose expression evaluates to NULL is not included when offset rows are counted. If { IGNORE | RESPECT } NULLS is not specified, the default is RESPECT NULLS.
  • The PARTITION BY clause partitions the result set produced by the FROM clause into partitions to which the function is applied. For more information about this and the ORDER BY clause, see Analytic / Window Functions.
  • The ORDER BY clause orders the data within each partition.

Examples

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

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 |
+--------+------+----------+--------------+
CREATE OR REPLACE TABLE t1 (c1 NUMBER, c2 NUMBER);
INSERT INTO t1 VALUES (1,5),(2,4),(3,NULL),(4,2),(5,NULL),(6,NULL),(7,6);

SELECT c1, c2, LAG(c2) IGNORE NULLS OVER (ORDER BY c1) FROM t1;

+----+------+-----------------------------------------+
| C1 | C2   | LAG(C2) IGNORE NULLS OVER (ORDER BY C1) |
|----+------+-----------------------------------------|
|  1 |  5   |                                    NULL |
|  2 |  4   |                                       5 |
|  3 | NULL |                                       4 |
|  4 |  2   |                                       4 |
|  5 | NULL |                                       2 |
|  6 | NULL |                                       2 |
|  7 |  6   |                                       2 |
+----+------+-----------------------------------------+