ClickHouse常用函数速查大全

ClickHouse提供了非常丰富的函数库，主要分为两种函数：常规函数和聚合函数，除此之外，还有 ‘arrayJoin’ 等特殊函数，我们将分别介绍。需要注意的是ClickHouse具有强类型限制，换句话说，它不进行类型之间的隐式转换，每个函数都适用于特定的类型参数。这意味着有时需要使用类型转换函数。

如果下面函数执行报错，则可能是clickhouse版本不支持。

1. 算术运算

对于所有算术函数，计算结果类型向上兼容。

SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0);

┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8         │ UInt16                 │ UInt32                          │ UInt64                                   │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘

-- 加法，日期加整数表示加天数，时间加整数表示加秒数。
plus(a, b), a + b
-- 减法
minus(a, b), a - b
-- 乘法
multiply(a, b), a * b
-- 除法，除法的结果类型都是浮点数，如果除数是0，则结果是 ‘inf’, ‘-inf’, or ‘nan’。
divide(a, b), a / b
-- 整除，如果结果是正数，则向下取整；如果结果是负数，则向上取整；如果除数是0，则抛异常。
intDiv(a, b)
-- 整除或0，与intDiv不同的是，它在除以0或将最小负数除以- 1时返回0。
intDivOrZero(a, b)
-- 取余，modulo支持浮点数操作，但是某些版本中如果参数是浮点数，则先去掉参数小数部分将其预转换为整数然后做modulo操作，所以使用前要先验证。如果除数为0，则报错。
modulo(a, b), a % b
-- 取余或0，与modulo不同的是，当除数为零时，返回零。
moduloOrZero(a, b)
-- 相反数
negate(a), -a operator
-- 绝对值，对于unsigned类型，它不做任何事情。对于有符号整数类型，它返回一个无符号整型。
abs(a)
-- 最大公约数，返回数字的最大公约数。若某个参数为0，将抛出异常。
gcd(a, b)
-- 最小公倍数，返回数字的最小公倍数。若某个参数为0，将抛出异常。
lcm(a, b)
-- 最大值，比较两个值并返回最大值。返回值被转换为Float64。
max2(value1, value2)
-- 最小值，比较两个值并返回最小值。返回值被转换为Float64。
min2(value1, value2)

2. 比较运算

支持类型：数值型、字符串、日期、时间。

-- 等于
equals, a = b, a == b
-- 不等于
notEquals, a != b, a <> b
-- 小于
less, <
-- 大于
greater, >
-- 小于等于
lessOrEquals, <=
-- 大于等于
greaterOrEquals, >=

3. 逻辑运算

0 参与逻辑运算为 false，非0值参与逻辑运算为 true。

注意：clickhouse逻辑函数和逻辑运算不存在优先计算，例如即使and的第一个条件是false，也会计算后面所有的条件，因为所有条件是一起计算的，所以要注意防止虽然第一个条件不成立，后面条件会抛异常的情况。在新版clickhouse中提供了short_circuit_function_evaluation参数用来控制短路计算，为enable表示为适合的函数启用短路函数求值(可能抛出异常或计算量大)，为force_enable表示使能所有函数的短路计算功能，为disable表示关闭短路计算功能，默认是enable。

-- 与，计算两个或多个值之间的逻辑与结果。如果有条件为0，则结果为0，如果有条件为NULL，则结果为NULL，其他为1。
and(val1, val2...)
-- 或，计算两个或多个值之间的逻辑或结果。如果有条件为1，则结果为1，如果有条件为NULL，则结果为NULL，其他为0。
or(val1, val2...)
-- 非，0返回1，非0值返回0，NULL返回NULL。
not(val)
-- 异或，两个值不同为1，相同为0，有NULL为NULL，对于两个以上的条件计算，先计算前两个值的异或，然后将结果与下一个值一起计算异或，以此类推。
xor(val1, val2...)

4. 字符串函数

不同于其他数据库，clickhouse中的字符串函数在处理含有中文的字符串时，一般需要使用带有UTF8格式的函数。

