clickhouse常用函数总结_clickhouse sql 截取字符串函数

检测函数类型（clickhouse中数据的类型）

SELECT toTypeName(0);-- UInt8(三位数为8)

SELECT toTypeName(-0);-- Int8

SELECT toTypeName(-343);-- Int16

SELECT toTypeName(12.43); -- Float64(默认浮点型的数据为64)，所以一般在处理浮点型的数据的时候尽量转成toFloat32(12.43)

SELECT toTypeName(12.34343); -- Float64

SELECT toTypeName(toDateTime(1502396027)); -- DateTime

算数函数

求和

SELECT plus(12, 21), plus(10, -10), plus(-10, -10);

差值

SELECT minus(10, 5), minus(10, -10),minus(-10, -10);

积

SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);

平均值

SELECT divide(12, 4), divide(10, 3), divide(2, 4), divide(-4, -2), divide(-4, 2), divide(-4.5, 3);

SELECT intDiv(10, 3), divide(10, 3); -- 3, 3.333(保留四位有效数字)

SELECT divide(10, 0), divide(-10, 0); -- 出现无穷大字符“ ∞ ”或“ -∞ ”

SELECT divide(0, 0); -- 特殊字符（类似乱码）

SELECT intDivOrZero(10, 0); -- 0

求余数

SELECT modulo(10, 3); --1

SELECT modulo(10.5, 3); --1

取反

SELECT negate(10), negate(-10); -- -10 10

绝对值

SELECT abs(-10), abs(10);

最大公约数

SELECT gcd(12, 24), gcd(-12, -24), gcd(-12, 24);

最小公倍数

SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4);

比较函数

比较函数(始终返回0表示false 或 1表示true)

SELECT 12 == 12, 12 != 10, 12 == 132, 12 != 12, 12 <> 12;

SELECT equals(12, 12), notEquals(12, 10), equals(12, 10), notEquals(12,123);

SELECT greater(12, 10), greater(10, 12), greater(12, 12);-- 前者是否大于后者

SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);-- 前者是否大于或等于后者

SELECT less(12, 21), less(12, 10), less(120, 120);-- 前者是否小于后者

SELECT lessOrEquals(12, 120), lessOrEquals(12, 12);-- 前世是否小于或等于或者

逻辑函数

逻辑操作符（返回0表示false 或 1表示true）

SELECT 12==12 or 12!=10;

SELECT 12==12 and 12!=10;

SELECT not 12, not 0;

SELECT or(equals(12, 12), notEquals(12, 10)); --函数表示法：或

SELECT and(equals(12, 12), notEquals(12, 10));--函数表示法：且

SELECT not(12), not(0);

类型转换函数

类型转换函数部分示例：

SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);

SELECT toString(now());

SELECT now() AS now_local, toString(now(), 'Asia/Yekaterinburg') AS now_yekat;

SELECT now() AS now_local, toDate(now()), toDateTime(now()), toUnixTimestamp(now());

SELECT

'2016-06-15 23:00:00' AS timestamp,

CAST(timestamp AS DateTime) AS datetime,

CAST(timestamp AS Date) AS date,

CAST(timestamp, 'String') AS string,

CAST(timestamp, 'FixedString(22)') AS fixed_string;

WITH

toDate('2019-01-01') AS date,

INTERVAL 1 WEEK AS interval_week,

toIntervalWeek(1) AS interval_to_week,

toIntervalMonth(1) AS interval_to_month

SELECT

date + interval_week,

date + interval_to_week,

date + interval_to_month;

WITH

toDateTime('2019-01-01 12:10:10') as datetime,

INTERVAL 1 HOUR AS interval_hour,

toIntervalHour(1) as invterval_to_hour

SELECT

plus(datetime, interval_hour),

plus(datetime, invterval_to_hour);

时间日期函数

时间日期函数

SELECT

toDateTime('2019-07-30 10:10:10') AS time,--将字符型时间日期转化为时间日期型

>select toDateTime(‘2021-12-24 10:10:00’)  

>返回 2021-12-24 10:10:00

-- 将DateTime转换成Unix时间戳

