MySQL LEAD function is very similar to the LAG function. We can think of the LEAD function as contradictory to the LAG funtion. In this tutorial, we will see everything about the LEAD function. We will start with the introduction and then proceed to the syntax and example to implement the LEAD function.
Read: MySQL Window Functions – MySQL LAG Function
Introduction to MySQL LEAD Function
The LEAD window function is used, as its title suggests, to obtain the result of the expression from the N rows next to the current row inside the same partition. Note that the same partitions are utilised with the window functions. For example, the same city, the same year, the same class, etc.
The method returns the default value if the leading row is absent. For instance, if N is 2, the method will return the default value for the last two rows. If either the N or default value is absent, the default value is NULL and the N is 1.
Note that, the N must be greater than 0. If the N is set to 0, the expression will be calculated for the current row.
From the MySQL Version 8.0.22, the N can not be empty and must be an positive integer.
Also read: Introduction to Mysql Window Functions
MySQL LEAD Function Syntax
The following syntax shows the basic structure of the LEAD function.
LEAD(expression, N, default_value)
OVER
(partition_code ...,
ORDER BY ...
)Code language: SQL (Structured Query Language) (sql)Where,
- expression is used to calculate the result from the N leading rows of the same partition which is then returned by the function.
- N is the number of rows that will be counted from the current row. As stated above, the N must be a positive integer and it is no longer an optional parameter.
- default_value is a value which is returned by the function if there is no leading row present. For example, if the N is 2, then the default value is returned for the last two rows. If no default value is specified, NULL is returned.
- partition_code contains the PARTITION BY clause to divide the similar rows.
- the ORDER BY clause is used to sort the rows in each partition before the LAG function is applied
MySQL LEAD Function Example
We need a table with some rows in it so that we can show how the LEAD function works. Therefore, let’s create a table called “sales” and add the data of total sales for each product and each year.
CREATE TABLE sales(
id INT AUTO_INCREMENT PRIMARY KEY,
year YEAR,
product VARCHAR(100),
sale FLOAT
);Code language: SQL (Structured Query Language) (sql)INSERT INTO sales(year,product,sale)
values(2018,"lenovo",240000),(2019,"lenovo",201881),
(2020,"lenovo",728170),(2021,"lenovo",82000),
(2018,"redMI",670000),(2019,"redMI",872000),(2020,"redMI",72600),
(2017,"nokia",100000),(2018,"nokia",728100),(2019,"nokia",721000),
(2020,"nokia",89000);Code language: SQL (Structured Query Language) (sql)Let’s display the table data to see if values are correctly inserted!
SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)
Perfect! Let’s now write a query to demonstrate the use of the LEAD function.
Here, we will display the sales of the next year for the same product. For the last year of each product, the function will return the default value as there is no leading row present.
For example, if Nokia has sales worth 100 in 2018, then the query will show the 100 for the 2017 year record.
SELECT year, product, sale,
LEAD(sale,1,0)
OVER(PARTITION BY product ORDER BY year) AS
pre_sale FROM sales;Code language: SQL (Structured Query Language) (sql)Here, we have used the product column to create a partition. Therefore, we will get the result for each product partition. Let’s see the output.
As you can see, the partition is made using the product column values.
Let’s write a LEAD function to create a partition by the year and see the output.
SELECT year, product, sale,
LEAD(sale,1,0)
OVER(PARTITION BY year ORDER
BY year) AS pre_sale FROM sales;Code language: SQL (Structured Query Language) (sql)Now we will see the partition by the year column values. That means we will get the output records which are grouped together by the year.
As you can see, the partitions are made using the year column values.
Summary
We have learnt about the LEAD window’s features in this tutorial. It is very similar to the LAG window function and works completely opposite. Because they are window functions, the LAG and LEAD functions cannot be utilised without the OVER clause. Complex business logic may be written using the LAG and LEAD functions with CTEs. You can learn about the MySQL CTE from here.
References
MySQL official documentation on LEAD function.