MySQL | LEAD() and LAG() Window Functions

These functions are also known as nonaggregate Window functions.

The LEAD() and LAG() function in MySQL are used to get the preceding and succeeding value of any row within its partition.

Window functions perform operations for each row of its partition/window. These functions produce the result for each row (Unlikely to the aggregate functions that group them and results in a single row).

· The Current row: The row on which operation is performing currently.

· The partition/Window: The set of rows that are related to the current row or using which function operates on the current row is named as partition/window.

The LAG() function:- The LAG() function is used to get a value from a row that precedes the current row.

The LEAD() function:- The Lead() function is used to get a value from a row that succeeds the current row.

Syntax:
For LEAD() function-

LEAD(expr) OVER (Window_specification | Window_name)

LEAD(expr, offset) OVER (Window_specification | Window_name)

LEAD(expr, offset, default) OVER (Window_specification | Window_name)

For LAG() function-

LAG(expr) OVER (Window_specification | Window_name)

LAG(expr, offset) OVER (Window_specification | Window_name)

LAG(expr, offset, default) OVER (Window_specification | Window_name)

The offset and default argument in the function is optional.

Parameters used:

1. expr: It can be a column or any built-in function.

2. offset: It is a positive value that determines the number of rows preceding/succeeding the current row. If it is omitted in the query then its default value is 1.

3. default: It is the default value return by function in-case no row precedes/succeeds the current row by N rows. If it is missing then it is by default NULL.

4. OVER(): It defines how rows are partitioned into groups. If OVER() is empty then function compute result using all rows.

5. Window_specification: It consists of a query partition clause which determines how the query rows are partitioned and ordered.

6. Window_name: If the window is specified elsewhere in the query then it is referenced using this Window_name.

Example

Assume that we have 5 values in the table (A to E) for demo perspective. Please find below the SQL statement for creating this table.

CREATE DATABASE LEAD_LAG;

use LEAD_LAG;

create table ALPHABETS(id CHAR);

insert INTO ALPHABETSVALUES(‘A’), (‘B’), (‘C’), (‘D’),(‘E’);

select * from ALPHABETS;

result as follows:-

Now run the below query

select id,

LEAD(id) OVER() AS expr_LEAD_BY,

LEAD(id,2) OVER() AS offset_LEAD_BY,

LEAD(id,3, “END”) OVER() AS default_LEAD_BY

from ALPHABETS;

The output of this query is as follows

For Lag() run the below SQL

select id,

LAG(id) OVER() AS expr_LAG_BY,

LAG(id,2) OVER() AS offset_LAG_BY,

LAG(id,3, “END”) OVER() AS default_LAG_BY

from ALPHABETS;

The output of this SQL is as follows:

Combining both the SQL into one SQL:

Important Point: The LEAD() and LAG() function are always used with OVER(). If Over() is missing it will raise an error.