The MATCH_RECOGNIZE clause performs pattern recognition in an Oracle CQL query. This query will export (make available for inclusion in the SELECT) the MEASURES clause values for events (tuples) that satisfy the PATTERN clause regular expression over the DEFINE clause conditions.

<query id="detectPerish"><![CDATA[ 
  select its.badItemId
  from tkrfid_ItemTempStream 
  MATCH_RECOGNIZE (
      PARTITION BY itemId
      MEASURES A.itemId as badItemId
      PATTERN (A B* C)
      DEFINE
          A  AS  (A.temp >= 25),
          B  AS  ((B.temp >= 25) and (to_timestamp(B.element_time) - to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)),
          C  AS  (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "0 00:00:05.00" DAY TO SECOND)
  ) as its
]]></query>

pattern_recognition_clause::=

(Figure 13-6, Figure 13-1, pattern_def_dur_clause::=)

pattern_def_dur_clause::=

(Figure 13-2, Figure 13-7, Figure 13-3, Figure 13-9, Figure 13-11)

Using MATCH_RECOGNIZE, you define conditions on the attributes of incoming events and identify these conditions by using identifiers called correlation variables. The previous example defines correlation variables A, B, and C. A sequence of consecutive events in the input stream satisfying these conditions constitutes a pattern.

The output of a MATCH_RECOGNIZE query is always a stream.

The principle MATCH_RECOGNIZE sub-clauses are:

• MEASURES: exports (makes available for inclusion in the SELECT) attribute values of events that successfully match the pattern you specify.

  See MEASURES Clause.

• PATTERN: specifies the pattern to be matched as a regular expression over one ore more correlation variables.

  See PATTERN Clause.

• DEFINE: specifies the condition for one or more correlation variables.

  See DEFINE Clause.

To refine pattern recognition, you may use the optional MATCH_RECOGNIZE sub-clauses, including:

• PARTITION BY Clause

• ALL MATCHES Clause

• WITHIN Clause

• DURATION Clause

• INCLUDE TIMER EVENTS Clause

• SUBSET Clause.

For more information, see:

• MATCH_RECOGNIZE and the WHERE Clause

• Referencing Singleton and Group Matches

• Referencing Aggregates.

  • Using count With *, identifier.*, and identifier.attr

  • Using first and last.

• Using prev

• MATCH_RECOGNIZE Examples.

The MEASURES clause exports (makes available for inclusion in the SELECT) attribute values of events that successfully match the pattern you specify.

You may specify expressions over correlation variables that reference partition attributes, order by attributes, singleton variables and aggregates on group variables, and aggregates on the attributes of the stream that is the source of the MATCH_RECOGNIZE clause.

You qualify attribute values by correlation variable to export the value of the attribute for the event that matches the correlation variable's condition. For example, within the MEASURES clause, A.c1 refers to the value of event attribute c1:

• In the tuple that last matched the condition corresponding to correlation variable A, if A is specified in the DEFINE clause.

• In the last processed tuple, if A is not specified in the DEFINE clause.

  This is because if A is not specified in the DEFINE clause, then A is considered as TRUE always. So effectively all the tuples in the input match to A.

You may include in the SELECT statement only attributes you specify in the MEASURES clause.

Figure 13-1 pattern_measures_clause::=

(non_mt_measure_list::=)

non_mt_measure_list::=

(measure_column::=)

measure_column::=

(arith_expr::=)

The pattern_measures_clause is:

MEASURES
    A.itemId as itemId

This section describes:

• Functions Over Correlation Variables in the MEASURES Clause.

For more information, see:

• Referencing Singleton and Group Matches

• Referencing Aggregates

• DEFINE Clause

• Functions.

The PATTERN clause specifies the pattern to be matched as a regular expression over one ore more correlation variables.

Incoming events must match these conditions in the order given (from left to right).

The regular expression may contain correlation variables that are:

• Defined in the DEFINE clause: such correlation variables are considered true only if their condition definition evaluates to TRUE.

  See DEFINE Clause.

• Not defined in the DEFINE clause: such correlation variables are considered as always TRUE; that is, they match every input.

Figure 13-2 pattern_clause::=

(regexp::=, Figure 13-8)

This section describes:

• Pattern Quantifiers and Regular Expressions

• Grouping and Alternation in the PATTERN Clause.

For more information, see:

• Pattern Detection

• Pattern Detection With PARTITION BY

• Pattern Detection With Aggregates.

The DEFINE clause specifies the boolean condition for each correlation variable.

You may specify any logical or arithmetic expression and apply any single-row or aggregate function to the attributes of events that match a condition.

On receiving a new tuple from the base stream, the conditions of the correlation variables that are relevant at that point in time are evaluated. A tuple is said to have matched a correlation variable if it satisfies its defining condition. A particular input can match zero, one, or more correlation variables. The relevant conditions to be evaluated on receiving an input are determined by logic governed by the PATTERN clause regular expression and the state in pattern recognition process that we have reached after processing the earlier inputs.

The condition can refer to any of the attributes of the schema of the stream or view that evaluates to a stream on which the MATCH_RECOGNIZE clause is being applied.

A correlation variable in the PATTERN clause need not be specified in the DEFINE clause: the default for such a correlation variable is a predicate that is always true. Such a correlation variable matches every event. It is an error to specify a correlation variable in the DEFINE clause which is not used in a PATTERN clause

No correlation variable defined by a SUBSET clause may be defined in the DEFINE clause.

Figure 13-3 pattern_definition_clause::=

(Figure 13-4)

Figure 13-4 non_mt_corrname_definition_list::=

(Figure 13-5)

Figure 13-5 correlation_name_definition::=

(correlation_name::=, non_mt_cond_list)

This section describes:

• Functions Over Correlation Variables in the DEFINE Clause

• Referencing Attributes in the DEFINE Clause

• Referencing One Correlation Variable From Another in the DEFINE Clause.

For more information, see:

• Referencing Singleton and Group Matches

• Referencing Aggregates

• Using first and last

• Using prev

• PATTERN Clause

• SUBSET Clause

• Functions.

Use this optional clause to specify the stream attributes by which a MATCH_RECOGNIZE clause should partition its results.

Without a PARTITION BY clause, all stream attributes belong to the same partition.

Figure 13-6 pattern_partition_clause::=

(non_mt_attr_list)

The pattern_partition_clause is:

PARTITION BY 
    itemId

The partition by clause in pattern means the input stream is logically divided based on the attributes mentioned in the partition list and pattern matching is done within a partition.

Consider a stream S with schema (c1 integer, c2 integer) with the input data.

     c1  c2
1000 10, 1
2000 10, 2
3000 20, 2
4000 20, 1

Consider the MATCH_RECOGNIZE query.

select T.p1, T.p2, T.p3 from S MATCH_RECOGNIZE(
    MEASURES
        A.ELEMENT_TIME as p1,
        B.ELEMENT_TIME as p2
        B.c2 as p3
    PATTERN (A B)
    DEFINE
        A as A.c1 = 10,
        B as B.c1 = 20
) as T

This query would output the following:

3000:+ 2000, 3000, 2

If we add PARTITION BY c2 to the query, then the output would change to:

3000:+ 2000, 3000, 2
4000:+ 1000, 4000, 1

This is because by adding the PARTITION BY clause, matches are done within partition only. Tuples at 1000 and 4000 belong to one partition and tuples at 2000 and 3000 belong to another partition owing to the value of c2 attribute in them. In the first partition A matches tuple at 1000 and B matches tuple at 4000. Even though a tuple at 3000 matches the B definition, it is not presented as a match for the first partition since that tuple belongs to different partition.

Use this optional clause to configure GGSA to match overlapping patterns.

With the ALL MATCHES clause, GGSA finds all possible matches. Matches may overlap and may start at the same event. In this case, there is no distinction between greedy and reluctant pattern quantifiers. For example, the following pattern:

ALL MATCHES
PATTERN (A* B)

produces the same result as:

ALL MATCHES
PATTERN (A*? B)

Without the ALL MATCHES clause, overlapping matches are not returned, and quantifiers such as the asterisk determine which among a set of candidate (and overlapping) matches is the preferred one for output. The rest of the overlapping matches are discarded.

