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