Pattern Matching

PARTITION BY

No

Logically divides the rows into groups.

ORDER BY

No

Logically orders the rows in a partition.

[ONE ROW | ALL ROWS] PER MATCH

No

Chooses summaries or details for each match.

• ONE ROW PER MATCH: Each match produces one summary row.
• ALL ROWS PER MATCH: A match spanning multiple rows will produce one output row for each row in the match.

The following provides an example:

		SELECT * FROM MyTable MATCH_RECOGNIZE
		(
		  MEASURES AVG(B.id) as Bid
		  ALL ROWS PER MATCH
		  PATTERN (A B C)
		  DEFINE
			A AS A.name = 'a',
			B AS B.name = 'b',
			C as C.name = 'c'
		) MR

Example description

Assume that the format of MyTable is (id, name) and there are three data records: (1, a), (2, b), and (3, c).

ONE ROW PER MATCH outputs the average value 2 of B.

ALL ROWS PER MATCH outputs each record and the average value of B, specifically, (1,a, null), (2,b,2), (3,c,2).

MEASURES

No

Defines calculations for export from the pattern matching.

PATTERN

Yes

Defines the row pattern that will be matched.

• PATTERN (A B C) indicates to detect concatenated events A, B, and C.
• PATTERN (A | B) indicates to detect A or B.

• Modifiers
  • *: 0 or more iterations. For example, A* indicates to match A for 0 or more times.
  • +: 1 or more iterations. For example, A+ indicates to match A for 1 or more times.
  • ? : 0 or 1 iteration. For example, A? indicates to match A for 0 times or once.
  • {n}: n iterations (n > 0). For example, A{5} indicates to match A for five times.
  • {n,}: n or more iterations (n ≥ 0). For example, A{5,} indicates to match A for five or more times.
  • {n, m}: between n and m (inclusive) iterations (0 ≤ n ≤ m, 0 < m). For example, A{3,6} indicates to match A for 3 to 6 times.
  • {, m}: between 0 and m (inclusive) iterations (m > 0). For example, A{,4} indicates to match A for 0 to 4 times.

DEFINE

Yes

Defines primary pattern variables.

AFTER MATCH SKIP

No

Defines where to restart the matching process after a match is found.

• SKIP TO NEXT ROW: Resumes pattern matching at the row after the first row of the current match.
• SKIP PAST LAST ROW: Resumes pattern matching at the next row after the last row of the current match.
• SKIP TO FIRST variable: Resumes pattern matching at the first row that is mapped to the pattern variable.
• SKIP TO LAST variable: Resumes pattern matching at the last row that is mapped to the pattern variable.
• SKIP TO variable: Same as SKIP TO LAST variable.

MATCH_NUMBER()

Finds which rows are in which match. It can be used in the MEASURES and DEFINE clauses.

CLASSIFIER()

Finds which pattern variable applies to which rows. It can be used in the MEASURES and DEFINE clauses.

FIRST()/LAST()

FIRST returns the value of an expression evaluated in the first row of the group of rows mapped to a pattern variable. LAST returns the value of an expression evaluated in the last row of the group of rows mapped to a pattern variable. In PATTERN (A B+ C), FIRST (B.id) indicates the ID of the first B in the match, and LAST (B.id) indicates the ID of the last B in the match.

NEXT()/PREV()

Relative offset, which can be used in DEFINE. For example, PATTERN (A B+) DEFINE B AS B.price > PREV(B.price)

RUNNING/FINAL

RUNNING indicates to match the middle value, while FINAL indicates to match the final result value. Generally, RUNNING/FINAL is valid only in ALL ROWS PER MATCH. For example, if there are three records (a, 2), (b, 6), and (c, 12), then the values of RUNNING AVG (A.price) and FINAL AVG (A.price) are (2,6), (4,6), (6,6).

Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

Aggregation operations. These functions can be used in the MEASURES and DEFINE clauses. For details, see Aggregate Functions.