clickhouse 26.6和duckdb 1.6dev的group by cube查询比较

对1亿行随机数分组汇总
duckdb 1.6dev

C:\\d>duckdb0619
DuckDB v1.6.0–dev9098 (Development Version, 2bfea2aa19)
Enter ".help" for usage hints.
memory D create table ren as select
((random()*1000)::int%2+1)::int c1,
((random()*1000)::int%100+1)::int c2,
((random()*1000)::int%30+1)::int c3,
((random()*1000)::int%60+1)::int c4,
((random()*1000)::int%5+1)::int c5
from generate_series(1,1e8::int);
memory D .timer on
memory D create table rc1c2c3c4 as select c1,c2,c3,c4,sum(1)cnt from ren group by cube(c1,c2,c3,c4);
Run Time (s): real 1.593 user 17.390625 sys 0.968750
memory D select count(*) from rc1c2c3c4;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 572973 │
└──────────────┘
Run Time (s): real 0.010 user 0.000000 sys 0.000000

clickhouse 26.6

C:\\d>wsl
root@DESKTOP–59T6U68:/mnt/c/d# ./clickhouse
ClickHouse local version 26.6.1.731 (official build).

:) create table ren as select
((rand()*1000)::int%2+1)::int c1,
((rand()*1000)::int%100+1)::int c2,
((rand()*1000)::int%30+1)::int c3,
((rand()*1000)::int%60+1)::int c4,
((rand()*1000)::int%5+1)::int c5
from generate_series(1,100000000);

CREATE TABLE ren
AS SELECT
CAST(((CAST((rand() * 1000), 'int') % 2) + 1), 'int') AS c1,
CAST(((CAST((rand() * 1000), 'int') % 100) + 1), 'int') AS c2,
CAST(((CAST((rand() * 1000), 'int') % 30) + 1), 'int') AS c3,
CAST(((CAST((rand() * 1000), 'int') % 60) + 1), 'int') AS c4,
CAST(((CAST((rand() * 1000), 'int') % 5) + 1), 'int') AS c5
FROM generate_series(1, 100000000)

Query id: e6a83d26–e6a9–40ba–8eed–b74f285fbc78

Ok.

100000000 rows in set. Elapsed: 5.758 sec. Processed 100.00 million rows, 800.00 MB (17.37 million rows/s., 138.94 MB/s.)
Peak memory usage: 63.47 MiB.

:) create table rc1c2c3c4 as select c1,c2,c3,c4,sum(1)cnt from ren group by cube(c1,c2,c3,c4);

CREATE TABLE rc1c2c3c4
AS SELECT
c1,
c2,
c3,
c4,
sum(1) AS cnt
FROM ren
GROUP BY
c1,
c2,
c3,
c4
WITH CUBE

Query id: b2eca8f3–32f2–4668–9786–d69cfd038f53

Ok.

1168 rows in set. Elapsed: 0.322 sec. Processed 100.00 million rows, 1.60 GB (310.67 million rows/s., 4.97 GB/s.)
Peak memory usage: 4.09 MiB.

与duckdb比较，clickhouse生成数据和汇总都快得出奇，但后者的行数明显过少，查看数据。

:) select * from ren;

SELECT *
FROM ren

Query id: a402aca9–009b–4399–9770–5bf3123b31db

┌─c1─┬──c2─┬──c3─┬──c4─┬─c5─┐
1. │ 1 │ 57 │ 7 │ 37 │ 2 │
2. │ 1 │ –47 │ –7 │ –7 │ –2 │
3. │ 1 │ –3 │ –13 │ –43 │ –3 │
4. │ 1 │ –59 │ 1 │ 1 │ 1 │
5. │ 1 │ –55 │ –25 │ –55 │ 0 │
6. │ 1 │ 1 │ 21 │ 21 │ 1 │
7. │ 1 │ –75 │ –5 │ –35 │ 0 │
8. │ 1 │ 93 │ 13 │ 13 │ 3 │
9. │ 1 │ 97 │ 7 │ 37 │ 2 │
10. │ 1 │ 61 │ 1 │ 1 │ 1 │
11. │ 1 │ –59 │ 1 │ 1 │ 1 │
12. │ 1 │ 81 │ 1 │ 1 │ 1 │
13. │ 1 │ 5 │ 25 │ 25 │ 5 │
14. │ 1 │ –23 │ –13 │ –43 │ –3 │

4227068. │ 1 │ 49 │ 19 │ 49 │ 4 │
4227069. │ 1 │ 5 │ 25 │ 25 │ 5 │
4227070. │ 1 │ –31 │ –1 │ –31 │ –1 │
4227071. │ 1 │ 93 │ 3 │ 33 │ 3 │
4227072. │ 1 │ 77 │ 27 │ 57 │ 2 │
└─c1─┴──c2─┴──c3─┴──c4─┴─c5─┘
Showed 1000 out of 100000000 rows.

