DEFINE clause for MATCH_RECOGNIZE

Applies to: Databricks SQL Databricks Runtime 19.0 and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Defines the boolean variables that the PATTERN and MEASURES clauses of MATCH_RECOGNIZE reference and test.

Syntax

DEFINE { rowPatternVar AS booleanExpr } [, ...]

Parameters

• rowPatternVar

  An identifier that is unique within the DEFINE clause. If you reuse a name, Databricks raises MATCH_RECOGNIZE_DUPLICATE_PATTERN_VARIABLE.

• booleanExpr

  A boolean expression that does not contain a subquery, aggregate function, or window function, and does not reference a column or column alias other than those from the table_reference that precedes the MATCH_RECOGNIZE clause.

  Column references can use the following functions specific to the DEFINE clause:

  • first()
  • last()
  • prev()
  • next()

  A column reference can be qualified by a row pattern variable name (rowPatternVar.colName). This selects values from rows mapped to that variable in the current match. For example, LAST(down.tstamp) is the timestamp of the last row classified as down. Unqualified column references refer to the current row in the match without selecting a specific pattern variable.

  If a row pattern variable is not yet matched, the qualified column reference evaluates to NULL.

  When rowPatternVar names the pattern variable currently being tested for the row, rowPatternVar.colName refers to that row's column value and is equivalent to an unqualified colName. When rowPatternVar names a different pattern variable, rowPatternVar.colName reads values from rows already mapped to that variable.

  Nondeterministic functions such as rand() and uuid() are not allowed in DEFINE predicates because NFA simulation can evaluate predicates multiple times per row. If you use one, Databricks raises MATCH_RECOGNIZE_NONDETERMINISTIC_DEFINE.

  General aggregate functions other than FIRST() and LAST() are not supported in DEFINE. If you use one, Databricks raises MATCH_RECOGNIZE_DEFINE_UNSUPPORTED_AGGREGATE.

Common error conditions

• MATCH_RECOGNIZE_DEFINE_UNSUPPORTED_AGGREGATE
• MATCH_RECOGNIZE_DUPLICATE_PATTERN_VARIABLE
• MATCH_RECOGNIZE_NONDETERMINISTIC_DEFINE

Related articles

• MATCH_RECOGNIZE clause
• MEASURES clause for MATCH_RECOGNIZE
• PATTERN clause for MATCH_RECOGNIZE
• MATCH_RECOGNIZE functions