LAG is an analytic function. It provides access to a multi-row table at the same time without a self join. Given a series of rows that are returned from a query and a cursor position, LAG can access a row at a given physical offset prior to the position. You can specify the offset parameter as an integer that is greater than zero. If you do not specify an offset, its default value is 1. If the offset exceeds the scope of the window, an optional value is returned. If you do not specify the default value, the default value is NULL.
Syntax
LAG { (value_expr [,offset [,default]]) [RESPECT|IGNORE] NULLS
|(value_expr [RESPECT | IGNORE] NULLS [,offset [,default] ]) }
OVER([query_partition_clause] order_by_clause)
Parameters
| Parameter | Description |
|---|---|
| value_expr | The field to be compared. You cannot use the LAG function or other analytic functions to nest value_expr. |
| offset | The offset of value_expr. This parameter is optional. |
| default | If you do not specify the default value, the default value is NULL. If the default value is not explicitly specified in LAG, the return value is NULL. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. |
| OVER | Uses the OVER clause to define a window for calculation. |
Notice
The LAG function must be followed by order_by_clause. query_partition_clause is optional.
Return type
The data type of the return value is not limited.
Examples
To create the emp_msg table and insert data into the table, execute the following statements:
CREATE TABLE emp_msg(deptno INT, ename varchar(30),sal INT);
INSERT INTO emp_msg VALUES(20,'ADAMS',1400);
INSERT INTO emp_msg VALUES(30,'ALLEN',1900);
INSERT INTO emp_msg VALUES(30,'BLAKE',3135);
INSERT INTO emp_msg VALUES(10,'CLARK',2750);
INSERT INTO emp_msg VALUES(20,'FORD',3300);
INSERT INTO emp_msg VALUES(30,'JAMES',1250);
INSERT INTO emp_msg VALUES(20,'JONES',3275);
INSERT INTO emp_msg VALUES(10,'KING',5300);
INSERT INTO emp_msg VALUES(30,'MARTIN',1550);
INSERT INTO emp_msg VALUES(10,'MILLER',1600);
INSERT INTO emp_msg VALUES(20,'SCOTT',3300);
INSERT INTO emp_msg VALUES(20,'SWITH',1100);
INSERT INTO emp_msg VALUES(30,'TURNER',1800);
INSERT INTO emp_msg VALUES(30,'WARD',1550);
Query the emp_msg table. Replace the last five values with Jane . Start to append the values that are sorted by the ename field in ascending order from the last but five value. Execute the following statement:
SELECT deptno, ename, sal, LAG(ename,5,'Jane') OVER (ORDER BY ename) AS new_ename
FROM emp_msg;
The following query result is returned:
+--------+--------+------+-----------+
| DEPTNO | ENAME | SAL | NEW_ENAME |
+--------+--------+------+-----------+
| 20 | ADAMS | 1400 | Jane |
| 30 | ALLEN | 1900 | Jane |
| 30 | BLAKE | 3135 | Jane |
| 10 | CLARK | 2750 | Jane |
| 20 | FORD | 3300 | Jane |
| 30 | JAMES | 1250 | ADAMS |
| 20 | JONES | 3275 | ALLEN |
| 10 | KING | 5300 | BLAKE |
| 30 | MARTIN | 1550 | CLARK |
| 10 | MILLER | 1600 | FORD |
| 20 | SCOTT | 3300 | JAMES |
| 20 | SWITH | 1100 | JONES |
| 30 | TURNER | 1800 | KING |
| 30 | WARD | 1550 | MARTIN |
+--------+--------+------+-----------+