Returns the value from a following row at a specified offset within a window partition — without joining the table to itself.
Limitations
LEAD is supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be combined with aggregate functions at the same query level.
Syntax
lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)Parameters
| Parameter | Required | Description |
|---|---|---|
expr | Yes | The expression to evaluate. Can be a column, a column operation, or a function call. |
offset | No | The number of rows ahead of the current row. Must be a BIGINT constant greater than or equal to 0. 0 returns the current row; 1 (the default) returns the next row. STRING and DOUBLE values are implicitly converted to BIGINT before evaluation. |
default | No | The value returned when the offset goes beyond the partition boundary. Must be a constant of the same data type as expr. Defaults to NULL. If expr is not a constant, the value is determined by the current row. |
partition_clause | No | Defines how rows are grouped into partitions. See windowing_definition. |
orderby_clause | Yes | Defines the sort order within each partition. See windowing_definition. |
Return value
Returns a value of the same data type as expr.
To access preceding rows instead of following rows, use the LAG function.
Sample data
The following examples use an emp table. Create the table and load the sample data:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp;The emp.txt file contains:
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Examples
The following example partitions employees by deptno, orders each partition by sal, and returns the salary of the next employee in the same department.
select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;Result:
+------------+------------+------------+------------+
| deptno | ename | sal | sal_new |
+------------+------------+------------+------------+
| 10 | TEBAGE | 1300 | 1300 |
| 10 | MILLER | 1300 | 2450 |
| 10 | CLARK | 2450 | 2450 |
| 10 | WELAN | 2450 | 5000 |
| 10 | KING | 5000 | 5000 |
| 10 | JACCKA | 5000 | NULL |
| 20 | SMITH | 800 | 1100 |
| 20 | ADAMS | 1100 | 2975 |
| 20 | JONES | 2975 | 3000 |
| 20 | SCOTT | 3000 | 3000 |
| 20 | FORD | 3000 | NULL |
| 30 | JAMES | 950 | 1250 |
| 30 | MARTIN | 1250 | 1250 |
| 30 | WARD | 1250 | 1500 |
| 30 | TURNER | 1500 | 1600 |
| 30 | ALLEN | 1600 | 2850 |
| 30 | BLAKE | 2850 | NULL |
+------------+------------+------------+------------+The last row in each partition (JACCKA, FORD, BLAKE) returns NULL for sal_new because there is no following row within the partition and no default value was specified.
Related functions
LEAD is a window function. For the full list of window functions and the windowing_definition syntax, see Window functions.