Syntax
LAG { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )} OVER ([ query_partition_clause ] order_by_clause)
Purpose
In a query, you can use the LAG() window function to retrieve data from the offsetth row of the same field in the current row. This operation can be implemented by using a self-join on the same table, but the LAG() window function 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 return value. The default value is NULL, which indicates that the return value is NULL if no default value is explicitly specified. |
[ { RESPECT | IGNORE } NULLS ] |
Specifies whether to consider NULL values. The default value is RESPECT NULLS, which indicates that NULL values are considered. |
order_by_clause |
Indicates the column on which the data should be sorted to determine the number of rows before and after the current row. |
query_partition_clause |
Indicates the query partition. If this parameter is not specified, the global data is used. |
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> 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