LAG/LEAD Function Syntax | Teradata Vantage - LAG/LEAD Function Syntax - Analytics Database - Teradata Vantage

ANSI

{ LAG | LEAD } ( value_expression [, offset_spec ] ] )
  [ { RESPECT | IGNORE } NULLS ]
  OVER ( [ PARTITION BY expression ] [ order_by_clause ] )

offset_spec

[ offset_value ] [, [ default_value_expression ] ]

order_by_clause

ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
  [ RESET WHEN expression ]

Teradata

{ LAG | LEAD }
  ( value_expression [ { IGNORE | RESPECT } NULLS ] [, offset_spec ] ] )
  OVER ( [ PARTITION BY expression ] [ order_by_clause ] )

offset_spec

[ offset_value ] [, [ default_value_expression ] ]

order_by_clause

ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
  [ RESET WHEN condition ]

Syntax Elements

value_expression

The expression cannot contain any ordered analytical functions.

value_expression is mandatory and can be any expression that returns a scalar value. It cannot be a table function.

offset_value

A literal unsigned integer value between 0 and 4096. If not specified, the default value is 1.

offset_value specifies the physical row position relative to the current row in a given window of rows. The row position is the row following the current row for the LEAD function, and the preceding row for the LAG function.

An offset_value of 0 specifies the current row.

default_value_expression

Any expression that returns a scalar value.

If not specified, the value is assumed to be NULL.

When running in ANSI mode, the default_value_expression data type must match value_expression. An error occurs if the data types do not match.

In Teradata mode, the database attempts to match the default_value_expression data type to value_expression by doing a cast to value_expression data type to execute the query. If there are casting rule violations, Vantage displays an error message.

IGNORE NULLS

If value_expression returns a NULL value where the preceding or following row, as determined by the specified offset_value, is within the scope of the window group, LAG or LEAD ignores the NULL value.

LAG or LEAD then continues searching for the non-NULL value_expression in the preceding or following row, which may be far from the current row but within the scope of the window group. The search terminates at the window boundaries:

• For LAG, the search terminates at the first row of the window group.
• For LEAD, the search terminates at the last row of the window group.

At the end of the search, LAG or LEAD returns default_value_expression if no non-NULL value_expression is found.

If the preceding or following row is outside the scope of the window group, LAG or LEAD returns default_value_expression.

If the optional NULL clause is not specified, the default option is RESPECT NULLS.

RESPECT NULLS

If the preceding or following row determined by offset_value is within the scope of the window group, and if the value_expression evaluation returns a NULL, LAG or LEAD returns NULL. This setting indicates that the NULL value is not ignored.

If the preceding or following row is outside the scope of the window group, LAG or LEAD returns default_value_expression.

If the optional NULL clause is not specified, the default option is RESPECT NULLS.

OVER

Specifies how values are grouped, ordered, and considered while computing the LAG or LEAD function.

Values are grouped by the optional PARTITION BY clause and the optional RESET WHEN clause. Values are sorted according to the ORDER BY clause in a given partition of rows.

PARTITION BY expression

The group or groups over which the function operates.

This is a comma-separated value expression list.

ORDER BY expression

The order in which the values in a group or partition are sorted.

This is a comma-separated value expression list.

ASC

That the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

Descending sort order.

NULLS FIRST

NULL results are to be listed first.

NULLS LAST

NULL results are to be listed last.

RESET WHEN condition

The group, or groups, over which the function operates, depending on the evaluation of the specified condition. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition.

If there are no RESET WHEN or PARTITION BY clauses, then the entire result set constitutes a single partition.