Skip to content

Instantly share code, notes, and snippets.

@den-crane
Created September 17, 2019 16:16
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save den-crane/d88f9ef887f879c7be71fb40ae943a94 to your computer and use it in GitHub Desktop.
Save den-crane/d88f9ef887f879c7be71fb40ae943a94 to your computer and use it in GitHub Desktop.
create table rank (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;
create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;
create materialized view nop_mv to rank as select id, rank_column, sumState(c) as c0 from nop group by id, rank_column
insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)
optimize table rank final;
create table rank_a (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;
insert into rank_a select * from rank where cityHash64(rank_column)%2=1
optimize table rank_a final;
create table rank_b (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;
insert into rank_b select * from rank where cityHash64(rank_column)%2=0
optimize table rank_b final;
-----------------------------------------------------------------
set max_threads=1
SELECT rank_column,
sumMerge(c0) AS c
FROM rank
GROUP BY rank_column
ORDER BY c DESC
LIMIT 20
20 rows in set. Elapsed: 16.566 sec. Processed 100.00 million rows, 3.48 GB (6.04 million rows/s., 209.94 MB/s.)
SELECT
rank_column,
c
FROM
(
SELECT
rank_column,
sumMerge(c0) AS c
FROM rank_a
GROUP BY rank_column
ORDER BY c DESC
LIMIT 20
UNION ALL
SELECT
rank_column,
sumMerge(c0) AS c
FROM rank_b
GROUP BY rank_column
ORDER BY c DESC
LIMIT 20
)
ORDER BY c DESC
LIMIT 20
20 rows in set. Elapsed: 15.670 sec. Processed 100.00 million rows, 3.48 GB (6.38 million rows/s., 221.94 MB/s.)
SELECT version()
┌─version()────┐
│ 19.15.1.1285 │
└──────────────┘
===================
== SimpleAggregateFunction ==
drop table rank;
drop table rank_a;
drop table rank_b;
drop table nop;
drop table nop_mv;
create table rank (id UInt64, rank_column LowCardinality(String), c0 SimpleAggregateFunction(sum, Int64))
engine = AggregatingMergeTree order by id;
create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;
create materialized view nop_mv to rank as select id, rank_column, sum(c) as c0 from nop group by id, rank_column
insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)
SELECT rank_column,
sum(c0) AS c
FROM rank
GROUP BY rank_column
ORDER BY c DESC
LIMIT 20
20 rows in set. Elapsed: 15.501 sec. Processed 100.00 million rows, 2.43 GB (6.45 million rows/s., 156.50 MB/s.)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment