LEAD() OVER ...
All functions > WINDOW FUNCTION > LEAD() OVER ...
Returns the value from the row that leads (follows) the current row by a specified offset within the result set partition.
Syntax
Diagram(
Sequence(
Terminal("LEAD"),
Terminal("("),NonTerminal('expr'), NonTerminal('offset'), NonTerminal('default_value'),Terminal(")"),
Terminal("OVER"),
Terminal("("),
NonTerminal("over_clause"),
Terminal(")"),
)
)| Parameter | Type | Required | Description |
|---|---|---|---|
expr | EXPRESSION | Yes | Value expression to return |
offset | BIGINT | No | Offset to lead/lag |
default_value | EXPRESSION | No | Default value if offset is out of bounds |
over_clause | OVER CLAUSE | Yes | OVER (PARTITION BY ... ORDER BY ... [FRAME ...]) |
Notes
- Accesses data from a subsequent row in the same result set
- First expression is the value to return
- Second expression (optional) is the offset (number of rows forward, default 1)
- Third expression (optional) is the default value if offset exceeds partition bounds
- Requires ORDER BY clause to determine row ordering
- PARTITION BY divides rows into groups
- Window frame can further restrict the set of rows considered
- Useful for comparing current row with future rows