Cheesy Code

The lead and lag functions offer the power to peek into adjacent rows and pull values from next or previous rows into the current row for processing. They are a convenient way of accessing multiple rows and cut processing time compared to the alternative of self-joins. Here’s a simple dataset to work with:

CREATE GLOBAL TEMPORARY TABLE dat(lvl NUMBER, letter CHAR(1))
ON COMMIT DELETE ROWS;

INSERT INTO dat
SELECT lvl, letter FROM
    (
    SELECT LEVEL lvl, CHR(LEVEL+64) letter
      FROM DUAL
    CONNECT BY LEVEL <= 5
    );

SELECT * FROM dat;

Now let's apply the LEAD and LAG, jumping only a single row jump ",1" and use (ORDER by lvl ASC) for the window specification which provides the definition of next and previous rows for the functions. Note that the resultant data can be independently sorted in the usual way with a ORDER BY clause. First LEAD and we can see that the 1 A row can see forward to the next row which contains a B:

SELECT lvl, letter, LEAD(letter,1) OVER (ORDER BY lvl ASC) next
  FROM dat;

Going the other way we can look backwards with LAG and now see that the 2 B row can see the A in the previous row.

SELECT lvl, letter, LAG(letter,1) OVER (ORDER BY lvl ASC) prev
  FROM dat;

One possible use of these functions is to test a large numbers of rows to determine whether or not they are a complete sequence. Deleting the middle row out of our sequence yields:

DELETE FROM dat WHERE lvl = 3;

SELECT * FROM dat;

Now we can use a LEAD function to easily check the next row against the current row to see whether or not the sequence is being maintained:

SELECT * FROM
(
    SELECT lvl, letter, LEAD(letter,1) OVER (ORDER BY lvl ASC) next
      FROM dat
)
WHERE CHR(ASCII(letter)+1) != next;

Of course it isn't in this case and we find that there is a problem after row 2 where there is a jump:

An alternate way to find gaps in what is expected to be a sequence is to perform an outer join against a generated sequence and that's the technique used in Weekly report on transactions.