MySQL LEAD and LAG functions are one of the most crucial window functions that are used to implement complex queries. In this tutorial, we will learn about the LAG function. We will understand what a LAG function is, why we need it and how to implement it. So, let’s get started!
Also read: Introduction to Mysql Window Functions
Introduction to MySQL LAG Function
As the name suggests, the LAG window function is used to get the result of the expression from the N previous rows from the current row within the same partition. Note that, the window functions are used for the same partitions. For example, the same city, the same year, the same class, etc.
If there is no previous row present, the function will return the default value. For example, if the N is 2, then the function will return the default value for the first two rows. If the N or default value is missing, then the N is 1 and the default value is NULL.
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.
Let’s see the syntax of the LAG function now.
MySQL LAG Function Syntax
The following syntax shows the basic structure of a LAG function.
LAG(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 previous 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 row present in the previous. For example, if the N is 2, then the default value is returned for the first 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.
Let’s now take an example to understand how to implement the LAG function and how it works.
MySQL LAG Function Example
To demonstrate the LAG function, we need a table with some records in it. So, let’s create a table ‘sales’ and insert the records of total sales made each year for each product.
CREATE TABLE sales(
id INT AUTO_INCREMENT PRIMARY KEY,
year YEAR,
product VARCHAR(100),
sale FLOAT
);
Code language: SQL (Structured Query Language) (sql)Now, let’s insert some values into it.
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 to check if the values are inserted correctly.
SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)
Perfect! Let’s now jump on to the LAG function.
Here, we will write a query which will display the sales of the previous year for the same product. For the first year of each product, we will get the default value as 0 because there is no previous record.
For example, if Lenovo has sales worth 100 in 2018, then the query will show the 100 for the next year’s record, i.e, for the year 2019.
Let’s write a query and check the output.
SELECT year, product, sale,
LAG(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 created a query in which we are defining three parameters to the LAG function. The first parameter is the column name of the table. The second parameter is the number of rows to look back and the third parameter is the default value if there is no previous row to evaluate the expression.
As you can see, we got the perfect output.
In the previous query, we used the product column for the partition. Now we will use the year as a partition column and check the output.
SELECT year, product, sale,
LAG(sale,1,0)
OVER(PARTITION BY year ORDER
BY year) AS pre_sale FROM sales;Code language: SQL (Structured Query Language) (sql)In the output, we will get the partitions by the year and not by the product, unlike the previous example.
As you can see, the partitions are made based on the year.
Summary
In this tutorial, we have learned about the LAG window functions. It is very similar and opposite to the LEAD window function. Note that, LAG and LEAD functions can not be used without the OVER clause because these are the window functions. The LAG and LEAD functions can be used with the CTEs also to write complex business logic. You can learn about the MySQL CTE from here.
References
MySQL Official Documentation on LAG function.