ClickHouse 其它的一些操作函数
楔子
在 ClickHouse 中还存在一些其它比较有意思的函数，我们来看一下。
and：计算多个值逻辑与连接的结果
该函数只能接收 整型、浮点型和 Null，其逻辑和 Python 中的 and 类似
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：计算多个值逻辑或连接的结果
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。
-- 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：想象成编程语言中的三元表达式即可
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 语句实现。
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 函数的参数如下：
if(cond, then, else)
而 multiIf 函数的参数如下：
multiIf(cond1, then1, cond2, then2, cond3, then3, ..., else)
所以从名字上也能看出来 multiIf 是干什么的，if 只能有一个条件，相当于编程语言中的 if ... else；而 multiIf 可以接收多个条件，相当于编程语言中的 if ... else if ... else if ... else。
-- 等价于 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
SELECT e();
/*
┌───────────────e()─┐
│ 2.718281828459045 │
└───────────────────┘
*/
pi：一个函数，调用之后返回圆周率 π
SELECT pi();
/*
┌──────────────pi()─┐
│ 3.141592653589793 │
└───────────────────┘
*/
exp：返回 e 的 x 次方
SELECT exp(1), exp(2);
/*
┌────────────exp(1)─┬────────────exp(2)─┐
│ 2.718281828460626 │ 7.389056098924109 │
└───────────────────┴───────────────────┘
*/
除了 exp 之外，还有 exp2 返回 2 的 x 次方，exp10 返回 10 的 x 次方。
SELECT exp2(2), exp10(2);
/*
┌─exp2(2)─┬─exp10(2)─┐
│ 4 │ 100 │
└─────────┴──────────┘
*/
log、ln：两者是等价的，都是以自然对数为底
SELECT log(e()), ln(e() * e());
/*
┌───────────log(e())─┬─log(multiply(e(), e()))─┐
│ 0.9999999987491066 │ 2.00000000029383 │
└────────────────────┴─────────────────────────┘
*/
同理还有 log2 以 2 为底，log10 以 10 为底。
SELECT log2(8), log10(1000);
/*
┌─log2(8)─┬─log10(1000)─┐
│ 3 │ 3 │
└─────────┴─────────────┘
*/
sqrt：返回一个数的平方根
SELECT sqrt(9);
/*
┌─sqrt(9)─┐
│ 3 │
└─────────┘
*/
cbrt：返回一个数的立方根
SELECT cbrt(27);
/*
┌───────────cbrt(27)─┐
│ 3.0000000000000004 │
└────────────────────┘
*/
pow：计算 x 的 y 次方
-- 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：给定两个直角边，计算斜边长度，等于 \({\sqrt{{x^2} + {y^2}}}\)
SELECT hypot(3, 4), hypot(6, 8);
/*
┌─hypot(3, 4)─┬─hypot(6, 8)─┐
│ 5 │ 10 │
└─────────────┴─────────────┘
*/
sign：小于 0 返回 -1、等于 0 返回 0、大于 0 返回 1
SELECT sign(-100), sign(0), sign(111);
/*
┌─sign(-100)─┬─sign(0)─┬─sign(111)─┐
│ -1 │ 0 │ 1 │
└────────────┴─────────┴───────────┘
*/
floor、ceil（或者 ceiling）：返回小于等于 x 的最大整数、大于等于 x 的最小整数，注意：说返回整数其实不太准确，因为返回的仍是 Float64
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：截断小数点
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：保留指定位数的小数
-- 不指定位数，将一位都不保留
SELECT round(3.1415926, 3), round(3.1415926);
/*
┌─round(3.1415926, 3)─┬─round(3.1415926)─┐
│ 3.142 │ 3 │
└─────────────────────┴──────────────────┘
*/
注意：ClickHouse 中的 round 还有一种特殊用法，那就是对整数四舍五入。
-- 当指定为负数时，表示对整数或者小数点前面的进行四舍五入
-- -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，返回 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 公司的业务而专门设计的
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：将一个数值四舍五入到某个数组中与之最接近的值，如果数值小于数组中的最小值，那么等于最小值
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 伪随机数
SELECT rand32(), rand64();
/*
┌───rand32()─┬─────────────rand64()─┐
│ 4261522186 │ 13571471280441249418 │
└────────────┴──────────────────────┘
*/
randConstant：生成一个 UInt32 伪随机数，但在一次查询中多次调用得到的结果一样
SELECT rand32(), randConstant() FROM numbers(3);
/*
┌───rand32()─┬─randConstant()─┐
│ 3054555439 │ 602145845 │
│ 1590396198 │ 602145845 │
│ 2065566003 │ 602145845 │
└────────────┴────────────────┘
*/
常见编码函数
以下是一些常见的编码函数，一起来看一下。
char：将 ASCII 码转成对应的字符，可以同时接收多个 ASCII 码
SELECT char(97), char(97, 98, 99);
/*
┌─char(97)─┬─char(97, 98, 99)─┐
│ a │ abc │
└──────────┴──────────────────┘
*/
hex：将整型用 16 进制表示
SELECT hex(97), hex(98), hex(99);
/*
┌─hex(97)─┬─hex(98)─┬─hex(99)─┐
│ 61 │ 62 │ 63 │
└─────────┴─────────┴─────────┘
*/
hex 除了接收整型之外，还可以接收字符串，将每个字符对应的 ASCII 码用 16 进制表示。
-- 十六进制：a -> 61, b -> 62, c->63
SELECT hex('abc');
/*
┌─hex('abc')─┐
│ 616263 │
└────────────┘
*/
unhex：hex 的逆运算，但只能接收字符串
SELECT unhex('616263');
/*
┌─unhex('616263')─┐
│ abc │
└─────────────────┘
*/
其它函数
hostName：返回当前 ClickHouse Server 所在节点的主机名
SELECT hostName();
/*
┌─hostName()─┐
│ satori │
└────────────┘
*/
getMacro：从服务器的宏配置中获取指定的值
<macros>
<name>satori</name>
</macros>
然后即可通过 getMacro(name) 获取，当然也可以查看所有的宏。
SELECT * FROM system.macros;
fqdn：返回全限定域名，和我当前的主机名是一样的
SELECT fqdn();
/*
┌─FQDN()─┐
│ satori │
└────────┘
*/
basename：返回路径中最后一个 / 或者 \ 后面的部分
SELECT '/root/girls/1.csv' file_path, basename(file_path) file_name;
/*
┌─file_path─────────┬─file_name─┐
│ /root/girls/1.csv │ 1.csv │
└───────────────────┴───────────┘
*/
visibleWidth：当以文本格式向控制台输出内容时，计算出所需要的宽度，用于美化输出
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：返回一个值的类型
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；然而该参数仍然会被考虑在内，因此一般用于基准测试
SELECT ignore(11), ignore([1, 2, 3]), ignore(Null);
/*
┌─ignore(11)─┬─ignore([1, 2, 3])─┬─ignore(NULL)─┐
│ 0 │ 0 │ 0 │
└────────────┴───────────────────┴──────────────┘
*/
currentDatabase：获取当前所在的数据库
SELECT currentDatabase();
/*
┌─currentDatabase()─┐
│ default │
└───────────────────┘
*/
currentUsere：获取当前的用户
-- 当前的默认用户也叫 default
SELECT currentUser();
/*
┌─currentUser()─┐
│ default │
└───────────────┘
*/
总结
以上就是 ClickHouse 的一些其它函数，当然还是那句话，ClickHouse 的提供的函数非常多，不止我们上面说的，只不过有很多个人觉得用不上，所以就不说了。当然如果你有兴趣的话可以去官网进行查看，链接： https://clickhouse.tech/docs/en/sql-reference/functions/ 。
如果觉得文章对您有所帮助，可以请囊中羞涩的作者喝杯柠檬水，万分感谢，愿每一个来到这里的人都生活愉快，幸福美满。
微信赞赏
支付宝赞赏
