LAG/LEAD Function Syntax | Teradata Vantage - LAG/LEAD Function Syntax - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
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.