clickhouse普通函数，聚合函数使用总结

一.创建clickhouse表

1.建表

CREATE TABLE ck_test
(
    `id` String COMMENT 'id',
    `int_1` UInt32 COMMENT '整型列1',
    `int_2` UInt32 COMMENT '整型列2',
    `str_1` String COMMENT '字符串列1',
    `str_2` String COMMENT '字符串列2'
)
ENGINE = MergeTree
ORDER BY id

2.插入数据

insert into ck_test values (1,1,10,'a','A'),(2,2,12,'b','B'),(3,3,13,'c','C'),
                           (4,4,14,'d','D'),(5,5,15,'e','E'),(6,6,15,'f','F')
                           (7,6,15,'f','F');    

需要注意的是，如果分两次插入，会出现以下结果

因为每一个批次的数据写入都会产生一个临时分区，这些临时分区会在写入结束后的某个时刻，可能是10-15分钟，clickhouse自动执行合并操作，也可以手动合并，但会产生大量写入操作，不推荐

optimize table ck_test final;

此外，如果出现了临时分区还没有合并的情况，any方法查询的结果，在合并前和合并后的结果是不一样的

二.函数

1.聚合函数

(1)count：计算行数
 

select count(1) from ck_test;

select count(distinct int_1) from ck_test;

(2)min：计算最小值

select min(int_1),min(str_1) from ck_test;

 

(3)max：计算最大值

 select max(int_1),max(str_1) from ck_test;

(4)sum：计算总和，只能计算数字之和

select sum(int_1),sum(int_2) from ck_test;

 (5)avg：算数平均值，仅支持数字

select avg(int_1),avg(int_2) from ck_test;

(6)any： 选择第一个遇到的值

select any(int_1),any(int_2),any(str_1),any(str_2) from ck_test;

(7)anyHeavy：列出频繁出现的值，一般情况，结果是不确定的

select anyHeavy(int_1),anyHeavy(str_1) from ck_test;

(8)anylast：选出最后一个出现的值

select anyLast(int_1),anyLast(str_1) from ck_test;

 

(9)argMin

语法1：argMin(arg,value)，计算value字段最小值对应的arg， 如果 value 最小值对应多个 arg ，输出第一个arg值。

select argMin(int_1,str_2) from ck_test;

语法2：argMin(tuple(arg1,arg2),value)，计算value字段最小值对应的tuple(arg1,arg2)字段组合

select argMin(tuple(int_1,str_2),int_1) from ck_test;

(10)argMax：类比argMin

(11)avgWeighted(x, weight)：加权算数平均值，x为值，weight为值的加权

算数平均值=x*weight分别求和/weight总和

select avgWeighted(int_1,int_2) from ck_test;

(12)topK(num)(col)：返回指定列中出现频率最多的值的数组，数组按照出现频率降序排序,num指定返回数组元素的个数，col为指定的字段

select topK(3)(str_2) from ck_test;

 

(13)topKWeighted(num)(col,weight)：类似于topK,col列每一个输入出现的次数，都会乘上weight权重，这个weight可以是类似于col的一个字段

select topKWeighted(3)(str_2,int_1) from ck_test;

(14)groupArray：生成数组

语法1：groupArray(x)

select int_2,groupArray(str_1) from ck_test group by int_2 order by int_2;

语法2：groupArray(num)(x)，其中num，限制生成的数组含有的元素的个数

select int_2,groupArray(2)(str_1) from ck_test group by int_2 order by int_2;

(15)groupUniqArray：类似于groupArray，不过会将生成的数组去重

(16)groupArrayInsertAt：参数指定位置插入数组

语法：groupArrayInsertAt(default_x, size)(x, pos)

其中，default_x为默认的在空位上替换的值，size为最终的数组长度，因为数组可能很长，会有空位，所以要用default_x填充

x为要被插入的值组成的数组，从0到pos这个位置都用default_x填充

按我的理解，int_2为15的那一行，应该有e,f,f三个值，不知道为啥只出现了e

(17)groupArraySample(max_size)(arg)：生成采样数组,max_size为数组最大长度，arg可以是字段，也可以是字段拼接的表达式，如concat('字符：',arg1)

select groupArraySample(3)(str_1) from ck_test;

select groupArraySample(3)(concat('字符',str_1)) from ck_test;

(18)uniq：计算字段去重后的近似数量

select uniq(int_2) from ck_test;

 (19)uniqExact：计算不同参数值的准确数量

(20)uniqCombined：计算不同参数值的近似数量，uniq虽然也是近似数量，但uniqCombined消耗内存多，精度也比uniq高

(21)quantile(level)(arg)：计算近似分位数，level是分位数常量，取值范围0-1，arg可以是数字类型，也可以是date，datetime类型

select quantile(0.75)(int_1) from ck_test;

(22)quantiles(level1,level2...)(arg)：可以同时计算多个分位数，返回结果为数组

select quantiles(0.25,0.5,0.75)(int_1) from ck_test;

 

2.条件函数

（1）if

select if(1==1,'等于','不等于') as judge

3.普通函数

（1）coalesce(exp1,exp2,exp3...)：可以用来对空值进行处理，如果exp1为空null，则判断exp2是否为空，exp2不为空，则返回exp2，如果exp1不为空，则返回exp1,不判断exp2是否为空

select coalesce(1,2,3) as not_null

select coalesce(null,2,3) as not_null

当多表关联的时候，可以通过coalesce方法，将join之后为空的行删除

select *
from t1 a
left join t2 b
on a.id = b.id
where coalesce(b.id,0)!=0

如果join拼接后，t2这张表中，id字段为空，coalesce的结果就是0,这样就可以将含有null的行删除