10_clickhouse,SummingMergeTree,AggregatingMergeTree(基于表，物化视图的使用)

8.SummingMergeTree
8.1.SummingMergeTree
8.2.案例
9.AggregatingMergeTree
9.1.AggregatingMergeTree
9.2.案例1：基于表
9.3.案例2：物化视图的使用

8.SummingMergeTree

8.1.SummingMergeTree

1. 根据排序键对数值类型的列进行汇总求和。
2. 相同排序键的行合并为一行。
3. 如果一个排序键对应大量的行，则该引擎能显著减少存储空间并加快数据查询的速度。
4. 建议该引擎与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

1. AggregatingMergeTree可用于增量数据聚合，包括物化视图的聚合。
2. 将相同排序健的所有行（在一个数据片段内）替换为一行，该行存储了聚合函数状态的组合。
3. 该引擎需结合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 :)