toUnixTimestamp(time) as unixTimestamp,

-- 保留 时-分-秒

toDate(time) as date_local,--将字符型日期转化为日期型

> toDate(‘2021-12-24’)  返回 2021-12-24

toTime(time) as date_time,-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。

>toTime(toDateTime(‘2021-12-11 11:12:13’))  返回 1970-01-02 11:12:13

-- 获取年份，月份，季度，小时，分钟，秒钟

toYear(time) as get_year,--取日期或时间日期的年份

>toYear(toDateTime(‘2021-12-11 11:12:13’)) toYear(toDate(‘2021-12-11’))  

> 返回 2021 返回 2021

toMonth(time) as get_month,

-- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）

toQuarter(time) as get_quarter,

toHour(time) as get_hour,

> toHour(toDateTime(‘2021-12-11 11:12:13’))  返回 11

toMinute(time) as get_minute,

> toMinute(toDateTime(‘2021-12-11 11:12:13’))  返回 12

toSecond(time) as get_second,

-- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几

toDayOfYear(time) as "当前年份中的第几天",

toDayOfMonth(time) as "当前月份的第几天",

>toMonth(toDayOfMonth(‘2021-12-11’))  返回 11

toDayOfWeek(time) as "星期",

> toDayOfWeek(toDate(‘2021-12-11’))  返回 2

toDate(time, 'Asia/Shanghai') AS date_shanghai,

toDateTime(time, 'Asia/Shanghai') AS time_shanghai,

-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻

toStartOfYear(time),

toStartOfMonth(time),

toStartOfQuarter(time),

> toStartOfQuarter(toDateTime(‘2021-12-11 11:12:13’)) 返回 2021-10-01

toStartOfDay(time) AS cur_start_daytime,

toStartOfHour(time) as cur_start_hour,

> toStartOfHour(toDateTime(‘2021-12-11 11:12:13’))  返回 2021-12-11 11:00:00

toStartOfMinute(time) AS cur_start_minute,

>toStartOfMinute(toDateTime(‘2021-12-11 11:12:13’))  返回 2021-12-11 11:12:00

-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号

toRelativeYearNum(time),

toRelativeQuarterNum(time);

timeSlot()  ---将时间日期中，分钟大于等于30的归于30，分钟数小于30的归为00  

> timeSlot(toDateTime(‘2021-12-11 11:13:13’))

> timeSlot(toDateTime(‘2021-12-11 11:33:13’))  返回 2021-12-11 11:00:00返回 2021-12-11 11:30:00

SELECT

toDateTime('2019-07-30 14:27:30') as time,

toISOYear(time) AS iso_year,

toISOWeek(time) AS iso_week,

now() AS cur_dateTime1, -- 返回当前时间yyyy-MM-dd HH:mm:ss

today() AS cur_dateTime2, -- 其功能与'toDate(now())'相同

> today()  返回 2021-05-22

yesterday() AS yesterday, -- 当前日期的上一天

> yesterday()  返回 2021-12-12

-- timeSlot(1) AS timeSlot_1, -- 出现异常！！将时间向前取整半小时

toDate(time) as getY_M_d;

-- 目前只有这三种格式，没有什么toYYYY(),toYYYddmm()之类的函数，不要想当然。

SELECT

now() as nowTime,--生成当前日期

> now()  返回 2021-05-13 10:10:12

-- 将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 100 + MM）

toYYYYMMDDhhmmss(nowTime),

toYYYYMMDD(nowTime),

toYYYYMM(nowTime);

-- formatDateTime(Time, Format[,Timezone])函数引用

SELECT

now() as now_time,

toDateTime('2019-07-31 18:20:30') AS def_datetime,

formatDateTime(now_time, '%D') AS now_time_day_month_year,-- 07/30/19

-- toDateTime('2019-07-31 18:20:30', 'Asia/Shanghai') AS def_datetime1, -- 指定时区

formatDateTime(def_datetime, '%Y') AS def_datetime_year, -- 2019（指定日期为2019年）

formatDateTime(def_datetime, '%y') AS def_datetime_year_litter, -- 19（指定日期为19年,Year, last two digits (00-99),本世纪的第19年）

