Row pattern recognition with MATCH_RECOGNIZE

[bluestreak01]

Summary

Implement SQL:2016 MATCH_RECOGNIZE for row pattern recognition over ordered data. This enables detecting temporal patterns in time-series — sequences of states like "ramp-up → plateau → sudden drop" — using declarative SQL instead of fragile nested window function workarounds.

Time-series databases are the most natural home for this feature. Sensor data, financial tick data, and telemetry logs are inherently sequential, and the questions asked of them are fundamentally pattern-matching problems: "find all sequences where X happened, then Y, then Z."

Syntax

SELECT *
FROM telemetry
MATCH_RECOGNIZE (
  PARTITION BY robot_id, sensor
  ORDER BY ts
  MEASURES
    A.ts AS pattern_start,
    LAST(C.ts) AS pattern_end,
    MAX(B.value) AS peak_value
  ONE ROW PER MATCH          -- or ALL ROWS PER MATCH
  PATTERN (A B+ C)
  DEFINE
    A AS value > PREV(value),               -- rising
    B AS value > PREV(value) * 0.95,        -- sustaining (within 5%)
    C AS value < PREV(value) * 0.8          -- sudden drop (> 20%)
)

Clause	Purpose
PARTITION BY	Independent pattern matching per series
ORDER BY	Row ordering (always timestamp for time-series)
MEASURES	Values to extract from each match
PATTERN	Regex-like sequence of row categories
DEFINE	Boolean conditions that classify each row
ONE ROW PER MATCH	One summary row per pattern instance
ALL ROWS PER MATCH	Every row in the match, with pattern variables

Use Cases

Thermal runaway detection

-- Find: temperature steadily rising → plateau at dangerous level → thermal shutdown
SELECT robot_id, pattern_start, shutdown_ts, peak_temp, duration
FROM telemetry
WHERE sensor = 'motor_temp'
MATCH_RECOGNIZE (
  PARTITION BY robot_id
  ORDER BY ts
  MEASURES
    FIRST(A.ts) AS pattern_start,
    C.ts AS shutdown_ts,
    MAX(B.value) AS peak_temp,
    C.ts - FIRST(A.ts) AS duration
  ONE ROW PER MATCH
  PATTERN (A+ B+ C)
  DEFINE
    A AS value > PREV(value),                    -- rising
    B AS abs(value - PREV(value)) < 0.5,         -- plateau
    C AS value < PREV(value) * 0.85              -- sudden drop (shutdown)
)

Collision / near-miss detection

-- Pattern: approach (closing distance) → emergency stop → reverse
SELECT robot_id, approach_start, stop_ts, min_distance
FROM telemetry
WHERE sensor = 'obstacle_distance'
MATCH_RECOGNIZE (
  PARTITION BY robot_id
  ORDER BY ts
  MEASURES
    FIRST(A.ts) AS approach_start,
    B.ts AS stop_ts,
    B.value AS min_distance,
    LAST(C.ts) AS recovery_end
  ONE ROW PER MATCH
  PATTERN (A+ B C+)
  DEFINE
    A AS value < PREV(value) AND value < 2.0,    -- closing, within 2m
    B AS abs(value - PREV(value)) < 0.01,        -- stopped
    C AS value > PREV(value)                     -- reversing away
)

Calibration drift detection

-- Pattern: slow creep away from zero → sudden correction → creep again
SELECT robot_id, sensor, drift_start, correction_ts, max_drift, recurrence_start
FROM telemetry
WHERE sensor LIKE 'joint_%_error'
MATCH_RECOGNIZE (
  PARTITION BY robot_id, sensor
  ORDER BY ts
  MEASURES
    FIRST(A.ts) AS drift_start,
    B.ts AS correction_ts,
    MAX(A.value) AS max_drift,
    FIRST(C.ts) AS recurrence_start
  ONE ROW PER MATCH
  PATTERN (A{5,} B C{5,})
  DEFINE
    A AS abs(value) > abs(PREV(value)),          -- monotonically drifting
    B AS abs(value) < abs(PREV(value)) * 0.3,    -- sudden correction (>70%)
    C AS abs(value) > abs(PREV(value))           -- drifting again
)

Degradation trending

