This is used within a query for pattern matching, which allows detection of patterns of events across one or more sources.
MATCHING [interval:pattern]
ON { {source.column = source.column [=...]}|
{source.column = constant }|
{getOpcode() = opcode_constant} [AND...]
}
pattern:[!]{event | (event)} [&&| || |,}event]
| MATCHING | Identifies the MATCHING clause. |
| interval:pattern | interval specifies the interval and pattern specifies the matching patterns. |
| source.column | The name of the source input and the column. |
| getOpcode() | Includes opcode conditions on the pattern. |
| opcode_constant | Specifies the opcode. |
| pattern | The pattern you want to identify. Contains events connected by event operators. |
| event | Events compared in the pattern. |
The MATCHING clause immediately follows the FROM clause in a SELECT statement. The FROM clause contains the derived elements that are used as inputs for pattern matching.
SELECT statements containing a MATCHING clause cannot include any filtering or aggregation criteria.
The MATCHING clause consists of a mandatory interval and pattern specification.
The interval specifies the time period within which the pattern must be detected. It supports microsecond granularity and can either be represented as an interval constant (refer to the interval data type) or a parameter.
The pattern specification indicates the events or groups of events that must occur, or not occur, within the specified interval to meet the pattern matching criteria. Where a pattern specification consists of more than one event, the events or groups of events must be connected with the operators listed in the following table:
| Operator | Operator Name | Description |
|---|---|---|
| ! | Not operator | Specifies a negative condition for a pattern component. Pattern conditions are met when the pattern component does not occur within the specified time interval. Since this is a negative condition, the pattern match is deemed successful only after the expiration of the specified time interval. |
| && | Conjunction (logical AND) operator | Both pattern components linked by the conjunction operator must occur for the match condition to be met, but they do not have to occur in the order listed. |
| || | Disjunction (Logical OR) operator | One or both pattern components linked by the Disjunction operator must occur to meet the conditions of the match. Each output row produced by a Disjunction match shows the match for one of the members of the Disjunction, and NULL values for the other members. This is true even when several members of the disjunction produce events. |
| , | Followed by operator | Pattern components linked by this operator must both occur, in the order listed, to meet the conditions of the match. |
The default order of precedence in which pattern components are analyzed for a possible pattern match follows the order of operators, as they are listed in the table. The tightest binding between an operator and a pattern component is that of the Not operator. The bindings then get progressively looser, for events linked with a conjunction, disjunction, and sequence operators, respectively. This default order of precedence can be overridden by enclosing a pattern component in parentheses.
Since pattern matching on a not operator is deemed successful only after the expiration of the specified time interval, a not operator when included with a followed by operator must be its last component. This is because events succeeding the not operator will never be evaluated by the pattern rule engine owing to the expiration of the time interval.
The MATCHING clause of a SELECT statement that includes multiple derived elements in the FROM clause can contain an optional ON sub-clause, which defines one or more equality expressions that further refine the pattern matching criteria.
The equality expression is used to compare the column values of the input records or their opcodes. The left hand side of the equality can either contain a fully qualified column name, or the function 1.The right hand side of the equality can contain a fully qualified column name, a constant value, or a parameter.
If the left hand side contains the function 2, the right hand side must contain a constant specifying the desired opcode. Valid opcode values are insert, update and delete.