LAG and LEAD functions

The LAG and LEAD functions are OLAP ranking functions that return the value of their expression argument for the row at a specified offset from the current row within the current window partition.

Syntax

Notes:

¹ See OVER clause for OLAP window expressions


Element	Description	Restrictions	Syntax
`default`	Value to return if `offset` goes beyond the scope of the current window partition	If `default` is not specified, the value of NULL is returned for any out-of-scope row.	Column Expressions
`expression`	Column name, alias, or constant expression to return for the row at `offset` rows from the current row	If `expression` references a column, the column must also be in the select list of the Projection clause.	Column Expressions
`offset`	Non-negative integer constant defining an offset from the position of the current row	Requires a window PARTITION clause. If zero, specifies the current row. If `offset` is not specified, the value `1` is used.	Literal integer

Usage

The expression argument is required. The data type of the return value from the LAG or LEAD function is the data type of the expression.

Based on the sort order that the window ORDER clause imposes for each window partition, the LEAD and LAG functions return the value of the expression for every row at offset rows from the current row:

For the LAG function, the offset indicates the row that precedes the current row by offset rows.
For the LEAD function, the offset indicates the row that follows the current row by offset rows.

If the OVER clause includes no window PARTITION clause, these functions return the expression values for the entire result set of the query.

If a window PARTITION clause is specified, the second argument to the LAG function (offset) means offset rows before the current row and within the current partition. For the LEAD function, the second argument means offset rows after the current row and within the current partition.

For both functions, if offset is not specified, the value 1 is used. If you specify the optional third argument (default), which can be an expression, its value is returned if the offset goes beyond the scope of the current partition. Otherwise, the NULL value is returned. When the third argument is specified, the second argument must also be specified.

Handling NULL values

In a LAG or LEAD function expression, the optional RESPECT NULLS or IGNORE NULLS keywords can be specified in either of two locations:

In the argument list
Immediately following the closing parenthesis that delimits the argument list

The database server issues an exception, however, if you include RESPECT NULLS or IGNORE NULLS in both of these locations within the same LAG or LEAD expression.

The RESPECT NULLS and IGNORE NULLS keywords have these effects:

If you specify the RESPECT NULLS keywords, rows whose expression evaluates to NULL are included when offset rows are counted.
If you specify the IGNORE NULLS keywords, any row whose expression evaluates to NULL is not included when offset rows are counted.

If you specify IGNORE NULLS and all of the expression values for the rows in the window partition are NULL, the LAG or LEAD function returns the default value for each row. The function returns the NULL value if no default argument is specified.

Example: LEAD and LAG functions

In the following query, the LAG function and the LEAD function each defines an OLAP window that partitions employees by department and lists their salary. The LAG function shows how much more compensation each employee receives, compared to the employee in the same department with the next lower salary value. The LEAD function shows how much less each employee receives, compared to the employee in the same department with the next higher salary value.

SELECT name, salary,  LAG(salary)    
                 OVER (PARTITION BY dept ORDER BY salary),
                      LEAD(salary, 1, 0) 
                 OVER (PARTITION BY dept ORDER BY salary)
FROM employee;

         name      salary     (lag)       (lead) 
         John      35,000                 38,400
         Jack      38,400    35,000       41,200
        Julie      41,200    38,400       45,600
        Manny      45,600    41,200       47,300
        Nancy      47,300    45,600       49,500
          Pat      49,500    47,300       51,300
          Ray      51,300    49,500            0

The first row with name John has a NULL value for the LAG function because no default value is specified. The last row with name Ray has a 0 value for the LEAD function because 0 is specified as the default value of the third argument in the LEAD function.