Figure 13-7 pattern_skip_match_clause::=

Consider the query tkpattern_q41 that uses ALL MATCHES and the data stream tkpattern_S11. Stream tkpattern_S11 has schema (c1 integer, c2 integer). The query returns the stream.

The query tkpattern_q41 will report a match when the input stream values, when plotted, form the shape of the English letter W. The relation shows an example of overlapping instances of this W-pattern match.

There are two types of overlapping pattern instances:

• Total: Example of total overlapping: Rows from time 3000-9000 and 4000-9000 in the input, both match the given pattern expression. Here the longest one (3000-9000) will be preferred if ALL MATCHES clause is not present.

• Partial: Example of Partial overlapping: Rows from time 12000-21000 and 16000-23000 in the input, both match the given pattern expression. Here the one which appears earlier is preferred when ALL MATCHES clause is not present. This is because when ALL MATCHES clause is omitted, we start looking for the next instance of pattern match at a tuple which is next to the last tuple in the previous matched instance of the pattern.

<query id="tkpattern_q41"><![CDATA[ 
    select 
        T.firstW, T.lastZ 
    from 
        tkpattern_S11 
    MATCH_RECOGNIZE ( 
        MEASURES A.c1 as firstW, last(Z.c1) as lastZ 
        ALL MATCHES 
        PATTERN(A W+ X+ Y+ Z+) 
        DEFINE 
            W as W.c2 < prev(W.c2), 
            X as X.c2 > prev(X.c2), 
            Y as Y.c2 < prev(Y.c2), 
            Z as Z.c2 > prev(Z.c2)
    ) as T
]]></query>

Timestamp   Tuple
 1000         1,8
 2000         2,8
 3000         3,8
 4000         4,6
 5000         5,3
 6000         6,7
 7000         7,6
 8000         8,2
 9000         9,6
10000        10,2
11000        11,9
12000        12,9
13000        13,8
14000        14,5
15000        15,0
16000        16,9
17000        17,2
18000        18,0
19000        19,2
20000        20,3
21000        21,8
22000        22,5
23000        23,9
24000        24,9
25000        25,4
26000        26,7
27000        27,2
28000        28,8
29000        29,0
30000        30,4
31000        31,4
32000        32,7
33000        33,8
34000        34,6
35000        35,4
36000        36,5
37000        37,1
38000        38,7
39000        39,5
40000        40,8
41000        41,6
42000        42,6
43000        43,0
44000        44,6
45000        45,8
46000        46,4
47000        47,3
48000        48,8
49000        49,2
50000        50,5
51000        51,3
52000        52,3
53000        53,9
54000        54,8
55000        55,5
56000        56,5
57000        57,9
58000        58,7
59000        59,3
60000        60,3

Timestamp   Tuple Kind  Tuple
 9000:      +      3,9
 9000:      +      4,9
11000:      +      6,11
11000:      +      7,11
19000:      +     12,19
19000:      +     13,19
19000:      +     14,19
20000:      +     12,20
20000:      +     13,20
20000:      +     14,20
21000:      +     12,21
21000:      +     13,21
21000:      +     14,21
23000:      +     16,23
23000:      +     17,23
28000:      +     24,28
30000:      +     26,30
38000:      +     33,38
38000:      +     34,38
40000:      +     36,40
48000:      +     42,48
50000:      +     45,50
50000:      +     46,50

The ALL MATCHES clause reports all the matched pattern instances on receiving a particular input. For example, at time 20000, all of the tuples {12,20}, {13,20}, and {14,20} are output.

For more information, see Pattern Quantifiers and Regular Expressions.

The WITHIN clause is an optional clause that outputs a pattern_clause match if and only if the match occurs within the specified time duration.

Figure 13-8 within_clause::=

(time_spec)

That is, if and only if:

TL - TF < WD

Where:

• TL - Timestamp of last event matching the pattern.

• TF - Timestamp of first event matching the pattern.

• WD - Duration specified in the WITHIN clause.

The WITHIN INCLUSIVE clause tries to match events at the boundary case as well. That is, it outputs a match if and only if:

TL - TF <= WD

