The LAG() function in MySQL is a powerful tool that allows you to retrieve data from the previous row in your result set. This is particularly useful when you need to calculate changes in values from one row to the next, such as tracking sales changes over consecutive days.

Example of Using LAG() in MySQL

Suppose you have a sales table structured as follows

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 200.00),
('2023-01-04', 250.00),
('2023-01-05', 300.00);

sql
If you want to see the daily sales changes compared to the previous day, you can use the LAG() function as follows

SELECT 
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS previous_amount,
    amount - LAG(amount) OVER (ORDER BY sale_date) AS change_amount
FROM 
    sales;

The result will be

+------------+--------+-----------------+--------------+
| sale_date  | amount | previous_amount | change_amount|
+------------+--------+-------- --------+--------------+
| 2023-01-01 | 100.00 |            NULL |         NULL |
| 2023-01-02 | 150.00 |          100.00 |        50.00 |
| 2023-01-03 | 200.00 |          150.00 |        50.00 |
| 2023-01-04 | 250.00 |          200.00 |        50.00 |
| 2023-01-05 | 300.00 |          250.00 |        50.00 |
+------------+--------+----------------+--------------+

In this example

• LAG(amount) OVER (ORDER BY sale_date) retrieves the amount from the previous row, ordered by sale_date
• amount - LAG(amount) OVER (ORDER BY sale_date) calculates the daily sales change compared to the previous day.

Availability of LAG() in MySQL Versions
The LAG() function is available in MySQL starting from version 8.0. If you are using MySQL 8.0 or later, you can take advantage of this function to simplify your queries and calculations.
For those using versions earlier than 8.0, you would need to use alternative methods such as subqueries or self-joins, which can be more complex and less efficient.
For more information about the LAG() function in MySQL, you can refer to the MySQL 8.0 Reference Manual.