SQL Reference - MATCH

MATCH_RECOGNIZE

MATCH_RECOGNIZE in SQL is an advanced ROW pattern matching method. It is used to detect complex patterns in a table's row sequence and structures its output according to identified matches. It provides precise control over the input table's partitioning and ordering, taking regular expression concepts and applying them to the identification and output of information.

Example

SELECT * FROM t
MATCH_RECOGNIZE (
  ORDER BY t.a
  MEASURES A.a AS a1, LAST(B.a) AS a2
  ALL ROWS PER MATCH
  PATTERN (A B*)
  DEFINE B AS B.a > A.a
)

Output

+----+----+
| A1 | A2 |
+----+----+
|  1 |  2 |
|  2 |  4 |
|  5 |  6 |
+----+----+

Explanation

The MATCH_RECOGNIZE clause is used to perform data pattern matching and recognizing records that belong to a pattern. The given pattern is defined by a regular expression and allows complex pattern matching operations that go beyond what basic SQL comparisons and functions offer. In this example, the pattern (A B*) is defined, where B follows A and B’s value is always greater than A’s value. The ALL ROWS PER MATCH option is specified which means that it returns one row for each match found. The output contains the first and last value in each match.