empty(x)         -- 检查是否为空字符串''
notEmpty(x)      -- 检查是否为非空字符串
length(x)    	 -- 字符串字节长度，注意不是字符，中文会根据单字符长度返回
lengthUTF8(x)  	 -- UTF-8编码文本的Unicode码位(而不是字符)表示的字符串长度
char_length(x), CHAR_LENGTH  			-- 和lengthUTF8相同
character_length, CHARACTER_LENGTH		-- 和lengthUTF8相同
leftPad('string', 'length'[, 'pad_string'])  -- 从'string'左侧填充'pad_string'，直到字符串长度为'length'
rightPad('string', 'length'[, 'pad_string']) -- 同leftPad，右侧填充
leftPadUTF8      -- 同leftPad，只是长度以Unicode码位(而不是字符)计算
rightPadUTF8	 -- 同leftPadUTF8，右侧填充
lower, lcase	 -- 将字符串中的ASCII拉丁符号转换为小写
upper, ucase	 -- 将字符串中的ASCII拉丁符号转换为大写
lowerUTF8        -- UTF8编码文本转为小写
upperUTF8		 -- UTF8编码文本转为大写
isValidUTF8		 -- 判断字符串是否是有效的UTF8编码
toValidUTF8		 -- 转为UTF8
repeat(s, n)	 -- 将字符串s重复n次，如果n小于1，返回空字符串
reverse			 -- 按照字节序列翻转字符串，UTF8中文会乱码
reverseUTF8		 -- 翻转UTF-8字符串
format(pattern, s0, s1, …)	-- 同python format格式化函数，如SELECT format('{1} {0} {1}', 'World', 'Hello')，如果需要输出大括号，则使用 '{{}}'
concat(s1, s2, ...)   -- 字符串拼接，有NULL返回NULL
concatAssumeInjective -- 和concat结果相同，但是在GROUP BY语句中可以保证“injective”（单射），即除非每个元素都相同，才会分到一个组，否则即使拼接后的字符串相同也不会聚合为一个组
substring(s, offset, length), mid(s, offset, length), substr(s, offset, length) -- 按照字节序从offset处（索引从1开始）截取length长度
substringUTF8(s, offset, length) -- 同substring，适用UTF8编码字符串
appendTrailingCharIfAbsent(s, c) -- 如果s不是空串，且最后一个字符不是c，则将c加到末尾，否则返回s
convertCharset(s, from, to)      -- 转换编码格式
base64Encode(s), TO_BASE64		 -- 将s编码为base64
base64Decode(s), FROM_BASE64	 -- 将s从base64解码
tryBase64Decode(s)      -- 类似于base64Decode，但是如果解码失败，则返回空串
endsWith(s, suffix)     -- 是否以指定后缀结束
startsWith(str, prefix) -- 是否以指定前缀开始
trim([[LEADING|TRAILING|BOTH] trim_character FROM] input_string) -- 从input_string的左/右/首位移除trim_character中包含的所有字符
trimLeft(input_string), ltrim  -- 删除input_string左侧的所有空格（不包括tab）
trimRight(input_string), rtrim -- 删除input_string右侧的所有空格（不包括tab）
trimBoth(input_string), trim   -- 删除input_string两端的所有空格（不包括tab）
encodeXMLComponent(x)  -- 转义字符，以便存入xml文件中，<, &, >, ", ' 五个字符将被替换
decodeXMLComponent(x)  -- 解码xml字符
extractTextFromHTML(x) -- HTML或XHTML中提取文本

