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> [ { ASC | DESC } ] )

Arguments

• <expr>
  The expression to be returned based on the specified offset.

• <offset>
  The number of rows backward from the current row from which to obtain a value. For example, an offset of 2 returns the <expr> value with an interval of 2 rows.
  Note: Setting a negative offset has the same effect as using the LEAD function.
  Default: 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: NULL

• { IGNORE | RESPECT } NULLS
  Whether to ignore or respect NULL values when an <expr> contains NULL values:

  • IGNORE NULLS excludes any row whose expression evaluates to NULL when offset rows are counted.
  • RESPECT NULLS includes any row whose expression evaluates to NULL when offset rows are counted.
    Default: RESPECT NULLS

Usage notes

• The PARTITION BY clause partitions the result set produced by the FROM clause into partitions to which the function is applied. For more information, see Window function syntax and usage.
• The ORDER BY clause orders the data within each partition.

Examples

This query shows the difference between this year’s revenue and the previous year’s revenue:

SELECT 
    emp_id,
    year,
    revenue,
    revenue - LAG(revenue, 1, 0) OVER (
        PARTITION BY emp_id 
        ORDER BY year
    ) AS diff_to_prev
FROM (
    SELECT 0 AS emp_id, 2010 AS year, 1000.00 AS revenue UNION ALL
    SELECT 0, 2011, 1500.00 UNION ALL
    SELECT 0, 2012, 500.00 UNION ALL
    SELECT 0, 2013, 750.00 UNION ALL
    SELECT 1, 2010, 10000.00 UNION ALL
    SELECT 1, 2011, 12500.00 UNION ALL
    SELECT 1, 2012, 15000.00 UNION ALL
    SELECT 1, 2013, 20000.00 UNION ALL
    SELECT 2, 2012, 500.00 UNION ALL
    SELECT 2, 2013, 800.00
) sales
ORDER BY emp_id, year;

Output:

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

This query shows how the IGNORE NULLS clause affects the output. All rows (except the first) contain non-NULL values even if the preceding row contained NULL. If the preceding row contained NULL, then the current row uses the most recent non-NULL value.

SELECT 
    col_1,
    col_2,
    LAG(col_2 IGNORE NULLS) OVER (
        ORDER BY col_1
    ) 
FROM (
    SELECT 1 AS col_1, 5 AS col_2 UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 3, NULL UNION ALL
    SELECT 4, 2 UNION ALL
    SELECT 5, NULL UNION ALL
    SELECT 6, NULL UNION ALL
    SELECT 7, 6
) t1
ORDER BY col_1;

Output:

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