gistfile1.txt

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.)