-- 搜索函数，所有子串数组的长度都应小于 2^8 --
position(haystack, needle[, start_pos]), locate(haystack, needle[, start_pos]) -- 在haystack中搜索needle，区分大小写，返回子串字节大小位置，返回0表示不含子串。中文应使用positionUTF8
position(needle IN haystack)    -- 和position(haystack, needle)相同
positionCaseInsensitive         -- 同position，不区分大小写
positionUTF8                    -- 同position，针对UTF8编码返回字符大小位置
positionCaseInsensitiveUTF8		-- 同positionUTF8，不区分大小写
multiSearchAllPositions(haystack, [needle1, needle2, ..., needlen])  -- 同position，可同时搜索多个子串所在数组，返回结果数组
multiSearchAllPositionsCaseInsensitive     -- 同multiSearchAllPositions，不区分大小写
multiSearchAllPositionsUTF8		-- 同multiSearchAllPositions，适用于UTF8
multiSearchAllPositionsCaseInsensitiveUTF8 -- 同multiSearchAllPositionsUTF8，不区分大小写
multiSearchFirstPosition(haystack, [needle1, needle2, …, needlen])   -- 同multiSearchAllPositions，但只返回所有搜索位置结果最左边（最小）的值
multiSearchFirstPositionCaseInsensitive
multiSearchFirstPositionUTF8
multiSearchFirstPositionCaseInsensitiveUTF8
multiSearchFirstIndex(haystack, [needle1, needle2, …, needlen]) -- 返回子串数据组中按顺序第一个被搜索到的子串在数据索引，如haystack含有needle1，则返回1，如果没有needle1含有needle2，则返回2
multiSearchFirstIndexCaseInsensitive
multiSearchFirstIndexUTF8
multiSearchFirstIndexCaseInsensitiveUTF8
multiSearchAny(haystack, [needle1, needle2, …, needlen])  -- haystack含有任意一个子串就返回1，否则返回0
multiSearchAnyCaseInsensitive
multiSearchAnyUTF8
multiSearchAnyCaseInsensitiveUTF8
match(haystack, pattern)         -- 正则匹配，匹配到返回1，否则0，尽可能使用LIKE或者position，因为他们效率更高
multiMatchAny(haystack, [pattern1, pattern2, …, patternn])  -- 匹配多个正则
multiMatchAnyIndex(haystack, [pattern1, pattern2, …, patternn])
multiMatchAllIndices(haystack, [pattern1, pattern2, …, patternn])
extract(haystack, pattern) 		-- 正则匹配从haystack中提取符合pattern模式的第一个子串，若无则返回''
extractAll(haystack, pattern)   -- 返回一个数组，匹配所有符合pattern的子串，若无，则返回''
extractAllGroupsHorizontal(haystack, pattern) -- 返回长度为2的二维数组，按照正则模式组匹配，如果pattern不包含正则组，则抛异常。第一个子数组是第一个模式组，第二个子数组是第二个模式组
extractAllGroupsVertical(haystack, pattern)   -- 同extractAllGroupsHorizontal，但是返回结果的每个子数组是每个匹配到的元素对，且比extractAllGroupsHorizontal更快
like(haystack, pattern), haystack LIKE pattern operator  -- like正则查询，只支持%和_正则符号，%表示任意个任意字符，_表示任意一个字节
notLike(haystack, pattern), haystack NOT LIKE pattern operator
ilike(haystack, pattern), haystack ILIKE pattern operator -- 同like，但是不区分大小写
countSubstrings(haystack, needle[, start_pos]) -- 统计haystack中子串needle出现的次数
countSubstringsCaseInsensitive(haystack, needle[, start_pos]) -- 同countSubstrings，不区分大小写
countSubstringsCaseInsensitiveUTF8(haystack, needle[, start_pos]) -- -- 同countSubstringsCaseInsensitive，utf8编码
countMatches(haystack, pattern) -- 统计haystack中正则子串pattern出现的次数

-- 替换函数
replaceOne(haystack, pattern, replacement) -- 把haystack中的第一个正则子串pattern替换为replacement
replaceAll(haystack, pattern, replacement), replace(haystack, pattern, replacement) -- 替换所有正则子串
replaceRegexpOne(haystack, pattern, replacement) -- 按照正则模式组替换，见下面Example。\1-\9表示每个子模式编号，\0表示整个正则表达式
replaceRegexpAll(haystack, pattern, replacement) -- 同replaceRegexpOne，但替换所有，见下面Example和[官方文档](https://clickhouse.com/docs/en/sql-reference/functions/string-replace-functions/#replaceregexpallhaystack-pattern-replacement)

-- 分隔函数
splitByChar(separator, s)		-- 按照字符separator（单字符）分隔s，同split，返回数组
splitByString(separator, s)		-- 按照字符串separator（多字符）分隔s，同split，返回数组。若separator为''，表示分隔s的每个字符
splitByRegexp(regexp, s)		-- 按照正则分隔s
splitByWhitespace(s)			-- 按照空格分隔s
splitByNonAlpha(s)				-- 按照空格或者标点符号分隔s
arrayStringConcat(arr[, separator])	-- 通过separator连接arr的元素
alphaTokens(s)					-- 选择连续的字母字符串，见下面Example
ngrams(string, ngramsize)		-- 将UTF-8字符串string拆分为长度为ngramsize的n-grams字符串，见下面Example
tokens(string)					-- 使用除字母数字以外的字符分隔string，见下面Example

Example：

SELECT extractAllGroupsHorizontal('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');
┌─extractAllGroupsHorizontal('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','def','ghi'],['111','222','333']]                                                │
└──────────────────────────────────────────────────────────────────────────────────────────┘

