Purpose
You can call this function to retrieve data of the first offset number of rows in one field from a query and store it as a separate column in the table. It is an analytic function. This operation can serve as a substitute of self-joins on a table.
Syntax
LAG { (value_expr [,offset [,default]]) [{ RESPECTIGNORE } NULLS ]
(value_expr [{ RESPECTIGNORE } 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 use analytic functions such as LAG as value_expr. |
| offset | (Optional) The offset of value_expr to be queried, which is an integer greater than zero. Default value: 1. |
| default | (Optional) The default value for no match. It returns the default value if the offset goes beyond the scope of the window. Default value: NULL. |
| {RESPECT IGNORE} NULLS | (Optional) Specifies whether to ignore NULL values. 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 about analytic functions, see Analytic functions. |
Return types
This function returns data of any data type.
Examples
Assume that you have created the table emp_msg. Query the emp_msg table, replace the first three values with XXX. 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