LAG

Syntax

LAG { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )} OVER ([ query_partition_clause ] order_by_clause)

Description

LAG() is a window function that returns the value of the same field in the current row of a query, offset by offset rows. This operation can be achieved using a self-join on the same table, but LAG() is more efficient.

Parameters

The following table describes the parameters of the LAG() function.

Parameter	Description
value_expr	The field to be compared.
offset	The offset of value_expr.
default	The default value. The default value is NULL, which indicates that the return value is NULL if default is not explicitly set.
[ { RESPECT | IGNORE } NULLS ]	Specifies whether to consider NULL values. The default value is RESPECT NULLS, which indicates to consider NULL values.
order_by_clause	Indicates that the data should be sorted by a column before the concept of the number of rows before and after can be determined.
query_partition_clause	Indicates the query partition. If this parameter is not specified, the global data is used.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.036 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

SELECT LAST_NAME, LEAD(SALARY) OVER(ORDER BY SALARY) LEAD, LAG(SALARY) OVER(ORDER BY SALARY) LAG FROM EXPLOYEES;
+-----------+-------+-------+
| LAST_NAME | LEAD  | LAG   |
+-----------+-------+-------+
| JIM       | 11000 |  NULL |
| TOM       | 12000 |  2000 |
| MIKE      | 13000 | 11000 |
| LILY      |  NULL | 12000 |
+-----------+-------+-------+
4 rows in set (0.002 sec)