假设有这样⼀种查询需求:终端⽤户只需要查询数据的汇总结果,不关⼼明细数据,并且数据的汇总条件是预先明确的(GROUP BY 条件明确,且不会随意改变)。
对于这样的查询场景,在ClickHouse中如何解决呢?最直接的⽅ 案就是使⽤MergeTree存储数据,然后通过GROUP BY聚合查询,并利⽤ SUM聚合函数汇总结果。这种⽅案存在两个问题。
1. 存在额外的存储开销:终端⽤户不会查询任何明细数据,只关⼼汇总结果,所以不应该⼀直保存所有的明细数据。
2. 存在额外的查询开销:终端⽤户只关⼼汇总结果,虽然 MergeTree性能强⼤,但是每次查询都进⾏实时聚合计算也是⼀种性能消耗。SummingMergeTree就是为了应对这类查询场景⽽⽣的。顾名思义,它能够在合并分区的时候按照预先定义的条件聚合汇总数据,将同⼀分组下的多⾏数据汇总合并成⼀⾏,这样既减少了数据⾏,⼜降低了后续汇总查询的开销。
1、未指定求和的字段
drop table if exists summing_table;
CREATE TABLE summing_table
(
id String,
city String,
sal UInt32,
comm Float64,
ctime DateTime
) ENGINE = SummingMergeTree()
PARTITION BY toDate(ctime)
ORDER BY (id, city)
PRIMARY KEY id;
-- 在合并的时候 ,分区内, 相同排序的⾏数据的所有的数值字段都会求和(sum)
-- 插⼊数据
insert into summing_table
values
(1,'shanghai',10,20,'2021-06-12 01:11:12'),
(1,'shanghai',20,30,'2021-06-12 01:11:12'),
(3,'shanghai',10,20,'2021-11-12 01:11:12'),
(3,'Beijing',10,20,'2021-11-12 01:11:12') ;
optimize table summing_table ;
select * from summing_table
2、指定求和的字段
上⾯的例⼦中没有指定sum的字段 ,那么表中符合要求的所有的数值字段都会进⾏求和 ,我们可以在建表的时候指定求和的字段。
drop table if exists summing_table2 ;
CREATE TABLE summing_table2(
id String,
city String,
money UInt32,
num UInt32,
ctime DateTime
)ENGINE = SummingMergeTree(money)
PARTITION BY toDate(ctime)
ORDER BY city ;
--每个城市每天的销售总额
insert into summing_table2 values(1,'BJ',100,11,now()),
(2,'BJ',100,11,now()),
(3,'BJ',100,11,now()),
(4,'NJ',100,11,now()),
(5,'NJ',100,11,now()),
(6,'SH',100,11,now()),
(7,'BJ',100,11,'2021-05-18 11:11:11'),
(8,'BJ',100,11,'2021-05-18 11:11:11') ;
SELECT * FROM summing_table2 ;
总结:
(1)⽤ORBER BY排序键作为聚合数据的条件Key。
(2)只有在合并分区的时候才会触发汇总的逻辑。
(3)以数据分区为单位来聚合数据。当分区合并时,同⼀数据分区内聚合Key相同的数据会被合并汇总,⽽不同分区之间的数据则不会被汇总。
(4)如果在定义引擎时指定了columns汇总列(⾮主键的数值类 型字段),则SUM汇总这些列字段;如果未指定,则聚合所有⾮主键的数值类型字段。
(5)在进⾏数据汇总时,因为分区内的数据已经基于ORBER BY排序,所以能够找到相邻且拥有相同聚合Key的数据。
(6)在汇总数据时,同⼀分区内,相同聚合Key的多⾏数据会合并成⼀⾏。其中,汇总字段会进⾏SUM计算;对于那些⾮汇总字段,则会使⽤第⼀⾏数据的取值。
(7)⽀持嵌套结构,但列字段名称必须以Map后缀结尾。嵌套类 型中,默认以第⼀个字段作为聚合Key。除第⼀个字段以外,任何名称 以Key、Id或Type为后缀结尾的字段,都将和第⼀个字段⼀起组成复合 Key。