formatDateTime(def_datetime, '%H') AS hour24, -- 18 下午六点

formatDateTime(def_datetime, '%I') AS hour12, -- 06下午六点

formatDateTime(def_datetime, '%p') AS PMorAM, -- 指定时间是上午还是下午

formatDateTime(def_datetime, '%w') AS def_datetime_get_curWeek,-- 3(指定日期为星期三)

formatDateTime(def_datetime, '%F') AS def_datetime_get_date,-- 2019-07-31

formatDateTime(def_datetime, '%T') AS def_datetime_get_time,-- 18:20:30

formatDateTime(def_datetime, '%M') AS def_datetime_get_minute,-- 20(得到指定事件的“分”，minute (00-59))

formatDateTime(def_datetime, '%S') AS def_datetime_get_second;-- 30(得到指定事件的“秒”，second (00-59))

1.跳转到之后的日期函数

第一种，日期格式（指定日期，需注意时区的问题）

WITH

toDate('2019-09-09') AS date,

toDateTime('2019-09-09 00:00: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,-- 当前时间

addYears(date, 1) AS add_years_with_date,-- 之后1年

addYears(date_time, 1) AS add_years_with_date_time,

addMonths(date, 1) AS add_months_with_date,-- 之后1月

addMonths(date_time, 1) AS add_months_with_date_time,

addWeeks(date, 1) AS add_weeks_with_date,--之后1周

addWeeks(date_time, 1) AS add_weeks_with_date_time,

addDays(date, 1) AS add_days_with_date,-- 之后1天

addDays(date_time, 1) AS add_days_with_date_time,

addHours(date_time, 1) AS add_hours_with_date_time,--之后1小时

addMinutes(date_time, 1) AS add_minutes_with_date_time,--之后1分中

addSeconds(date_time, 10) AS add_seconds_with_date_time,-- 之后10秒钟

addQuarters(date, 1) AS add_quarters_with_date, -- 之后1个季度

addQuarters(date_time, 1) AS add_quarters_with_date_time;

2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)

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;

SELECT toDate('2019-07-31', 'Asia/GuangZhou') as date_guangzhou;

SELECT toDate('2019-07-31'), toDate('2019-07-31', 'Asia/Beijing') as date_beijing;

-- 亚洲只能加载上海的timezone？？？

SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;

-- 计算某个时刻在不同时间单位下的差值

-- 第一种：指定时间计算差值示例

WITH

toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,

toDateTime('2020-10-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;

timeSlot(StartTime, Duration, [,Size])

它返回一个时间数组，其中包括从从“StartTime”开始到“StartTime + Duration 秒”内的所有符合“size”（以秒为单位）步长的时间点

作用:搜索在相应会话中综合浏览量是非常有用的。

SELECT

timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600)) as dateTimeArray,

dateTimeArray[0] as arr_index_0, -- no result.

dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:00:00

dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:30:00

dateTimeArray[3] as arr_index_3, -- no result.

dateTimeArray[4] as arr_index_4; -- no result.

-- toUInt32(600) 表示之后间距20秒的时刻

SELECT

timeSlots(now(), toUInt32(600), 20) as dateTimeArray, -- 类似于：引用地址

dateTimeArray[0] as arr_index_0, -- no result.为什么？

dateTimeArray[1] as arr_index_1,

dateTimeArray[2] as arr_index_2,

dateTimeArray[3] as arr_index_3,

dateTimeArray[4] as arr_index_4,

dateTimeArray[5] as arr_index_5;

-- 指定时间为基准，之后每个元素增加20秒

SELECT

timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600), 20) as cur_dateTimeArray, -- 类似于：引用地址

cur_dateTimeArray[0] as arr_index_0, -- no result.为什么？

cur_dateTimeArray[1] as arr_index_1, -- 2012-01-01 20:20:00

cur_dateTimeArray[2] as arr_index_2, -- 2012-01-01 20:20:20

cur_dateTimeArray[3] as arr_index_3, -- 2012-01-01 20:20:40

cur_dateTimeArray[4] as arr_index_4, -- 2012-01-01 20:21:00

