Device Fault Pattern Recognition (MATCH_RECOGNIZE)

# Device Fault Pattern Recognition Case

In IoT / industrial scenarios, many alarms are not "single-point threshold crossings" but a group of events appearing in a specific order — consecutive threshold crossings, rise-then-drop, a start→run→stop workflow, a vibration burst. These "event-sequence patterns" are exactly what MATCH_RECOGNIZE (pattern matching / CEP) is built for.

This case centers on a single device reading stream stream and chains six sub-scenarios covering the most common pattern-matching shapes:

Sub-scenario	Pattern	Key capability
A. Consecutive threshold debounce	`A{3}`	Fixed count repeat — avoid single-point jitter false alarms
B. Rise then sudden drop (failure precursor)	`A+ B`	Quantifier + sequence — sustained state then transition
C. Vibration burst	`A{5,}`	Lower-bound repeat — high-frequency burst detection
D. Start/stop workflow	`Start Running Stop`	Cross-event-type sequence
E. Out-of-order auth	`PERMUTE(A, B)`	Any order — order doesn't matter
F. Time-constrained confirmation	`A B ... WITHIN`	Time window — must complete within N seconds

How to run these SQL queries

Each scenario below shows only SQL + input + output; for how to run them see Case Studies · How to Run Case SQL. Pattern-matching queries must use Emit + AddSink (EmitSync does not support MATCH_RECOGNIZE); use AddSyncSink to preserve order.

# Scenario A: Consecutive threshold debounce (`A{3}`)

# Business goal

Occasional high readings from temperature are normal (EMI, sampling glitches). Only 3 consecutive crossings of 80°C count as a real overheat and raise an alarm, avoiding single-point jitter. Each device counts independently (PARTITION BY deviceId).

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY deviceId ORDER BY ts
    MEASURES MATCH_NUMBER() AS mn, COUNT(*) AS hits, LAST(A.temp) AS peak
    ONE ROW PER MATCH
    PATTERN (A{3})
    WITHIN '1h'
    DEFINE A AS temp > 80
)

1
2
3
4
5
6
7
8
9

# Input and output

dev-01 crosses 80 three times in a row → one match; dev-02 only twice, not enough for 3:

{"deviceId": "dev-01", "ts": 1, "temp": 60}
{"deviceId": "dev-01", "ts": 2, "temp": 82}
{"deviceId": "dev-01", "ts": 3, "temp": 85}
{"deviceId": "dev-01", "ts": 4, "temp": 88}
{"deviceId": "dev-02", "ts": 5, "temp": 90}
{"deviceId": "dev-02", "ts": 6, "temp": 91}

1
2
3
4
5
6

Output (rows 2–4 of dev-01 form the A{3}):

[alert] {deviceId:dev-01 mn:1 hits:3 peak:88}

# Behavior notes

PATTERN (A{3}): A repeats exactly 3 times; a row that doesn't satisfy temp>80 in between breaks the run, which must restart from 3.
PARTITION BY deviceId: each device matches on its own; dev-02's two rows are not counted into dev-01.
COUNT(*) counts the rows in the match (always 3 here); LAST(A.temp) takes the last temperature as the peak.
Default AFTER MATCH SKIP PAST LAST ROW: matches don't overlap; the next search starts after this match's last row.

# Scenario B: Rise then sudden drop (failure precursor, `A+ B`)

# Business goal

Before failure, devices often show "sustained high-temperature run → sudden temperature drop" (coolant failure, sensor disconnect). Detect "several consecutive high rows followed immediately by a row dropping back to low".

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY deviceId ORDER BY ts
    MEASURES MATCH_NUMBER() AS mn, MAX(A.temp) AS peak, B.temp AS drop_to
    ONE ROW PER MATCH
    PATTERN (A+ B)
    WITHIN '1h'
    DEFINE
        A AS temp > 80,
        B AS temp < 30
)

1
2
3
4
5
6
7
8
9
10
11

# Input and output

dev-01 runs hot for 3 rows then drops to 25:

{"deviceId": "dev-01", "ts": 1, "temp": 85}
{"deviceId": "dev-01", "ts": 2, "temp": 90}
{"deviceId": "dev-01", "ts": 3, "temp": 95}
{"deviceId": "dev-01", "ts": 4, "temp": 25}

1
2
3
4

Output:

[alert] {deviceId:dev-01 mn:1 peak:95 drop_to:25}

# Behavior notes

A+ (greedy, at least 1): keeps consuming temp>80 rows, matching as many as possible; B consumes the first temp<30 row.
The whole match [85,90,95,25]: A = first three rows, B = the 25 row.
MAX(A.temp) aggregates the peak of the A segment; B.temp takes B's temperature (a symbol-qualified field takes that symbol's last occurrence).
If the high segment never drops (stays hot to end-of-stream), the match stays open and is only emitted on Stop via Flush (greedy waits for the run to terminate).

