Clickhouse常用函数还是比较丰富的，一下分别整理了字符串函数、数组函数、取整函数、日期函数、链路函数和一些常用的高阶函数，enjoy~~
一、字符串函数
1，字符串基础操作
select empty(’’);
select empty(‘123a’); // 判断字符串是空为1，否则为0
返回 1
返回 0
select notEmpty(’’);
select notEmpty(‘123a’); // 判断字符串是非空为1，否则为0
返回 0
返回 1
select length(’’);
select length(‘123a’); // 返回字符串的长度
返回 0返回 4
select lower(‘aBc’); // 将字符串转为小写
返回 abc
select upper(‘aBc’); // 将字符串转为大写
返回 ABC
select reverse(‘abc’); // 将字符串反转
返回 cba
select substring(‘123abcABC’, 2, 3); // 字符串截取
返回 23a
select appendTrailingCharIfAbsent(‘123abc’, ‘b’);
select appendTrailingCharIfAbsent(‘123abc’, ‘c’); // 如果字符串s非空，则将s后追加一个字符c(s最后一个字符与c不同)，否则不处理
返回 123abcb
返回 123abc
2、字符串切割：
select splitByChar(’-’, ‘qw-asaf-asfqw-2312-asd’) // 以单个字符分割字符串
返回：[‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’]
select splitByString(’-’, ‘qw-asaf-asfqw-2312-asd’)splitByString(’-a’, ‘qw-asaf-asfqw-2312-asd’); //以单个或多个字符分割字符串
返回：[‘qw’,‘asaf’,‘asfqw’,‘2312’,‘asd’] [‘qw’,‘saf’,‘sfqw-2312’,‘sd’]
3，字符串拼接
select concat(‘123’, ‘abc’, ‘ABC’); // 将字符串拼接
返回：123abcABC
4，字符串替换
select replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’); //替换第一个匹配到的pattern
返回 a-123cbbj464sd
select replaceOne(‘asd123cbbj464sd’, ‘sd’, ‘-’); //替换所有匹配到的pattern
返回 a-123cbbj464-
select replaceRegexpOne(‘Hello, World!’, ‘o’, '- '); // 正则匹配替换第一个匹配到的pattern
返回 Hell- , World!
select replaceRegexpAll(‘Hello, World!’, ‘^’, 'here: ')
select replaceRegexpAll(‘Hello, World!’, ‘o’, '-- '); //正则匹配替换所有匹配到的pattern
返回 here: Hello, World!
返回 Hell-- , W-- rld!
5，字符串查找
select match(‘avhsca’,'vh’); // 字符串正则匹配，返回0或1
返回 1
select extract(‘iioomAj12123124OOBJB’, ‘\d+’); //返回匹配到的第一个子串
返回 12123124
select extractAll(‘iioomAj12123124OOBJ123B’, ‘\d+’); // 返回匹配到的所有子串，输出列表
返回 [12123124,123]
select like(‘avhsca’,’%vh%’)
select like(‘avhsca’,’%vabjh%’); // 匹配到的数据返回1，否则返回0
返回 1
返回 0
select notLike(‘avhsca’,’%vh%’)
select notLike(‘avhsca’,’%vabjh%’); // 与like()函数相反
返回 0
返回 1
select position('ckhelloworldhellospark','hello') AS positionSearch; // 显示hello在字符串中第一个出现的位置。
返回 3
6、json解析：
select visitParamExtractString('{"name":"zss","age":21}' , 'name') ;
二、数组函数
1、数组定义：
select array('a','b','b','c');
2、返回数组下标
select arrayEnumerate(['a','b','c'])
3、数组求和
select arraySum([1,2,3,4]); --10
select arrayCumSum([1,2,3,4]); --[1,3,6,10]
4、数组去重：
select arrayDistinct(['a','b','b','c']); --['a','b','c']
5、数组去重统计个数
select uniqArray(['a','b','b','c']); --3
6、合并数组：
select arrayConcat(['a','b'],['hello','jim'],['e','f'])
7、数组添加、移除元素：
select arrayPushBack(['a','b'],'c');
select arrayPopBack(['a','b','c']);
8、数组排序：
select arraySort(range(2, 10, 2));
select arrayReverseSort(range(2, 10, 2));
9、数组反转
select arrayReverse(['a','b','b','c']); --['c','b','b','a']
10、数组拉链操作：
select arrayZip(['a','b','c'],['1','2','3'])
三、取整函数
1，向下取数
格式：floor(x[, N])
示例 ：
select floor(123.883, 1)
select floor(123.883, -1);
返回 123.8
返回 120
2，向上取数
格式：ceil(x[, N])
示例：
select ceil(123.883, 1);
select ceil(123.883, -1);
返回 123.9
返回 130
3，四舍五入
格式：round(x[, N])
示例：
select round(123.883, 1);
select round(123.883, -1);
返回 123.9
返回 120
四、日期函数
1，现在日期
now() // 2023-03-02 06:10:00 取当前时间
toYear() // 2023 取日志中的年份
toMonth() // 03 取日子中的月份
today() // 2023-03-02 今天的日期
yesterday() // 2023-03-01 昨天的日期
toDayOfYear() // 取一年中的第几天
toDayOfWeek() // 4 取一周中的第几天
toHour() 06 取小时
toMinute() 10 取分钟
toSecond() 00 取秒
toStartOfYear() 2023-01-01 取一年中的第一天
toStartOfMonth() 2023-03-01 取当月的第一天
toStartOfQuarter(time)
formatDatetime(now(), '%Y-%m-%d') // 2023-03-02 指定时间格式
toYYYYMM() //202303
toYYYYMMDD() //20230302
toYYYYMMDDhhmmss() //20230302061000
toUnixTimestamp(time) 将datetime格式转换成Unix时间戳
toQuarter(time) 季度(一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）)
2，未来日期
-- 第一种，日期格式（指定日期，需注意时区的问题）
WITH
toDate('2023-03-02') AS date,
toDateTime('2023-03-02 06:10:00') AS date_time
SELECT
addYears(date, 1) AS add_years_with_date,
addYears(date_time, 0) AS add_years_with_date_time;
-- 第二种，日期格式（当前，本地时间）
WITH
toDate(now()) as date,
toDateTime(now()) as date_time
SELECT
now() as now_time,-- 当前时间
-- 之后1年
addYears(date, 1) AS add_years_with_date,
addYears(date_time, 1) AS add_years_with_date_time,
-- 之后1月
addMonths(date, 1) AS add_months_with_date,
addMonths(date_time, 1) AS add_months_with_date_time,
--之后1周
addWeeks(date, 1) AS add_weeks_with_date,
addWeeks(date_time, 1) AS add_weeks_with_date_time,
-- 之后1天
addDays(date, 1) AS add_days_with_date,
addDays(date_time, 1) AS add_days_with_date_time,
--之后1小时
addHours(date_time, 1) AS add_hours_with_date_time,
--之后1分中
addMinutes(date_time, 1) AS add_minutes_with_date_time,
-- 之后10秒钟
addSeconds(date_time, 10) AS add_seconds_with_date_time,
-- 之后1个季度
addQuarters(date, 1) AS add_quarters_with_date,
addQuarters(date_time, 1) AS add_quarters_with_date_time;
3，过去日期
WITH
toDate(now()) as date,
toDateTime(now()) as date_time
SELECT
subtractYears(date, 1) AS subtract_years_with_date,
subtractYears(date_time, 1) AS subtract_years_with_date_time,
subtractQuarters(date, 1) AS subtract_Quarters_with_date,
subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
subtractMonths(date, 1) AS subtract_Months_with_date,
subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
subtractWeeks(date, 1) AS subtract_Weeks_with_date,
subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
subtractDays(date, 1) AS subtract_Days_with_date,
subtractDays(date_time, 1) AS subtract_Days_with_date_time,
subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;
4，计算时间差值
-- 第一种：指定时间计算差值示例
WITH
toDateTime('2023-03-02 06:10:10', 'Asia/Shanghai') as date_shanghai_one,
toDateTime('2023-05-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
SELECT
dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
-- 第二种：本地当前时间示例
WITH
now() as date_time
SELECT
dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;
五、链路函数
1，sequenceMatch(pattern)(time, cond1, cond2, …)
判断是否存在满足根据时间排序且依次条件1,条件2,…条件n都成立的链路.若满足 返回 1,否则为 0 .
2， sequenceCount(pattern)(time, cond1, cond2, …)
判断是否存在满足根据时间排序且依次条件1,条件2,…条件n都成立的链路 若满足 返回出现的次数
3， windowFunnel(window)(time, cond1, cond2, …)
判断是否存在满足根据时间排序且依次条件1,条件2,…条件n都成立的链路. 并且设置链路的窗口时间. 一般用于计算漏斗模型
select uid,
windowFunnel(4)(toDateTime(eventTime), eventid = 'login', eventid = 'view', eventid = 'buy') as funnel
from test_funnel
group by uid;
4，retention(cond1, cond2, …, cond32)
SELECT
uid,
retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
ORDER BY uid ASC
六、高阶函数
1，If
If 可以加到任何聚合函数之后。
加了-If之后聚合函数需要接受一个额外的参数，一个条件（Uint8类型），如果条件满足，那聚合函数处理当前的行数据，
如果不满足，那返回默认值（通常是0或者空字符串）。
使用条件聚合函数，您可以一次计算多个条件的聚合，而无需使用子查询和 JOIN例如，在Yandex.Metrica，条件聚合函数用于实现段比较功能。
sumIf(column, cond)
2，Array
Array后缀可以附加到任何聚合函数。
在这种情况下，聚合函数采用的参数 Array(T) 类型（数组）而不是 T 类型参数。
如果聚合函数接受多个参数，则它必须是长度相等的数组。
在处理数组时，聚合函数的工作方式与所有数组元素的原始聚合函数类似。
任何聚合函数都可以通过增加后缀 Array, 来使原来的参数类型 T 变为新的参数类型 Array(T)。
如果和 -If 组合，Array 必须先来，然后 If. 例： uniqArrayIf(arr, cond)， quantilesTimingArrayIf(level1, level2)(arr, cond)。由于这个顺序，该 cond 参数不会是数组。
示例1：
sumArray(arr) 对 Arrays 中的所有元素进行求和，即 sum(arraySum(arr))。
uniqArray(arr) – 计算``arr`中唯一元素的个数。这可以是一个更简单的方法：
uniq(arrayJoin(arr))，但它并不总是可以添加 arrayJoin 到查询。
3，ForEach
将对 table 使用的聚合函数，转换为对数组的聚合函数。对数组的每一项进行处理，返回一个结果数组。
4，OrDefault
格式：xxxOrDefault(x)
如果聚合函数没有输入值，则使用此组合器它返回其返回数据类型的默认值。 适用于可以采用空输入数据的聚合函数。
注意：OrDefault 可与其他组合器一起使用。
5，OrNull
格式：xxxOrNull(x)
更改聚合函数的行为。
此组合器将聚合函数的结果转换为 可为空 数据类型。 如果聚合函数没有值来计算它返回 NULL.
注意：OrNull 可与其他组合器一起使用。
6，Resample
格式：xxxResample(start, end, step)(<aggfunction_params>, resampling_key)
允许您将数据划分为组，然后单独聚合这些组中的数据。 通过将一列中的值拆分为间隔来创建组。
参数：
start — resampling_key 开始值。
stop — resampling_key 结束边界。 区间内部不包含 stop 值，即 [start, stop).
step — 分组的步长。 The aggFunction 在每个子区间上独立执行。
resampling_key — 取样列，被用来分组.
aggFunction_params — aggFunction 参数。
返回值：
aggFunction 每个子区间的结果，结果为数组。
7,State
返回的不是结果值，返回的是中间状态。 这个是与 AggregatingMergeTree 来配合使用的。
如果应用此combinator，则聚合函数不会返回结果值（例如唯一值的数量 uniq 函数），但是返回聚合的中间状态（对于 uniq，返回的是计算唯一值的数量的哈希表）。 这是一个 AggregateFunction(...) 可用于进一步处理或存储在表中以完成稍后的聚合。
要使用这些状态，请使用：
AggregatingMergeTree 表引擎。
finalizeAggregation 功能。
runningAccumulate 功能。
-Merge combinator
-MergeState combinator
8，Merge
聚合函数会把中间状态会为参数，进行 Merge，来完成聚合，返回最终的结果值。
如果应用此组合器，则聚合函数将中间聚合状态作为参数，组合状态以完成聚合，并返回结果值。
9，MergeState
与 -Merge 类似，但是返回的不是结果值，而是类似于 -State 的中间状态。
以与-Merge 相同的方式合并中间聚合状态。 但是，它不会返回结果值，而是返回中间聚合状态，类似于-State。
文章参考：
https://clickhouse.com/docs/en/intro
https://blog.csdn.net/u012111465/article/details/85250030
版权声明：本文为weixin_42011858原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。