lag analytic window function (Databricks SQL)

Returns the value of expr from a preceding row within the partition.

Syntax

lag( expr [, offset [, default] ] )

Arguments

  • expr: An expression of any type.
  • offset: An optional INTEGER literal specifying the offset.
  • default: An expression of the same type as expr.

Returns

The result type matches expr.

If offset is positive the value originates from the row preceding 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 following 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. You must provide an ORDER BY clause (Databricks SQL).

This function is a synonym to lead(expr, -offset, default).

Examples

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