LAG Function In Oracle

LAG is one of the vital Analytic functions of Oracle. It is used to query more than one row in a table at a time. With the use of LAG function there is no need to join the table to itself. The result is the values from a previous row in the table. The LAG function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax:

LAG ( expression, offset, default )
OVER ( [ query_partition_clause ] order_by_clause )

Parameters:
expression: It is used to specify the expressions containing built-in functions but no analytic functions.
offset: It is an optional parameter which is used to specify the physical offset from the current row in the table with a default value of 1.
default: It is also an optional parameter which is used to specify a value that is returned if the offset goes out of the bounds of the table with a default value of NULL.
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.

Example 1: Without using query_partition_clause.
Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT id, marks,
LAG (marks,1) OVER (ORDER BY marks) AS prev_marks
FROM students;

Output:

ID	MARKS	PREV_MARKS
4	75	NULL
1	80	75
5	85	80
3	95	85
2	100	95

Explanation:
Here, the LAG function will sort all of the ‘marks’ in ascending order in the ‘students’ table and since we used an offset of 1, it will then return the previous marks. The first record in the result set has a value of NULL for the ‘prev_marks’ as there is no lower ‘marks’ value.

Example 2: Using query_partition_clause.
Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT extra_marks, marks,
LAG (marks,1) OVER (PARTITION BY extra_marks ORDER BY marks) AS prev
_marks
FROM students;

Output:

EXTRA_MARKS	MARKS	PREV_MARKS
10	        75	NULL
10	        80	75
15	        85	NULL
15	        95	85
20	        100	NULL

Explanation:
Here, the LAG function will sort all of the ‘marks’ in ascending order after the partition of the results by ‘extra_marks’ in the ‘students’ table and since we used an offset of 1, it will then return the previous marks. The LAG function will restart its calculations, whenever a new ‘extra_marks’ is encountered. The first record in the result set is the first record for the partition where ‘extra_marks’ is 10 and thus has a value of NULL for the ‘prev_marks’ as there is no lower ‘marks’ value in this partition. This is also true for the 3rd record which is the first record for the partition where the ‘extra_marks’ is 15 and the 5th record which is the first record for the partition where the ‘extra_marks’ is 20.