sequence_count

Aggregate function that counts the number of non-overlapping occurrences of a pattern in the event stream.

Signature

sequence_count(pattern VARCHAR, timestamp TIMESTAMP,
               cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> BIGINT

Parameters:

Returns: BIGINT -- the number of non-overlapping matches of the pattern in the event stream.

Usage

-- Count how many times a user viewed then purchased
SELECT user_id,
  sequence_count('(?1)(?2)', event_time,
    event_type = 'view',
    event_type = 'purchase'
  ) as conversion_count
FROM events
GROUP BY user_id;

Behavior

1. Events are sorted by timestamp.
2. The pattern is compiled and matched with the same engine as sequence_match.
3. Each time the pattern matches, the count increments and the search resumes where the match ended: after the last matched event or, when the pattern ends with a time constraint, at the event that satisfied it.
4. Matches are non-overlapping: once a set of events is consumed by a match, those events cannot participate in another match. A match that consumes no events (for example .*, or (?t<=6).*) still advances one event, as in ClickHouse, so sequence_count('.*', ...) counts one match per event.

Example

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

Result: 2

• First match: events at 10:00 and 10:10.
• Second match: events at 10:20 and 10:30.
• The event at 10:40 has no subsequent cond2 event.

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: ...

An out-of-range condition number and a time constraint with no (?N) or . before it are errors too (see sequence_match).

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

The pattern argument must be the same for every row of a group (normally a literal). A group with two different non-NULL values is an error, whatever the row order (... the pattern argument must be the same for every row of a group). NULL values are ignored.

Timestamp types

TIMESTAMP and TIMESTAMPTZ are both accepted and read as microseconds since the epoch; for TIMESTAMPTZ that is the instant itself, independent of the session time zone. Casting TIMESTAMPTZ to TIMESTAMP instead converts to local time, which can reorder events around a daylight-saving change. TIMESTAMP_S, TIMESTAMP_MS and DATE are cast to TIMESTAMP implicitly. TIMESTAMP_NS is too, which truncates to microseconds: events less than a microsecond apart become ties.

Implementation

At benchmark scale, sequence_count processes 100 million events in 1.18 s (85 Melem/s).

See Also

• sequence_match -- check whether the pattern matches (boolean)
• sequence_match_events -- return the timestamps of each matched step
• sequence_next_node -- find the next event value after a pattern match