LEAD and LAG : Oracle Analytic Functions

Last updated on May 1st, 2019 at 04:37 pm

Lead Function

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.

LAG function

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.

Leave a Reply