MySQL includes some nonaggregate window functions that allow us to get a value from a specific row. We can use such functions to do things like, compare the value in the specified row with the value in the current row, even if both values are in the same column.
Below are five functions that we can use to do this.
Option 1: The LAG() Function
We can use the LAG() function to get the value from a previous row:
SELECT
IdiotName,
IQ,
LAG( IQ ) OVER win AS "Lag Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+-----------+ | IdiotName | IQ | Lag Value | +-------------------+------+-----------+ | Dumbest | 30 | NULL | | Homer | 40 | 30 | | Patrick Star | 40 | 40 | | Ed | 40 | 40 | | Dumber | 50 | 40 | | Peter Griffin | 50 | 50 | | Cosmo | 55 | 50 | | Dumb | 60 | 55 | | Ralph Wiggum | 65 | 60 | | Richard Watterson | 70 | 65 | +-------------------+------+-----------+
By default, LAG() goes back one row. We can see this in the above example. The Lag Value column contains the value from the previous row in the IQ column. The first row is NULL because there’s no previous row in that case.
We can provide a second argument to specify how many rows back to go:
SELECT
IdiotName,
IQ,
LAG( IQ, 3 ) OVER win AS "Lag Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+-----------+ | IdiotName | IQ | Lag Value | +-------------------+------+-----------+ | Dumbest | 30 | NULL | | Homer | 40 | NULL | | Patrick Star | 40 | NULL | | Ed | 40 | 30 | | Dumber | 50 | 40 | | Peter Griffin | 50 | 40 | | Cosmo | 55 | 40 | | Dumb | 60 | 50 | | Ralph Wiggum | 65 | 50 | | Richard Watterson | 70 | 55 | +-------------------+------+-----------+
Here, I used LAG( IQ, 3 ), which caused it to go back three rows.
Option 2: The LEAD() Function
The LEAD() function works similar to LAG(), except that it gets its value from a subsequent row:
SELECT
IdiotName,
IQ,
LEAD( IQ ) OVER win AS "Lead Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+ | IdiotName | IQ | Lead Value | +-------------------+------+------------+ | Dumbest | 30 | 40 | | Homer | 40 | 40 | | Patrick Star | 40 | 40 | | Ed | 40 | 50 | | Dumber | 50 | 50 | | Peter Griffin | 50 | 55 | | Cosmo | 55 | 60 | | Dumb | 60 | 65 | | Ralph Wiggum | 65 | 70 | | Richard Watterson | 70 | NULL | +-------------------+------+------------+
This time we can see that it takes the value from the next row. Similarly to LAG() we can provide a second argument to specify how many rows forward to take its value from:
SELECT
IdiotName,
IQ,
LEAD( IQ, 3 ) OVER win AS "Lead Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+ | IdiotName | IQ | Lead Value | +-------------------+------+------------+ | Dumbest | 30 | 40 | | Homer | 40 | 50 | | Patrick Star | 40 | 50 | | Ed | 40 | 55 | | Dumber | 50 | 60 | | Peter Griffin | 50 | 65 | | Cosmo | 55 | 70 | | Dumb | 60 | NULL | | Ralph Wiggum | 65 | NULL | | Richard Watterson | 70 | NULL | +-------------------+------+------------+
Of course, this results in the last three rows being NULL, due to the fact that there are no more rows to get a value from.
Option 3: The NTH_VALUE() Function
The NTH_VALUE() function is similar to LAG() and LEAD(), except that it allows us to choose the row number of the value we want to return:
SELECT
IdiotName,
IQ,
NTH_VALUE( IQ, 3 ) OVER win AS "Nth Value"
FROM Idiots
WINDOW win AS (
ORDER BY IQ
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);Result:
+-------------------+------+-----------+ | IdiotName | IQ | Nth Value | +-------------------+------+-----------+ | Dumbest | 30 | 40 | | Homer | 40 | 40 | | Patrick Star | 40 | 40 | | Ed | 40 | 40 | | Dumber | 50 | 40 | | Peter Griffin | 50 | 40 | | Cosmo | 55 | 40 | | Dumb | 60 | 40 | | Ralph Wiggum | 65 | 40 | | Richard Watterson | 70 | 40 | +-------------------+------+-----------+
Option 4: The FIRST_VALUE() Function
The FIRST_VALUE() function is similar to the above functions, except that it gets its value from the first row in the window frame.
Example:
SELECT
IdiotName,
IQ,
FIRST_VALUE( IQ ) OVER win AS "First Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+-------------+ | IdiotName | IQ | First Value | +-------------------+------+-------------+ | Dumbest | 30 | 30 | | Homer | 40 | 30 | | Patrick Star | 40 | 30 | | Ed | 40 | 30 | | Dumber | 50 | 30 | | Peter Griffin | 50 | 30 | | Cosmo | 55 | 30 | | Dumb | 60 | 30 | | Ralph Wiggum | 65 | 30 | | Richard Watterson | 70 | 30 | +-------------------+------+-------------+
If we partition the results, this will use the first row in each partition.
Option 5: The LAST_VALUE() Function
The LAST_VALUE() function is similar to the FIRST_VALUE() function, except that it gets its value from the last row in the window frame.
Example:
SELECT
IdiotName,
IQ,
LAST_VALUE( IQ ) OVER win AS "Last Value"
FROM Idiots
WINDOW win AS (
ORDER BY IQ
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);Result:
+-------------------+------+------------+ | IdiotName | IQ | Last Value | +-------------------+------+------------+ | Dumbest | 30 | 70 | | Homer | 40 | 70 | | Patrick Star | 40 | 70 | | Ed | 40 | 70 | | Dumber | 50 | 70 | | Peter Griffin | 50 | 70 | | Cosmo | 55 | 70 | | Dumb | 60 | 70 | | Ralph Wiggum | 65 | 70 | | Richard Watterson | 70 | 70 | +-------------------+------+------------+
Here I used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to expand the range to the end of the result set. Omitting this produces the following result:
SELECT
IdiotName,
IQ,
LAST_VALUE( IQ ) OVER win AS "Last Value"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );Result:
+-------------------+------+------------+ | IdiotName | IQ | Last Value | +-------------------+------+------------+ | Dumbest | 30 | 30 | | Homer | 40 | 40 | | Patrick Star | 40 | 40 | | Ed | 40 | 40 | | Dumber | 50 | 50 | | Peter Griffin | 50 | 50 | | Cosmo | 55 | 55 | | Dumb | 60 | 60 | | Ralph Wiggum | 65 | 65 | | Richard Watterson | 70 | 70 | +-------------------+------+------------+