100000000 rows in set. Elapsed: 0.497 sec. Processed 100.00 million rows, 2.00 GB (201.16 million rows/s., 4.02 GB/s.)
Peak memory usage: 188.60 MiB.

:) select * from rc1c2c3c4 limit 10;

SELECT *
FROM rc1c2c3c4
LIMIT 10

Query id: c892b34d–8eb4–46cd–811e–cecddf035987

┌─c1─┬─c2─┬─c3─┬─c4─┬────cnt─┐
1. │ 1 │ 93 │ 13 │ 13 │ 667216 │
2. │ 1 │ 81 │ 21 │ 21 │ 666623 │
3. │ 1 │ 41 │ 1 │ 1 │ 665197 │
4. │ 1 │ 5 │ 5 │ 5 │ 666132 │
5. │ 1 │ 9 │ 29 │ 29 │ 666747 │
6. │ 1 │ 77 │ 17 │ 17 │ 666084 │
7. │ 1 │ 5 │ 25 │ 25 │ 666766 │
8. │ 1 │ 89 │ 29 │ 29 │ 666258 │
9. │ 1 │ 85 │ 5 │ 5 │ 667259 │
10. │ 1 │ 49 │ 9 │ 9 │ 666100 │
└────┴────┴────┴────┴────────┘

10 rows in set. Elapsed: 0.005 sec. Processed 1.17 thousand rows, 28.03 KB (217.09 thousand rows/s., 5.21 MB/s.)
Peak memory usage: 39.17 KiB.

:)

:) select rand();

SELECT rand()

Query id: 5000b861–343a–4e26–b35c–0f40e4c66415

┌─────rand()─┐
1. │ 4067771733 │ — 4.07 billion
└────────────┘

1 row in set. Elapsed: 0.003 sec.

create table ren as select
(rand()%2+1)::int c1,
(rand()%100+1)::int c2,
(rand()%30+1)::int c3,
(rand()%60+1)::int c4,
(rand()%5+1)::int c5
from generate_series(1,100000000);

CREATE TABLE ren
AS SELECT
CAST(((rand() % 2) + 1), 'int') AS c1,
CAST(((rand() % 100) + 1), 'int') AS c2,
CAST(((rand() % 30) + 1), 'int') AS c3,
CAST(((rand() % 60) + 1), 'int') AS c4,
CAST(((rand() % 5) + 1), 'int') AS c5
FROM generate_series(1, 100000000)

Query id: 8907c2c1–3cb2–4903–a0a1–d350a8407d36

Ok.

100000000 rows in set. Elapsed: 5.601 sec. Processed 100.00 million rows, 800.00 MB (17.85 million rows/s., 142.84 MB/s.)
Peak memory usage: 63.60 MiB.

:) select * from ren limit 20;

SELECT *
FROM ren
LIMIT 20

Query id: 8e3d2756–a3b3–4814–b84d–dbc199c1fa95

┌─c1─┬─c2─┬─c3─┬─c4─┬─c5─┐
1. │ 2 │ 26 │ 16 │ 46 │ 1 │
2. │ 1 │ 49 │ 19 │ 49 │ 4 │
3. │ 1 │ 53 │ 23 │ 53 │ 3 │
4. │ 2 │ 4 │ 24 │ 24 │ 4 │
5. │ 2 │ 78 │ 18 │ 18 │ 3 │
6. │ 2 │ 62 │ 12 │ 42 │ 2 │
7. │ 1 │ 3 │ 13 │ 43 │ 3 │
8. │ 1 │ 61 │ 1 │ 1 │ 1 │
9. │ 2 │ 2 │ 12 │ 42 │ 2 │
10. │ 1 │ 29 │ 19 │ 49 │ 4 │
11. │ 1 │ 93 │ 3 │ 33 │ 3 │
12. │ 2 │ 36 │ 26 │ 56 │ 1 │
13. │ 1 │ 35 │ 25 │ 55 │ 5 │
14. │ 2 │ 46 │ 6 │ 6 │ 1 │
15. │ 1 │ 39 │ 29 │ 59 │ 4 │
16. │ 2 │ 84 │ 4 │ 4 │ 4 │
17. │ 2 │ 28 │ 18 │ 48 │ 3 │
18. │ 2 │ 52 │ 2 │ 32 │ 2 │
19. │ 2 │ 40 │ 10 │ 40 │ 5 │
20. │ 1 │ 27 │ 17 │ 47 │ 2 │
└────┴────┴────┴────┴────┘

20 rows in set. Elapsed: 0.005 sec.

:)
:) select rand(),rand();

SELECT
rand(),
rand()

Query id: 1af2ae02–cec7–4703–8bd3–2466b67260da

┌─────rand()─┬─────rand()─┐
1. │ 3590268032 │ 3590268032 │ — 3.59 billion
└────────────┴────────────┘

