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() the function is a window function that allows you to look back at 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],...
)

expression

The LAG() the function returns the value  expression from the row that precedes the current row by offset a 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. It 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 the 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() the function will consider the whole result set as a single partition.

ORDER BY clause

The ORDER BY the clause specifies the order of rows in each partition before the LAG() function is applied.

The LAG() the 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;

Here is the output:

In this example:

• First, we used a common table expression to get the order value of every product 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.