All Products
Search
Document Center

LAG

Last Updated: Jun 18, 2021

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