You can create symbol predicates that compare a row to a previous or subsequent row, using a LAG or LEAD operator.
LAG Expression Syntax
{ current_expr operator LAG (previous_expr, lag_rows [, default]) | LAG (previous_expr, lag_rows [, default]) operator current_expr }
- current_expr
- Name of a column from the current row, or an expression operating on a column from the current row.
- operator
- Either >, >=, <, <=, =, or !=.
- previous_expr
- Name of a column from a previous row, or an expression operating on a column from a previous row.
- lag_rows
- Number of rows to count backward from the current row to reach the previous row. For example, if lag_rows is 1, the previous row is the immediately preceding row.
- default
- Value to use for previous_expr when there is no previous row (that is, when the current row is the first row or there is no row that is lag_rows before the current row).
LAG and LEAD Expression Rules
- A symbol definition can have multiple LAG and LEAD expressions.
- A symbol definition that has a LAG or LEAD expression cannot have an OR operator.
- If a symbol definition has a LAG or LEAD expression and the input is not a table, you must create an alias of the input query, as in LAG and LEAD Expressions Example: Input Query with Alias.