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/ 。