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.
Introduction to MySQL LAG() function
The LAG() function is a window function that allows you to access data from a previous row in a result set from the current row without using a self-join.
Here’s the basic syntax of the LAG() function:
LAG(expression,offset, default_value)
OVER (
PARTITION BY partition_expression
ORDER BY order_expresion ASC|DESC
)Code language: SQL (Structured Query Language) (sql)expression
The expression is a column or an expression from which you want to retrieve the previous value.
offset
The offset is the number of rows to go back from the current row. The offset must be zero or a positive integer number.
If offset is zero, then the LAG() function returns the current row. If you don’t provide the offset argument, it defaults to 1.
default_value
If there is no preceding row, then the LAG() function returns the default_value. If you omit the default_value, the LAG() function will return NULL.
PARTITION BY clause
The PARTITION BY clause divides the result set into partitions to which the LAG() function is applied independently.
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 can be useful for calculating the difference between the current and previous rows.
MySQL LAG() function examples
Let’s take some examples of using the LAG() function. We’ll use the following sales table for demonstration:
CREATE TABLE sales(
sales_employee VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
sale DECIMAL(14,2) NOT NULL,
PRIMARY KEY(sales_employee,fiscal_year)
);
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
('Bob',2017,150),
('Bob',2018,200),
('Alice',2016,150),
('Alice',2017,100),
('Alice',2018,200),
('John',2016,200),
('John',2017,150),
('John',2018,250);
SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)Output:
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.00 sec)Code language: JavaScript (javascript)1) Basic MySQL LAG() function example
The following query uses the LAG function to compare the sales of a year with the previous one:
SELECT
sales_employee,
fiscal_year,
sale,
LAG(sale, 1 , 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) 'previous year sale'
FROM
sales;Code language: SQL (Structured Query Language) (sql)Output:
+----------------+-------------+--------+--------------------+
| sales_employee | fiscal_year | sale | previous year sale |
+----------------+-------------+--------+--------------------+
| Alice | 2016 | 150.00 | 0.00 |
| Alice | 2017 | 100.00 | 150.00 |
| Alice | 2018 | 200.00 | 100.00 |
| Bob | 2016 | 100.00 | 0.00 |
| Bob | 2017 | 150.00 | 100.00 |
| Bob | 2018 | 200.00 | 150.00 |
| John | 2016 | 200.00 | 0.00 |
| John | 2017 | 150.00 | 200.00 |
| John | 2018 | 250.00 | 150.00 |
+----------------+-------------+--------+--------------------+
9 rows in set (0.00 sec)Code language: JavaScript (javascript)How it works.
The LAG() function divides the rows in the sales table by sales employees into partitions. Since we have three sales employees, it creates three partitions:
PARTITION BY sales_employee Code language: SQL (Structured Query Language) (sql)In each partition, the LAG() function sorts the rows by fiscal years. Hence, the rows in each partition are sorted by fiscal year column:
ORDER BY fiscal_yearCode language: SQL (Structured Query Language) (sql)For each row in a partition, the LAG() function returns the value in the sale column of the previous row. If there is no previous row, it returns 0 as we specify in the default_value argument of the LAG() function:
LAG(sale, 1 , 0)Code language: SQL (Structured Query Language) (sql)As a result, the LAG() function returns the sales of the previous year (or zero) from the current row.
2) Using multiple LAG functions
To compare the sales of the “current” year with the previous year, you can use an additional LAG() function as follows:
SELECT
sales_employee,
fiscal_year,
sale,
LAG(sale, 1, 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS previous_year_sale,
sale - LAG(sale, 1, 0) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS vs_previous_year
FROM
sales;Code language: SQL (Structured Query Language) (sql)Output:
+----------------+-------------+--------+--------------------+------------------+
| sales_employee | fiscal_year | sale | previous_year_sale | vs_previous_year |
+----------------+-------------+--------+--------------------+------------------+
| Alice | 2016 | 150.00 | 0.00 | 150.00 |
| Alice | 2017 | 100.00 | 150.00 | -50.00 |
| Alice | 2018 | 200.00 | 100.00 | 100.00 |
| Bob | 2016 | 100.00 | 0.00 | 100.00 |
| Bob | 2017 | 150.00 | 100.00 | 50.00 |
| Bob | 2018 | 200.00 | 150.00 | 50.00 |
| John | 2016 | 200.00 | 0.00 | 200.00 |
| John | 2017 | 150.00 | 200.00 | -50.00 |
| John | 2018 | 250.00 | 150.00 | 100.00 |
+----------------+-------------+--------+--------------------+------------------+
9 rows in set (0.00 sec)Code language: JavaScript (javascript)Summary
- Use the MySQL
LAG()function to access data of the previous row from the current row.
