Lead And Lag Analytic Functions

Analytic functions are also called the windowing functions and have been introduced in Oracle 8i. These functions, unlike the aggregate functions, do not reduce the number of rows returned, however, just like aggregate functions they also operate on a set of rows. This time we are looking at two of the analytics functions –

LEAD
LAG

These functions were introduced to give access to multiple rows within a table without the need for a self-join.

Both the functions have the same usage, as 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 – It could be a column or a built in function, except any other analytic function.

offset – The number of rows preceding/following the current row, from which data is retrived. The default value is 1.

default – The value returned if the offset is outside the scope of the window. The default value is NULL.

THE LAG FUNCTION

Going by its name, the lag function is used to access data from a previous row.

THE LEAD FUNCTION

The LEAD function is used to return data from the next row.

UNDERSTANDING FUNCTIONS

— THE BELOW QUERY CREATES THE TABLE FOR DEMO PURPOSE

CREATE TABLE UNDERSTAND_LAG_LEAD

(

empno integer,

empname varchar2(22),

sal number(20,2)

);

— INSERTING DATA INTO UNDERSTAND_LAG_LEAD TABLE

INSERT INTO UNDERSTAND_LAG_LEAD

VALUES

(4,’Varsha’,330300);

commit;

— view the data

select * from understand_lag_lead

order by sal;

2 Avinash 130000

3 Aniket 330000

4 Varsha 330300

1 Avantika 9876234

— UNDERSTANDING THE LAG FUNCTION

SELECT empno,

empname,

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 understand_lag_lead

;

2 Avinash 130000 0 130000

3 Aniket 330000 130000 200000

4 Varsha 330300 330000 300

1 Avantika 9876234 330300 9545934

The third column is showing the salary of the individual.

The fourth column is showing the salary of the individual whose record is before them.

The fifth column shows the difference between the salary of the individual and the one before him/her.

This is how lag function works. It helps you to see the data that is of the previous rows. Here, the offset value is set to , so the data is shown of the one just before.

Let us see what happens if the offset value gets changed.

We have set the offset value to 2.

2 Avinash 130000 0

3 Aniket 330000 0

4 Varsha 330300 130000

1 Avantika 9876234 330000

The above result gets displayed.

THE LEAD FUNCTION

Lead function is the complementary function of the LAG function. The lag shows data of the previous rows, the lead function shows the data of the rows ahead of it.

TheTechWorld

Tanubhrt.This.Side