ClickHouse常用函数整理
1. 日期类函数
1.1 时间或日期截取函数（to）—— 返回非日期
| 函数 |
用途 |
举例 |
结果 |
| toYear() |
取日期或时间日期的年份 |
toYear(toDateTime(‘2018-12-11 11:12:13’)) toYear(toDate(‘2018-12-11’)) |
返回 2018 返回 2018 |
| toMonth() |
取日期或时间日期的月份 |
toMonth(toDateTime(‘2018-12-11 11:12:13’)) toMonth(toDate(‘2018-12-11’)) |
返回 12 返回 12 |
| toDayOfMonth() |
取日期或时间日期的天（1-31） |
toMonth(toDayOfMonth(‘2018-12-11 11:12:13’)) toMonth(toDayOfMonth(‘2018-12-11’)) |
返回 11 返回 11 |
| toDayOfWeek() |
取日期或时间日期的星期（星期一为1，星期日为7）。 |
toDayOfWeek(toDateTime(‘2018-12-11 11:12:13’)) toDayOfWeek(toDate(‘2018-12-11’)) |
返回 2 返回 2 |
| toHour() |
取时间日期的小时 |
toHour(toDateTime(‘2018-12-11 11:12:13’)) |
返回 11 |
| toMinute() |
取时间日期的分钟 |
toMinute(toDateTime(‘2018-12-11 11:12:13’)) |
返回 12 |
| toSecond() |
取时间日期的秒 |
toSecond(toDateTime(‘2018-12-11 11:12:13’)) |
返回 13 |
| toMonday() |
取时间日期最近的周一（返回日期） |
toMonday(toDate(‘2018-12-11’)) toMonday(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-10 返回 2018-12-10 |
| toTime() |
将时间日期的日期固定到某一天，保留原始时间 |
toTime(toDateTime(‘2018-12-11 11:12:13’)) |
返回 1970-01-02 11:12:13 |
1.2 时间或日期截取函数（toStartOf）—— 返回日期
| 函数 |
用途 |
举例 |
结果 |
| toStartOfMonth() |
取日期或时间日期的月份的第一天，返回日期 |
toStartOfMonth(toDateTime(‘2018-12-11 11:12:13’)) toStartOfMonth(toDate(‘2018-12-11’)) |
返回 2018-12-01 返回 2018-12-01 |
| toStartOfQuarter() |
取日期或时间日期的季度的第一天，返回日期 |
toStartOfQuarter(toDateTime(‘2018-12-11 11:12:13’)) toStartOfQuarter(toDate(‘2018-12-11’)) |
返回 2018-10-01 返回 2018-10-01 |
| toStartOfYear() |
取日期或时间日期的年份的第一天，返回日期 |
toStartOfYear(toDateTime(‘2018-12-11 11:12:13’))toStartOfYear(toDate(‘2018-12-11’)) |
返回 2018-01-01 返回 2018-01-01 |
| toStartOfMinute() |
截取时间日期到分钟（之后归零），返回日期 |
toStartOfMinute(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-11 11:12:00 |
| toStartOfFiveMinute() |
截取时间日期到最近的5的倍数分钟（之后归零），返回日期 |
toStartOfFiveMinute(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-11 11:10:00 |
| toStartOfFifteenMinutes() |
截取时间日期到最近的15的倍数分钟（之后归零），返回日期 |
toStartOfFifteenMinutes(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-11 11:00:00 |
| toStartOfHour() |
截取时间日期到小时（之后归零），返回日期 |
toStartOfHour(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-11 11:00:00 |
| toStartOfDay() |
截取时间日期到天（之后归零），返回日期 |
toStartOfDay(toDateTime(‘2018-12-11 11:12:13’)) |
返回 2018-12-11 00:00:00 |
| timeSlot() |
将时间日期中，分钟大于等于30的归于30，分钟数小于30的归为00 |
timeSlot(toDateTime(‘2018-12-11 11:33:13’)) timeSlot(toDateTime(‘2018-12-11 11:33:13’)) |
返回 2018-12-11 11:00:00 返回 2018-12-11 11:30:00 |
1.3 日期或时间日期生成函数
| 函数 |
用途 |
举例 |
结果 |
| now() |
生成当前时间日期 |
now() |
返回 2018-12-13 10:10:12 |
| today() |
生成今天的日期 |
today() |
返回 2018-12-13 |
| yesterday() |
生成昨天的日期 |
yesterday() |
返回 2018-12-12 |
Function formats a Time according given Format string. N.B.: Format is a constant expression, e.g. you can not have multiple formats for single result column.
Supported modifiers for Format: ("Example" column shows formatting result for time 2018-01-02 22:33:44)
| Modifier |
Description |
Example |
| %C |
year divided by 100 and truncated to integer (00-99) |
20 |
| %d |
day of the month, zero-padded (01-31) |
2 |
| %D |
Short MM/DD/YY date, equivalent to %m/%d/%y |
01/02/2018 |
| %e |
day of the month, space-padded ( 1-31) |
2 |
| %F |
short YYYY-MM-DD date, equivalent to %Y-%m-%d |
2018/1/2 |
| %H |
hour in 24h format (00-23) |
22 |
| %I |
hour in 12h format (01-12) |
10 |
| %j |
day of the year (001-366) |
2 |
| %m |
month as a decimal number (01-12) |
1 |
| %M |
minute (00-59) |
33 |
| %n |
new-line character ('\n') |
|
| %p |
AM or PM designation |
PM |
| %R |
24-hour HH:MM time, equivalent to %H:%M |
22:33 |
| %S |
second (00-59) |
44 |
| %t |
horizontal-tab character ('\t') |
|
| %T |
ISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S |
22:33:44 |
| %u |
ISO 8601 weekday as number with Monday as 1 (1-7) |
2 |
| %V |
ISO 8601 week number (01-53) |
1 |
| %w |
weekday as a decimal number with Sunday as 0 (0-6) |
2 |
| %y |
Year, last two digits (00-99) |
18 |
| %Y |
Year |
2018 |
| %% |
a % sign |
% |
2. 类型转化类函数
2.1 精度保留（非四舍五入）
| 函数 |
用途 |
举例 |
结果 |
| toDecimal32(‘whdwjfew’,8) |
将数值型或者含有非数字的字符串进行精度保留 |
toDecimal32(23.12291, 3)toDecimal32(’_23.12291’, 3) |
返回 23.122 返回 0.000 |
| toDecimal64(‘whdwjfew’,8) |
将数值型或者含有非数字的字符串进行精度保留 |
toDecimal64(23.12291, 3) toDecimal64(’_23.12291’, 3) |
返回 23.122 返回 0.000 |
| toDecimal128(‘whdwjfew’,8) |
将数值型或者含有非数字的字符串进行精度保留 |
toDecimal128(23.12291, 3) toDecimal128(’_23.12291’, 3) |
返回 23.122 返回 0.000 |
2.2 字符串转化为整数（非整数的字符串返回0）
| 函数 |
用途 |
举例 |
结果 |
| toUInt8OrZero() |
将无符号整数字符型转化为整数型，否则返回0 |
toUInt8OrZero(‘123’) toUInt8OrZero(‘123.12’) |
返回 123 返回 0 |
| toInt8OrZero() |
将整数字符型转化为整数型，否则返回0 |
toInt8OrZero(‘123’) toInt8OrZero(’-123’) |
返回 123 返回 -123 |
| toFloat32OrZero() |
将数值字符串型转化为数值型，注意：从toFloat32OrZero开始，丢32的没有对应的函数 |
toFloat32OrZero(‘-123’)toFloat32OrZero(‘123.123’) |
返回 -123 返回 123.123 |
2.3 日期与时间日期转化
| 函数 |
用途 |
举例 |
结果 |
| toDate() |
将字符型日期转化为日期型 |
toDate(‘2018-12-24’) |
返回 2018-12-24 |
| toDateTime() |
将字符型时间日期转化为时间日期型 |
toDateTime(‘2018-12-24 10:10:00’) |
返回 2018-12-24 10:10:00 |
| toUnixTimestamp() |
将DateTime转换成Unix时间戳 |
toUnixTimestamp(now()) |
返回 1589618717 |
2.4 转化为字符型
| 函数 |
用途 |
举例 |
结果 |
| toString() |
将数值型、字符型、日期等转化为字符型 |
toString(‘2018-12-24’) toString(‘123’) |
返回 2018-12-24 返回 123 |
2.5 查看数据类型
| 函数 |
用途 |
举例 |
结果 |
| toTypeName() |
返回数据的类型 |
toTypeName(toString(‘123’)) toTypeName(toDate(‘2018-12-24’)) |
返回 String 返回 Date |
3. 字符串操作
3.1 基本字符串操作
| 函数 |
用途 |
举例 |
结果 |
| empty() |
判断字符串是空为1，否则为0 |
empty(’’)empty(‘123a’) |
返回 1返回 0 |
| notEmpty() |
判断字符串是非空为1，否则为0 |
notEmpty(’’)notEmpty(‘123a’) |
返回 0返回 1 |
| length() |
返回字符串的长度 |
length(’’)length(‘123a’) |
返回 0返回 4 |
| lower() |
将字符串转为小写 |
lower(‘aBc’) |
返回 abc |
| upper() |
将字符串转为大写 |
upper(‘aBc’) |
返回 ABC |
| reverse() |
将字符串反转 |
reverse(‘abc’) |
返回 cba |
| substring(s, offset, length) |
字符串截取 |
substring(‘123abcABC’, 2, 3) |
返回 23a |
| appendTrailingCharIfAbsent(s, c) |
如果字符串s非空，则将s后追加一个字符c(s最后一个字符与c不同)，否则不处理 |
appendTrailingCharIfAbsent(‘123abc’, ‘b’)appendTrailingCharIfAbsent(‘123abc’, ‘c’) |
返回 123abcb返回 123abc |
3.2 字符串查找
| 函数 |
用途 |
举例 |
结果 |
| match(haystack,pattern) |
字符串正则匹配，返回0或1 |
match(‘avhsca’,'vh’) |
返回 1 |
| extract(haystack,pattern) |
返回匹配到的第一个子串 |
extract(‘iioomAj12123124OOBJB’, ‘\d+’) |
返回 12123124 |
| extractAll(haystack,pattern) |
返回匹配到的所有子串，输出列表 |
extractAll(‘iioomAj12123124OOBJ123B’, ‘\d+’) |
返回 [12123124,123] |
| like(haystack,pattern) |
匹配到的数据返回1，否则返回0 |
like(‘avhsca’,’%vh%’)like(‘avhsca’,’%vabjh%’) |
返回 1返回 0 |
| notLike(haystack, pattern) |
与like()函数相反 |
notLike(‘avhsca’,’%vh%’)notLike(‘avhsca’,’%vabjh%’) |
返回 0返回 1 |
3.3 字符串替换
| 函数 |
用途 |
举例 |
结果 |
| replaceOne(haystack,pattern,replacement) |
替换第一个匹配到的pattern |
replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’) |
返回 a-123cbbj464sd |
| replaceAll(haystack,pattern,replacement) |
替换所有匹配到的pattern |
replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’) |
返回 a-123cbbj464- |
| replaceRegexpOne(haystack, pattern, replacement) |
正则匹配替换第一个匹配到的pattern |
replaceRegexpOne(‘Hello, World!’, ‘o’, '- ') |
返回 Hell- , World! |
| replaceRegexpAll(haystack,pattern,replacement) |
正则匹配替换所有匹配到的pattern |
replaceRegexpAll(‘Hello, World!’, ‘^’, 'here: ')replaceRegexpAll(‘Hello, World!’, ‘o’, '-- ') |
返回 here: Hello, World! 返回 Hell-- , W-- rld! |
3.4 字符串分割
| 函数 |
用途 |
举例 |
结果 |
| splitByChar(separator, s) |
以单个字符分割字符串 |
splitByChar(’-’, ‘qw-asaf-asfqw-2312-asd’) |
返回 [‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’] |
| splitByString(separator, s) |
以单个或多个字符分割字符串 |
splitByString(’-’, ‘qw-asaf-asfqw-2312-asd’)splitByString(’-a’, ‘qw-asaf-asfqw-2312-asd’) |
返回 [‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’]返回 [‘qw’,‘saf’,‘sfqw-2312’,‘sd’] |
3.5 字符串拼接
| 函数 |
用途 |
举例 |
结果 |
| concat(s1,s2,…) |
将字符串拼接 |
concat(‘123’, ‘abc’, ‘ABC’) |
返回 123abcABC |
4. 条件语句
| 函数 |
用途 |
举例 |
结果 |
| if(cond,then,else) |
条件输出 |
if(1 > 2, ‘正确’, ‘错误’) |
返回 错误 |
| multiIf(cond_1, then_1, cond_2, then_2…else) |
多条件输出 |
multiIf(1 > 2, ‘正确’, 2 < 0, ‘正确’, ‘错误’) |
返回 错误 |
5.数学函数
| 函数 |
用途 |
举例 |
结果 |
| e() |
返回e的值 |
e() |
返回 2.718281828459045 |
| pi() |
返回pi的值 |
pi() |
返回 3.141592653589793 |
| exp(x) |
返回e的x次方 |
exp(1) |
返回 2.718281828459045 |
| exp2(x) |
返回2的x次方 |
exp2(2) |
返回 4 |
| exp10(x) |
返回10的x次方 |
exp10(1) |
返回 10 |
| log(x) |
返回log以e为底的对数值 |
log(e()) |
返回 1 |
| log2(x) |
返回log以2为底的对数值 |
log2(2) |
返回 1 |
| log10(x) |
返回log以10为底的对数值 |
log10(100) |
返回 2 |
| sqrt(x) |
对x开平方 |
sqrt(4) |
返回 2 |
| cbrt(x) |
对x开立方 |
cbrt(8) |
返回 2 |
| pow(x, y) |
返回x的y次方 |
pow(2, 3) |
返回 8 |
6. 舍入函数
| 函数 |
用途 |
举例 |
结果 |
| floor(x[, N]) |
向下取数 |
floor(123.883, 1)floor(123.883, -1) |
返回 123.8返回 120 |
| ceil(x[, N]) |
向上取数 |
ceil(123.883, 1)ceil(123.883, -1) |
返回 123.9返回 130 |
| round(x[, N]) |
四舍五入 |
round(123.883, 1)round(123.883, -1) |
返回 123.9返回 120 |
7. URL操作函数
8. IP操作函数
| 函数 |
用途 |
举例 |
| IPv4StringToNum(s) |
将IPV4转为数值，非IPV4的转化为0 |
IPv4StringToNum(‘23.217.198.69’)
IPv4StringToNum(‘adwh.124.qwfqw’) |
| IPv4NumToString(num) |
将数值转为IPV4 |
IPv4NumToString(400148037) |
9. 表操作
9.1 表连接操作
| 函数 |
用途 |
举例 |
结果 |
| LIMITN |
查询N条数据，一般跟ORDER BY 连用 |
ORDER BY hit DESC LIMIT 10 |
按照hit列降排取前10 |
| LIMIT N BY Clause |
按照Clause列查询N条数据，一般跟ORDER BY 连用 |
SELECT date， domain, count(1) AS hit from db.tb where…GROUP BY date， domain,ORDER BY hit DESC LIMIT 10 BY date |
取每天TOP10的域名 |
9.2 字典操作
| 函数 |
用途 |
举例 |
结果 |
| dictGetString() |
字典映射 |
dictGetString(‘ck_abc_dic’, ‘ck_value’, tuple(_abc)) |
|
10. 参考资料
[ClickHouse函数整理（详细）](
