clickhouse 函数语法

# SPL文档(常用函数)

## 日期类函数

### 时间或日期截取函数（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

toDateTime()| 将时间戳转换为时间类型 |toDateTime(at_timestamp/1000)|

### 时间或日期截取函数（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

### 日期或时间日期生成函数

函数 | 用途 | 举例 | 结果

---|---|---|---

now()| 生成当前时间日期 |now()|2018-12-13 10:10:12

today()| 生成今天的日期 |today()|2018-12-13

yesterday()| 生成昨天的日期 |yesterday()|2018-12-12

## 类型转化类函数

### 精度保留（非四舍五入）

函数 | 用途 | 举例 | 结果

---|---|---|---

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

### 字符串转化为整数

函数 | 用途 | 举例 | 结果

---|---|---|---

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

### 日期与时间日期转化

### 字符串转化为整数

函数 | 用途 | 举例 | 结果

---|---|---|---

toDate()| 将字符型日期转化为日期型 |toDate('2018-12-24')|2018-12-24

toDateTime()| 将字符型时间日期转化为时间日期型 |toDateTime('2018-12-24 10:10:00')|2018-12-24 10:10:00

### 转化为字符型

函数 | 用途 | 举例 | 结果

---|---|---|---

toString()| 将数值型、字符型、日期等转化为字符型 |toString('2018-12-24')
toString('123')|2018-12-24
123

### 查看数据类型

函数 | 用途 | 举例 | 结果

---|---|---|---

toTypeName()| 返回数据的类型 |toTypeName(toString('123'))
toTypeName(toDate('2018-12-24'))|String
Date

## 字符串操作

### 基本字符串操作

函数 | 用途 | 举例 | 结果

---|---|---|---

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

### 字符串查找

函数 | 用途 | 举例 | 结果

---|---|---|---

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

### 字符串替换

函数 | 用途 | 举例 | 结果

---|---|---|---

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!

### 字符串分割

函数 | 用途 | 举例 | 结果

---|---|---|---

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']

### 字符串拼接

函数 | 用途 | 举例 | 结果

---|---|---|---

concat(s1,s2,...)| 将字符串拼接 |concat('123', 'abc', 'ABC')|123abcABC

## 条件语句

函数 | 用途 | 举例 | 结果

---|---|---|---

if(cond,then,else)| 条件输出 |if(1 > 2, '正确', '错误')| 错误

multiIf(cond_1, then_1, cond_2, then_2...else)| 多条件输出 |multiIf(1 > 2, '正确', 2 < 0, '正确', '错误')| 错误

case field when 1 then 11 when 2 then 22 else 33 end| 多条件输出 |case method when 'GET' then 'GET1' when 'POST' then 'POST1' else 0 end|

## 数学函数

函数 | 用途 | 举例 | 结果

---|---|---|---

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

## 舍入函数

函数 | 用途 | 举例 | 结果

---|---|---|---

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

## LIMIT操作

函数 | 用途 | 举例 | 结果

---|---|---|---

LIMIT N| 查询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的域名

## json数据处理

函数 | 用途

---|---

visitParamExtractUInt(params, name)| 将名为“name”的字段的值解析成UInt64

visitParamExtractInt(params, name)| 与visitParamExtractUInt相同，但返回Int64。

visitParamExtractFloat(params, name)| 与visitParamExtractUInt相同，但返回Float64

visitParamExtractRaw(params, name)| 返回字段的值，包含空格符

visitParamExtractString(params, name)| 使用双引号解析字符串。这个值没有进行转义。如果转义失败，它将返回一个空白字符串 | visitParamExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0'

## url函数

函数 | 用途 |

---|---

protocol| 返回URL的协议 |

domain| 获取域名|

domainWithoutWWW| 返回域名并删除第一个'www.'

topLevelDomain| 返回顶级域名。例如：.ru

path| 返回URL路径

pathFull| 返回URL路径,包括请求参数和fragment。例如：/top/news.html?page=2#comments

queryString| 返回请求参数。例如：page=1&lr=213,请求参数不包含问号已经#以及#之后所有的内容

extractURLParameter(URL, name)| 返回URL请求参数中名称为‘name’的参数。如果不存在则返回一个空字符串。如果存在多个匹配项则返回第一个相匹配的。

extractURLParameters(URL)| 返回一个数组，其中以name=value的字符串形式返回url的所有请求参数。

decodeURLComponent(URL)| 返回已经解码的URL

## 数组函数

函数 | 用途

---|---

arrayElement(arr, n), operator arr[n]| 从数组arr中获取索引为“n”的元素 |

length| 返回数组中的元素个数。结果类型是UInt64

arrayConcat| 合并参数中传递的所有数组

## Nullable处理函数

函数 | 用途 | 用法

---|---|---

isNull| 判断参数是否为NULL|isNull(x)

isNotNull| 判断参数是否不为NULL|isNotNull(x)

ifNull| 如果第一个参数为“NULL”，则返回第二个参数的值 |ifNull(x,alt)

## 常用语法

### 取最近10分钟内的数据

```

select col1 from table where at_timestamp between (toUnixTimestamp(now())-600)*1000 and toUnixTimestamp(now())*1000

select col1 from table where _at_timestamp_ between toDateTime(now()-300) and now()

```

### 趋势图时间粒度

```

select toDateTime((at_timestamp - at_timestamp%60000)/1000) as ttime,count(1) from ecs where at_timestamp between $time$ and $time$ group by ttime order by ttime desc

# 60000 是1min

select toStartOfInterval(_at_timestamp_,INTERVAL 5 minute,'Asia/Shanghai') as ttime,count(1) from ecs where _at_timestamp_ between ${begin_time} and ${end_time} group by ttime order by ttime desc

```

### 时区转换

```

select toDateTime(now(),'UTC');

```

### 行数据转列

```

select arrayJoin(splitByString('}, {',replaceRegexpAll('[{"desc": "desc1"}, {"desc": "desc2"}]','\\[\\{|\\}\\]','')))

```

## 更多用法

[更多用法](https://clickhouse.tech/docs/zh/ "更多用法")

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,053评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,527评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,779评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,685评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,699评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,609评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,989评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,654评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,890评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,634评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,716评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,394评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,976评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,950评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,191评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,849评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,458评论 2赞 342

clickhouse 函数语法

推荐阅读更多精彩内容