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.
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.
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 for the demonstration.
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.
Leave a Reply