If the match completes within the specified time duration, then the event is output as soon as it happens. That is, if the match can be output, it is output with the timestamp at which it completes. The WITHIN clause does not wait for the time duration to expire as the DURATION clause does.

When the WITHIN clause duration expires, it discards any potential candidate matches which are incomplete.

For more information, see Pattern Detection With the WITHIN Clause.

The DURATION clause is an optional clause that you should use only when writing a query involving non-event detection. Non-event detection is the detection of a situation when a certain event which should have occurred in a particular time limit does not occur in that time frame.

Figure 13-9 duration_clause::=

(Figure 7-10)

Using this clause, a match is reported only when the regular expression in the PATTERN clause is matched completely and no other event or input arrives until the duration specified in the DURATION clause expires. The duration is measured from the time of arrival of the first event in the pattern match.

You must use the INCLUDE TIMER EVENTS clause when using the DURATION clause. For more information, see INCLUDE TIMER EVENTS Clause.

This section describes:

• Fixed Duration Non-Event Detection

• Recurring Non-Event Detection.

Use this clause in conjunction with the DURATION clause for non-event detection queries.

Typically, in most pattern match queries, a pattern match output is always triggered by an input event on the input stream over which pattern is being matched. The only exception is non-event detection queries where there could be an output triggered by a timer expiry event (as opposed to an explicit input event on the input stream).

Figure 13-10 pattern_inc_timer_evs_clause::=

(Figure 13-2, Figure 13-7, Figure 13-3, Figure 13-9, Figure 13-11)

For more information, see DURATION Clause.

Using this clause, you can group together one or more correlation variables that are defined in the DEFINE clause. You can use this named subset in the MEASURES and DEFINE clauses just like any other correlation variable.

For example:

SUBSET S1 = (Z,X)

The right-hand side of the subset ((Z,X)) is a comma-separated list of one or more correlation variables as defined in the PATTERN clause.

The left-hand side of the subset (S1) is the union of the correlation variables on the right-hand side.

You cannot include a subset variable in the right-hand side of a subset.

Figure 13-11 subset_clause::=

(Figure 13-12)

Figure 13-12 non_mt_subset_definition_list::=

(Figure 13-13)

Figure 13-13 subset_definition::=

(Figure 13-14, Figure 13-15)

Figure 13-14 subset_name::=

(Figure 7-3)

Figure 13-15 non_mt_corr_list::=

(correlation_name::=)

Consider the query q55 inExample 13-1 and the data stream S11 in Example 13-2. Stream S11 has schema (c1 integer, c2 integer). This example defines subsets S1 through S6. This query outputs a match if the c2 attribute values in the input stream form the shape of the English letter W. Now suppose we want to know the sum of the values of c2 for those tuples which form the incrementing arms of this W shape. The correlation variable X represents tuples that are part of the first incrementing arm and Z represent the tuples that are part of the second incrementing arm. So we need some way to group the tuples that match both. Such a requirement can be captured by defining a SUBSET clause as the example shows.

Subset S4 is defined as (X,Z). It refers to the tuples in the input stream that match either X or Z. This subset is used in the MEASURES clause statement sum(S4.c2) as sumIncrArm. This computes the sum of the value of c2 attribute in the tuples that match either X or Z. A reference to S4.c2 in a DEFINE clause like S4.c2 = 10 will refer to the value of c2 in the latest among the last tuple that matched X and the last tuple that matched Z.

Subset S6 is defined as (Y). It refers to all the tuples that match correlation variable Y.

The query returns the stream.

Timestamp   Tuple Kind  Tuple
 9000:      +           3,9,25,13,5.428571
21000:      +           12,21,24,22,4.6
28000:      +           24,28,15,15,6.0
38000:      +           33,38,19,12,5.1666665
48000:      +           42,48,13,22,5.0

For more information, see:

• Running Aggregates and Final Aggregates

• MEASURES Clause

• PATTERN Clause

• DEFINE Clause.

The following examples illustrate basic MATCH_RECOGNIZE practices:

• Pattern Detection

• Pattern Detection With PARTITION BY

• Pattern Detection With Aggregates

• Fixed Duration Non-Event Detection.