LEAD/LAG are analytic functions that provide access to more than one row of a table at the same time, without a self join. Let’s see how.
Take a table that stores the master list of exam grades, mapped to the upper limit up to which the grade applies. The table (GRADE_MASTER) has two columns: {GRADE_CODE, SCORE_UPTO}. For exam scores in the range 0-100, GRADE_MASTER specifies the A-F.
SQL> desc grade_master Name Null? Type ----------------- -------- ----------- GRADE_CODE NOT NULL VARCHAR2(2) SCORE_UPTO NOT NULL NUMBER(3) SQL> select * from grade_master; GR SCORE_UPTO -- ---------- F 59 D 69 C 79 B 89 A 100
The above data means that grade F applies to scores 0-59, D applies to scores 60-69, C to scores 70-79, and so on.
To find the grade for a given the examination score, the SQL needs to compare the EXTENT values across *two* rows. Comparing a column’s value across more than one row can be tricky to implement – unless you turn to Oracle functions LEAD/LAG.
From a series of rows returned from a query and a position of the cursor,
- LEAD lets lets you access a row at a given offset after that position
- LAG lets you access a row at a given offset before that position
LEAD/LAG Syntax
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
If the offset is unspecified, it is assumed to be 1. If the offset goes beyond the scope of the window, the default value gets used.
Place a Value between extents with LEAD/LAG: Solution Approach
To find the position of a value within extents, the trick is to:
- sort the extents
- transpose consecutive extents across two rows in the form of range_low and range_high in a single row
- get the row where value is BETWEEN range_low and range_high
Achieving 1 and 2 as above, using LAG:
select grade_code
-- Set default to 0 for
-- range_low of first row
, lag(score_upto,1,-1)
over (order by score_upto) + 1
score_range_low
, score_upto score_range_high
from grade_master;
Place a value between extents with LEAD/LAG: Runtime Output
SQL> select grade_code 2 -- Set default to 0 for 3 -- range_low of first row 4 , lag(score_upto,1,-1) 5 over (order by score_upto) + 1 6 score_range_low 7 , score_upto score_range_high 8 from grade_master; GR SCORE_RANGE_LOW SCORE_RANGE_HIGH -- --------------- ---------------- F 0 59 D 60 69 C 70 79 B 80 89 A 90 100
Applying 3, after pushing 1 and 2 into an inline view:
SQL> select grade_code 2 from ( 3 select grade_code 4 , lag(score_upto,1,-1) 5 over (order by score_upto) + 1 6 score_range_low 7 , score_upto score_range_high 8 from grade_master 9 ) 10 where 34 -- input score, b/w 0 and 59 11 between score_range_low and score_range_high; GR -- F SQL> select grade_code 2 from ( 3 select grade_code 4 , lag(score_upto,1,-1) 5 over (order by score_upto) + 1 6 score_range_low 7 , score_upto score_range_high 8 from grade_master 9 ) 10 where 69 -- input score, b/w 60 and 69 11 between score_range_low and score_range_high; GR -- D SQL> select grade_code 2 from ( 3 select grade_code 4 , lag(score_upto,1,-1) 5 over (order by score_upto) + 1 6 score_range_low 7 , score_upto score_range_high 8 from grade_master 9 ) 10 where 85 -- input score, b/w 80 and 89 11 between score_range_low and score_range_high; GR -- B
Summary
This article shows you how analytic functions LEAD and LAG can be used to access a row in a table given an offset value from another row, without needing a self-join.
This feature can be used to find the position of a value within extents, where the extents are defined by the upper bound only and the lower bound is derived via comparison to the previous/next row.
The example of finding the grade for an exam score shows one possible implementation of LEAD and LAG.