-- Find sensors showing staircase degradation: stable → step up → stable at new level → step up
SELECT robot_id, sensor, num_steps, first_step_ts, final_level
FROM telemetry
MATCH_RECOGNIZE (
  PARTITION BY robot_id, sensor
  ORDER BY ts
  MEASURES
    COUNT(B.ts) AS num_steps,
    FIRST(B.ts) AS first_step_ts,
    LAST(A.value) AS final_level
  ONE ROW PER MATCH
  PATTERN ((A+ B)+ A+)
  DEFINE
    A AS abs(value - PREV(value)) < 0.1,         -- stable
    B AS value > PREV(value) + 1.0               -- step up (> 1.0 jump)
)

Capital markets: momentum patterns

-- Find: accumulation (low volume, flat price) → breakout (high volume, sharp move)
SELECT symbol, accumulation_start, breakout_ts, breakout_direction, volume_ratio
FROM trades
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES
    FIRST(A.ts) AS accumulation_start,
    FIRST(B.ts) AS breakout_ts,
    CASE WHEN B.price > A.price THEN 'up' ELSE 'down' END AS breakout_direction,
    AVG(B.volume) / AVG(A.volume) AS volume_ratio
  ONE ROW PER MATCH
  PATTERN (A{10,} B{3,})
  DEFINE
    A AS volume < AVG(volume) * 0.5
       AND abs(price - PREV(price)) < price * 0.001,
    B AS volume > AVG(volume) * 2.0
)

IoT: power anomaly detection

-- Find: normal consumption → spike → sustained high → return to normal
-- Indicates equipment malfunction or unexpected load
SELECT device_id, spike_start, spike_end, peak_watts, normal_watts
FROM power_telemetry
MATCH_RECOGNIZE (
  PARTITION BY device_id
  ORDER BY ts
  MEASURES
    LAST(N.ts) AS normal_before,
    FIRST(S.ts) AS spike_start,
    LAST(H.ts) AS spike_end,
    MAX(H.value) AS peak_watts,
    AVG(N.value) AS normal_watts
  ONE ROW PER MATCH
  PATTERN (N+ S H+ R)
  DEFINE
    N AS value BETWEEN 100 AND 500,              -- normal range
    S AS value > PREV(value) * 2,                -- spike (> 2x jump)
    H AS value > 800,                            -- sustained high
    R AS value < 500                             -- return to normal
)

Why this belongs in a time-series database

MATCH_RECOGNIZE is part of SQL:2016 but is only implemented by Oracle, Trino/Presto, and Apache Flink. General-purpose databases largely haven't adopted it.

Time-series databases have the strongest case for it:

• Data is inherently ordered by time — the ORDER BY ts is always there
• The questions are sequential — "what happened, then what happened next?"
• The alternative (nested window functions with conditional sums for sessionization) is fragile, hard to read, and hard to optimize
• Pattern matching over sorted, partitioned data aligns perfectly with QuestDB's storage model — especially with compound sort keys (Compound sort keys for multi-series tables #119) where each series is a contiguous sorted run

With #119, the engine can seek directly to a specific robot's sensor segment and run pattern matching over a contiguous sorted run. This is the ideal execution model.

Execution model

Aspect	Description
Input	Sorted rows within a partition (leverages compound sort key ordering from #119)
Matching	NFA (nondeterministic finite automaton) or row-by-row state machine
Output	One row per match (summary) or all rows per match (annotated)
Parallelism	Independent per PARTITION BY value — each series can be matched in parallel
Early termination	Patterns with bounded quantifiers can skip ahead on mismatch

SQL:2016 conformance scope

Include in initial implementation

• PARTITION BY, ORDER BY
• MEASURES with aggregates and navigation (FIRST, LAST, PREV, NEXT)
• PATTERN with concatenation, alternation (|), quantifiers (+, *, ?, {n,m})
• DEFINE with boolean expressions referencing current and previous rows
• ONE ROW PER MATCH, ALL ROWS PER MATCH
• AFTER MATCH SKIP options (past last row, to next row, etc.)

Defer

• SUBSET (union of pattern variables)
• PERMUTE (match pattern variables in any order)
• Nested MATCH_RECOGNIZE

Related

• Compound sort keys for multi-series tables #119 — Compound sort keys (provides contiguous sorted runs per series — ideal input for pattern matching)
• Statistical SQL functions for fleet-wide and cohort analysis #120 — Statistical SQL functions (statistical aggregates usable within DEFINE conditions and MEASURES)
• Parallel HORIZON JOIN for markout analysis #103 — HORIZON JOIN (complementary: HORIZON JOIN for event impact analysis, MATCH_RECOGNIZE for event detection)
• CDC and Pub-Sub with client-side callbacks #111 — CDC/Pub-Sub (pattern matches on live data could trigger callbacks for real-time alerting)