One of my all time favorite analytical functions are the LAG (look back) and LEAD (look ahead) functions, which were first introduced in Oracle 8i. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG/LEAD provides access to a row at a given physical offset prior to that position.
Usage of these functions are -
LAG(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause) LEAD(value_expr [, offset ] [, default ]) OVER ([ query_partition_clause ] order_by_clause)
- value_expr – You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.
- offset – If you do not specify offset, then its default is 1.
- default – The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.
The following example provides, for each person in the emp table, the salary of the employee hired just before and after ;
SQL> select ename, hiredate, sal, 2 LAG(sal, 1, 0) OVER (ORDER BY hiredate) AS prev_sal, -- Look Back 3 LEAD(sal, 1, 0) OVER (ORDER BY hiredate) AS next_sal -- Look Ahead 4 FROM scott.emp 5 / ENAME HIREDATE SAL PREV_SAL NEXT_SAL ---------- --------- ---------- ---------- ---------- SMITH 17-DEC-80 800 0 1600 ALLEN 20-FEB-81 1600 800 1250 WARD 22-FEB-81 1250 1600 2975 JONES 02-APR-81 2975 1250 2850 BLAKE 01-MAY-81 2850 2975 2450 CLARK 09-JUN-81 2450 2850 1500 TURNER 08-SEP-81 1500 2450 1250 MARTIN 28-SEP-81 1250 1500 5000 KING 17-NOV-81 5000 1250 950 JAMES 03-DEC-81 950 5000 3000 FORD 03-DEC-81 3000 950 1300 MILLER 23-JAN-82 1300 3000 3000 SCOTT 19-APR-87 3000 1300 1100 ADAMS 23-MAY-87 1100 3000 0
