mysql
  1. mysql-lead-and-lag-function
PreviousNext

LEAD and LAG Function in MySQL

The LEAD and LAG functions are window functions in MySQL that allow you to access data from a previous or subsequent row within the result set. These functions are useful for calculating running totals, finding the difference between consecutive rows, and more. In this tutorial, we'll go over the syntax and usage of the LEAD and LAG functions in MySQL.

Syntax

The syntax for the LEAD and LAG functions in MySQL is as follows:

LEAD(value_expression[, offset[, default_expression]]) OVER (
  [PARTITION BY partition_expression, ... ]
  ORDER BY sort_expression [ASC | DESC], ...
)

LAG(value_expression[, offset[, default_expression]]) OVER (
  [PARTITION BY partition_expression, ... ]
  ORDER BY sort_expression [ASC | DESC], ...
)

Where:

  • value_expression: the expression to retrieve the value of the current row or a row at a specified offset.
  • offset: the number of rows forward or backward to look up. The default value is 1.
  • default_expression: the value to return if the value of the offset row is null. The default value is NULL.
  • partition_expression: the expression used to partition the result set into smaller subsets.
  • sort_expression: the expression used to sort the rows within each partition.

Example

Suppose that we have the following orders table:

order_id customer_id order_date order_total
1 1 2021-01-01 100
2 1 2021-02-01 150
3 2 2021-03-01 200
4 3 2021-04-01 50

We can use the LAG function to get the difference between the current and previous order totals for each customer:

SELECT order_id, customer_id, order_total,
  (order_total - LAG(order_total) OVER (PARTITION BY customer_id ORDER BY order_date)) AS diff
FROM orders
ORDER BY customer_id, order_date;

The result will look like this:

order_id customer_id order_total diff
1 1 100 NULL
2 1 150 50
3 2 200 NULL
4 3 50 NULL

Explanation

In this example, we used the LAG function with the order_total column to get the difference between the current and previous order totals for each customer. We partitioned the result set by the customer_id column and sorted it by the order_date column. We also specified a default value of NULL for the diff column because there is no previous order total for the first order of each customer.

Use

The LEAD and LAG functions are useful for calculating running totals, finding the difference between consecutive rows, and more. These functions are particularly useful when used with window functions to provide a context for calculations.

Important Points

  • The LEAD function retrieves the value of a subsequent row while the LAG function retrieves the value of a previous row within the result set.
  • The window functions can be partitioned and sorted to provide more context for the calculation.
  • The default offset value is 1, which retrieves the value of the row that is immediately after or before the current one.

Summary

In this tutorial, we went over the syntax and usage of the LEAD and LAG functions in MySQL. These functions allow you to access data from a previous or subsequent row within the result set and are useful for calculating running totals, finding the difference between consecutive rows, and more. By partitioning and sorting the result set, you can provide more context for the calculation.

UUIDCTE
Published on: