Summary: in this tutorial, you will learn how to use the MySQL LEAD() function to access data from the next row in the result set
Introduction to MySQL LEAD() function
The LEAD() function is a window function that allows you to access data from the next row in a result set
Here’s the basic syntax of the LEAD() function:
LEAD(expression, offset, default_value)
OVER (
PARTITION BY partition_expression
ORDER BY sort_expression
)Code language: SQL (Structured Query Language) (sql)In this syntax:
expression
This is the column or expression from which you want to retrieve the next value.
offset
The offset specifies the number of rows to look ahead. If you skip it, it defaults to 1, which is the immediate row.
default_value
This is the default value if there is no next row. For example, the last row in the result set (or in a partition) will not have the next row.
If you don’t specify the default_value, it’ll default to NULL.
PARTITION BY partition_expression
The PARTITION BY is an optional clause that divides the result set into partitions to which the LEAD() function is applied independently.
ORDER BY order_expression
The ORDER BY clause specifies the order in which the rows are processed within each partition.
The LEAD() function can be useful for queries like finding the next value in a sequence or calculating the differences between the current and the next values in a column.
MySQL LEAD() function examples
Let’s take some examples of using the LEAD() function. We’ll use the following sales table for the 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: PHP (php)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 |
+----------------+-------------+--------+1) Basic MySQL LEAD() function example
The following example uses the LEAD() function to pull the sales of the next row into the current row:
SELECT
sales_employee,
fiscal_year,
sale,
LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS next_year_sale
FROM
sales;Code language: SQL (Structured Query Language) (sql)Output:
+----------------+-------------+--------+----------------+
| sales_employee | fiscal_year | sale | next_year_sale |
+----------------+-------------+--------+----------------+
| Alice | 2016 | 150.00 | 100.00 |
| Alice | 2017 | 100.00 | 200.00 |
| Alice | 2018 | 200.00 | NULL |
| Bob | 2016 | 100.00 | 150.00 |
| Bob | 2017 | 150.00 | 200.00 |
| Bob | 2018 | 200.00 | NULL |
| John | 2016 | 200.00 | 150.00 |
| John | 2017 | 150.00 | 250.00 |
| John | 2018 | 250.00 | NULL |
+----------------+-------------+--------+----------------+
9 rows in set (0.00 sec)Code language: plaintext (plaintext)How it works.
First, the PARTITION BY sales_employee divides the rows in the table into three partitions, each representing the sales of one sales employee.
Second, the ORDER BY fiscal_year sorts the rows in each partition by fiscal year in ascending order.
Therefore, the LEAD() function returns the sales of the next year from the current year for each sales employee. If there is no next year such as for the year 2018, the LEAD() function returns NULL.
2) Using multiple LEAD() functions in a query
The following query uses multiple LEAD() functions to calculate the sales vs. next year’s in percentage:
SELECT
sales_employee,
fiscal_year,
sale,
LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year
) AS next_year_sale,
ROUND(sale - (LEAD(sale) OVER (
PARTITION BY sales_employee
ORDER BY fiscal_year)) / sale * 100, 2) 'vs_next_year (%)'
FROM
sales;Code language: PHP (php)Output:
+----------------+-------------+--------+----------------+------------------+
| sales_employee | fiscal_year | sale | next_year_sale | vs_next_year (%) |
+----------------+-------------+--------+----------------+------------------+
| Alice | 2016 | 150.00 | 100.00 | 83.33 |
| Alice | 2017 | 100.00 | 200.00 | -100.00 |
| Alice | 2018 | 200.00 | NULL | NULL |
| Bob | 2016 | 100.00 | 150.00 | -50.00 |
| Bob | 2017 | 150.00 | 200.00 | 16.67 |
| Bob | 2018 | 200.00 | NULL | NULL |
| John | 2016 | 200.00 | 150.00 | 125.00 |
| John | 2017 | 150.00 | 250.00 | -16.67 |
| John | 2018 | 250.00 | NULL | NULL |
+----------------+-------------+--------+----------------+------------------+
9 rows in set (0.00 sec)Code language: PHP (php)In this example, we added a new column named vs_next_year (%). This column calculates the percentage change from the current year’s sales to the next year’s sales using the formula:
( current year's sale - next year's sale ) x 100 / next year's saleCode language: JavaScript (javascript)This expression calculates the percentage change and assigns it to the vs_next_year (%)
Summary
- Use the MySQL
LEAD()function to retrieve the value from the next row in a specified sequence within a partitioned result set.
