PATTERN clause for MATCH_RECOGNIZE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19.0 and above

Important

This feature is in Beta.

Specifies the pattern to match in a MATCH_RECOGNIZE operation.

Syntax

PATTERN ( row_pattern )

row_pattern
  { { pattern_primary | pattern_operator } [ pattern_quantifier ] }

pattern_primary
  { variable
  | $
  | ^ }

pattern_operator
  { ( [ row_pattern ] )
  | PERMUTE ( row_pattern [, ...] )
  | row_pattern | row_pattern
  | row_pattern row_pattern }

pattern_quantifier
  { *
  | +
  | ?
  | { n }
  | { min , [ max ] }
  | { , max } }

Parameters

Pattern primary

  • variable

    A variable defined in the DEFINE clause. An undefined variable matches all rows.

  • $

    The end of the partition.

  • ^

    The beginning of the partition.

    In this release, ^ and $ are supported only at the leading or trailing position of the top-level pattern. For example, ^ A B $ is supported, but mid-sequence anchors such as A ^ B and per-branch anchors such as (^ A) | (B $) are not supported.

Pattern operator

  • ( [ row_pattern ] )

    A grouping that overrides precedence.

  • PERMUTE ( row_pattern [, ...] )

    Matches row patterns in any permutation of order. PERMUTE requires at least two patterns. If you specify fewer than two, Azure Databricks raises MATCH_RECOGNIZE_PERMUTE_TOO_FEW_PATTERNS. If you specify more than the supported maximum, Azure Databricks raises MATCH_RECOGNIZE_PERMUTE_TOO_MANY_PATTERNS.

  • row_pattern | row_pattern

    Matches either of the row patterns.

  • row_pattern row_pattern

    Matches a sequence of row patterns.

Pattern quantifier

Specifies how often the pattern_primary must be matched:

  • *

    Zero or more times.

  • +

    One or more times.

  • ?

    At most once.

  • { n }

    Exactly n times. n must be an INTEGER literal greater than 0.

  • { min , [ max ] }

    At least min times, and if specified up to max times.

  • { , max }

    No more than max times.

If you omit a quantifier, the default is { 1 }: exactly one match.

Common error conditions

  • Sist oppdatert