How to use SequenceMatch

Question

Regarding the sequencematch function, I have read the official documentation, but still have a few problems when trying it on my computer.

The following is my table creation statement

drop table tbl;
create table tbl(uid int, time DateTime, number UInt8) ENGINE = MergeTree () PRIMARY KEY (uid);
-- number=1 --> PlayerLogin
-- number=2 --> ChargeFlow
-- number=3 --> PlayerLogout
insert into tbl values
   (1, 1, 1), 
   (1, 2, 2), 
   (1, 3, 2), 
   (1, 4, 3), 
   (2, 1, 1), 
   (2, 2, 2),
   (2, 3, 3),
   (2, 4, 2);
   (3, 1, 1), 
   (3, 2, 3),
   (3, 3, 2),
   (3, 4, 2),
   (4, 1, 1), 
   (4, 3, 2),
   (4, 2, 3);

Firstly i need to find out the users who exist such number sequence: 1-2-2, and there cannot be 3 between 1 and 2, so I wrote the following sql:

SELECT uid, sequenceMatch('(?1)(?2).*(?2)')(time, number = 1, number = 2,  number = 3) FROM tbl group by uid;

There is no problem with the return of this SQL:

uid   sequenceMatch 
1          1
2          1
3          0
4          0

Secondly, i want to add a condition that events 1 and 2 cannot occur more than 1 day ago, so I wrote the following sql:

SELECT uid, sequenceMatch('(?1)(?t<=86400)(?2).*(?2)')(time, number = 1, number = 2,  number = 3) FROM tbl group by uid;

But the result is a little wrong:

uid   sequenceMatch 
1          1
2          1
3          1
4          0

As we can see, uid=3 is added to the result set, but for the event that occurs with uid=3, there is 3 between 1 and 2, so it should not appear in the result set.

I checked the introduction on the official website:

(?t operator value) — Sets the time in seconds that should separate two events.

We define t as the difference in seconds between two times, For example, pattern (?1)(?t>1800)(?2) matches events that occur more than 1800 seconds from each other. pattern (?1)(?t>10000)(?2) matches events that occur more than 10000 seconds from each other. An arbitrary number of any events can lay between these events. You can use the >=, >, <, <=, == operators.

It seems that once the t operator is added, any other event can occur between the two events. This does not meet my needs. Is there any solution?

See all content above