sequence_match_events

Aggregate function that returns the timestamps of each matched condition step in a pattern. This is the diagnostic counterpart to sequence_match: instead of returning a boolean, it returns the actual timestamps where each (?N) step was satisfied.

Signature

sequence_match_events(pattern VARCHAR, timestamp TIMESTAMP,
                      cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> LIST(TIMESTAMP)

Parameters:

Parameter	Type	Description
`pattern`	`VARCHAR`	Pattern string (same syntax as `sequence_match`)
`timestamp`	`TIMESTAMP`	Event timestamp
`cond1..condN`	`BOOLEAN`	Event conditions (2 to 32)

Returns: LIST(TIMESTAMP) -- a list of timestamps corresponding to each matched (?N) step in the pattern. Returns an empty list if no match is found.

Usage

-- Get the timestamps where view and purchase occurred
SELECT user_id,
  sequence_match_events('(?1).*(?2)', event_time,
    event_type = 'view',
    event_type = 'purchase'
  ) as match_timestamps
FROM events
GROUP BY user_id;
-- Returns e.g. [2024-01-01 10:00:00, 2024-01-01 11:30:00]

Behavior

Events are sorted by (timestamp, conditions).
The pattern is compiled and executed using a timestamp-collecting variant of the NFA engine.
When a (?N) condition step matches, the event's timestamp is recorded.
If the full pattern matches, the collected timestamps of the first complete match are returned as a list.
If the full pattern never matches, the timestamps of the longest partial chain matched anywhere are returned — exactly ClickHouse's sequenceMatchEvents behavior ("timestamps for the longest chain of events matched the pattern"). The list is empty only when no condition ever fired.

The returned list contains one timestamp per matched (?N) step. Wildcard steps (. and .*) and time constraint steps do not contribute timestamps to the output.

Example

Given events for a user with pattern (?1).*(?2):

event_time	cond1 (view)	cond2 (purchase)
10:00	true	false
10:15	false	false
10:30	false	true

Result: [10:00, 10:30]

The timestamp of the (?1) match (10:00) and the (?2) match (10:30).

Pattern Syntax

Uses the same pattern syntax as sequence_match. Refer to that page for the full syntax reference.

Errors

A malformed pattern aborts the query with the parser's position-annotated message instead of silently returning NULL:

invalid sequence pattern '(?1)(?': pattern error at position 6: ...

A NULL pattern yields a NULL result (lenient), matching SQL aggregate conventions.

Implementation

The NFA executor uses a separate NfaStateWithTimestamps type that tracks collected: Vec<i64> alongside the standard state index. This keeps the timestamp collection path separate from the performance-critical execute_pattern and count_pattern code paths.

Operation	Complexity
Update	O(1) amortized (event append)
Combine	O(m) where m = events in other state
Finalize	O(n * s) NFA execution, where n = events, s = pattern steps
Space	O(n) -- all collected events

At benchmark scale, sequence_match_events processes 100 million events in 1.07 s (93 Melem/s).

duckdb-behavioral