MySQL LAG Function
Summary: in this tutorial, you will learn how to use the MySQL LAG() function to access data of a previous row from the current row in the same result set.
The LAG() function is a window function that allows you to look back a number of rows and access data of that row from the current row.
The following illustrates the syntax of the LAG() function:
LAG(<expression>[,offset[, default_value]]) OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
Code language: SQL (Structured Query Language) (sql)
expression
The LAG() function returns the value of the expression from the row that precedes the current row by offset number of rows within its partition or result set.
offset
The offset is the number of rows back from the current row from which to get the value. The offset must be zero or a literal positive integer. If offset is zero, then the LAG() function evaluates the expression for the current row. If you don’t specify the offset, then the LAG() function uses one by default.
default_value
If there is no preceding row, then the LAG() function returns the default_value. For example if offset is 2, the return value for the first row is the default_value. If you omit the default_value, the LAG() function returns NULL by default.
PARTITION BY clause
The PARTITION BY clause divides the rows in the result set into partitions to which the LAG() function is applied. If you omit the PARTITION BY clause, the LAG() function will consider the whole result set as a single partition.
ORDER BY clause
The ORDER BY clause specifies the order of rows in each partition before the LAG() function is applied.
The LAG() function is useful for calculating the difference between the current and previous rows.
MySQL LAG() function example
We will use the orders, orderDetails, and productLines tables from the the sample database for the demonstration.
The following statement returns the order value of every product line in a specific year and the previous year:
WITH productline_sales AS (
SELECT productline,
YEAR(orderDate) order_year,
ROUND(SUM(quantityOrdered * priceEach),0) order_value
FROM orders
INNER JOIN orderdetails USING (orderNumber)
INNER JOIN products USING (productCode)
GROUP BY productline, order_year
)
SELECT
productline,
order_year,
order_value,
LAG(order_value, 1) OVER (
PARTITION BY productLine
ORDER BY order_year
) prev_year_order_value
FROM
productline_sales;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this example:
- First, we used a common table expression to get the order value of every product in every year.
- Then, we divided the products using the product lines into partitions, sorted each partition by order year, and applied the
LAG()function to each sorted partition to get the previous year’s order value of each product.
Note that we used the ROUND() function to round the order values to zero decimal places.
In this tutorial, you have learned how to use the MySQL LAG() function to access data of the previous row from the current row.