# Scenario C: Vibration burst (`A{5,}`)

# Business goal

A vibration sensor chatters at high frequency when a device loses balance. Detect bursts of at least 5 consecutive amplitude-over-threshold rows (A{5,} = 5 or more).

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY deviceId ORDER BY ts
    MEASURES MATCH_NUMBER() AS mn, COUNT(*) AS bursts, MAX(A.amp) AS max_amp
    ONE ROW PER MATCH
    PATTERN (A{5,})
    WITHIN '1h'
    DEFINE A AS amp > 50
)

1
2
3
4
5
6
7
8
9

# Input and output

dev-01 has 6 consecutive high amplitudes (≥5, matches); the trailing amp=40 fails A, ending the burst and triggering output:

{"deviceId": "dev-01", "ts": 1, "amp": 60}
{"deviceId": "dev-01", "ts": 2, "amp": 65}
{"deviceId": "dev-01", "ts": 3, "amp": 70}
{"deviceId": "dev-01", "ts": 4, "amp": 62}
{"deviceId": "dev-01", "ts": 5, "amp": 68}
{"deviceId": "dev-01", "ts": 6, "amp": 71}
{"deviceId": "dev-01", "ts": 7, "amp": 40}

1
2
3
4
5
6
7

Output:

[alert] {deviceId:dev-01 mn:1 bursts:6 max_amp:71}

# Behavior notes

A{5,} (greedy): at least 5, unbounded above. All 6 rows of dev-01 enter one match.
A greedy quantifier with no natural endpoint extends until the next row that fails A (here amp=40) before emitting and picking the longest; if it stays satisfied to end-of-stream, it is emitted on Stop via Flush. A burst below 5 rows does not emit.
A single row with amp≤50 in the middle breaks the burst; AFTER MATCH SKIP PAST LAST ROW then resumes accumulating after the break.

# Scenario D: Start/stop workflow (cross-event-type sequence)

# Business goal

Lifecycle events are distinguished by a type field (start/running/stop). Detect the complete "start → running → stop" sequence and report the running peak for that cycle. Each symbol matches a different type.

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY deviceId ORDER BY ts
    MEASURES MATCH_NUMBER() AS cycle, MAX(Running.power) AS peak_power
    ONE ROW PER MATCH
    PATTERN (Start Running+ Stop)
    WITHIN '24h'
    DEFINE
        Start   AS type == "start",
        Running AS type == "running",
        Stop    AS type == "stop"
)

1
2
3
4
5
6
7
8
9
10
11
12

# Input and output

dev-01 one full cycle: start → two running rows → stop:

{"deviceId": "dev-01", "ts": 1, "type": "start",   "power": 0}
{"deviceId": "dev-01", "ts": 2, "type": "running", "power": 120}
{"deviceId": "dev-01", "ts": 3, "type": "running", "power": 150}
{"deviceId": "dev-01", "ts": 4, "type": "stop",    "power": 0}

1
2
3
4

Output:

[cycle] {deviceId:dev-01 cycle:1 peak_power:150}

# Behavior notes

Each symbol matches a different event via type == "..." (string equality uses ==).
Running+: the running state can span multiple rows; MAX(Running.power) takes the running-segment peak.
WITHIN '24h': a start/stop cycle shouldn't span days; out-of-window partial matches are actively reaped so a "started but never stopped" event doesn't linger forever.
Symbol names are case-sensitive; meaningful names (Start/Running/Stop) improve readability.

# Scenario E: Out-of-order auth (`PERMUTE`, any order)

# Business goal

Security audit requires "a session contains both a login and an auth", but the order doesn't matter (some clients auth before login). Use PERMUTE to match either order.

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY sessionId ORDER BY ts
    MEASURES MATCH_NUMBER() AS mn, FIRST(Login.ts) AS t1, FIRST(Auth.ts) AS t2
    ONE ROW PER MATCH
    PATTERN (PERMUTE(Login, Auth))
    WITHIN '10m'
    DEFINE
        Login AS event == "login",
        Auth  AS event == "auth"
)

1
2
3
4
5
6
7
8
9
10
11

# Input and output

Session s1 logs in then auths; session s2 auths then logs in — both match:

{"sessionId": "s1", "ts": 1, "event": "login"}
{"sessionId": "s1", "ts": 2, "event": "auth"}
{"sessionId": "s2", "ts": 3, "event": "auth"}
{"sessionId": "s2", "ts": 4, "event": "login"}

1
2
3
4

Output:

[audit] {sessionId:s1 mn:1 t1:1 t2:2}
[audit] {sessionId:s2 mn:1 t1:4 t2:3}

1
2

# Behavior notes

PERMUTE(Login, Auth) is equivalent to (Login Auth) | (Auth Login) — both orders match.
PARTITION BY sessionId: each session is detected independently.
FIRST(Login.ts) / FIRST(Auth.ts): each symbol's first-occurrence timestamp, revealing the actual order.
PERMUTE compiles to a full permutation (N!) — usually 2–3 symbols at the edge.

# Scenario F: Time-constrained confirmation (`WITHIN`)

# Business goal

After an alert, ops must ack within 30 seconds. Detect "an ack event within 30s of an alert event" — past the window, the match fails (the alert wasn't acknowledged in time). This relies on the WITHIN time window.

# SQL

SELECT * FROM stream
MATCH_RECOGNIZE (
    PARTITION BY deviceId ORDER BY ts
    MEASURES MATCH_NUMBER() AS mn, Alert.ts AS alert_at, Ack.ts AS ack_at
    ONE ROW PER MATCH
    PATTERN (Alert Ack)
    WITHIN '30s'
    DEFINE
        Alert AS event == "alert",
        Ack   AS event == "ack"
)

1
2
3
4
5
6
7
8
9
10
11

ts is a millisecond-epoch timestamp (the WITH (TIMESTAMP='ts', TIMEUNIT='ms') semantics; MATCH_RECOGNIZE's ORDER BY field is unit-normalized automatically).

# Input and output

dev-01 acks 10s after the alert (matches); dev-02 acks 60s later (past the 30s window, the match fails and is expired):

{"deviceId": "dev-01", "ts": 1700000000000, "event": "alert"}
{"deviceId": "dev-01", "ts": 1700000010000, "event": "ack"}
{"deviceId": "dev-02", "ts": 1700000020000, "event": "alert"}
{"deviceId": "dev-02", "ts": 1700000080000, "event": "ack"}

1
2
3
4

Output:

[ok] {deviceId:dev-01 mn:1 alert_at:1700000000000 ack_at:1700000010000}

# Behavior notes

WITHIN '30s': Alert to Ack must be within 30s. dev-01 10s ✓; dev-02 60s ✗.
dev-02's partial match (Alert matched, waiting for Ack) is actively reaped by the background sweeper past 30s — no need to wait for the Ack event; idle partitions' out-of-window state does not linger.
Alerts not completed within the window produce no output (set up a separate rule to count "not acked in time").
ts in epoch magnitude (s/ms/μs/ns) triggers wall-clock active expiry; small ordinal values (1,2,3…) stay passive (checked on event arrival).

# Scenario comparison

Scenario	Pattern skeleton	Key clause	Solves
A debounce	`A{3}`	fixed count	single-point jitter false alarm
B failure precursor	`A+ B`	greedy quantifier + sequence	sustained state then transition
C burst	`A{5,}`	lower-bound count	high-frequency repeat segment
D workflow	`Start Running+ Stop`	cross-type + quantifier	lifecycle sequence
E out-of-order	`PERMUTE(A, B)`	permutation	order doesn't matter
F time constraint	`A B WITHIN`	time window + active expiry	must complete within N seconds

Pattern Matching (MATCH_RECOGNIZE) — full syntax reference (PATTERN / DEFINE / MEASURES / navigation / aggregates / SUBSET / FINAL-RUNNING / SKIP)
How to Run Case SQL — the unified Go boilerplate
Analytic functions — adjacent-event change detection (lag/had_changed), complementary to CEP

Edit this page on GitHub

Last Updated: 2026/07/14, 05:43:12

← IoT Temperature Alerting and Metrics Aggregation

Device Fault Pattern Recognition (MATCH_RECOGNIZE)

# Device Fault Pattern Recognition Case

# Scenario A: Consecutive threshold debounce (A{3})

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario B: Rise then sudden drop (failure precursor, A+ B)

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario C: Vibration burst (A{5,})

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario D: Start/stop workflow (cross-event-type sequence)

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario E: Out-of-order auth (PERMUTE, any order)

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario F: Time-constrained confirmation (WITHIN)

# Business goal

# SQL

# Input and output

# Behavior notes

# Scenario comparison

# 📚 Related docs

# Scenario A: Consecutive threshold debounce (`A{3}`)

# Scenario B: Rise then sudden drop (failure precursor, `A+ B`)

# Scenario C: Vibration burst (`A{5,}`)

# Scenario E: Out-of-order auth (`PERMUTE`, any order)

# Scenario F: Time-constrained confirmation (`WITHIN`)