负数是整数乘法溢出的结果，rand()返回32位整数，去掉多余的*1000，看出问题了，对20, 60, 100取模的结果都一样，原因是clickhouse在同一行多次调用rand()，返回同一个值，所以从c1到c5, 它们的值都是固定的函数关系，导致分组过少。
而duckdb的同一行多个random()返回不同的值。

memory D select random(),random();
┌─────────────────────┬────────────────────┐
│ random() │ random() │
│ double │ double │
├─────────────────────┼────────────────────┤
│ 0.46592717838277703 │ 0.7400053681100808 │
└─────────────────────┴────────────────────┘

暂时没找到方法让clickhouse返回不同的值。先把duckdb数据表复制到parquet文件，然后用clickhouse查询此文件。

memory D copy ren to 'ren1e8.parquet';

:) create table rc1c2c3c4 as select c1,c2,c3,c4,sum(1)cnt from file('ren1e8.parquet') group by cube(c1,c2,c3,c4);

CREATE TABLE rc1c2c3c4
AS SELECT
c1,
c2,
c3,
c4,
sum(1) AS cnt
FROM file('ren1e8.parquet')
GROUP BY
c1,
c2,
c3,
c4
WITH CUBE

Query id: 143fa1f0–b99c–4dae–a727–6944ee250bab

Ok.

572973 rows in set. Elapsed: 1.476 sec. Processed 100.00 million rows, 239.94 MB (67.75 million rows/s., 162.55 MB/s.)
Peak memory usage: 253.44 MiB.

这下返回行数和duckdb完全相同，和duckdb查询内存表的时间相比，快了0.2s。再将文件复制到clickhouse内存表。

:) create table ren as select * from file('ren1e8.parquet');

CREATE TABLE ren
AS SELECT *
FROM file('ren1e8.parquet')

Query id: 0fc6193f–b75d–4ef8–b906–d8e8f2a738a6

Ok.

100000000 rows in set. Elapsed: 5.980 sec. Processed 100.00 million rows, 277.89 MB (16.72 million rows/s., 46.47 MB/s.)
Peak memory usage: 277.83 MiB.

:) create table rc1c2c3c4m as select c1,c2,c3,c4,sum(1)cnt from ren group by cube(c1,c2,c3,c4);

CREATE TABLE rc1c2c3c4m
AS SELECT
c1,
c2,
c3,
c4,
sum(1) AS cnt
FROM ren
GROUP BY
c1,
c2,
c3,
c4
WITH CUBE

Query id: a5561df0–ad59–4395–9976–5ad60417bfe9

Ok.

572973 rows in set. Elapsed: 1.252 sec. Processed 100.00 million rows, 1.60 GB (79.88 million rows/s., 1.28 GB/s.)
Peak memory usage: 215.22 MiB.

结果又快了0.2秒。
补记：
我把rand()返回同一个值的问题在clickhouse存储库中提问，得到了解答(https://github.com/ClickHouse/ClickHouse/discussions/108051)。很简单，填一个不同的参数即可。

./clickhouse
ClickHouse local version 26.6.1.731 (official build).

:) create table ren as select
(rand(1)%2+1)::int c1,
(rand(2)%100+1)::int c2,
(rand(3)%30+1)::int c3,
(rand(4)%60+1)::int c4,
(rand(5)%5+1)::int c5
from generate_series(1,100000000);

CREATE TABLE ren
AS SELECT
CAST(((rand(1) % 2) + 1), 'int') AS c1,
CAST(((rand(2) % 100) + 1), 'int') AS c2,
CAST(((rand(3) % 30) + 1), 'int') AS c3,
CAST(((rand(4) % 60) + 1), 'int') AS c4,
CAST(((rand(5) % 5) + 1), 'int') AS c5
FROM generate_series(1, 100000000)

Query id: b7d52a36–042a–4357–b2dc–c49a3d277b84

Ok.

100000000 rows in set. Elapsed: 5.764 sec. Processed 100.00 million rows, 800.00 MB (17.34 million rows/s., 138.76 MB/s.)
Peak memory usage: 63.60 MiB.

:) create table rc1c2c3c4m as select c1,c2,c3,c4,sum(1)cnt from ren group by cube(c1,c2,c3,c4);

CREATE TABLE rc1c2c3c4m
AS SELECT
c1,
c2,
c3,
c4,
sum(1) AS cnt
FROM ren
GROUP BY
c1,
c2,
c3,
c4
WITH CUBE

Query id: f391c8e7–9a6b–4cc0–9472–5451f4e48204

Ok.

572973 rows in set. Elapsed: 1.091 sec. Processed 100.00 million rows, 1.60 GB (91.64 million rows/s., 1.47 GB/s.)
Peak memory usage: 215.22 MiB.

:)