MySQL LAG()

Introdution to MySQL LAG() Function

MySQL LAG() Function is responsible to fetch the result records from a table where it includes the data info of a preceding row accessed from the present row in the same output. A MySQL LAG() function can be defined as a type of Windows function that helps to view back a number of records and to access data values of that row form the existing one. Here, a Windows function is MySQL query that is useful to solve any query problems in easier, new ways along with better performance. Hence, a LAG() function allows retrieving data value from the previous table row that precedes the present row. Its function is unlike MySQL LEAD() function which evaluates to provide data form the row that succeeds in the existing row.

Syntax

MySQL LAG() function has the following syntax structure that illustrates the working of this MySQL function on the server:

LAG(Expr, OffsetValue, DefaultValue) OVER (PARTITION BY expression,…..
ORDER BY Expr [DESC/ASC],….);

Here is the description of terms used in the above syntax for LAG() function in MySQL:

• Expr: This denotes the value to be returned by the LAG() function from the previous row that can be accessed through the current row with the help of a number of rows within the result or partition set.
• OffsetValue: It defines the value either zero or any accurate positive integer that is the number of rows posterior from the current row from which we will receive the value. If the offset value is set to zero then, the LAG() function will execute the value of ‘expr’ for the present row otherwise if it is not specified then, the LAG() function takes the value one by default.
• DefaultValue: The LAG() function uses this default value that is specified to display as a result if there exists no preceding row in the function parameters. Supposing if the offset is set to 2, then, the return value will be the default value for the first row. But if you do not provide default value then, the LAG() function results NULL as a default result.
• OVER: This MySQL function is used with the LAG() function to have partition rows into groups in the query execution. But if LAG() misses OVER() or if it is left empty then, the result computed by LAG() using all table rows.
• PARTITION BY: This MySQL clause is helpful for LAG() function because PARTITION BY is used to divide the table rows in the output set into partitions so that the LAG function is allowed to work properly. If the PARTITION BY is not applied to LAG() function then, the function will suppose the whole output set as one partition.
• ORDER BY: Before the LAG() function works, this ORDER BY clause defines the order of table rows in every partition. We can also add sorting terms used in MySQL for ordering then, we can use ASC and DESC.

How MySQL LAG() Function works?

The MySQL LAG() function is a non-aggregate MySQL window function which is applied to generate the previous value of any table row within its partition. The window function can be said as a MySQL function which implements operations for every record of the partition or called a window. Hence, this result produced for each row when queried which is not likely as aggregate functions in MySQL as this function groups the rows, and the result is displayed in one record row. A current or present row is the row where the operation or query is to occur. A window is termed to a set of rows in a table that is associated with the current one or that allows the function to operate on the current table row. Here, the ‘expr’ in the syntax of LAG() can be any column or a built-in function in MySQL.

Examples to Implement LAG() function in MySQL

Let us consider the following examples to implement a LAG() function in MySQL:

Suppose we have created a Table Persons with fields such as PersonID, PersonName, Address, Salary, JoinDate and also inserted some data values in the sample table. The output of the table is given below:

select * from Persons;

Example #1

Now, first of all, let us query the following statement to get the data from the table ordered by Salary column value firstly:

Code:

SELECT PersonID, Personname, Address, Salary FROM Persons ORDER BY Salary;

Output:

Example #2

For the LAG() function, let us write the query to produce the result where PARTITION is not mentioned, and offset value is set to 1 and default value to 0. The query is as follows where the LAG() function is applied to the column ‘Salary’ form ‘Persons’:

Code:

SELECT PersonID, Personname, Address, Salary, JoinDate, LAG(Salary, 1, 0) OVER( ORDER BY JoinDate ASC) AS Previous_Salary FROM Persons;

Output:

Explanation: As you can see, the first row of LAG() function new column holds the previous value of the row and as we have put offset one, it provides preceding values within the rows for the salary column.

Example #3

Again, let us consider another query where we have added the JoinDate column where the LAG() function is to be operated on, with Offset value as 2 and the default value is not provided. The query is shown below:

Code:

SELECT PersonID, Personname, Address, Salary, JoinDate, LAG(JoinDate, 2) OVER(ORDER BY JoinDate ASC) AS Previous_Date FROM Persons;

Output:

Explanation: We can notice that the first & seconds take the NULL as default values for the Previous_Date column in the result set as no previous rows are available. The offset value is set to 2 so, the Lag function uses 2 previous rows in the result set. But in 3^rdrow it receives value from the first row in the JoinDate column.

Example #4

Now, using PARTITION BY in LAG() and OVER(),  the Address column is grouped and then accordingly the LAG() function is implemented to result in the set as follows:

Code:

SELECT PersonID, Personname, Address, Salary, JoinDate, LAG(Salary,1, 0) OVER( PARTITION BY Address ORDER BY JoinDate ASC) AS Previous_Salary FROM Persons;

Output:

Explanation: The first row of each Address column is set to default value 0 and takes offset 1 to show the previous value in the Previous_Salary column from the person table.

Conclusion

It is noted that we need to use a LAG function with OVER() function, PARTITION BY, etc. in MySQL to run the LAG(), otherwise, it may produce an error on the server. Also, see if any other parameters are not missing in the function syntax code. Thus, the MySQL LAG() function is significant to calculate the difference between the present and previous table rows to produce the desired result.

Recommended Articles

This is a guide to MySQL LAG(). Here we discuss an introduction to MySQL LAG(), syntax, how does it work, and example with code and output. You can also go through our other related articles to learn more –

1. MySQL IN Operator
2. MySQL Subquery
3. MySQL Constraints
4. ANY in MySQL