SELECT extractAllGroupsVertical('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)');
┌─extractAllGroupsVertical('abc=111, def=222, ghi=333', '("[^"]+"|\\w+)=("[^"]+"|\\w+)')─┐
│ [['abc','111'],['def','222'],['ghi','333']]                                            │
└────────────────────────────────────────────────────────────────────────────────────────┘

SELECT countSubstrings('aaaa', 'aa');
┌─countSubstrings('aaaa', 'aa')─┐
│                             2 │
└───────────────────────────────┘

SELECT DISTINCT
    EventDate,
    replaceRegexpOne(toString(EventDate), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res
FROM test.hits
LIMIT 7
FORMAT TabSeparated;
2014-03-17      03/17/2014
2014-03-18      03/18/2014
2014-03-19      03/19/2014
2014-03-20      03/20/2014
2014-03-21      03/21/2014
2014-03-22      03/22/2014
2014-03-23      03/23/2014

-- 复制10次
SELECT replaceRegexpOne('Hello, World!', '.*', '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0') AS res;
┌─res────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World! │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT replaceRegexpAll('Hello, World!', '.', '\\0\\0') AS res;
┌─res────────────────────────┐
│ HHeelllloo,,  WWoorrlldd!! │
└────────────────────────────┘

SELECT alphaTokens('abca1abc');
┌─alphaTokens('abca1abc')─┐
│ ['abca','abc']          │
└─────────────────────────┘

SELECT ngrams('ClickHouse', 3);
┌─ngrams('ClickHouse', 3)───────────────────────────┐
│ ['Cli','lic','ick','ckH','kHo','Hou','ous','use'] │
└───────────────────────────────────────────────────┘

SELECT tokens('test1,;\\ test2,;\\ test3,;\\   test4') AS tokens;
┌─tokens────────────────────────────┐
│ ['test1','test2','test3','test4'] │
└───────────────────────────────────┘

5. 条件函数

对于某些条件函数，可以通过设置short_circuit_function_evaluation进行短路运算，避免出现异常。NULL参与的条件判断结果都是NULL。

5.1 if

if(cond, then, else)

cond为true则返回then结果，如果为false或者null则返回else表达式结果。

5.2 三目运算符

cond ? then : else

同if，cond 结果必须是UInt8类型。

5.3 multiIf

multiIf(cond_1, then_1, cond_2, then_2, ..., else)

类似于 case when。

6. 数学函数

所有数学函数的返回结果都是Float64类型（NULL除外）。

e()				-- 自然指数e
pi()			-- 圆周率π
exp(x)			-- 指数运算（以e为底）
log(x), ln(x)	-- 对数运算（以e为底）
exp2(x)			-- 指数运算（以2为底）
log2(x)			-- 对数运算（以2为底）
exp10(x)		-- 指数运算（以10为底）
log10(x)		-- 对数运算（以10为底）
sqrt(x)			-- 平方根
cbrt(x)			-- 三次方根
sin(x)			-- 三角函数
cos(x)
tan(x)
asin(x)
acos(x)
atan(x)
pow(x, y), power(x, y)	-- x的y次方
intExp2(x)		-- 2的x次方
intExp10(x)		-- 10的x次方
cosh(x)			-- 双曲余弦函数，-∞ < x < +∞，x为弧度，返回值1 <= cosh(x) < +∞
acosh(x)		-- 反双曲余弦函数，1 <= x < +∞，返回值是弧度表示的角度值，0 <= acosh(x) < +∞
sinh(x)			-- 双曲正弦函数，-∞ < x < +∞，-∞ < sinh(x) < +∞
asinh(x)		-- 反双曲正弦，-∞ < x < +∞，-∞ < asinh(x) < +∞
atanh(x)		-- 反双曲正切，atanh(x)，-∞ < atanh(x) < +∞
hypot(x, y)		-- 根据直角三角形的两个直角边，计算斜边长，勾股定理
log1p(x)		-- 计算log(1+x)，-1 < x < +∞，对于非常小的x值，log1p(x)比log(1+x)更精确
sign(x)			-- sign函数，判断x是 >0，=0，<0
degrees(x)		-- 弧度转角度
radians(x)		-- 角度转弧度

7. 取整函数

floor(x[, N])					-- 返回小于或等于x的最大整数，N表示精度
ceil(x[, N]), ceiling(x[, N])	-- 返回大于或等于x的最小整数
trunc(x[, N]), truncate(x[, N]) -- 和floor类似，返回小于或等于x的绝对值的整数
round(x[, N])					-- 四舍五入，**注意见下文说明
roundBankers(expression [, decimal_places])	-- 银行家舍入法，见下文
roundToExp2(num)				-- 接受一个数字。如果数字小于1，则返回0。否则，它将数字四舍五入到最接近的2的次方值
roundDown(num, arr)				-- 接受一个数字并将其舍入为指定数组中的一个元素。如果该值小于最低界限，则返回最低界限

*注意：不同于其他数据库和编程语言，clickhouse中的round函数对于Decimal类型直接四舍五入，但是对于浮点型是按照“银行家舍入法”计算的，即：四舍六入五取偶。round(3.5) = 4，round(4.5) = 4，因为4是偶数，5是奇数，同样 round(45, -1) = 40。银行家舍入法的好处是对于一些有限制的计算不会产生异常的结果，例如男生的比例是50.5%，女生的比例是49.5%，四舍五入取整分别为51%和50%，就会出现加和为101%的错误结果，如果按照银行家舍入法结果就是50%和50%，求和结果是100%。

8. 映射(map)函数

8.1 map

map(key1, value1[, key2, value2, ...])

生成map结果，可参考map类型。

SELECT map('key1', number, 'key2', number * 2) FROM numbers(3);

┌─map('key1', number, 'key2', multiply(number, 2))─┐
│ {'key1':0,'key2':0}                              │
│ {'key1':1,'key2':2}                              │
│ {'key1':2,'key2':4}                              │
└──────────────────────────────────────────────────┘

8.2 mapContains

mapContains(map, key)

判断map中是否为key的键。

SELECT mapContains(a, 'name') FROM test;

┌─mapContains(a, 'name')─┐
│                      1 │
│                      0 │
└────────────────────────┘

8.3 mapKeys

mapKeys(map)

返回包含map中所有key的数组。可以通过启用optimize_functions_to_subcolumns设置进行优化，使用optimize_functions_to_subcolumns = 1时，函数只读取键的子列，而不是读取和处理整个列数据。SELECT mapKeys(m) FROM表转换为SELECT m.keys FROM表。

SELECT mapKeys(a) FROM test;

┌─mapKeys(a)────────────┐
│ ['name','age']        │
│ ['number','position'] │
└───────────────────────┘

8.4 mapValues

mapValues(map)

返回包含map中所有value的数组。同样可设置optimize_functions_to_subcolumns，查询SELECT mapValues(m) FROM表转换为SELECT m.values FROM表。

8.5 mapContainsKeyLike

mapContainsKeyLike(map, pattern)

同mapContains，正则查找。

SELECT mapContainsKeyLike(a, 'a%') FROM test;

┌─mapContainsKeyLike(a, 'a%')─┐
│                           1 │
│                           0 │
└─────────────────────────────┘  

8.6 mapExtractKeyLike

mapExtractKeyLike(map, pattern)

同mapContainsKeyLike，正则查找，但返回查找结果。

CREATE TABLE test (a Map(String,String)) ENGINE = Memory;
INSERT INTO test VALUES ({'abc':'abc','def':'def'}), ({'hij':'hij','klm':'klm'});
SELECT mapExtractKeyLike(a, 'a%') FROM test;

┌─mapExtractKeyLike(a, 'a%')─┐
│ {'abc':'abc'}              │
│ {}                         │
└────────────────────────────┘