MATCH_RECOGNIZE
A row pattern recognition clause defined in the SQL standard that allows pattern matching on consecutive row sequences within a table using syntax analogous to regular expressions. Its core clauses include PARTITION BY, ORDER BY, DEFINE, PATTERN, and MEASURES.
Timeline (last 90 days)
MATCH_RECOGNIZE is a row pattern recognition clause defined in the SQL standard, used to match consecutive rows in a data table in a manner similar to regular expressions
In MATCH_RECOGNIZE, PARTITION BY determines the grouping dimension, and ORDER BY defines the ordering of rows
In MATCH_RECOGNIZE, the DEFINE clause is used to name row states and define their evaluation conditions, while the PATTERN clause describes the order of appearance of state variables using regex-like syntax
The quantifiers supported by PATTERN are largely aligned with those of regular expressions, including * (zero or more), + (one or more), ? (zero or one), as well as exact occurrence controls such as {n} and {n,m}
The MEASURES clause is responsible for extracting information from matched row sequences; ONE ROW PER MATCH returns one summary row per match, while ALL ROWS PER MATCH returns the details of every row within the matched interval
In MEASURES, the CLASSIFIER() function can be used to obtain the name of the pattern variable each row is assigned to, the MATCH_NUMBER() function numbers multiple matches within the same partition, and FIRST() and LAST() retrieve field values from the first and last matched rows of a pattern variable respectively
MATCH_RECOGNIZE was incorporated into the SQL:2016 standard (ISO/IEC 9075-2:2016) in 2016
Oracle Database 12c Release 2 was the first mainstream database to implement MATCH_RECOGNIZE
Snowflake added support for MATCH_RECOGNIZE around 2021
The MATCH_RECOGNIZE implementation in Apache Flink differs from that in batch-processing databases, for example in its handling mechanisms for unbounded streams and the definition of timeout semantics
3 more timeline events
All Facts (13)
MATCH_RECOGNIZE is a row pattern recognition clause defined in the SQL standard, used to match consecutive rows in a data table in a manner similar to regular expressions
50%UnverifiedIn MATCH_RECOGNIZE, PARTITION BY determines the grouping dimension, and ORDER BY defines the ordering of rows
50%UnverifiedIn MATCH_RECOGNIZE, the DEFINE clause is used to name row states and define their evaluation conditions, while the PATTERN clause describes the order of appearance of state variables using regex-like syntax
50%UnverifiedThe quantifiers supported by PATTERN are largely aligned with those of regular expressions, including * (zero or more), + (one or more), ? (zero or one), as well as exact occurrence controls such as {n} and {n,m}
50%UnverifiedThe MEASURES clause is responsible for extracting information from matched row sequences; ONE ROW PER MATCH returns one summary row per match, while ALL ROWS PER MATCH returns the details of every row within the matched interval
50%UnverifiedIn MEASURES, the CLASSIFIER() function can be used to obtain the name of the pattern variable each row is assigned to, the MATCH_NUMBER() function numbers multiple matches within the same partition, and FIRST() and LAST() retrieve field values from the first and last matched rows of a pattern variable respectively
50%UnverifiedMATCH_RECOGNIZE was incorporated into the SQL:2016 standard (ISO/IEC 9075-2:2016) in 2016
50%UnverifiedOracle Database 12c Release 2 was the first mainstream database to implement MATCH_RECOGNIZE
50%UnverifiedSnowflake added support for MATCH_RECOGNIZE around 2021
50%UnverifiedThe MATCH_RECOGNIZE implementation in Apache Flink differs from that in batch-processing databases, for example in its handling mechanisms for unbounded streams and the definition of timeout semantics
50%UnverifiedPATTERN (FAIL+ SUCCESS) represents one or more failures followed by one success, and can be used to detect login behavior sequences where a brute-force attack succeeds
50%UnverifiedMATCH_RECOGNIZE is suited for analytical scenarios involving event ordering, including financial risk control, user behavior analysis, IoT monitoring, and stock price technical pattern recognition
50%UnverifiedFor PostgreSQL, which does not support MATCH_RECOGNIZE, workarounds based on recursive CTEs or window functions exist in the community, but code complexity increases significantly and performance varies
50%