ClickHouse 其它的一些操作函数 (十五)

​ 本文来源: ( https://www.cnblogs.com/traditional/tag/ClickHouse:一款速度快到让人发指的列式存储数据库/ )


楔子

在 ClickHouse 中还存在一些其它比较有意思的函数,我们来看一下。

and:计算多个值逻辑与连接的结果

该函数只能接收 整型、浮点型和 Null,其逻辑和 Python 中的 and 类似

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT and(1, 2, 0, Null, 3, 5);
/*
┌─and(1, 2, 0, NULL, 3, 5)─┐
│ 0 │
└──────────────────────────┘
*/

-- 等价于
SELECT 1 AND 2 AND 0 AND Null AND 3 AND 5;
/*
┌─and(1, 2, 0, NULL, 3, 5)─┐
│ 0 │
└──────────────────────────┘
*/

or:计算多个值逻辑或连接的结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT or(1, 2, 0, Null, 3, 5);
/*
┌─or(1, 2, 0, NULL, 3, 5)─┐
│ 1 │
└─────────────────────────┘
*/

-- 等价于
SELECT 1 OR 2 OR 0 OR Null OR 3 OR 5;
/*
┌─or(1, 2, 0, NULL, 3, 5)─┐
│ 1 │
└─────────────────────────┘
*/

not:同样只能接收整型、浮点型、Null,用于逻辑取反

举个栗子:如果是非 0、非 Null,那么逻辑上就为真,因此调用 not 之后会得到假,也就是 0。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- not(0) 得到 1,not(Null) 还是 Null,not(非0、非 Null) 得到 0
SELECT not(123), not(Null), not(0), not(333);
/*
┌─not(123)─┬─not(NULL)─┬─not(0)─┬─not(333)─┐
│ 0 │ ᴺᵁᴸᴸ │ 1 │ 0 │
└──────────┴───────────┴────────┴──────────┘
*/

-- 等价于
SELECT NOT 123, NOT Null, NOT 0, NOT 333;
/*
┌─not(123)─┬─not(NULL)─┬─not(0)─┬─not(333)─┐
│ 0 │ ᴺᵁᴸᴸ │ 1 │ 0 │
└──────────┴───────────┴────────┴──────────┘
*/

if:想象成编程语言中的三元表达式即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT number, if(number < 5, 'less than 5', 'greater than or equal to 5') 
FROM (SELECT number FROM numbers(10));
/*
┌─number─┬─if(less(number, 5), 'less than 5', 'greater than or equal to 5')─┐
│ 0 │ less than 5 │
│ 1 │ less than 5 │
│ 2 │ less than 5 │
│ 3 │ less than 5 │
│ 4 │ less than 5 │
│ 5 │ greater than or equal to 5 │
│ 6 │ greater than or equal to 5 │
│ 7 │ greater than or equal to 5 │
│ 8 │ greater than or equal to 5 │
│ 9 │ greater than or equal to 5 │
└────────┴──────────────────────────────────────────────────────────────────┘
*/

-- 另外 ClickHouse 本身也支持三元表达式,底层依旧会转成 if
SELECT number, number < 5 ? 'less than 5' : 'greater than or equal to 5'
FROM (SELECT number FROM numbers(10));
/*
┌─number─┬─if(less(number, 5), 'less than 5', 'greater than or equal to 5')─┐
│ 0 │ less than 5 │
│ 1 │ less than 5 │
│ 2 │ less than 5 │
│ 3 │ less than 5 │
│ 4 │ less than 5 │
│ 5 │ greater than or equal to 5 │
│ 6 │ greater than or equal to 5 │
│ 7 │ greater than or equal to 5 │
│ 8 │ greater than or equal to 5 │
│ 9 │ greater than or equal to 5 │
└────────┴──────────────────────────────────────────────────────────────────┘
*/

同样的,我们可以使用 CASE WHEN 语句实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT number, CASE WHEN number < 5 THEN 'less than 5' ELSE 'greater than or equal to 5' END
FROM (SELECT number FROM numbers(10));
/*
┌─number─┬─multiIf(less(number, 5), 'less than 5', 'greater than or equal to 5')─┐
│ 0 │ less than 5 │
│ 1 │ less than 5 │
│ 2 │ less than 5 │
│ 3 │ less than 5 │
│ 4 │ less than 5 │
│ 5 │ greater than or equal to 5 │
│ 6 │ greater than or equal to 5 │
│ 7 │ greater than or equal to 5 │
│ 8 │ greater than or equal to 5 │
│ 9 │ greater than or equal to 5 │
└────────┴───────────────────────────────────────────────────────────────────────┘
*/

我们看到底层转化成了 multiIf,那么这个 multiIf 是做什么的呢?首先 if 函数的参数如下:

1
if(cond, then, else)

而 multiIf 函数的参数如下:

1
multiIf(cond1, then1, cond2, then2, cond3, then3, ..., else)

所以从名字上也能看出来 multiIf 是干什么的,if 只能有一个条件,相当于编程语言中的 if … else;而 multiIf 可以接收多个条件,相当于编程语言中的 if … else if … else if … else。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 等价于 multiIf(number < 5, 'less than 5', number = 5, 'equal to 5', 'greater than 5')
SELECT number,
CASE WHEN number < 5 THEN 'less than 5' WHEN number = 5 THEN 'equal to 5' ELSE 'greater than 5' END
FROM (SELECT number FROM numbers(10));
/*
┌─number─┬─multiIf(less(number, 5), 'less than 5', equals(number, 5), 'equal to 5', 'greater than 5')─┐
│ 0 │ less than 5 │
│ 1 │ less than 5 │
│ 2 │ less than 5 │
│ 3 │ less than 5 │
│ 4 │ less than 5 │
│ 5 │ equal to 5 │
│ 6 │ greater than 5 │
│ 7 │ greater than 5 │
│ 8 │ greater than 5 │
│ 9 │ greater than 5 │
└────────┴────────────────────────────────────────────────────────────────────────────────────────────┘
*/

根据返回的结果集的字段我们发现底层会变成函数调用:

  • a < b 等价于 less(a, b)
  • a = b 等价于 equals(a, b)
  • a > b 等价于 greater(a, b)
  • a <= b 等价于 lessOrEquals(a, b)
  • a >= b 等价于 greaterOrEquals(a, b)
  • a != b 等价于 notEquals(a, b)

但还是正如我们之前所说的,可以直接使用 CASE WHEN 进行实现,因为它也是关系型数据中非常常用的语法,这样读起来会更加的亲切。关于大小比较,我们也是直接使用操作符即可,没必要使用 less、equals、greater 等函数。

数学计算函数

以下是关于数学计算的一些函数,来看一下。

e:一个函数,调用之后返回底数 e

1
2
3
4
5
6
SELECT e();
/*
┌───────────────e()─┐
│ 2.718281828459045 │
└───────────────────┘
*/

pi:一个函数,调用之后返回圆周率 π

1
2
3
4
5
6
SELECT pi();
/*
┌──────────────pi()─┐
│ 3.141592653589793 │
└───────────────────┘
*/

exp:返回 e 的 x 次方

1
2
3
4
5
6
SELECT exp(1), exp(2);
/*
┌────────────exp(1)─┬────────────exp(2)─┐
│ 2.718281828460626 │ 7.389056098924109 │
└───────────────────┴───────────────────┘
*/

除了 exp 之外,还有 exp2 返回 2 的 x 次方,exp10 返回 10 的 x 次方。

1
2
3
4
5
6
SELECT exp2(2), exp10(2);
/*
┌─exp2(2)─┬─exp10(2)─┐
│ 4 │ 100 │
└─────────┴──────────┘
*/

log、ln:两者是等价的,都是以自然对数为底

1
2
3
4
5
6
SELECT log(e()), ln(e() * e());
/*
┌───────────log(e())─┬─log(multiply(e(), e()))─┐
│ 0.9999999987491066 │ 2.00000000029383 │
└────────────────────┴─────────────────────────┘
*/

同理还有 log2 以 2 为底,log10 以 10 为底。

1
2
3
4
5
6
SELECT log2(8), log10(1000);
/*
┌─log2(8)─┬─log10(1000)─┐
│ 3 │ 3 │
└─────────┴─────────────┘
*/

sqrt:返回一个数的平方根

1
2
3
4
5
6
SELECT sqrt(9);
/*
┌─sqrt(9)─┐
│ 3 │
└─────────┘
*/

cbrt:返回一个数的立方根

1
2
3
4
5
6
SELECT cbrt(27);
/*
┌───────────cbrt(27)─┐
│ 3.0000000000000004 │
└────────────────────┘
*/

pow:计算 x 的 y 次方

1
2
3
4
5
6
7
-- pow 也可以写成 power
SELECT pow(3, 4);
/*
┌─pow(3, 4)─┐
│ 81 │
└───────────┘
*/

sin、cos、tan:计算正弦值、余弦值、正切值

asin、acos、atan:计算反正弦值、反余弦值、反正切值

sinh、cosh、tanh:计算双曲正弦值、双曲余弦值、双曲正切值

asinh、acosh、atanh:计算反双曲正弦值、反双曲余弦值、反双曲正切值

atan2:atan 的增强版,具体细节可以百度或者谷歌

hypot:给定两个直角边,计算斜边长度,等于 x2+y2−−−−−−√�2+�2

1
2
3
4
5
6
SELECT hypot(3, 4), hypot(6, 8);
/*
┌─hypot(3, 4)─┬─hypot(6, 8)─┐
│ 5 │ 10 │
└─────────────┴─────────────┘
*/

sign:小于 0 返回 -1、等于 0 返回 0、大于 0 返回 1

1
2
3
4
5
6
SELECT sign(-100), sign(0), sign(111);
/*
┌─sign(-100)─┬─sign(0)─┬─sign(111)─┐
│ -1 │ 0 │ 1 │
└────────────┴─────────┴───────────┘
*/

floor、ceil(或者 ceiling):返回小于等于 x 的最大整数、大于等于 x 的最小整数,注意:说返回整数其实不太准确,因为返回的仍是 Float64

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT floor(3.14), ceil(3.14);
/*
┌─floor(3.14)─┬─ceil(3.14)─┐
│ 3 │ 4 │
└─────────────┴────────────┘
*/

-- 还可以选择精度,会保留一位,默认是一位都不保留
SELECT floor(3.14, 1), ceil(3.14, 2), ceiling(3.14, 3);
/*
┌─floor(3.14, 1)─┬─ceil(3.14, 2)─┬─ceil(3.14, 3)─┐
│ 3.1 │ 3.14 │ 3.14 │
└────────────────┴───────────────┴───────────────┘
*/

truncate、trunc:截断小数点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT trunc(3.14), trunc(-2.17);
/*
┌─trunc(3.14)─┬─trunc(-2.17)─┐
│ 3 │ -2 │
└─────────────┴──────────────┘
*/

-- 仍然可以选择保留位数
SELECT trunc(3.14, 1), trunc(-2.17, 1);
/*
┌─trunc(3.14, 1)─┬─trunc(-2.17, 1)─┐
│ 3.1 │ -2.1 │
└────────────────┴─────────────────┘
*/

round:保留指定位数的小数

1
2
3
4
5
6
7
-- 不指定位数,将一位都不保留
SELECT round(3.1415926, 3), round(3.1415926);
/*
┌─round(3.1415926, 3)─┬─round(3.1415926)─┐
│ 3.142 │ 3 │
└─────────────────────┴──────────────────┘
*/

注意:ClickHouse 中的 round 还有一种特殊用法,那就是对整数四舍五入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 当指定为负数时,表示对整数或者小数点前面的进行四舍五入
-- -1 表示针对最后一位,所以 round(222, -1) 得到的结果是 220,round(228, -1) 得到的结果是 230
SELECT round(222, -1), round(228, -1);
/*
┌─round(222, -1)─┬─round(228, -1)─┐
│ 220 │ 230 │
└────────────────┴────────────────┘
*/

-- -2 表示针对最后两位,所以 round(-350, -2) 得到的结果是 -400,round(349, -2) 得到的结果是 300
-- 因为 50 达到了 100 的一半,49 没有达到 100 的一半
SELECT round(-350, -2), round(349, -2);
/*
┌─round(-350, -2)─┬─round(349, -2)─┐
│ -400 │ 300 │
└─────────────────┴────────────────┘
*/

-- -3 表示针对最后三位,所以 round(499, -3) 得到的结果是 0,round(500, -3) 得到的结果是 1000
-- 因为 499 没有达到 1000 的一半,500 达到了 1000 的一半
SELECT round(499, -3), round(500, -3);
/*
┌─round(499, -3)─┬─round(500, -3)─┐
│ 0 │ 1000 │
└────────────────┴────────────────┘
*/

roundToExp2:将数值转为某个最接近的 2 的整数次幂,比如 roundToExp2(33) 得到的就是 32,因为 32 是 2 的 5 次幂;roundToExp2(31) 得到的就是 16,因为 16 是 2 的 4 次幂

1
2
3
4
5
6
7
-- 小于 1,返回 0
SELECT roundToExp2(33), roundToExp2(31), roundToExp2(1), roundToExp2(-11);
/*
┌─roundToExp2(33)─┬─roundToExp2(31)─┬─roundToExp2(1)─┬─roundToExp2(-11)─┐
│ 32 │ 16 │ 1 │ 0 │
└─────────────────┴─────────────────┴────────────────┴──────────────────┘
*/

roundAge:如果一个数值小于 18,返回其本身;否则将其转成 18、25、35、45、55 当中与之最接近的一个值,很明显这个函数是针对 Yandex 公司的业务而专门设计的

1
2
3
4
5
6
SELECT roundAge(15), roundAge(20), roundAge(29), roundAge(38), roundAge(1000);
/*
┌─roundAge(15)─┬─roundAge(20)─┬─roundAge(29)─┬─roundAge(38)─┬─roundAge(1000)─┐
│ 17 │ 18 │ 25 │ 35 │ 55 │
└──────────────┴──────────────┴──────────────┴──────────────┴────────────────┘
*/

roundDown:将一个数值四舍五入到某个数组中与之最接近的值,如果数值小于数组中的最小值,那么等于最小值

1
2
3
4
5
6
7
WITH [18, 25, 35, 45, 55] AS arr
SELECT roundDown(15, arr), roundDown(20, arr), roundDown(29, arr), roundDown(38, arr), roundDown(1000, arr)
/*
┌─roundDown(15, arr)─┬─roundDown(20, arr)─┬─roundDown(29, arr)─┬─roundDown(38, arr)─┬─roundDown(1000, arr)─┐
│ 18 │ 18 │ 25 │ 35 │ 55 │
└────────────────────┴────────────────────┴────────────────────┴────────────────────┴──────────────────────┘
*/

rand、rand32:生成一个 UInt32 伪随机数

rand64:生成一个 UInt64 伪随机数

1
2
3
4
5
6
SELECT rand32(), rand64();
/*
┌───rand32()─┬─────────────rand64()─┐
│ 4261522186 │ 13571471280441249418 │
└────────────┴──────────────────────┘
*/

randConstant:生成一个 UInt32 伪随机数,但在一次查询中多次调用得到的结果一样

1
2
3
4
5
6
7
8
SELECT rand32(), randConstant() FROM numbers(3);
/*
┌───rand32()─┬─randConstant()─┐
│ 3054555439 │ 602145845 │
│ 1590396198 │ 602145845 │
│ 2065566003 │ 602145845 │
└────────────┴────────────────┘
*/

常见编码函数

以下是一些常见的编码函数,一起来看一下。

char:将 ASCII 码转成对应的字符,可以同时接收多个 ASCII 码

1
2
3
4
5
6
SELECT char(97), char(97, 98, 99);
/*
┌─char(97)─┬─char(97, 98, 99)─┐
│ a │ abc │
└──────────┴──────────────────┘
*/

hex:将整型用 16 进制表示

1
2
3
4
5
6
SELECT hex(97), hex(98), hex(99);
/*
┌─hex(97)─┬─hex(98)─┬─hex(99)─┐
│ 61 │ 62 │ 63 │
└─────────┴─────────┴─────────┘
*/

hex 除了接收整型之外,还可以接收字符串,将每个字符对应的 ASCII 码用 16 进制表示。

1
2
3
4
5
6
7
-- 十六进制:a -> 61, b -> 62, c->63
SELECT hex('abc');
/*
┌─hex('abc')─┐
│ 616263 │
└────────────┘
*/

unhex:hex 的逆运算,但只能接收字符串

1
2
3
4
5
6
SELECT unhex('616263');
/*
┌─unhex('616263')─┐
│ abc │
└─────────────────┘
*/

其它函数

hostName:返回当前 ClickHouse Server 所在节点的主机名

1
2
3
4
5
6
SELECT hostName();
/*
┌─hostName()─┐
│ satori │
└────────────┘
*/

getMacro:从服务器的宏配置中获取指定的值

1
2
3
<macros>
<name>satori</name>
</macros>

然后即可通过 getMacro(name) 获取,当然也可以查看所有的宏。

1
SELECT * FROM system.macros;

fqdn:返回全限定域名,和我当前的主机名是一样的

1
2
3
4
5
6
SELECT fqdn();
/*
┌─FQDN()─┐
│ satori │
└────────┘
*/

basename:返回路径中最后一个 / 或者 \ 后面的部分

1
2
3
4
5
6
SELECT '/root/girls/1.csv' file_path, basename(file_path) file_name;
/*
┌─file_path─────────┬─file_name─┐
│ /root/girls/1.csv │ 1.csv │
└───────────────────┴───────────┘
*/

visibleWidth:当以文本格式向控制台输出内容时,计算出所需要的宽度,用于美化输出

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT visibleWidth(3.1415), visibleWidth('satori'), visibleWidth(Null);
/*
┌─visibleWidth(3.1415)─┬─visibleWidth('satori')─┬─visibleWidth(NULL)─┐
│ 6 │ 6 │ 4 │
└──────────────────────┴────────────────────────┴────────────────────┘
*/

SELECT visibleWidth([1,2,3,4,Null]), length('[1,2,3,4,Null]');
/*
┌─visibleWidth([1, 2, 3, 4, NULL])─┬─length('[1,2,3,4,Null]')─┐
│ 14 │ 14 │
└──────────────────────────────────┴──────────────────────────┘
*/

可以看到就是把内容当成纯文本,计算所占的长度。

toTypeName:返回一个值的类型

1
2
3
4
5
6
7
8
9
SELECT toTypeName([1, 2, 3]), toTypeName(123), toTypeName((11, '22'));
/*
┌─toTypeName([1, 2, 3])─┬─toTypeName(123)─┬─toTypeName((11, '22'))─┐
│ Array(UInt8) │ UInt8 │ Tuple(UInt8, String) │
└───────────────────────┴─────────────────┴────────────────────────┘
*/

-- 数组中如果包含 Null,那么 Array(...) 会变成 Array(Nullable(...))
-- 而 Null 的类型本身则是 Nullable(Nothing)

ignore:接收任何参数,包括 Null,但总是返回 0;然而该参数仍然会被考虑在内,因此一般用于基准测试

1
2
3
4
5
6
SELECT ignore(11), ignore([1, 2, 3]), ignore(Null);
/*
┌─ignore(11)─┬─ignore([1, 2, 3])─┬─ignore(NULL)─┐
│ 0 │ 0 │ 0 │
└────────────┴───────────────────┴──────────────┘
*/

currentDatabase:获取当前所在的数据库

1
2
3
4
5
6
SELECT currentDatabase();
/*
┌─currentDatabase()─┐
│ default │
└───────────────────┘
*/

currentUsere:获取当前的用户

1
2
3
4
5
6
7
-- 当前的默认用户也叫 default
SELECT currentUser();
/*
┌─currentUser()─┐
│ default │
└───────────────┘
*/

总结

以上就是 ClickHouse 的一些其它函数,当然还是那句话,ClickHouse 的提供的函数非常多,不止我们上面说的,只不过有很多个人觉得用不上,所以就不说了。当然如果你有兴趣的话可以去官网进行查看,链接: https://clickhouse.tech/docs/en/sql-reference/functions/