LAG and LEAD - Analytic Function

Analytic functions compute an aggregate value based on a group of rows called window which determines the range of rows used to perform the calculations for the current row. Following are most used Analytic functions.
- RANK, DENSE_RANK and ROW_NUMBER
- LAG and LEAD
- FIRST_VALUE and LAST_VALUE


LAG is an analytic function which provides access to a row prior to Nth position of current row within window of ordered set of rows. LEAD is same as LAG analytic function but it provides access to a row following Nth position of current row within window of ordered set of rows.

Both LAG and LEAD take 3 arguments (value_expression [,offset] [,default])
- value_expression is usually a column name or a function
- offset is Nth position of row from the current row by which the data is to be retrieved. The default value is 1.
- default defines the value returned if the offset is outside the scope of the window. The default value is NULL.

Example of LAG:

SQL> select
  2    empno, ename, deptno, sal,
  3    lag(sal,1,0) over (partition by deptno order by sal) lag_sal
  4  from
  5    scott.emp;

     EMPNO ENAME          DEPTNO        SAL    LAG_SAL
---------- ---------- ---------- ---------- ----------
      7934 MILLER             10       1300          0
      7782 CLARK              10       2450       1300
      7839 KING               10       5000       2450
      7369 SMITH              20        800          0
      7876 ADAMS              20       1100        800
      7566 JONES              20       2975       1100
      7788 SCOTT              20       3000       2975
      7902 FORD               20       3000       3000
      7900 JAMES              30        950          0
      7654 MARTIN             30       1250        950
      7521 WARD               30       1250       1250
      7844 TURNER             30       1500       1250
      7499 ALLEN              30       1600       1500
      7698 BLAKE              30       2850       1600

14 rows selected.  

Example of LEAD:

SQL> select
  2    empno, ename, deptno, sal,
  3    lead(sal,1,0) over (partition by deptno order by sal) lag_sal
  4  from
  5    scott.emp;

     EMPNO ENAME          DEPTNO        SAL    LAG_SAL
---------- ---------- ---------- ---------- ----------
      7934 MILLER             10       1300       2450
      7782 CLARK              10       2450       5000
      7839 KING               10       5000          0
      7369 SMITH              20        800       1100
      7876 ADAMS              20       1100       2975
      7566 JONES              20       2975       3000
      7788 SCOTT              20       3000       3000
      7902 FORD               20       3000          0
      7900 JAMES              30        950       1250
      7654 MARTIN             30       1250       1250
      7521 WARD               30       1250       1500
      7844 TURNER             30       1500       1600
      7499 ALLEN              30       1600       2850
      7698 BLAKE              30       2850          0

14 rows selected.

Related Posts:
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- FIRST_VALUE and LAST_VALUE with Windowing Clause
- Fill Gaps in Sparse Data - Partitioned Outer Join
- Oracle: Getting Cumulative Sum (Running Total) Using Analytical Functions
- Oracle: Simulate Lead Analytical Function (Manual Lead)