Two cool functions of SQL

There are two really cool functions in Oracle SQL that are really very helpful when we are trying to do manipulations with date. The two functions are LEAD and LAG. These are called analytical functions in oracle.

The LEAD and LAG functions are used to give access to multiple rows in a table without the need of a self join. By using the LEAD and LAG functions one can fetch the current and previous row or current and next row values in a single row. Lets go by an example

Assuming we have data in the following format

SELECT empno,
     ename,
     job,
     sal
FROM   Job
ORDER BY sal;

   EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
    7369 SMITH      CLERK            800
    7900 JAMES      CLERK            950
    7876 ADAMS      CLERK           1100
    7521 WARD       SALESMAN        1250
    7654 MARTIN     SALESMAN        1250
    7934 MILLER     CLERK           1300
    7844 TURNER     SALESMAN        1500
    7499 ALLEN      SALESMAN        1600
    7782 CLARK      MANAGER         2450
    7698 BLAKE      MANAGER         2850
    7566 JONES      MANAGER         2975
    7788 SCOTT      ANALYST         3000
    7902 FORD       ANALYST         3000
    7839 KING       PRESIDENT       5000

Now using the LAG function we can get the current employees salary as well as the previous employees salary. This is done by using the following query

The general syntax of LAG function is

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)

SELECT empno,
      ename,
      job,
      sal,
      LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
FROM   job;

    EMPNO ENAME      JOB              SAL   SAL_PREV  
---------- ---------- --------- ---------- ----------
     7369 SMITH      CLERK            800          0       
     7900 JAMES      CLERK            950        800       
     7876 ADAMS      CLERK           1100        950       
     7521 WARD       SALESMAN        1250       1100       
     7654 MARTIN     SALESMAN        1250       1250         
     7934 MILLER     CLERK           1300       1250        
     7844 TURNER     SALESMAN        1500       1300       
     7499 ALLEN      SALESMAN        1600       1500       
     7782 CLARK      MANAGER         2450       1600       
     7698 BLAKE      MANAGER         2850       2450       
     7566 JONES      MANAGER         2975       2850       
     7788 SCOTT      ANALYST         3000       2975        
     7902 FORD       ANALYST         3000       3000         
     7839 KING       PRESIDENT       5000       3000       

The Sal_prev field will hold the salary of the previous row employee.

Now lets see how LEAD function will work for the same given data. The general syntax of the LEAD function is

LEAD  (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)

SELECT empno,

       ename,
      job,
      sal,
      LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
FROM   job;

    EMPNO ENAME      JOB              SAL   SAL_NEXT
---------- ---------- --------- ---------- ----------
     7369 SMITH      CLERK            800        950       
     7900 JAMES      CLERK            950       1100       
     7876 ADAMS      CLERK           1100       1250       
     7521 WARD       SALESMAN        1250       1250         
     7654 MARTIN     SALESMAN        1250       1300        
     7934 MILLER     CLERK           1300       1500       
     7844 TURNER     SALESMAN        1500       1600       
     7499 ALLEN      SALESMAN        1600       2450       
     7782 CLARK      MANAGER         2450       2850       
     7698 BLAKE      MANAGER         2850       2975       
     7566 JONES      MANAGER         2975       3000        
     7788 SCOTT      ANALYST         3000       3000         
     7902 FORD       ANALYST         3000       5000      
     7839 KING       PRESIDENT       5000          0     

The SAL_NEXT field will hold the value of the next row salary.

This feature can be used in numerous occasions, one occasion where we find it to be useful is in peoplesoft where we deal with the effdt, If we want the job_end_dt of a particular person and you know only his job_begin_dt, then using the LEAD and LAG functions we can determine the job_end_dt, by doing LEAD function to the job_begin_dt and have -1 of date

for example if we have data

Emplid JOB_BEGIN_DT JOBCODE
---------- ----------------------- ----------------
0001 01/01/2006 001
0001 02/01/2006 ABC
0001 04/01/2006 CBA

Now If we need to determine the job_end_dt in each and every jobcode or how long a person has stayed in a particular job code, then the query goes this way.

select EMPLID,
JOB_BEGIN_DT,
JOBCODE
LEAD(JOB_BEGIN_DT,1,0) OVER (ORDER BY JOB_BEGIN_DT) -1 AS JOB_END_DT

The above query will determine the next job_begin_dt in the table and subtract one day from it which will become the job_end_dt for the specified. The resultant data will look like

Emplid JOB_BEGIN_DT JOBCODE JOB_END_DT
---------- ----------------------- ---------------- --------------------
0001 01/01/2006 001 01/31/2006
0001 02/01/2006 ABC 03/31/2006
0001 04/01/2006 CBA


Hope the explanation was useful !!!