Oracle SQL - CEP Syntax

CEP is naturally an important capability in real-time processing. Mature frameworks such as Siddhi on GitHub and EsperTech implement it with their own developer-facing syntax. Wouldn’t it be interesting to add CEP to the versatile SQL language?

See the Oracle documentation. The syntax explanations and examples in this post all come from the official documentation.

Quick Overview

Start with a simple example: the following chart shows real-time stock prices. We want to detect the X-Y-W-Z pattern (fall-rise-fall-rise) in real time and obtain its start and end points. The SQL below contains quite a few unfamiliar constructs, doesn’t it?
Let’s examine them one by one.

1
SELECT
2
    T.A,
3
    T.lastZ
4
FROM
5
    STREAM
6
MATCH_RECOGNIZE (
7
    MEASURES
8
        A,
9
        last(Z) as lastZ
10
    PATTERN(A W+ X+ Y+ Z+)
11
    DEFINE
12
        W as W.price < prev(W.price),
13
        X as X.price > prev(X.price),
14
        Y as Y.price < prev(Y.price),
15
        Z as Z.price > prev(Z.price)
16
) as T

MATCH_RECOGNIZE

This identifies a CEP operation, generally written as SELECT … FROM S MATCH_RECOGNIZE ( …. ) as T WHERE ….

MEASURES

Produces the measures of T. Measures can refer to events declared in DEFINE, using values such as Z.price in the example or UDAP functions such as sum.

PATTERN

The example’s (A W+ X+ Y+ Z+) specifies a strict event sequence: A can be followed by N W events, the last W by N X events, the last X by N Y events, and the last Y by N Z events.

• | *? | Match 0 to N events

• | +? | Match 1 to N events ? | ?? | Match 0 or 1 event

For example, in *(A B C)**, if an event satisfies both B and C in DEFINE, greedy matching assigns it to B. If B uses the reluctant *? quantifier, it is assigned to C.
Regular expressions such as (A B+ | C) are also supported.

DEFINE

Defines events and their business conditions. For example, W as W.price < prev(W.price) requires a W event’s price to be below that of the previous W event.

PARTITION BY

Partitions data, similarly to window functions. To partition the earlier stock chart by industry, the SQL can be written as follows:

1
PARTITION BY
2
    A.industry
3
MEASURES
4
    A,
5
    last(Z) as lastZ
6
PATTERN(A W+ X+ Y+ Z+)
7
DEFINE
8
    W as W.price < prev(W.price),
9
    X as X.price > prev(X.price),
10
    Y as Y.price < prev(Y.price),
11
    Z as Z.price > prev(Z.price)

DURATION

Suppose you want to avoid excessive oscillation: within 10 minutes, only the W, X, Y, Z trend should occur, with no further turning points after it completes. You can require that no other events intervene during those 10 minutes.

1
MEASURES
2
    A,
3
    last(Z) as lastZ
4
include timer events // 和duration 10配合使用
5
PATTERN(A W+ X+ Y+ Z+)
6
duration 10
7
DEFINE
8
    W as W.price < prev(W.price),
9
    X as X.price > prev(X.price),
10
    Y as Y.price < prev(Y.price),
11
    Z as Z.price > prev(Z.price)

WITHIN

Suppose you do not want to wait a whole day for the trend: it is valid only if the conditions are met within 10 minutes. Note that WITHIN and WITHIN INCLUSIVE differ in whether they include the boundary.

1
MEASURES
2
    A,
3
    last(Z) as lastZ
4
PATTERN(A W+ X+ Y+ Z+)
5
DEFINE
6
    W as W.price < prev(W.price),
7
    X as X.price > prev(X.price),
8
    Y as Y.price < prev(Y.price),
9
    Z as Z.price > prev(Z.price)

ALL MATCHES

On the stock chart, any point along X’s descending segment satisfies DEFINE. ALL MATCHES includes all of them. Without ALL MATCHES, after a match the processor automatically advances to the position of its final event.