LEAD
Returns an expression evaluated at some offset after the current row.
If there is no such row returns NULL
instead.
Syntax
LEAD ( expression [, offset]) OVER ( [PARTITION BY (col | expr), ...] [ORDER BY (col | expr), ...] )
Arguments
expression: any expression. This may be a column name, the result of another function, or a math operation
offset: A constant integer that specifies the number of rows after the current row to evaluate the expression. Defaults to 1 if not specified
Return Type
The result of expression
Examples
CREATE TABLE lead_example (a int, b int); INSERT INTO lead_example VALUES(1,2),(2,2),(3,3),(4,3);
SELECT a, LEAD(a) OVER(ORDER BY (a)) FROM lead_example; **** +------+----------------------------+ | a | LEAD(a) OVER(ORDER BY (a)) | +------+----------------------------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | | 4 | NULL | +------+----------------------------+
SELECT a, LEAD(a,2) OVER(ORDER BY (a)) FROM lead_example; **** +------+------------------------------+ | a | LEAD(a,2) OVER(ORDER BY (a)) | +------+------------------------------+ | 1 | 3 | | 2 | 4 | | 3 | NULL | | 4 | NULL | +------+------------------------------+