Purpose
This function is an analytic function. It retrieves the data from the offsetth row of the same field in the query and stores it as an independent column in the table. This operation can replace a self-join of the table.
Syntax
LEAD { (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 | Specifies the field to query. Notice You cannot nest value_expr in the LEAD function or other analytic functions. |
| offset | Specifies the offset for querying value_expr. It is an integer greater than 0. This parameter is optional. The default value is 1. |
| default | Specifies the default value. If the offset exceeds the window range, the default value is returned. This parameter is optional. The default value is NULL. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. This parameter is optional. The default value is RESPECT NULLS.
|
| OVER | Defines the window for calculation by using the OVER clause. For more information, see Analytic functions. |
Return type
The return type can be any data type.
Examples
An emp_msg table has been created. Query the emp_msg table, retrieve the values of the ename field, sort the values in ascending order, and replace the values of 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