LEAD

Description

The LEAD function is a type of window function used to access a row at a specified offset from the current row. By using the LEAD function, you can obtain the value of an expression for a row that is a specified number of rows ahead in the partition. This is very useful in data analysis and processing, such as comparing the data difference between the current row and the next row.

Syntax

LEAD(expr[, offset[, default]]) OVER ([PARTITION BY clause] ORDER BY clause)

Parameters

• expr: An expression of any type, which can be a column name, constant, function, etc.
• offset: Optional parameter, a bigint type constant, default value is 1, indicating the row after the current row. When offset is 0, it indicates the current row itself.
• default: Optional parameter, the default value used when the offset exceeds the boundary of the window. The type is the same as expr, and the default value is null.

Return Result

The return value type is the same as expr.

Usage Example

1. Basic Usage

SELECT a, b, LEAD(b) OVER (PARTITION BY a ORDER BY b) as next_b FROM VALUES ('A', 2), ('A', 1), ('B', 3), ('A', 1) tab(a, b);

Results:

A	1	1
A	1	2
A	2	null
B	3	null

2. Using offset and default parameters

SELECT a, b, LEAD(b, 2, 0) OVER (PARTITION BY a ORDER BY b) as next_b FROM VALUES ('A', 2), ('A', 1), ('B', 3), ('A', 1) tab(a, b);

Results:

A	1	2
A	1	0
A	2	0
B	3	0

Notes

• When the offset value is greater than the number of rows in the window, the LEAD function will return the value specified by default.
• The LEAD function is typically used with the ORDER BY clause of window functions to determine how to access related rows.
• When using the LEAD function, ensure that the PARTITION BY clause is set correctly to partition the data properly.