MATCH_RECOGNIZE

Oracle

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.