函数"/>
ClickHouse特殊函数
clickhouse 很多特殊的函数
1. count
计算行数或非NULL的行数。
ClickHouse支持以下计数语法:
COUNT(expr)
COUNT(DISTINCT expr)
COUNT()
COUNT(*).
使用COUNT需要注意如下两点:
调用不带参数的函数(COUNT() 和COUNT(*))将返回所有的行。
如果传递了表达式参数,只有表达式结果为非NULL的行被计数。
COUNT函数返回结果的数据类型为UInt64。
示例:
DROP TABLE t_count_test;
CREATE TABLE t_count_test
(id Nullable(UInt8)
)
ENGINE = TinyLog;
INSERT INTO t_count_test VALUES(1),(NULL),(3),(1);
查看数据:
SELECT * FROM t_count_test┌───id─┐
│ 1 │
│ ᴺᵁᴸᴸ │
│ 3 │
│ 1 │
└──────┘
统计总行数:
SELECT count(), count(*) FROM t_count_test;┌─count()─┬─count()─┐
│ 4 │ 4 │
└─────────┴─────────┘统计非NULL的记录数:
SELECT count(id) FROM t_count_test;
┌─count(id)─┐
│ 3 │
└───────────┘当使用COUNT(DISTINCT ...) 对表达式去重计数,默认是计算不同值的确切计数。ClickHouse提供了几种近似评估的算法来计数,对于计数准确性不太敏感的场景可极大提升效率。计数去重的行为可通过count_distinct_implementation设置,可设置值有:uniq、uniqCombined、uniqCombined64、uniqHLL12、uniqExact,默认值为uniqExact,其他设置都是近似的计数。示例:
SELECT name, value FROM system.settings WHERE name = 'count_distinct_implementation';
┌─name──────────────────────────┬─value─────┐
│ count_distinct_implementation │ uniqExact │
└───────────────────────────────┴───────────┘
select count(distinct id) from t_count_test;
┌─uniqExact(id)─┐
│ 2 │
└───────────────┘
Note: COUNT(DISTINCT ...) 会忽略NULL值的行。
2. any
选择第一个出现的值。
andy函数的结果是不确定的, 可通过min或max获得确定的值。
如果要依赖执行的顺序, 可通过SELECT子句使用带ORDER BY子查询实现。
在查询语句中出现的所有列,都必须在聚合key或聚合函数中出现,这与MySQL不一样。
select destip, count() as cnt from tsv_demo group by destip order by time;
由于time没有在聚合key或聚合函数中出现,上面的语句报错:
Code: 215. DB::Exception: Received from localhost:9000. DB::Exception: Column time is not under aggregate function and not in GROUP BY..
为了实现类似MySQL的功能,可将需要使用的列放在any函数中,例如将上面的语句改写如下:
select destip, any(time) as time, count() as cnt from tsv_demo group by destip order by time;3. any的变体anyHeavy(x)/anyLast(x)
3.1 anyHeavy(x)
anyHeavy函数使用heavy hitters算法选择一个经常出现的值。如果在每个查询的执行线程中,有一个值出现的次数超过一半,则返回该值。这个结果正常情况下是不确定的。
3.2 anyLast(x)
选择最一个出现的值。andyLast函数的结果是不确定的
4. groupArray(x)
groupArray(x)用于创建一个数组,该数组的元素由聚合函数的参数值组成。数据元素的顺序是不确定的。
groupArray(max_size)(x)在groupArray(x)函数的基础上,限制数组的大小为max_size。
SELECT number % 3 AS k, groupArray(number), groupArray(2)(number)
FROM
(SELECT numberFROM system.numbersLIMIT 10
)
GROUP BY k
查看数据:
┌─k─┬─groupArray(number)─┬─groupArray(2)(number)─┐
│ 0 │ [0,3,6,9] │ [0,3] │
│ 1 │ [1,4,7] │ [1,4] │
│ 2 │ [2,5,8] │ [2,5] │
└───┴────────────────────┴───────────────────────┘5. groupBitAnd/groupBitOr/groupBitXor
groupBitAnd/groupBitOr/groupBitXor分别表示按位与、按位或和按位异或。
如下示例:
SELECT number % 4 AS k, groupArray(number), groupBitOr(number), groupBitAnd(number), groupBitXor(number)
FROM
(SELECT * FROM system.numbers LIMIT 8
)
GROUP BY k
ORDER BY k;
┌─k─┬─groupArray(number)─┬─groupBitOr(number)─┬─groupBitAnd(number)─┬─groupBitXor(number)─┐
│ 0 │ [0,4] │ 4 │ 0 │ 4 │
│ 1 │ [1,5] │ 5 │ 1 │ 4 │
│ 2 │ [2,6] │ 6 │ 2 │ 4 │
│ 3 │ [3,7] │ 7 │ 3 │ 4 │
└───┴────────────────────┴────────────────────┴─────────────────────┴─────────────────────┘
6. min(x)/max(x)/argMin(arg, val)/argMax(arg, val)
min(x)/max(x)用于计算最小值和最大值。
argMin(arg, val)用于计算最小的val值对应的arg值,argMax(arg, val)用于计算最大的val值对应的arg值
drop table t_score;
create table t_score(name String, sex String, score UInt8) ENGINE=TinyLog;
insert into t_score values
('xiaohe', 'M', 88)
('zhangsan', 'M', 70)
('xiangli', 'M', 98)
('xiaoxue', 'F', 99)
('lisi', 'F', 95)
;
查看数据:
┌─name─────┬─sex─┬─score─┐
│ xiaohe │ M │ 88 │
│ zhangsan │ M │ 70 │
│ xiangli │ M │ 98 │
│ xiaoxue │ F │ 99 │
│ lisi │ F │ 95 │
└──────────┴─────┴───────┘
分别统计男生/女生的最高分和姓名:
SELECT sex, argMax(name, score) AS name, max(score)
FROM t_score
GROUP BY sex;
┌─sex─┬─name────┬─max(score)─┐
│ F │ xiaoxue │ 99 │
│ M │ xiangli │ 98 │
└─────┴─────────┴────────────┘
7. sum(x)/sumWithOverflow(x)/sumMap(key, value)
sum(x)用于求和,只适用于数字。
sumWithOverFlow(x)计算结果使用与传入参数相同的数据类型,如果计算结果溢出(超出数据类型的最大值),则返回错误的结果。
sumMap(key, value)函数的key和value都是数组, 根据key数组的元素对value元素中的数组计数,key和value数组必须大小相等。该函数返回两个数组,第一个数组为排序的key,第二数组为对应的key的总和。
创建测试表和数据:
drop table t_sum_overflow;
create table t_sum_overflow(id String, num UInt8) ENGINE=TinyLog;
insert into t_sum_overflow values
('id001', 1), ('id001', 255);
查看数据:
SELECT * FROM t_sum_overflow;
┌─id────┬─num─┐
│ id001 │ 1 │
│ id001 │ 255 │
└───────┴─────┘
sumWithOverflow聚合:
SELECT sumWithOverflow(num) FROM t_sum_overflow
┌─sumWithOverflow(num)─┐
│ 0 │
└──────────────────────┘
聚合的结果返回了0,并不是我们预期的256,这是由于256超出了UInt8类型的最大值255。示例2:sumMap
创建测试表和数据:
DROP TABLE sum_map;
CREATE TABLE sum_map(timeslot DateTime,statusMap Nested(status UInt16,requests UInt64)
) ENGINE = Log;
INSERT INTO sum_map VALUES('2000-01-01 00:00:00', [1, 2, 3], [10, 20, 30]),('2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),('2000-01-01 00:01:00', [4, 6, 6], [10, 10, 10]),('2000-01-01 00:01:00', [6, 7, 7], [10, 10, 10]);
查看数据:
SELECT * FROM sum_map;
┌────────────timeslot─┬─statusMap.status─┬─statusMap.requests─┐
│ 2000-01-01 00:00:00 │ [1,2,3] │ [10,20,30] │
│ 2000-01-01 00:00:00 │ [3,4,5] │ [10,10,10] │
│ 2000-01-01 00:01:00 │ [4,6,6] │ [10,10,10] │
│ 2000-01-01 00:01:00 │ [6,7,7] │ [10,10,10] │
└─────────────────────┴──────────────────┴────────────────────┘
sumMap聚合:
SELECT timeslot, sumMap(statusMap.status, statusMap.requests)
FROM sum_map
GROUP BY timeslot;
┌────────────timeslot─┬─sumMap(statusMap.status, statusMap.requests)─┐
│ 2000-01-01 00:00:00 │ ([1,2,3,4,5],[10,20,40,10,10]) │
│ 2000-01-01 00:01:00 │ ([4,6,7],[10,30,20]) │
└─────────────────────┴──────────────────────────────────────────────┘
8. uniq/uniqCombined/uniqCombined64/uniqHLL12/uniqExact
用于计算不同值的数量,其中uniqExact可以精确地计算不同值的数量,其他函数使用不同的算法计算不同值的大概数量。
可选的函数参数的类型为Tuple、Array、Date、DateTime、String和数字类型。
推荐在大部分场景下使用uniq函数。
9. topK(N)(x)/topKWeighted(N)(x, weight)
topK(N)(x)返回指定列最频繁出现的值的数组,结果按照值的近似频率降序排序,注意不是根据值排序。
topK(N)(x)返回的结果是近似值。建议N<10, 使用较大的N值会降低性能,N的最大值为65536。
如果省略N,则默认使用N=10。
topKWeighted(N)(x, weight)与topK(N)(x)类似, 但是增加了一个整型的权重参数weight。列的值按照权重乘以频率的结果进行排序。
SELECT topKWeighted(10)(number, number%88) FROM numbers(1000) ;
┌─topKWeighted(10)(number, modulo(number, 88))─┐
│ [957,953,952,938,939,789,942,943,791,790] │
└──────────────────────────────────────────────┘
10 . quantile
select quantile(0.25)(number) from ( select number from system.numbers limit 20 );
┌─quantile(0.25)(number)─┐
│ 4.75 │
└────────────────────────┘
select quantile(1)(number) from ( select number from system.numbers limit 20 );
┌─quantile(1)(number)─┐
│ 19 │
└─────────────────────┘要想获得一个确定的值,可以使用:quantileDeterministic
select quantileDeterministic(1)(number,23) from ( select number from system.numbers limit 20 );
更多推荐
ClickHouse特殊函数
发布评论