cur_dateTimeArray[5] as arr_index_5; -- 2012-01-01 20:21:20

字符串函数

SELECT

length('hello world') as str_length, -- 按照Unicode编码计算长度“你好”的长度为6

empty('hello world'),-- 判断字符串是否为空，空为1，非空为0

notEmpty('hello world'),

lengthUTF8('hello world'), -- 按照实际字符计算长度“你好”为2

char_length('hello world'), -- 同 lengthUTF8()

character_length('hello world'), -- 同 lengthUTF8(),

lower('abcd123--'),--字母全部小写（将字符串中的ASCII转换为小写。）

upper('abcd123--'),--字母全部大写（将字符串中的ASCII转换为大写。）

lowerUTF8('abcd123-/*\8asd-\\'), -- abcd123-/*8asd-\

upperUTF8('abcd123--'), -- ABCD123--

isValidUTF8('abcd123--/*\*'); --检查字符串是否为有效的UTF-8编码，是则返回1，否则返回0。

SELECT notEmpty(''), notEmpty(NULL), notEmpty('he'); -- 0,空,1

SELECT toValidUTF8('\x61\xF0\x80\x80\x80b');

-- reverseUTF8():以Unicode字符为单位反转UTF-8编码的字符串。如果字符串不是UTF-8编码，则可能获取到一个非预期的结果（不会抛出异常）

SELECT reverse('abcdefg'), reverseUTF8('abcdefg');

1. 字符串维度自定义安排

SELECT format('{1} {0} {1}', 'World', 'Hello'); -- 输出：Hello World Hello

SELECT format('{0} {0} {1} {1}', 'one', 'two'); -- 输出：one one two two

SELECT format('{} {}', 'Hello', 'World'); -- 输出：Hello World

2. 字符串拼接 concat(s1,s2,s3,...)

SELECT concat('Hello',' ','World', '!');-- Hello World!

-- 与concat相同，区别在于，你需要保证concat(s1, s2, s3) -> s4是单射的，它将用于GROUP BY的优化。

SELECT concatAssumeInjective('Hello',' ','World', '!');-- Hello World!

3.字符串截取：substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)

-- 以字节为单位截取指定位置字符串，返回以‘offset’位置为开头，长度为‘length’的子串。‘offset’从1开始（与标准SQL相同）。‘offset’和‘length’参数必须是常量。

SELECT

substring('abcdefg', 1, 3),-- abc

substring('你好，世界', 1, 3),-- 你

substringUTF8('你好，世界', 1, 3); -- 你好，

4. 字符串拼接：appendTrailingCharIfAbsent(s, c)

-- 如果‘s’字符串非空并且末尾不包含‘c’字符，则将‘c’字符附加到末尾。

SELECT

appendTrailingCharIfAbsent('good','c'), -- goodc

appendTrailingCharIfAbsent('goodccc','c'); -- goodccc

5.字符串编码转换：convertCharset(s, from, to) 返回从‘from’中的编码转换为‘to’中的编码的字符串‘s’。

SELECT

convertCharset('hello', 'UTF8','Unicode'),

convertCharset('hello', 'Unicode', 'UTF8'),

convertCharset('hello', 'Unicode', 'ASCII'),

convertCharset('hello', 'ascii', 'ascii'),--hello

convertCharset('hello', 'UTF8','UTF8');-- hello

SELECT

base64Encode('username+password'),-- dXNlcm5hbWUrcGFzc3dvcmQ=

base64Decode('dXNlcm5hbWUrcGFzc3dvcmQ='), -- username+password

-- 使用base64将字符串解码成原始字符串。但如果出现错误，将返回空字符串。

tryBase64Decode('dXNlcm5hbWUrcGFzc3dvcmQ=');

7.判断字符串是否已什么结尾或结束，返回1：true，0：flase

-- endsWith(s, suffix) 返回是否以指定的后缀结尾。如果字符串以指定的后缀结束，则返回1，否则返回0

-- startWith(s, prefix) 返回是否以指定的前缀开头。如果字符串以指定的前缀开头，则返回1，否则返回0。

SELECT

endsWith('string','g'),

startsWith('string', 'str'); -- 1 true

8.删除左侧空白字符

-- trimLeft(s) 返回一个字符串，用于删除左侧的空白字符

-- trimRight(s) 返回一个字符串，用于删除右侧的空白字符

-- trimBoth(s) 返回一个字符串，用于删除左侧和右侧的空白字符

SELECT

trimLeft(' sdfdgs'), -- sdfdgs

trimRight('abcd '), -- abcd

trimBoth(' abcd '); -- abcd

字符串搜索函数

pasition(haystack, needle), 显示needle在haystack的第一个出现的位置。

SELECT

POSITION('2121stringstrstrstrstr','str') AS positionSearch, -- 5

POSITION('你好，hello,12323-你好，你，好sdfd*dg', '你，好'),-- 31

positionUTF8('n12你好','你好') AS positionUTF8,-- 4

positionCaseInsensitive('ABCDCDEFABCD','bc') AS positionCaseInsensitive, --2

locate('hellohellohellohello','ello'); -- 2

-- multiSearchAllPositions(haystack, [needle1, needle2, ..., needlen])

-- 注意：在所有multiSearch*函数中，由于实现规范，needles的数量应小于2^8。

-- 函数返回一个数组，其中包含所有匹配needlei的位置

SELECT

multiSearchAllPositions('goodnamegoodnamegoodhellohihihi', ['dn', 'good']) as multiSearch,-- [4,1]

multiSearchAllPositionsCaseInsensitive('nameSsdfagpSSDFDFetgfderef', ['SS','fa']) as multiCaseInsensitive,

multiSearchAllPositionsUTF8('nameSsdfazz轴功率gpSSDFDFetgfderef', ['Ss','fa', 'zz轴']) AS multiSearchUTF8,

multiSearchAllPositionsCaseInsensitiveUTF8('nameSsdfazz轴功率gpSSDFDFetgfderef', ['Ss','fa', 'zz轴']) AS multiCaseInsensitiveUTF8;

-- 检查字符串是否与pattern正则表达式匹配。pattern可以是一个任意的re2正则表达式。re2正则表达式的语法比Perl正则表达式的语法存在更多限制。

-- match(haystack, pattern) 匹配到了则返回1，否则返回0

SELECT

match('1232434sadgaDDFSrefds', '[0-9a-zA-Z]'), -- 存在匹配的字符，返回1

match('1232321', '[a-z]'); -- 不存在匹配的字符，返回0

-- 与match相同，但如果所有正则表达式都不匹配，则返回0；如果任何模式匹配，则返回1。它使用hyperscan库。对于在字符串中搜索子字符串的模式，最好使用“multisearchany”，因为它更高效。

-- multiMatchAny(haystack, [pattern1, pattern2, ..., patternn])

-- 注意：任何haystack字符串的长度必须小于232字节，否则抛出异常。这种限制是因为hyperscan API而产生的。

-- 多个正则表达式对原始字符进行匹配，如若只有一个正则表达式匹配上了则返回1，否则返回0

SELECT

multiMatchAny('abcABC',['[0-9]','[a-zA-Z]']) AS multiMatchAnyOne, -- 1

multiMatchAny('123abcABC',['[0-9]','[a-zA-Z]']) AS multiMatchAnyTwo, --1

-- 与multiMatchAny相同，但返回与haystack匹配的任何内容的索引位置。

multiMatchAnyIndex('123abcABC', ['[0-9]','[a-zA-Z]']) as multiMatchAnyIndex; --2

-- 模糊匹配：like()函数，注意大写敏感。

-- % 表示任何字节数（包括零字符）

-- _ 表示任何一个字节

SELECT

'hello' LIKE '%h%' as LIKE_UP, -- 1

'hello' like 'he' AS like_low, -- 0

'hello' not like 'he' AS not_like, -- 1

'hello' like '%he%' AS like_litter, -- 1

like('adgadgadfa1232', '_12_') AS like_func,

like('sdfasdfasd', '[a-z]') AS like_func2, -- 0

notLike('1232423', '[a-zA-Z]') AS not_like_func; -- 1

-- 使用字符串截取字符串：extract(haystack, pattern)

-- 使用正则表达式截取字符串。如果‘haystack’与‘pattern’不匹配，则返回空字符串。如果正则表达式中不包含子模式，它将获取与整个正则表达式匹配的子串。否则，它将获取与第一个子模式匹配的子串。

SELECT

extractAll('hellogoodaimantIdeaIDEAfasd123232', '[0-9]'), -- ['1','2','3','2','3','2']

extractAll('12323dSDFRE', '[A-Z]'),-- ['S','D','F','R','E']

extract('helloclickhouse', '[a-z]');-- h

-- ngramSearch(haystack, needle)

-- 基于4-gram计算haystack和needle之间的距离：计算两个4-gram集合之间的对称差异，并用它们的基数和对其进行归一化。

-- 返回0到1之间的任何浮点数 -- 越接近0则表示越多的字符串彼此相似。

-- 如果常量的needle或haystack超过32KB，函数将抛出异常。如果非常量的haystack或needle字符串超过32Kb，则距离始终为1。

SELECT

ngramDistance('hello123456789','123') AS ngramDistance,

ngramDistanceCaseInsensitive('hello123456789','123') AS ngramDistanceCaseInsensitive,

ngramDistanceUTF8('hello123456789','123') AS ngramDistanceUTF8,

ngramDistanceCaseInsensitiveUTF8('hello123456789','123') AS ngramDistanceCaseInsensitiveUTF8;

-- 注意：对于UTF-8，我们使用3-gram。所有这些都不是完全公平的n-gram距离。

-- 我们使用2字节哈希来散列n-gram，然后计算这些哈希表之间的（非）对称差异 - 可能会发生冲突。

-- 对于UTF-8不区分大小写的格式，我们不使用公平的tolower函数

-- 我们将每个Unicode字符字节的第5位（从零开始）和字节的第一位归零

-- 这适用于拉丁语，主要用于所有西里尔字母。

字符串替换函数

替换匹配到的字符串

replaceOne(haystack, pattern, replacement)

用‘replacement’子串替换‘haystack’中与‘pattern’子串第一个匹配的匹配项（如果存在）。‘pattern’和‘replacement’必须是常量。

replaceAll(haystack, pattern, replacement), replace(haystack, pattern, replacement)

用‘replacement’子串替换‘haystack’中出现的所有‘pattern’子串。

SELECT

replaceOne('hed1234544', '4', '*') AS replaceOne,-- hed123*544

replaceRegexpOne('hed1234544', '4', '*') AS replaceRegexpOne,-- hed123*544

replace('hed1234544', '4', '*') AS replace, -- hed123*5**

replaceAll('hed1234544', '4', '*') AS replaceAll;-- hed123*5**

-- 实例：2019-07-31 改变成 07/31/2019

SELECT

toDate(now()) AS now_date,

replaceRegexpOne(toString(now_date), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS format_date;

-- 示例：赋值字符串10次

SELECT replaceRegexpOne('Hello, World!', '.*', '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0') AS res;

-- replaceRegexpAll(haystack, pattern, replacement)

-- 与replaceRegexpOne相同，但会替换所有出现的匹配项。例如：

SELECT replaceRegexpAll('hello,world!', '.', '\\0\\0') as res; -- hheelllloo,,wwoorrlldd!!

SELECT replaceRegexpAll('hello o o, world.', ' ', '*') as res; -- hello*o*o,*world.

函数：regexpQuoteMeta(s) 该函数用于在字符串中的某些预定义字符之前添加反斜杠。

预定义字符：'0'，'\'，'|'，'('，')'，'^'，'$'，'。'，'['，']'，'？'，'* '，'+'，'{'，'：'，' - '。

这个实现与re2 :: RE2 :: QuoteMeta略有不同。它以\0而不是\x00转义零字节，它只转义所需的字符

简言之，就是不处理转义字符，一般如果没有用的这个函数，都会有转义的情况出现。

SELECT regexpQuoteMeta('\\\\|[]{}+_-=@!~`&^*%$#'); -- \\\\\|

\{}\+_\-=@!~`&\^\*%\$#

SELECT toString('\\\\'); -- \\

数学函数

SELECT

1 * e() AS E,

1 * pi() AS PI,

sqrt(25) AS sqrt_25, --接受一个数值类型的参数并返回它的平方根。

cbrt(27) AS cbrt_27, --接受一个数值类型的参数并返回它的立方根。

exp(10), --接受一个数值类型的参数并返回它的指数

exp10(10), --接受一个数值类型的参数并返回它的10的x次幂。

log(10) AS LOG,

log2(10) AS LOG2, --接受一个数值类型的参数并返回它的底2对数。

ln(e()) AS LOG10; --接受一个数值类型的参数并返回它的自然对数

-- 示例：三西格玛准则

SELECT erf(3 / sqrt(2)); -- 0.997

SELECT

sin(90), -- 返回x的三角正弦值。

cos(90), -- 返回x的三角余弦值。

tan(90), -- 返回x的三角正切值

acos(0), -- 返回x的反三角余弦值。

asin(1), -- 返回x的反三角正弦值。

atan(45); -- 返回x的反三角正切值。

-- pow(x, y), power(x, y) 接受x和y两个参数。返回x的y次方。

SELECT

pow(2, 3), -- 2的三次方

pow(3, 2); -- 3的平方

SELECT

intExp2(4), --2^4 接受一个数值类型的参数并返回它的2的x次幂（UInt64）。

intExp10(2);--10^2 接受一个数值类型的参数并返回它的10的x次幂（UInt64）。

取整函数

1.向下取整：floor(x[,N])

SELECT

floor(toFloat32(12.08098), 2), -- 12.08

floor(toFloat32(12.2323), 2), -- 12.23

floor(toFloat32(12.89788), -1), -- 10

floor(toFloat32(12.09590), 3), -- 12.095 (注意：如果按照正常的四舍五入，则应该是12.096，为什么呢？)

floor(toFloat32(12.0987), 3),-- 12.098

floor(10, 2); -- 10

2.四舍五入：round(expression [, decimal_places])

如果decimal_places=0,则取整数；

如果>0,则将值舍入小数点右侧；

如果<0,则将小数点左侧的值四舍五入。

SELECT

round(toFloat32(12.1234), 3),

round(toFloat32(12.0025), 3), -- 12.002(注意：为什么不是12.003呢？)

-- round函数只会最多保留三位有效数字

round(toFloat32(12.0025), 4), -- 12.002

round(toFloat32(12.0025002323), 100); -- 12.003

-- 示例：

SELECT

round(toFloat32(10 / 3)), -- 3

round(toFloat32(10 / 3), 2), -- 3.33

round(toFloat32(10.000/3), 3), -- 3.333

round(toFloat32(10.000/3), 6); -- 3.333

-- roundToExp2() 接受一个数字。如果数字小于1，则返回0。否则，它将数字向下舍入到最接近的（整个非负）2的x次幂。

SELECT

roundToExp2(12.0129), -- 8 = 2^3

roundToExp2(toFloat32(0.01)); -- 0.008

3.向上取整：ceil(x[, N]) 或者 ceiling(x[, N])

SELECT

ceil(12.34343, 3), -- 12.344

ceil(toFloat64(12.34343), 3), -- 12.344

ceil(toFloat32(12.34343), 3), -- 12.344

ceil(12.0011, 3); -- 12.002

条件函数

if(cond, then, else)函数：类似于三元操作符。

中文字符使用双引号，英文字符可不使用引号也可使用当引号或双引号，根据具体情况而定。

如果cond ！= 0则返回then，如果cond = 0则返回else。cond必须是UInt8类型，then和else必须存在最低的共同类型。

注意：then和else可以是NULL

SELECT

12 > 10 ? 'desc' : 'asc' AS "三元操作符",

if(12 > 10, 'desc' , 'asc') AS "if()函数",

if(12 > 10, NULL, NULL);

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

允许您在查询中更紧凑地编写CASE运算符。类似于java中的switch语法（可以接受2n+1个参数）

SELECT multiIf(1,'one',2,'two',3,'three','not this index');-- 关联case条件表达式