ClickHouse特殊函数

函数"/>

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 );