MySQL LEAD and LAG Function

If your interested to learn about the MySQL UUID

The LEAD and LAG is a window function in MySQL used to access the preceding and succeeding value of specified rows from the current row within its partition. These functions are the kind of non-aggregate function. The Window functions in MySQL are used to perform operations or calculations for each row within the partition or window. These functions produce the result similar to a calculation done by using the aggregate functions. But, unlike aggregate functions that perform operations on an entire table, window functions do not produce a result to be grouped into a single row. Therefore each row maintains the unique identities. In the window function, we must know about these things:

• The row on which function evaluation occurs is termed as the current row.
• A window is the set of rows related to the current row or using which function operates on that row.

Let us learn about these functions in detail.

MySQL LEAD Function

This function allows us to look forward rows or succeeding rows to get/access the value of that row from the current row. It is a very useful method to calculate the difference between the current and subsequent rows within the same output. Below is the general syntax to use the LEAD function in MySQL:

LEAD(expression, offset , default_value) OVER (  
    PARTITION BY (expr)  
    ORDER BY (expr)  
)  

Parameter Explanation

The LEAD function syntax contains the following parameters.

Parameter	Descriptions
expression	It is a column name or any built-in function whose value return by the function.
offset	It contains the number of rows succeeding from the current row. It should be a positive integer value. If it is zero, the function evaluates the result for the current row. If we omit this, the function uses 1 by default.
default_value	It is a value that will return when we have no subsequent row from the current row. If we omit this, the function returns the null value.
OVER	OVER It is responsible for partitioning rows into groups. If it is empty, the function performs an operation using all rows.
PARTITION BY	It split the rows in the result set into partitions to which a function is applied. If we have not specified this clause, all rows treated as a single row in the result set.
ORDER BY	It determines the sequence of rows in the partitions before the function is applied.

MySQL LEAD() Function Example

Here, we are going to understand how the LEAD function works with the MySQL table. First, we need to create a table named sales_table using the below statement.

CREATE TABLE sales_table (  
    Employee_Name VARCHAR(45) NOT NULL,  
    Year INT NOT NULL,  
    Country VARCHAR(45) NOT NULL,  
    Product VARCHAR(45) NOT NULL,  
    Sale DECIMAL(12,2) NOT NULL,  
    PRIMARY KEY(Employee_Name, Year)    
);  

Next, we will add records into this table using the INSERT statement as follows:

INSERT INTO sales_table VALUES  
('Stephen', 2017, 'India', 'Laptop', 10000),    
('Stephen', 2018, 'India', 'Laptop', 15000),    
('Stephen', 2019, 'India', 'TV', 20000),    
('Bob', 2017, 'US', 'Computer', 15000),    
('Bob', 2018, 'US', 'Computer', 10000),    
('Bob', 2019, 'US', 'TV', 20000),    
('Mandy', 2017, 'Canada', 'Mobile', 20000),    
('Mandy', 2018, 'Canada', 'Calculator', 1500),    
('Mandy', 2019, 'Canada', 'Mobile', 25000);  

We can verify the records into a table using the SELECT statement. It will give the output as below:

The following statement finds the sale and next sales detail of each employee:

SELECT Year, Product, Sale,     
LEAD(Sale,1) OVER (  
PARTITION BY Year  
ORDER BY Country) AS Next_Sale    
FROM sales_table;

This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LEAD() function on each partition to get the next sales detail. The below output explains it more clearly:

In the output, we can see the null value in each partition. When the succeeding row crosses the partition boundary, the next value in each partition’s last row always becomes NULL.

Lead function with a default value

Let’s specify a default value for the argument default and rerun the query.

1234	SELECT *,        LEAD(JoiningDate, 1,0) OVER(       ORDER BY JoiningDate ASC) AS EndDateFROM @Employee;

We get an error message – Operand type clash: int is incompatible with date. In the table variable, we have the JoiningDate column defined as Date. We also applied the SQL Server Lead function on the date data type. We cannot use an integer data type value for the date data type. Due to this, we get this error message of the incompatible data type. Let’s specify the default date in this argument and rerun the query.

1234	SELECT *,        LEAD(JoiningDate, 1,’2018-01-01′) OVER(       ORDER BY JoiningDate ASC) AS EndDateFROM @Employee;

In this output, we get the specified default date instead of the NULL value.

MySQL LAG Function

This function allows us to look information about backword rows or preceding rows to get/access the value of a previous row from the current row. It is a very useful method to calculate the difference between the current and the previous row within the same result set. MySQL LEAD and LAG Function Below is the general syntax to use the LAG function in MySQL:

LAG (expression, offset , default_value) OVER (  
    PARTITION BY (expr)  
    ORDER BY (expr [ASC|DESC])  
)  

Parameter Explanation

The LAG function syntax contains the following parameters.

Parameter	Descriptions
expression	It is a column name or any built-in function.
offset	It contains the number of rows preceding from the current row. It should be zero or any positive integer value. If it is zero, the function evaluates the result for the current row. If we omit this, the function uses 1 by default.
default_value	It is a value that will return when we have no preceding row from the current row. If we omit this, the function returns the null value.

The other parameters such as OVER, PARTITION BY, ORDER BY clause meaning are the same as the LEAD function.

MySQL LAG() Function Example

Here, we are going to understand how the LAG function works with the MySQL table. We can use the above table named sales_table . The following statement finds the sale and previous sales detail of each employee:

SELECT Year, Product, Sale,     
LAG(Sale, 1, 0) OVER (  
PARTITION BY Year  
ORDER BY Country) AS Previous_Sale_LAG  
FROM sales_table;  

This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LAG() function on each partition to get the previous sales detail. After execution of the above statement, we can see the below output:

In the output, we can see the 0.00 value in each partition. It indicates the value of the row that will not exist in a table. If we have not provided the default value, it will become NULL.

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:

<code>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><small>Code language: SQL (Structured Query Language) (sql)</small>

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.

NOTE: The LEAD() and LAG() function are introduced in MySQL version 8.0. So we cannot use it in the previous versions. They are always used with the OVER() clause. If we have not used this clause, it will raise an error.