Introduction
Both LAG and LEAD functions have the same usage, as shown below.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
value_expression :- Can be a column or a built-in function, except for other analytic functions.
offset :- The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default :- The value returned if the offset is outside the scope of the window. The default value is NULL.
Looking at the EMP table, we query the data in salary (SAL) order.
SELECT empno,
ename,
job,
sal
FROM emp
ORDER BY sal;
SQL>
LAG
The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.
SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;
SQL>
LEAD
The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.
SELECT empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;
SQL>
