Purpose
You can call this function to retrieve data of the last offset number of rows in one field from a query and store it as a separate column in the table. This operation can serve as a substitute of self-joins on a table.
Syntax
LEAD { (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 LEAD as value_expr. |
| offset | (Optional) Specifies 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. Retrieve the value in the ename field. Sort the data by the ename field in the ascending order. Replace values in the last 5 rows with XXX.
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, LEAD(ename,5,'XXX') OVER (ORDER BY ename desc) AS new_ename
FROM emp_msg;
+--------+--------+------+-----------+
DEPTNO ENAME SAL NEW_ENAME
+--------+--------+------+-----------+
20 ADAMS 1400 JAMES
30 ALLEN 1900 JONES
30 BLAKE 3150 KING
10 CLARK 2750 MARTIN
20 FORD 3300 MILLER
30 JAMES 1250 SCLARK
20 JONES 3275 SCOTT
10 KING 5300 SMITH
30 MARTIN 1550 TURNER
10 MILLER 1600 WARD
30 SCLARK 1750 XXX
20 SCOTT 3300 XXX
20 SMITH 1100 XXX
30 TURNER 1800 XXX
30 WARD 1550 XXX
+--------+--------+------+-----------+
15 rows in set