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,The SAL_NEXT field will hold the value of the next row salary.
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
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 !!!
Posts
