Let Us Learn Oracle - Part 36 of N [ Lag function of Oracle ]
Purpose:It is an analytic function.This function returns result set starting from the previous row in the table. This function is available since Oracle 8i.
Syntax:Lag(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)
Where,
Expression => A table column or built-in function but not analytical functions
Offset => It is optional and represents the physical offset from the current row in the table. If not specified, the default value is 1 and cannot accept negative numbers.
Default = > It is again optional. If not specified, then whenever the offset value goes out of the table bounds, then default null is returned.
Partition_By_clause = > Partition the query result set. It is again optional
Order By Clause = > Indicates how the data is ordered within the partition.
N.B.~Lag and Lead are the opposite side of a coin and henceforth whatever example we have seen for Lead will be applicable for Lag. Henceforth, it will not be wise to repeat the same scenarios here.We will see some of the example of Lag though
Find the previous match date and the previous match between the teams
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lag (MatchBetween) Over(Order by ScheduleDate) PrevMatchBetween
7 ,Lag (ScheduleDate) Over(Order by ScheduleDate) PrevMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED PREVMATCHBETWEEN PREVMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Australia 14-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 India VS Pakistan 15-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 India VS Newzealand 16-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Australia VS Pakistan 17-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 Australia VS Newzealand 18-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 Newzealand VS Pakistan 19-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS WestIndies 20-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 USA VS Ireland 21-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 USA VS Bangaladesh 22-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 WestIndies VS Ireland 23-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
12 rows selected.
The above result can also be achieve by using the below query
SQL> Select
2 MatchID
3 ,MatchGroup
4 ,MatchBetween
5 ,ScheduleDate
6 ,Lag (MatchBetween,1) Over(Order by ScheduleDate) PrevMatchBetween
7 ,Lag (ScheduleDate,1) Over(Order by ScheduleDate) PrevMatchDate
8 From MatchTable;
MATCHID MATCHGROUP MATCHBETWEEN SCHEDULED PREVMATCHBETWEEN PREVMATCH
---------- ---------- -------------------------------------------------- --------- -----------------
1 Group-A India VS Australia 14-AUG-11
2 Group-A India VS Pakistan 15-AUG-11 India VS Australia 14-AUG-11
3 Group-A India VS Newzealand 16-AUG-11 India VS Pakistan 15-AUG-11
4 Group-A Australia VS Pakistan 17-AUG-11 India VS Newzealand 16-AUG-11
5 Group-A Australia VS Newzealand 18-AUG-11 Australia VS Pakistan 17-AUG-11
6 Group-A Newzealand VS Pakistan 19-AUG-11 Australia VS Newzealand 18-AUG-11
7 Group-B USA VS WestIndies 20-AUG-11 Newzealand VS Pakistan 19-AUG-11
8 Group-B USA VS Ireland 21-AUG-11 USA VS WestIndies 20-AUG-11
9 Group-B USA VS Bangaladesh 22-AUG-11 USA VS Ireland 21-AUG-11
10 Group-B WestIndies VS Ireland 23-AUG-11 USA VS Bangaladesh 22-AUG-11
11 Group-B WestIndies VS Bangaladesh 24-AUG-11 WestIndies VS Ireland 23-AUG-11
12 Group-B Ireland VS Bangaladesh 25-AUG-11 WestIndies VS Bangaladesh 24-AUG-11
12 rows selected.
Now let us try to understand the behavior of the Lag function. We have not specified any offset in this query and hence the default value of 1 has been taken into granted. Henceforth, it ended at N-1 th row from the bottom. However, if we explicitly specify the offset value as 1, it will return the same result.
As specified earlier, whatever example follows for Lead will be applicable for Lag. Henceforth, the scenarios are not repeated here
Prior to Denali CTP3, there was no inbuilt Lag function.But we have this now.You can read about Lag of Sql Server from here
Hope you have enjoyed it.Thanks for reading