Purpose
LAG() is an analytic function. It retrieves data of the first offset number of rows in one field from a query and stores it as a separate column in the table. This operation can serve as a substitute of self-joins on a table.
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 name of the field to be queried. Notice: You cannot nest value_expr in analytic functions such as LAG. |
| offset | Optional. The offset of value_expr to be queried, which is an integer greater than zero. Default value: 1. |
| default | The default value for no match. It returns the default value if the offset goes beyond the scope of the window. This parameter is optional. Default value: NULL. |
| {RESPECT | IGNORE} NULLS | Optional. Specifies whether to ignore NULLs. Default value: RESPECT NULLS.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
This function returns data of any data type.
Examples
The table emp_msg has been created. Query the emp_msg table, replace the first three values with XXX. Starting from the fourth value, append values that are sorted by the ename field in ascending order.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
| DEPTNO | ENAME | SAL | MGR |
+--------+--------+------+------+
| 10 | CLARK | 2750 | 7839 |
| 10 | KING | 5300 | NULL |
| 10 | MILLER | 1600 | 7782 |
| 20 | ADAMS | 1400 | 7788 |
| 20 | FORD | 3300 | 7566 |
| 20 | JONES | 3275 | 7839 |
| 20 | SCOTT | 3300 | 7566 |
| 20 | SMITH | 1100 | 7902 |
| 30 | ALLEN | 1900 | 7698 |
| 30 | BLAKE | 3150 | 7839 |
| 30 | JAMES | 1250 | 7698 |
| 30 | MARTIN | 1550 | 7698 |
| 30 | TURNER | 1800 | 7698 |
| 30 | WARD | 1550 | 7698 |
| 30 | SCLARK | 1750 | 7839 |
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename, sal,LAG(ename,3,'XXX') OVER (ORDER BY ename) AS new_ename
FROM emp_msg;
+--------+--------+------+-----------+
| DEPTNO | ENAME | SAL | NEW_ENAME |
+--------+--------+------+-----------+
| 20 | ADAMS | 1400 | XXX |
| 30 | ALLEN | 1900 | XXX |
| 30 | BLAKE | 3150 | XXX |
| 10 | CLARK | 2750 | ADAMS |
| 20 | FORD | 3300 | ALLEN |
| 30 | JAMES | 1250 | BLAKE |
| 20 | JONES | 3275 | CLARK |
| 10 | KING | 5300 | FORD |
| 30 | MARTIN | 1550 | JAMES |
| 10 | MILLER | 1600 | JONES |
| 30 | SCLARK | 1750 | KING |
| 20 | SCOTT | 3300 | MARTIN |
| 20 | SMITH | 1100 | MILLER |
| 30 | TURNER | 1800 | SCLARK |
| 30 | WARD | 1550 | SCOTT |
+--------+--------+------+-----------+
15 rows in set