ClickHouse数据类型之AggregateFunctionAggregateFunction

86 阅读 0 评论 57 点赞

我是靠谱客的博主老迟到可乐，这篇文章主要介绍ClickHouse数据类型之AggregateFunctionAggregateFunction，现在分享给大家，希望可以做个参考。

AggregateFunction

AggregateFunction是聚合函数的中间状态，可以通过聚合函数名称加-State后缀的形式得到它。与此同时，当您需要访问该类型的最终状态数据时，您需要以相同的聚合函数名加-Merge后缀的形式来得到最终状态数据。
AggregateFunction就有些许数据立方体的意思，它能够在合并分区的时候，按照预先定义的条件，聚合数据。同时，根据预先定义的聚合函数，计算数据并通过二进制的格式存入表内。将同一分组 下的多行数据，聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。

1.定义形式

AggregateFunction(name, types_of_arguments…)
-name 聚合函数名
-types_of_arguments 聚合函数参数的类型
示例：
AggregateFunction(sum, Decimal32(2))
AggregateFunction(uniq, String)

2.使用指南

2.1 建表

-- 示例
CREATE TABLE emp_aggregatingmergeTree
(
emp_id
UInt16 COMMENT '员工id',
name
String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age
UInt8 COMMENT '员工年龄',
depart
String COMMENT '部门',
salary
AggregateFunction(sum, Decimal32(2)) COMMENT '工资'
) ENGINE = AggregatingMergeTree()
ORDER BY (emp_id, name)
PRIMARY KEY emp_id
PARTITION BY work_place;
其中的sum是指定的聚合函数。大家可以在[ClickHouse中文官网聚合函数](https://clickhouse.tech/docs/zh/sql-reference/aggregate-functions/) 下查看更多的相关函数。AggregateFunction是ClickHouse提供的一种特殊的数据类型，它能够以二进制的形式存储中间状态结果。

2.2 数据写入

当需要写入数据时，您需要将数据包含在INSERT SELECT语句中，同时对于AggregateFunction类型的数据，您需要使用对应的以-State为后缀的函数进行处理。
函数使用示例：
uniqState(UserID)
quantilesState(0.5, 0.9)(SendTiming)
不同于uniq和quantiles函数返回聚合结果的最终值，以-State后缀的函数总是返回AggregateFunction类型的数据的中间状态。
对于SELECT而言，AggregateFunction类型总是以特定的二进制形式展现在所有的输出格式中。
-- 示例
INSERT INTO TABLE emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));

2.3 数据查询

当从AggregatingMergeTree表中查询数据时，对于AggregateFunction类型的字段，您需要使用以-Merge为后缀的相同聚合函数来聚合数据。对于非AggregateFunction类型的字段，请将它们包含在GROUP BY子句中。
以-Merge为后缀的聚合函数，可以将多个AggregateFunction类型的中间状态组合计算为最终的聚合结果。
在查询数据时，如果直接使用列名访问code和value，将会是无法显示的二进制。此时，则需要调用 与uniq、sum对应的uniqMerge、sumMerge函数。
-- 示例
SELECT emp_id,name,sumMerge(salary) FROM emp_aggregatingmergeTree GROUP BY emp_id,name;

3.物化视图

AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。
通常会使用MergeTree作为底表，存储全量的数据，并且新建一张物化视图。

3.1 创建一个MereTree引擎的明细表

CREATE TABLE emp_mergetree_base
(
emp_id
UInt16 COMMENT '员工id',
name
String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age
UInt8 COMMENT '员工年龄',
depart
String COMMENT '部门',
salary
Decimal32(2) COMMENT '工资'
) ENGINE = MergeTree()
ORDER BY (emp_id, name)
PARTITION BY work_place;

3.2 创建一张物化视图

CREATE MATERIALIZED VIEW view_emp_agg
ENGINE = AggregatingMergeTree()
PARTITION BY emp_id
ORDER BY (emp_id, name)
AS SELECT emp_id, name, sumState(salary) AS salary
FROM emp_mergetree_base
GROUP BY emp_id, name;

3.3 向基础明细表插入数据

INSERT INTO emp_mergetree_base VALUES (1,'tom','上海',25,'技术部',20000),(1,'tom','上海',26,'人事部',10000);
INSERT INTO emp_mergetree_base VALUES (2,'jane','上海',25,'技术部',30000),(2,'jane','上海',26,'人事部',20000);
INSERT INTO emp_mergetree_base VALUES (1,'tom','上海',25,'技术部',20000),(1,'tom','上海',26,'人事部',40000);
INSERT INTO emp_mergetree_base VALUES (2,'jane','上海',25,'技术部',30000),(2,'jane','上海',26,'人事部',50000);

3.4 查询物化视图

-- 直接查询物化视图表，会以二进制形式展示
select * from view_emp_agg;
┌─emp_id─┬─name─┬─salary─┐
│
2 │ jane │ @KL
│
└────────┴──────┴────────┘
┌─emp_id─┬─name─┬─salary─┐
│
1 │ tom
│ ��-
└────────┴──────┴────────┘
--
SELECT emp_id,name,sumMerge(salary)
FROM view_emp_agg
GROUP BY emp_id,name;