进入正文前,感谢宝子们订阅专题、点赞、评论、收藏!关注IT贫道,获取高质量博客内容!
🏡个人主页:含各种IT体系技术,IT贫道_Apache Doris,大数据OLAP体系技术栈,Kerberos安全认证-CSDN博客
📌订阅:拥抱独家专题,你的订阅将点燃我的创作热情!
👍点赞:赞同优秀创作,你的点赞是对我创作最大的认可!
⭐️ 收藏:收藏原创博文,让我们一起打造IT界的荣耀与辉煌!
✏️评论:留下心声墨迹,你的评论将是我努力改进的方向!
目录
1. SummingMergeTree建表语句
2. 示例
2.1 测试不指定聚合字段同时测试不同分区内,相同排序key数据不会被合并
2.2 测试指定一个聚合字段
2.3 测试指定多个聚合字段
该引擎继承了MergeTree引擎,当合并 SummingMergeTree 表的数据片段时,ClickHouse 会把所有具有相同主键的行合并为一行,该行包含了被合并的行中具有数值数据类型的列的汇总值,即如果存在重复的数据,会对对这些重复的数据进行合并成一条数据,类似于group by的效果,可以显著减少存储空间并加快数据查询速度。
如果用户只需要查询数据的汇总结果,不关心明细数据,并且数据的汇总条件是预先明确的,即GROUP BY的分组字段是确定的,可以使用该表引擎。
1. SummingMergeTree建表语句
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...) ENGINE = SummingMergeTree([columns])[PARTITION BY expr][ORDER BY expr][SAMPLE BY expr][SETTINGS name=value, ...]
对以上建表语句的解释如下:
- [columns]: 将要被汇总的列,或者多个列,多个列需要写在元组中。可选参数。所选的列必须是数值类型,并且不可位于主键中。如果没有指定 [columns],ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。
使用SummingMergeTree注意以下几点:
- SummingMergeTree是根据什么对两条数据进行合并的
用ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的,则会合并成一条数据,并对指定的合并字段进行聚合。
- 仅对分区内的相同排序key的数据行进行合并
以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并汇总,而不同分区之间的数据则不会被汇总。
- 如果没有指定聚合字段,会怎么聚合
如果没有指定聚合字段,则会按照非主键的数值类型字段进行聚合。
- 对于非汇总字段的数据,该保留哪一条
如果两行数据除了排序字段相同,其他的非聚合字段不相同,那么在聚合发生时,会保留最初的那条数据,新插入的数据对应的那个字段值会被舍弃。
2. 示例
2.1 测试不指定聚合字段同时测试不同分区内,相同排序key数据不会被合并
#创建表 t_summing_mt ,使用SummingMergeTree表引擎node1 :) create table t_summing_mt(:-] id UInt8,:-] name String,:-] age UInt8,:-] loc String,:-] dept String,:-] workdays UInt8,:-] salary Decimal32(2):-] ) engine = SummingMergeTree():-] order by (id,age):-] primary key id:-] partition by loc;#向表 t_summing_mt 中插入以下数据node1 :) insert into t_summing_mt values (1,'张三',18,'北京','大数据',24,10000),:-] (2,'李四',19,'上海','java',22,8000),:-] (3,'王五',20,'北京','java',26,12000);#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#向表 t_summing_mt 中插入一条排序键相同的数据node1 :) insert into t_summing_mt values (1,'马六',18,'北京','前端',27,15000);#查看表 t_summing_mt中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐│ 1 │ 马六 │ 18 │ 北京 │ 前端 │ 27 │ 15000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#手动执行optimize 命令触发合并相同分区数据node1 :) optimize table t_summing_mt;#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 51 │ 25000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘注意:我们可以看到当不指定 聚合字段时,有相同排序字段行进行聚合时,会将数值类型的字段进行聚合合并。#继续向表 t_summing_mt中插入以下数据:node1 :) insert into t_summing_mt values (1,'张三',18,'南京','java',18,12000);#查看表 t_summing_mt中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 南京 │ java │ 18 │ 12000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 51 │ 25000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#手动指定optimize 命令合并相同排序key的数据node1 :) optimize table t_summing_mt;#查看表 t_summing_mt中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 南京 │ java │ 18 │ 12000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 51 │ 25000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘注意:不同分区内相同的排序key的数据不能被合并
2.2 测试指定一个聚合字段
#删除表 t_summing_mt,重新创建表 t_summing_mt ,使用SummingMergeTree引擎node1 :) create table t_summing_mt(:-] id UInt8,:-] name String,:-] age UInt8,:-] loc String,:-] dept String,:-] workdays UInt8,:-] salary Decimal32(2):-] ) engine = SummingMergeTree(salary):-] order by (id,age):-] primary key id:-] partition by loc;#向表 t_summing_mt 中插入以下数据node1 :) insert into t_summing_mt values (1,'张三',18,'北京','大数据',24,10000),:-] (2,'李四',19,'上海','java',22,8000),:-] (3,'王五',20,'北京','java',26,12000);#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#向表 t_summing_mt 中插入一条排序键相同的数据node1 :) insert into t_summing_mt values (1,'马六',18,'北京','前端',27,15000);#查看表 t_summing_mt中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐│ 1 │ 马六 │ 18 │ 北京 │ 前端 │ 27 │ 15000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#手动执行optimize 命令触发合并相同分区数据node1 :) optimize table t_summing_mt;#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 25000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘注意:我们可以看到当指定一个聚合字段时,有相同排序字段行进行聚合时,会按照这个数值字段进行合并,其他的保留最开始一条数据的信息。
2.3 测试指定多个聚合字段
#删除表 t_summing_mt,重新创建表 t_summing_mt ,使用SummingMergeTree引擎node1 :) create table t_summing_mt(:-] id UInt8,:-] name String,:-] age UInt8,:-] loc String,:-] dept String,:-] workdays UInt8,:-] salary Decimal32(2):-] ) engine = SummingMergeTree((salary,workdays)):-] order by (id,age):-] primary key id:-] partition by loc;#向表 t_summing_mt 中插入以下数据node1 :) insert into t_summing_mt values (1,'张三',18,'北京','大数据',24,10000),:-] (2,'李四',19,'上海','java',22,8000),:-] (3,'王五',20,'北京','java',26,12000);#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#向表 t_summing_mt 中插入一条排序键相同的数据node1 :) insert into t_summing_mt values (1,'马六',18,'北京','前端',27,15000);#查看表 t_summing_mt中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 24 │ 10000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬───salary─┐│ 1 │ 马六 │ 18 │ 北京 │ 前端 │ 27 │ 15000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘#手动执行optimize 命令触发合并相同分区数据node1 :) optimize table t_summing_mt;#查看表 t_summing_mt 中的数据node1 :) select * from t_summing_mt;┌─id─┬─name─┬─age─┬─loc──┬─dept───┬─workdays─┬───salary─┐│ 1 │ 张三 │ 18 │ 北京 │ 大数据 │ 51 │ 25000.00 ││ 3 │ 王五 │ 20 │ 北京 │ java │ 26 │ 12000.00 │└────┴──────┴─────┴──────┴────────┴──────────┴──────────┘┌─id─┬─name─┬─age─┬─loc──┬─dept─┬─workdays─┬──salary─┐│ 2 │ 李四 │ 19 │ 上海 │ java │ 22 │ 8000.00 │└────┴──────┴─────┴──────┴──────┴──────────┴─────────┘注意:我们可以看到当指定多个聚合字段时,有相同排序字段行进行聚合时,会按照指定的多个数值字段进行合并,其他的保留最开始一条数据的信息。
👨💻如需博文中的资料请私信博主。