MySQL LAG() Function

MySQL version 8.0 introduced the MySQL window functions, allowing you to perform queries in an easier and organized method, thereby increasing processing and performance. Such functions include: RANK() ROW_RANK(), LAST_VALUE() and many more.

In this tutorial, we shall focus on using one of the MySQL functions: LAG(), a window function that allows you to access and fetch the value of previous rows from the current row within the same result set.

Basic Syntax

The general syntax for using MySQL LAG() function is:

LAG(expr ,offset, default_value) OVER (
  PARTITION BY [expr]
  ORDER BY expr [ASC|DESC]
)

Let us take a moment to explain some of the parameters in the LAG() function syntax. They are as follows:

1. Expr: This is the value returned by the function from the row that leads the current row by the offset value specified.
2. Offset: This represents the number of rows back from the current row from which to get the value. This value must be a 0 or a value higher than 0. NOTE: The value of 0 represents the current row.
3. Default_value: This is returned as the default value by the function if no preceding row exists. If the default value is undefined in the function parameter and no preceding row exists, the function returns a NULL value.
4. Partition by: The partition by clause divides the rows in the result array into various partitions. The lag function is then applied to the divided partitions.
5. Order by: As usual, it specifies the order of the rows in the available partitions.

Example Use Cases

Let me illustrate how the LAG function works using an example. In this example, I will demonstrate how to use the LAG function using the sample Sakila database. The resource to download the database is below:

MySQL :: Other MySQL Documentation

Consider the payment table in the Sakila database. We can get specific columns and order the result based on the amount as shown in the query below:

SELECT
	payment_id,
	rental_id,
	amount
FROM
	sakila.payment
WHERE
	amount > 0
ORDER BY
	amount;

Output:

We can apply the LAG function as shown in the query below:

SELECT
	payment_id,
	rental_id,
	LAG(amount, 1) OVER (PARTITION BY payment_date ORDER BY amount ASC) AS prev_amnt
FROM
	sakila.payment;

This will give an output similar to the one shown below:

In the output above, we divided the result set by the payment_date and then ordered the values in each partition by the amount. We then finished by applying the LAG function to the partitions, which gives us the previous amount details.

NOTE: Since we did not define the default_value, the function returns a null value, as seen in the table above.

Conclusion

This tutorial has discussed how the LAG() function works to get values of the previous rows in the current row.