In MySQL, we can use the LEAD() function to get the value of a subsequent row. For example we can get a value from the next row, or the one after that, and so on.
This enables us to do things like compute the difference between a value in the current row and a value in a following row. We can do this even if both values are in the same column.
Example
Here’s an example of calculating the difference between the current row and the following one:
SELECT
IdiotName,
IQ,
LEAD( IQ, 1 ) OVER win AS "Lead Value",
IQ - LEAD( IQ, 1 ) OVER win AS "Current Minus Next",
LEAD( IQ, 1 ) OVER win - IQ AS "Next Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+--------------------+--------------------+ | IdiotName | IQ | Lead Value | Current Minus Next | Next Minus Current | +-------------------+------+------------+--------------------+--------------------+ | Dumbest | 30 | 40 | -10 | 10 | | Homer | 40 | 40 | 0 | 0 | | Patrick Star | 40 | 40 | 0 | 0 | | Ed | 40 | 50 | -10 | 10 | | Dumber | 50 | 50 | 0 | 0 | | Peter Griffin | 50 | 55 | -5 | 5 | | Cosmo | 55 | 60 | -5 | 5 | | Dumb | 60 | 65 | -5 | 5 | | Ralph Wiggum | 65 | 70 | -5 | 5 | | Richard Watterson | 70 | NULL | NULL | NULL | +-------------------+------+------------+--------------------+--------------------+
Here, I used LEAD( IQ, 1 ) for the lead value. The IQ part means the IQ column, and 1 means that we go forward one row.
In the Current Minus Next column we subtracted the LEAD() value from the IQ column (i.e. the value of the current row in that column). In the Next Minus Current column we did the opposite – we subtracted the IQ column from the LEAD() output.
The lead value in the last row is NULL because there’s no subsequent row for which to retrieve a value.
In this example I used the WINDOW clause to create a named window. This allowed me to refer to that window from various OVER clauses in the SELECT list. This saved me from having to replicate the same code multiple times.
Going Forward More than One Row
We’re not limited to just the next row. We could go forward as many rows as we want. Let’s go forward two rows:
SELECT
IdiotName,
IQ,
LEAD( IQ, 2 ) OVER win AS "Lead Value",
IQ - LEAD( IQ, 2 ) OVER win AS "Current Minus 2 Forward",
LEAD( IQ, 2 ) OVER win - IQ AS "2 Forward Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+-------------------------+-------------------------+ | IdiotName | IQ | Lead Value | Current Minus 2 Forward | 2 Forward Minus Current | +-------------------+------+------------+-------------------------+-------------------------+ | Dumbest | 30 | 40 | -10 | 10 | | Homer | 40 | 40 | 0 | 0 | | Patrick Star | 40 | 50 | -10 | 10 | | Ed | 40 | 50 | -10 | 10 | | Dumber | 50 | 55 | -5 | 5 | | Peter Griffin | 50 | 60 | -10 | 10 | | Cosmo | 55 | 65 | -10 | 10 | | Dumb | 60 | 70 | -10 | 10 | | Ralph Wiggum | 65 | NULL | NULL | NULL | | Richard Watterson | 70 | NULL | NULL | NULL | +-------------------+------+------------+-------------------------+-------------------------+
We now get NULL in the last two rows.
Setting a Default Value for NULLs
We can set a value to use in cases where LEAD() returns NULL. We can do this by adding a third argument:
SELECT
IdiotName,
IQ,
LEAD( IQ, 2, 0 ) OVER win AS "Lead Value",
IQ - LEAD( IQ, 2, 0 ) OVER win AS "Current Minus 2 Forward",
LEAD( IQ, 2, 0 ) OVER win - IQ AS "2 Forward Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+-------------------------+-------------------------+ | IdiotName | IQ | Lead Value | Current Minus 2 Forward | 2 Forward Minus Current | +-------------------+------+------------+-------------------------+-------------------------+ | Dumbest | 30 | 40 | -10 | 10 | | Homer | 40 | 40 | 0 | 0 | | Patrick Star | 40 | 50 | -10 | 10 | | Ed | 40 | 50 | -10 | 10 | | Dumber | 50 | 55 | -5 | 5 | | Peter Griffin | 50 | 60 | -10 | 10 | | Cosmo | 55 | 65 | -10 | 10 | | Dumb | 60 | 70 | -10 | 10 | | Ralph Wiggum | 65 | 0 | 65 | -65 | | Richard Watterson | 70 | 0 | 70 | -70 | +-------------------+------+------------+-------------------------+-------------------------+
Here we used 0 as the default value to use. Now we get 0 instead of NULL. This changes the result of our calculations on the next two columns.
Partitions
Suppose we run a similar (non-partitioned) query against a different table:
SELECT
DogName,
Activity,
Score,
LEAD( Score, 1, 0 ) OVER win AS "Lead",
Score - LEAD( Score, 1, 0 ) OVER win AS "Score Minus Lead",
LEAD( Score, 1, 0 ) OVER win - Score AS "Lead Minus Score"
FROM Dogs
WINDOW win AS ( ORDER BY Score );Result:
+---------+-------------+-------+------+------------------+------------------+ | DogName | Activity | Score | Lead | Score Minus Lead | Lead Minus Score | +---------+-------------+-------+------+------------------+------------------+ | Max | Keep Quiet | 1 | 2 | -1 | 1 | | Bruno | Keep Quiet | 2 | 8 | -6 | 6 | | Cooper | Keep Quiet | 8 | 43 | -35 | 35 | | Bruno | Fetch Stick | 43 | 51 | -8 | 8 | | Cooper | Wag Tail | 51 | 65 | -14 | 14 | | Bruno | Wag Tail | 65 | 67 | -2 | 2 | | Cooper | Fetch Stick | 67 | 87 | -20 | 20 | | Max | Wag Tail | 87 | 91 | -4 | 4 | | Max | Fetch Stick | 91 | 0 | 91 | -91 | +---------+-------------+-------+------+------------------+------------------+
Here’s the same query partitioned by activity:
SELECT
DogName,
Activity,
Score,
LEAD( Score, 1, 0 ) OVER win AS "Lead",
Score - LEAD( Score, 1, 0 ) OVER win AS "Score Minus Lead",
LEAD( Score, 1, 0 ) OVER win - Score AS "Lead Minus Score"
FROM Dogs
WINDOW win AS ( PARTITION BY Activity ORDER BY Score );Result:
+---------+-------------+-------+------+------------------+------------------+ | DogName | Activity | Score | Lead | Score Minus Lead | Lead Minus Score | +---------+-------------+-------+------+------------------+------------------+ | Bruno | Fetch Stick | 43 | 67 | -24 | 24 | | Cooper | Fetch Stick | 67 | 91 | -24 | 24 | | Max | Fetch Stick | 91 | 0 | 91 | -91 | | Max | Keep Quiet | 1 | 2 | -1 | 1 | | Bruno | Keep Quiet | 2 | 8 | -6 | 6 | | Cooper | Keep Quiet | 8 | 0 | 8 | -8 | | Cooper | Wag Tail | 51 | 65 | -14 | 14 | | Bruno | Wag Tail | 65 | 87 | -22 | 22 | | Max | Wag Tail | 87 | 0 | 87 | -87 | +---------+-------------+-------+------+------------------+------------------+
To partition the results, we simply added a PARTITION BY clause to our named window. Now the LEAD() value is based on the current partition. This means that there’s no lead value for the last row of each partition (because there are no subsequent rows in that partition). So in our case, instead of having one row with a NULL value (that we replaced with a default value of 0), we have three. We can see this by the fact that three rows have 0 in the Lead Value column (due to the fact that we replaced all NULLs with 0).