1.SummingMergeTree
- 根据排序键对数值类型的列进行汇总求和。
- 相同排序键的行合并为一行。
- 如果一个排序键对应大量的行,则该引擎能显著减少存储空间并加快数据查询的速度
- 建议该引擎与MergeTree引擎结合。完整的数据存储在MergeTree表中,使用SummingMergeTree存储聚合数据,可以防止排序键的组合不正确而丢失有价值的数据。
指定表引擎:
- ENGINE = SummingMergeTree([columns])
- 参数:columns,具有列名称的元组,其中的值将被汇总。可选参数。
- 列必须是数值类型,并且不能是主键中列。
- 如果columns参数没有指定,ClickHouse将汇总除了主键列之外的所有数值类型列的值。
汇总规则:
- 数值类型的列的值会被汇总,列的集合由参数columns定义。
- 如果求和的所有列中的值都为0,则删除该行。
- 如果列不在主键中且未汇总,则从现有的值中任意选择一个值。
- 主键中的列不会汇总。
- ClickHouse可能不会完整地汇总所有行,因此需在查询中使用聚合函数sum和GROUP BY子句
案例
drop table summtt; CREATE TABLE summtt ( key String, name String, value Int32 ) ENGINE = SummingMergeTree() ORDER BY key; insert into summtt values('a', 'xiaoa1', 1); insert into summtt values('a', 'xiaoa2', 2); insert into summtt values('b', 'xiaob1', 10); insert into summtt values('b', 'xiaob2', 20); insert into summtt values('c', 'xiaob1', 10); insert into summtt values('c', 'xiaob2', -10);
然后执行
SELECT * FROM summtt; ┌─key─┬─name───┬─value─┐ │ a │ xiaoa1 │ 3 │ │ b │ xiaob1 │ 30 │ │ c │ xiaob1 │ 10 │ └─────┴────────┴───────┘ ┌─key─┬─name───┬─value─┐ │ c │ xiaob2 │ -10 │ └─────┴────────┴───────┘
执行计划外的合并:
optimize table summtt;
然后再查看结果:
xxxxx :) select * from summtt; SELECT * FROM summtt ┌─key─┬─name───┬─value─┐ │ a │ xiaoa1 │ 3 │ │ b │ xiaob2 │ 20 │ └─────┴────────┴───────┘ 2 rows in set. Elapsed: 0.006 sec. xxxxx :)
从上案例可以知道,排序键是key,没有指定key的时候,按照key键进行分组汇总,只对数值列进行汇总,非数值列随机取值,另外c键汇总后,value的值为0,此行被删除。
上面的自动合并的过程类似:
xxxxx :) select key, sum(value) from summtt group by key; SELECT key, sum(value) FROM summtt GROUP BY key ┌─key─┬─sum(value)─┐ │ b │ 30 │ │ a │ 3 │ └─────┴────────────┘ 2 rows in set. Elapsed: 0.008 sec. xxxxx :)
2.AggregatingMergeTree
- AggregatingMergeTree可用于增量数据聚合,包括物化视图的聚合。
- 将相同排序健的所有行(在一个数据片段内)替换为一行,该行存储了聚合函数状态的组合。
- 该引擎需结合AggregateFunction数据类型的列使用。
指定表引擎:
ENGINE = AggregatingMergeTree()
数据插入:
使用带-State后缀的聚合函数。如sumState、uniqState等。
数据查询:
使用GROUP BY子句和聚合函数(与插入的聚合函数相同),但是使用-Merge后缀的聚合函数。如sumMerge、uniqueMerge等。
案例1:基于表
DROP TABLE IF EXISTS test_aggregates; CREATE TABLE test_aggregates ( d Date, sumV AggregateFunction(sum, UInt64), uniqV AggregateFunction(uniq, UInt64) ) ENGINE = AggregatingMergeTree() ORDER BY d; INSERT INTO test_aggregates SELECT toDate('2020-06-01') AS d, sumState(number) as sumV, uniqState(number) AS uniqV FROM ( SELECT toUInt64(number%8) as number FROM system.numbers LIMIT 10 ); xxxxx :) select * from test_aggregates; SELECT * FROM test_aggregates ┌──────────d─┬─sumV─┬─uniqV────────┐ │ 2020-06-01 │ _x001D_ │u鏘 ޑh⭋4uULԳE|ȧe │ └────────────┴──────┴──────────────┘ 1 rows in set. Elapsed: 0.005 sec. xxxxx :) select sumMerge(sumV),uniqMerge(uniqV) FROM test_aggregates; SELECT sumMerge(sumV), uniqMerge(uniqV) FROM test_aggregates ┌─sumMerge(sumV)─┬─uniqMerge(uniqV)─┐ │ 29 │ 8 │ └────────────────┴──────────────────┘ 1 rows in set. Elapsed: 0.007 sec. xxxxx :)
案例2:物化视图的使用
drop table t_basic; create table t_basic(key String, sign UInt8, userId String) ENGINE=MergeTree order by key; drop table t_m_view; CREATE MATERIALIZED VIEW t_m_view ENGINE = AggregatingMergeTree() ORDER BY (key) AS SELECT key, sumState(sign) AS sumSign, uniqState(userId) AS uniqUsers FROM t_basic GROUP BY key;
插入3条记录:
insert into t_basic values('a', 1, '11'),('a', 2, '22'),('a', 3, '11');
查看数据:
xxxxx :) select * from t_basic; SELECT * FROM t_basic ┌─key─┬─sign─┬─userId─┐ │ a │ 1 │ 11 │ │ a │ 2 │ 22 │ │ a │ 3 │ 11 │ └─────┴──────┴────────┘ xxxxx :) select key,sumMerge(sumSign),uniqMerge(uniqUsers) from t_m_view group by key; SELECT key, sumMerge(sumSign), uniqMerge(uniqUsers) FROM t_m_view GROUP BY key ┌─key─┬─sumMerge(sumSign)─┬─uniqMerge(uniqUsers)─┐ │ a │ 6 │ 2 │ └─────┴───────────────────┴──────────────────────┘ 1 rows in set. Elapsed: 0.006 sec. xxxxx :)