-
-
Save akurkin/18bfe2d9933fc6c1dca67b1031ea15aa to your computer and use it in GitHub Desktop.
Example of sequenceMatch aggregate function usage in ClickHouse.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ clickhouse-client | |
ClickHouse client version 0.0.53720. | |
Connecting to localhost:9000. | |
Connected to ClickHouse server version 1.1.53981. | |
:) CREATE TABLE test.funnel (userID UInt64, eventType Enum8('A' = 1, 'B' = 2), EventTime Date, Age UInt8) ENGINE = Memory | |
CREATE TABLE test.funnel | |
( | |
userID UInt64, | |
eventType Enum8('A' = 1, 'B' = 2), | |
EventTime Date, | |
Age UInt8 | |
) ENGINE = Memory | |
Ok. | |
0 rows in set. Elapsed: 0.014 sec. | |
:) INSERT INTO test.funnel VALUES (1,'A','2001-01-01',35),(2,'A','2001-01-01',35),(1,'B','2002-01-01',35),(1,'A','2010-01-01',35),(3,'A','2001-01-01',25),(3,'B','2011-01-01',25),(4,'A','2012-01-01',35),(4,'B','2011-01-01',35) | |
INSERT INTO test.funnel VALUES | |
Ok. | |
8 rows in set. Elapsed: 0.006 sec. | |
:) SELECT * FROM test.funnel | |
SELECT * | |
FROM test.funnel | |
┌─userID─┬─eventType─┬──EventTime─┬─Age─┐ | |
│ 1 │ A │ 2001-01-01 │ 35 │ | |
│ 2 │ A │ 2001-01-01 │ 35 │ | |
│ 1 │ B │ 2002-01-01 │ 35 │ | |
│ 1 │ A │ 2010-01-01 │ 35 │ | |
│ 3 │ A │ 2001-01-01 │ 25 │ | |
│ 3 │ B │ 2011-01-01 │ 25 │ | |
│ 4 │ A │ 2012-01-01 │ 35 │ | |
│ 4 │ B │ 2011-01-01 │ 35 │ | |
└────────┴───────────┴────────────┴─────┘ | |
8 rows in set. Elapsed: 0.007 sec. | |
:) SELECT userID FROM test.funnel WHERE Age > 30 GROUP BY userID HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B') | |
SELECT userID | |
FROM test.funnel | |
WHERE Age > 30 | |
GROUP BY userID | |
HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B') | |
┌─userID─┐ | |
│ 1 │ | |
└────────┘ | |
1 rows in set. Elapsed: 0.011 sec. | |
:) SELECT count() FROM (SELECT userID FROM test.funnel WHERE Age > 30 GROUP BY userID HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B')) | |
SELECT count() | |
FROM | |
( | |
SELECT userID | |
FROM test.funnel | |
WHERE Age > 30 | |
GROUP BY userID | |
HAVING sequenceMatch('(?1)(?2)')(toDateTime(EventTime), eventType = 'A', eventType = 'B') | |
) | |
┌─count()─┐ | |
│ 1 │ | |
└─────────┘ | |
1 rows in set. Elapsed: 0.003 sec. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment