lead analytic window function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the value of expr from a subsequent row within the partition. This function is a synonym to lag(expr, -offset, default).

Syntax

lead(expr [, offset [, default] ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER clause

Arguments

  • expr: An expression of any type.
  • offset: An optional INTEGER literal specifying the offset.
  • default: An expression of the same type as expr.
  • IGNORE NULLS or RESPECT NULLS: When IGNORE NULLS is specified, any expr value that is NULL is ignored. The default is RESPECT NULLS.
  • OVER clause: The clause describing the windowing. See: Window functions.

Returns

The result type matches expr.

If offset is positive the value originates from the row following the current row by offset specified the ORDER BY in the OVER clause. An offset of 0 uses the current row's value. A negative offset uses the value from a row preceding the current row. If you do not specify offset it defaults to 1, the immediately following row.

If there is no row at the specified offset within the partition the specified default is used. The default default is NULL. An ORDER BY clause must be provided.

Examples

> SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b)
    FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   2
 A1 2   NULL
 A2 3   NULL