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.