MySQL LAG() Function
Overview
The MySQL LAG() function is a window function that allows you to recover a column's value from a preceding row in the same result set. This function is very useful for comparing the current row to the preceding row in the result set or when calculating a running total or cumulative sum.
Syntax of MySQL LAG() Function
Let's see the syntax of the MySQL LAG() function.
Now, We will see each of these parameters in detail.
Parameters of MySQL LAG() Function
The function accepts three parameters.
- The first parameter is column_name, which is the name of the column for which you want to obtain the previous row's value.
- The second parameter is offset (optional) which sets the number of rows to look back. If you leave this option out, the function will default to row 1, which means it will go back to the previous row. The third parameter is 'default' (optional) which sets the default value to be returned if the function is unable to locate a prior row. If this parameter is omitted, NULL is returned by default."
- "The default parameter (optional) sets the default value to be returned if the function cannot locate a previous row. If this parameter is omitted, the function defaults to returning NULL.
The LAG() function is used in conjunction with the OVER clause to specify the window or subset of rows that should be used in the calculation. You can use the PARTITION BY clause to group rows, and the ORDER BY clause to indicate the order in which the rows should be evaluated."
- The OVER clause is used to define the partitioning and ordering of the rows that are used in the LAG() function.
- The PARTITION BY clause in MySQL's LAG() function is used to partition the rows in the result set by one or more columns. The LAG() function can then be applied to each division separately.
- In MySQL's LAG() function, the ORDER BY clause specifies the order in which the rows should be processed. The LAG() function can then be applied to each division separately.
Let's understand this with an example. Let's say we have a sales table.
| id | year | quarter | category | amount |
|---|---|---|---|---|
| 1 | 2020 | Q1 | Electronics | 10000 |
| 2 | 2020 | Q2 | Electronics | 12000 |
| 3 | 2020 | Q3 | Clothing | 8000 |
| 4 | 2020 | Q4 | Electronics | 14000 |
| 5 | 2021 | Q1 | Clothing | 9000 |
Now, Let's see how we can use the LAG() function with this table to get the quarter-over-quarter sales growth rate, broken down by category, Below is the query for the same.
In this example, the LAG() function with the PARTITION BY clause is used to group the rows by category, and the ORDER BY clause is used to organize the rows by year and quarter.
The LAG(amount, 1) function call obtains the amount value from the previous row (i.e., the previous quarter's sales), and the amount / LAG(amount, 1) - 1 expression computes the decimal quarter-over-quarter growth rate. To convert the growth rate to a percentage, use the final expression * 100. The output will look something like this.
| year | quarter | category | amount | qoq_growth |
|---|---|---|---|---|
| 2020 | Q1 | Electronics | 10000 | NULL |
| 2020 | Q2 | Electronics | 12000 | 20.00 |
| 2020 | Q3 | Clothing | 8000 | NULL |
| 2020 | Q4 | Electronics | 14000 | 16.67 |
| 2021 | Q1 | Clothing | 9000 | 12.50 |
As you can see, the qoq_growth column now contains the quarter-over-quarter sales growth rate for each quarter, partitioned by category, with the first row for each category having a NULL value because there is no previous quarter to compare it to.
Return Value of MySQL LAG() Function
The MySQL LAG() function returns the value of a given column from a previous row in the result set, based on a specified offset. If the function cannot find a previous row, the default value (if provided) is returned, or NULL is returned by default.
The return value of the LAG() function will depend on the data type of the column that is being queried. For example, if the column being queried is of type INTEGER, the return value will be an integer. Similarly, if the column being queried is of type VARCHAR, the return value will be a string.
Exceptions of MySQL LAG() Function
In normal operation, the MySQL LAG() method does not throw any exceptions. However, there are a few scenarios in which the function may fail to function properly:
- MySQL will throw an error if the column specified in the LAG() method is not found in the table.
- If the LAG() function's offset value is greater than the number of rows in the input table, the function will return NULL for all rows.
- MySQL will generate an error if the column given in the ORDER BY clause is not present in the SELECT statement.
- MySQL may provide unexpected results if the column specified in the ORDER BY clause is of a different data type than the column specified in the LAG() function.
How does the MySQL LAG() Function Work?
"The LAG() function searches back a specified number of rows for each row in the result set. It retrieves the value of the specified column in the previous row. If the function is unable to locate a previous row, it returns the default value (if specified) or NULL.
The LAG() function returns a result set that comprises the original columns as well as an extra column that contains the previous row's value. The return set will have the same number of rows as the input table, less the offset value.
Examples od MySQL LAG() Function
Let's say we have a table called "sales" with the following data:
| id | month | revenue |
|---|---|---|
| 1 | Jan | 100 |
| 2 | Feb | 150 |
| 3 | Mar | 200 |
Example 1: Basic LAG() function usage
The output will be
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | NULL |
| 2 | Feb | 150 | 100 |
| 3 | Mar | 200 | 150 |
In this example, we are using the LAG() function to retrieve the value of the "revenue" column from the previous row, based on the order of the "month" column. The result set includes an additional column called "prev_revenue" that holds the value of the previous row's revenue.
Example 2: LAG() function with offset
The output will be
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | NULL |
| 2 | Feb | 150 | NULL |
| 3 | Mar | 200 | 100 |
In this example, we're going to use the LAG() method with an offset of 2 to get the value of the "revenue" column from two rows ago. The result set includes an additional column called "prev_revenue" that contains the revenue figure from two rows ago.
Example 3: LAG() function with default value
The output will be
| id | month | revenue | prev_revenue |
|---|---|---|---|
| 1 | Jan | 100 | 0 |
| 2 | Feb | 150 | 100 |
| 3 | Mar | 200 | 150 |
In this example, we're using the LAG() method with a default value of 0 to return 0 if the function can't find the previous row. The result set includes an additional column named "prev_revenue" that contains the revenue value from the prior row, or 0 if no previous row exists.
Conclusion
- The MySQL LAG() function is a window function that retrieves the value of a column from a previous row in the same result set.
- The function takes three parameters: the column whose value you wish to get from the previous row, an offset (optional), and a default (optional).
- The LAG() function returns a value according to the data type of the column being searched.
- If the column supplied in the LAG() method is not found in the table, or if the offset value of the LAG() function is greater than the number of rows in the input table, the function may fail to work properly.