Implementation of sequenceNextNode - achimbab/ClickHouse GitHub Wiki

The sequenceNextNode(timestamp_column, event_column, event1, event2, ...) is an aggregate function that returns a value of next event that matched the events.

sequenceNextNode([descending_order])(timestamp, event_column, event1, event2, ... eventN)
return:
  event_column[next_index], if the pattern is matched and next value exists.
  null, if the pattern isn’t matched or next value doesn't exist.

For instance, it can be used when events are A->B->C->E->F and you want to know the event following B->C, which is E.

It is useful for flow analysis.

The query statement searching the event following B->C :

CREATE TABLE test_flow (dt DateTime, id int, action String) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(dt) order by id;
INSERT INTO test_flow VALUES (1, 1, 'A') (2, 1, 'B') (3, 1, 'C') (4, 1, 'E') (5, 1, 'F');
SELECT id, sequenceNextNode(0)(dt, action, action = 'B', action = 'C') FROM test_flow GROUP BY id;

Thank you.