ClickHouse常用函数统计

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u012111465/article/details/83070018

1、统计类函数

1.1 分位数

90%分位数:

quantile(0.9)()

在求(平均)响应时间时用到,quantile(0.9)(url_rt)

1.2 中位数

median()

还未使用到,median(url_rt)

1.3 相关系数

计算x与y的相关系数,计算公式:(xx)(yy)(xx)2(yy)2\dfrac{\sum(x-\overline{x})(y-\overline{y})}{\sqrt{\sum(x-\overline{x})^2\sum(y-\overline{y})^2}},取值范围[-1,1],其绝对值越大代表线性关系越强,当相关系数为0时,表示x与y之间没有线性相关关系(不代表没有非线性关系):
corr(x,y)

还未使用到,corr(url_rt,hit)

2、类型转换函数

2.1 转换为字符串

toString()

在Superset中应用较多,toString(date)

3、时间日期函数

3.1 截取年份

toYear()

应用形式toYear(date)

3.2 截取月份

toMonth()

应用形式toMonth(date),例如date = ‘2018-07-25’,得到结果为7

3.3 截取天

toDayOfDay()

应用形式toDayOfDay(date),例如date = ‘2018-07-25’,得到结果为25

3.4 截取计算星期

toDayOfWeek()

应用形式toDayOfWeek(date),例如date = ‘2018-07-25’,得到结果为3
上方

3.5 截取计算小时

toHour()

应用形式toHour(ts),例如date = ‘2018-07-25 08:10:20’,得到结果为8

3.6 截取计算分钟

toMinute()

与上方类似,用在物化视图中分钟级聚合

3.7 截取计算秒

toSecond()

与上方类似
以下函数与上方函数极为类似。

3.8 从小时开始截取

toStartOfHour()

应用形式toStartOfHour(ts),例如date = ‘2018-07-25 08:01:21’,得到结果为2018-07-25 08:00:00

3.9 从分钟开始截取(一分钟粒度)

toStartOfMinute()

应用形式toStartOfMinute(ts),例如date = ‘2018-07-25 08:01:21’,得到结果为2018-07-25 08:01:00

3.10 从分钟开始截取(五分钟粒度)

toStartOfFiveMinute()

应用形式toStartOfFiveMinute(ts),随着时间的推移将日期缩短到五分钟间隔的开始。例如date = ‘2018-07-25 08:05:21’,得到结果为2018-07-25 08:00:21,当秒级/分钟级数据量较大时,考虑五分钟聚合可以使用该函数。

4、字符串处理

4.1 小写转换

lower()

应用形式lower('aBcA')

4.2 大写转换

upper()

应用形式upper('aBcA')

4.3 字符串拼接

concat(s1,s2,...)

应用形式concat('adsa','121sf')

4.4 字符串截取

substring(s, offset, length)

应用形式substring('iioomAjOOBJB', 2, 3),从字符串的第2位起截取3个字符,得到ioo

4.5 字符串拆分

splitByString(separator , s)

SELECT splitByString(',','afffv,safe,efwee,htrjt' ) AS temp;,结果:[‘afffv’,‘safe’,‘efwee’,‘htrjt’]。
length()结合使用,SELECT length(splitByString(',','afffv,safe,efwee,htrjt' )) AS temp_num;,结果:4。

5、搜索函数

5.1 正则匹配之match()

  match(haystack,pattern)

