Skip to content

Instantly share code, notes, and snippets.

@akurkin
Forked from alexey-milovidov/example.txt
Created June 19, 2016 18:27
Show Gist options
  • Save akurkin/18bfe2d9933fc6c1dca67b1031ea15aa to your computer and use it in GitHub Desktop.
Save akurkin/18bfe2d9933fc6c1dca67b1031ea15aa to your computer and use it in GitHub Desktop.
Example of sequenceMatch aggregate function usage in ClickHouse.
$ 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