LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:
LEAD (<expr>, <offset>, <default>) OVER (<analytic_clause>)
<expr> is the expression to compute from the leading row.
<offset> is the index of the leading row relative to the current row and its default value is 1
<default> is the value to return if the <offset> points to a row outside the partition range.
SQL> SELECT deptno, empno, sal,LEAD(sal, 1, 0) OVER ( partition by deptno ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL FROM emp;
DEPTNO EMPNO SAL NEXT_LOWER_SAL
---------- ---------- ---------- --------------
10 7839 28573 0
10 7782 0 0
10 7934 0 0
20 7788 30000 30000
20 7902 30000 11000
20 7876 11000 8000
20 7369 8000 0
30 7698 28500 16000
30 7499 16000 15000
30 7844 15000 12500
30 7654 12500 12500
30 7521 12500 9500
30 7900 9500 0
13 rows selected.
Taking Different Default Value
SELECT deptno, empno, sal,LEAD(sal, 1,100) OVER ( partition by deptno ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL FROM emp;
SQL> SELECT deptno, empno, sal,LEAD(sal, 1,100) OVER ( partition by deptno ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL FROM emp;
DEPTNO EMPNO SAL NEXT_LOWER_SAL
---------- ---------- ---------- --------------
10 7839 28573 0
10 7782 0 0
10 7934 0 100
20 7788 30000 30000
20 7902 30000 11000
20 7876 11000 8000
20 7369 8000 100
30 7698 28500 16000
30 7499 16000 15000
30 7844 15000 12500
30 7654 12500 12500
30 7521 12500 9500
30 7900 9500 100
13 rows selected.
Similarly LAG provides the technique to compute on previous rows and return the value to the current row
LAG (<expr>, <offset>, <default>) OVER (<analytic_clause>)
<expr> is the expression to compute from the previous row.
<offset> is the index of the previous row relative to the current row and its default value is 1
<default> is the value to return if the <offset> points to a row outside the partition range.
SQL> SELECT deptno, empno, sal,LAG(sal, 1,0) OVER ( partition by deptno ORDER BY sal DESC NULLS LAST) LAST_HIGH_SAL FROM emp;
DEPTNO EMPNO SAL LAST_HIGH_SAL
---------- ---------- ---------- -------------
10 7839 28573 0
10 7782 0 28573
10 7934 0 0
20 7788 30000 0
20 7902 30000 30000
20 7876 11000 30000
20 7369 8000 11000
30 7698 28500 0
30 7499 16000 28500
30 7844 15000 16000
30 7654 12500 15000
30 7521 12500 12500
30 7900 9500 12500
13 rows selected.