应用形式match('avhsca','vh),是否由匹配字段,字符串中由’vh’字符串,结果输出为1

5.2 正则匹配之extract()

返回匹配到的第一个子串:

extract(haystack,pattern)

应用形式extract('iioomAj12123124OOBJB', '\\d+'),匹配多个数字,并返回第一个子串,结果输出为’12123124’
extract('iioomAj12123124OOBJB', '\\[a-z]+'),匹配小写字母的子串,并返回第一个子串,结果输出为’iioom’

5.3 正则匹配之extractAll()

返回匹配到的所有子串:

   extractAll(haystack,pattern)

应用形式extract('iioomAj12123124OOBJB', '\\[A-Z]+'),匹配大写字母的子串,并返回所有子串,结果输出为[‘A’,‘OOBJB’]

5.4 正则匹配之like()

返回匹配到的数据:

   like(haystack,pattern)

可以包含:%和_等字符
一般应用于模糊查找URL

5.5 正则匹配之notLike()

   notLike(haystack,pattern)

得到结果与like()相反。

5.6 正则替换之replaceOne()

  replaceOne(haystack,pattern,replacement)

只替换第一个匹配到的pattern。

5.7 正则替换之replaceAll()

  replaceAll(haystack,pattern,replacement)

替换匹配到的所有pattern。可以和length()函数搭配来统计某个字符的个数,SELECT (length(task_ids)-length(replaceAll(task_ids,',','')) + 1) AS task_num FROM dcm_mission_all ;统计任务的数量,task_ids可能取值为‘123,5325,564564,234’,执行后结果为:4。

5.8 正则替换之replaceRegexpAll()

 replaceRegexpAll(haystack,pattern,replacement)

与replaceAll()类似,pattern可以用正则表示,SELECT replaceRegexpAll('Hello, World!', '^', 'here: ') AS res;,得到:here: Hello, World!

6、条件语句

6.1 if()

if(cond,then,else)

条件cond为TRUE,返回then处的结果,否则,返回else处的结果。

7、URL操作函数

7.1 protocol()

用法:

SELECT  protocol('http://www.baidu.com.cn')

结果:http。

7.2 domain()

用法:

SELECT  domain('http://www.baidu.com.cn')

结果:www.baidu.com.cn

7.3 domainWithoutWWW()

用法:

SELECT  domainWithoutWWW('http://www.baidu.com.cn')

结果:baidu.com.cn

7.4 topLevelDomain()

用法:

SELECT topLevelDomain('http://www.baidu.com.cn')

结果:cn。

7.5 firstSignificantSubdomain()

用法:

SELECT  firstSignificantSubdomain('http://www.baidu.com.cn')`

结果:baidu。

7.6 path()

用法:

SELECT path('https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&rsv_spt=1&rsv_iqid=0xb79d2a30000683c8&issp=1&f=8&rsv_bp=1&rsv_idx=2&ie=utf-8&rqlang=cn&tn=baiduhome_pg&rsv_enter=0&oq=SQL%2520if()&rsv_t=c9ecC9JfyBAd%2Bobx3UmEq4ZivwZzO7M%2Bar6xjhU3caPXwxMwD76F9AUI%2FaX%2B6jUYE4Lb&inputT=7416&rsv_pq=c8d2f6750000cc4e&rsv_sug3=35&rsv_sug1=35&rsv_sug7=100&rsv_sug2=0&rsv_sug4=8521')

结果:/s。

7.7 cutQueryString()

用法:

SELECT  cutQueryString('https://www.baidu.com/s?wd=SQL%E4%B8%AD%E7%9A%84split&rsv_spt=1&rsv_iqid=0xb79d2a30000683c8&issp=1&f=8&rsv_bp=1&rsv_idx=2&ie=utf-8&rqlang=cn&tn=baiduhome_pg&rsv_enter=0&oq=SQL%2520if()&rsv_t=c9ecC9JfyBAd%2Bobx3UmEq4ZivwZzO7M%2Bar6xjhU3caPXwxMwD76F9AUI%2FaX%2B6jUYE4Lb&inputT=7416&rsv_pq=c8d2f6750000cc4e&rsv_sug3=35&rsv_sug1=35&rsv_sug7=100&rsv_sug2=0&rsv_sug4=8521')

结果:https://www.baidu.com/s。

8、字典函数

8.1 dictGetString()

用法:

SELECT dictGetString('ck_abc_dic', 'ck_value', tuple(_abc)) AS abc
FROM DB.TABL
WHERE date=today()
LIMIT 10;
展开阅读全文

没有更多推荐了,返回首页