clickhouse引擎--AggregatingMergeTree引擎

AggregatingMergeTree引擎

AggregatingMergeTree能够在合并分区的时候，按照预先定义的条件聚合数据，同时，根据预先定义的聚合函数计算数据并通过二进制的格式存入表内，将同一分组下的多行数据聚合成一行，既减少了数据行，又降低了后续聚合查询的开销，可以说AggregatingMergeTree是SummingMergeTree的升级版，它们许多设计思路是一致的，例如同时定义order by与primary key的原因和目的，但在使用方法上，两者存在明显差异，应该说AggregatingMergeTree的定义方式是MergeTree家族中最为特殊的一个。

AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照order by聚合，而使用何种聚合函数，以及针对哪些列字段计算，则是通过定义AggregateFunction数据类型实现的，在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

AggregateFunction[arg1,arg2];
参数——聚合函数
参数——数据类型

sumMerge
sumState

从聚合表中查询数据

--1)建立明细表
create table detail_table(
id UInt8,
ctime Date,
money UInt64
)engine=MergeTree()
partition by toDate(ctime)
order by id;

--2)插入明细数据
insert into detail_table values(1,'2021-08-06',100);
insert into detail_table values(1,'2021-08-06',100);
insert into detail_table values(2,'2021-08-07',200);
insert into detail_table values(2,'2021-08-07',200);

--3)建立预先聚合表
--注意：其中UserID一列的类型为：AggregateFunction(uniq,UInt64)
create table agg_table(
id UInt8,
ctime Date,
money AggregateFunction(sum,UInt64)
)engine=AggregatingMergeTree()
partition by toDate(ctime)
order by id;

--4)从明细表中读取数据，插入聚合表
--注意：子查询中使用的聚合函数为sumState,对应写入语法<agg>-state
insert into agg_tableselect id,ctime,sumState(money)from detail_tablegroup by id,ctime;


--不能使用普通insert语句向AggregatingMergeTree中插入数据
--本SQL会报错：cannot convert UInt64 to AggregateFunction(uniq,UInt64)insert into agg_table values(1,'2020-08-06',1);

--5)从聚合表中查询
--注意：select中使用的聚合函数为sumMerge,对应于查询语法<agg>-Merge
--直接查查不到数据
select id,ctime,sumMerge(money) as statefrom agg_tablegroup by id,ctime;
┌─id─┬──────ctime─┬─state─┐
│  2 │ 2021-08-07 │   400 │
│  1 │ 2021-08-06 │   200 │
└────┴────────────┴───────┘

使用物化视图同步聚合数据

--建立明细表
create table orders(
uid UInt64,
money UInt64,
ctime Date,
Sign Int8
)engine=MergeTree
partition by ctime
order by uid;

--插入数据
insert into orders values(1,100,toDate(now()),1);
insert into orders values(1,100,toDate(now()),1);
insert into orders values(1,100,toDate(now()),1);
insert into orders values(2,200,toDate(now()),1);
insert into orders values(2,200,toDate(now()),1);
insert into orders values(2,200,toDate(now()),1);

--创建物化视图
--向物化视图中同步数据
create materialized view orders_agg_view
engine=AggregatingMergeTree()
partition by ctime
order by uid
populate
as select uid,ctime,sumState(money) as mm  --注意别名
from orders group by uid,ctime;

--查询物化视图数据
select uid,ctime,sumMerge(mm) from orders_agg_view group by uid,ctime;
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   1 │ 2021-09-01 │          300 │
│   2 │ 2021-09-01 │          600 │
└─────┴────────────┴──────────────┘

--更新明细数据，物化视图中的数据实时计算更新
insert into orders values(1,100,toDate(now()),1);
select uid,ctime,sumMerge(mm) from orders_agg_view group by uid,ctime;
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   1 │ 2021-09-01 │          400 │
│   2 │ 2021-09-01 │          600 │
└─────┴────────────┴──────────────┘