8.SummingMergeTree
8.1.SummingMergeTree
8.2.案例
9.AggregatingMergeTree
9.1.AggregatingMergeTree
9.2.案例1:基于表
9.3.案例2:物化视图的使用
8.SummingMergeTree
8.1.SummingMergeTree
- 根据排序键对数值类型的列进行汇总求和。
- 相同排序键的行合并为一行。
- 如果一个排序键对应大量的行,则该引擎能显著减少存储空间并加快数据查询的速度。
- 建议该引擎与MergeTree引擎结合。完整的数据存储在MergeTree表中,使用SummingMergeTree存储聚合数据,可以防止排序键的组合不正确而丢失有价值的数据。
指定表引擎:
ENGINE = SummingMergeTree([columns])
参数:columns,具有列名称的元组,其中的值将被汇总。可选参数。
列必须是数值类型,并且不能是主键中列。
如果columns参数没有指定,ClickHouse将汇总除了主键列之外的所有数值类型列的值。
汇总规则:
数值类型的列的值会被汇总,列的集合由参数columns定义。
如果求和的所有列中的值都为0,则删除该行。
如果列不在主键中且未汇总,则从现有的值中任意选择一个值。
主键中的列不会汇总。
ClickHouse可能不会完整地汇总所有行,因此需在查询中使用聚合函数sum和GROUP BY子句。
8.2.案例
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 :)
9.AggregatingMergeTree
9.1.AggregatingMergeTree
- AggregatingMergeTree可用于增量数据聚合,包括物化视图的聚合。
- 将相同排序健的所有行(在一个数据片段内)替换为一行,该行存储了聚合函数状态的组合。
- 该引擎需结合AggregateFunction数据类型的列使用。
指定表引擎:
ENGINE = AggregatingMergeTree()
数据插入:
使用带-State后缀的聚合函数。如sumState、uniqState等。
数据查询:
使用GROUP BY子句和聚合函数(与插入的聚合函数相同),但是使用-Merge后缀的聚合函数。如sumMerge、uniqueMerge等。
9.2.案例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 :)
9.3.案例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 :)