ClickHouse常用函数

转载自：https://blog.csdn.net/qq_16504067/article/details/98069536

附ClickHouse的学习网址：http://www.hellow3.com/clickhouse/zh/

-- 计算数字的总和。 您还可以将Date或DateTime与整数进行相加。在Date的情况下，添加的整数意味着添加相应的天数。对于DateTime，这意味这添加相应的秒数。

SELECT plus(1, 1);

SELECT plus(toDate('2019-7-31'), 1);

 

-- 计算数字之间的差，结果总是有符号的。您还可以将Date或DateTime与整数进行相减。见上面的'plus'。

SELECT minus(1, 1);

SELECT minus(toDate('2019-7-31'), 1);

 

-- 计算数字的乘积。

SELECT multiply(1.23654111111110215, 1.21589654421111111111);

 

 

-- 计算数字的商。结果类型始终是浮点类型。 它不是整数除法。对于整数除法，请使用'intDiv'函数。 当除以零时，你得到'inf'，'- inf'或'nan'。

SELECT divide(1.23 , 1.11);

-- 计算整数数字的商，向下舍入（按绝对值）。 除以零或将最小负数除以-1时抛出异常。

SELECT intDiv(1.23 , 1.11);

 

-- 'intDivOrZero'与'intDiv'的不同之处在于它在除以零或将最小负数除以-1时返回零。

SELECT intDivOrZero(1.23 , 0);

 

-- 计算除法后的余数。 如果参数是浮点数，则通过删除小数部分将它们预转换为整数。 其余部分与C++中的含义相同。截断除法用于负数。 除以零或将最小负数除以-1时抛出异常。

SELECT modulo(5.32 , 2);

 

-- 计算一个数字的 用反转符号计算一个数字。

SELECT negate(2);

 

 

-- 比较函数

SELECT (1<2);

 

-- 逻辑函数

SELECT not(1<2);

 

--类型转换函数

SELECT toFloat32(1<2);

SELECT toFloat64(1<2);

SELECT toUInt8OrZero('255');

SELECT toUInt8OrNull('a');

SELECT toDateOrZero('2019-1-31');

-- DateTime参数的toString函数可以在第二个参数中包含时区名称。 例如：Asia/Yekaterinburg在这种情况下，时间根据指定的时区进行格式化。

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

-- 将value转换为精度为S的Decimal。value可以是数字或字符串。S参数为指定的小数位数。

SELECT toDecimal128(123.2158745233611105, 32)

 

--将String类型的参数转换为FixedString(N)类型的值（具有固定长度N的字符串）。N必须是一个常量。 如果字符串的字节数少于N，则向右填充空字节。如果字符串的字节数多于N，则抛出异常。

SELECT toFixedString('123.21', 8)

 

-- 将‘x’转换为‘t’数据类型。还支持语法CAST（x AS t）

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

 

 

 

--将Date或DateTime转换为包含年份编号（AD）的UInt16类型的数字。

SELECT toYear(now())

-- 将Date或DateTime转换为包含季度编号的UInt8类型的数字。

SELECT toQuarter(now())

-- 将Date或DateTime转换为包含月份编号（1-12）的UInt8类型的数字。

SELECT toMonth(now())

-- 将Date或DateTime转换为包含一年中的某一天的编号的UInt16（1-366）类型的数字。

SELECT toDayOfYear(now())

-- 将Date或DateTime转换为包含一月中的某一天的编号的UInt8（1-31）类型的数字。

SELECT toDayOfMonth(now())

-- 将Date或DateTime转换为包含一周中的某一天的编号的UInt8（周一是1, 周日是7）类型的数字。

SELECT toDayOfWeek(now())

-- 将DateTime转换为包含24小时制（0-23）小时数的UInt8数字。

--这个函数假设如果时钟向前移动，它是一个小时，发生在凌晨2点，如果时钟被移回，它是一个小时，发生在凌晨3点（这并非总是如此 - 即使在莫斯科时钟在不同的时间两次改变）

SELECT toHour(toDateTime('2019-07-01 00:00:00'))

--将DateTime转换为包含一小时中分钟数（0-59）的UInt8数字。

SELECT toMinute(toDateTime('2019-07-01 00:00:00'))

--将DateTime转换为包含一分钟中秒数（0-59）的UInt8数字。 闰秒不计算在内。

SELECT toSecond(toDateTime('2019-07-01 00:00:59'))

 

--将DateTime转换为unix时间戳(秒)。

SELECT toUnixTimestamp(toDateTime('2019-07-01 00:00:59'))

 

--将Date或DateTime向前取整到本年的第一天。 返回Date类型

SELECT toStartOfYear(toDateTime('2019-07-01 00:00:59'))

 

--将Date或DateTime向前取整到ISO本年的第一天。 返回Date类型。

SELECT toStartOfISOYear(toDateTime('2019-07-01 00:00:59'))

 

--将Date或DateTime向前取整到本季度的第一天。 返回Date类型。

SELECT toStartOfQuarter(toDateTime('2019-07-01 00:00:59'))

 

--将Date或DateTime向前取整到本月的第一天。 返回Date类型。

SELECT toStartOfMonth(toDateTime('2019-07-01 00:00:59'))

 

--将Date或DateTime向前取整到本周的星期一。 返回Date类型。

SELECT toMonday(toDateTime('2019-07-31 00:00:59'))

 

-- 将DateTime向前取整到当日的开始

SELECT toStartOfDay(toDateTime('2019-07-31 21:00:59'))

 

-- 将DateTime向前取整到当前小时的开始。

SELECT toStartOfHour(toDateTime('2019-07-31 21:00:59'))

 

-- 将DateTime向前取整到当前分钟的开始。

SELECT toStartOfMinute(toDateTime('2019-07-31 21:10:59'))

 

-- 将DateTime以十五分钟为单位向前取整到最接近的时间点。

SELECT toStartOfFifteenMinutes(toDateTime('2019-07-31 21:20:59'))

 

--toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])¶

--这是名为toStartOf*的所有函数的通用函数。

--例如， toStartOfInterval（t，INTERVAL 1 year）返回与toStartOfYear（t）相同的结果，

-- toStartOfInterval（t，INTERVAL 1 month）返回与toStartOfMonth（t）相同的结果，

--toStartOfInterval（t，INTERVAL 1 day）返回与toStartOfDay（t）相同的结果，

--toStartOfInterval（t，INTERVAL 15 minute）返回与toStartOfFifteenMinutes（t）相同的结果。

SELECT toStartOfInterval(toDateTime('2019-07-31 21:20:59'),INTERVAL 1 MINUTE)

 

 

-- 不接受任何参数并在请求执行时的某一刻返回当前时间（DateTime）。 此函数返回一个常量，即时请求需要很长时间能够完成。

SELECT now()

-- 不接受任何参数并在请求执行时的某一刻返回当前日期(Date)。 其功能与'toDate（now（））'相同

SELECT today()

--不接受任何参数并在请求执行时的某一刻返回昨天的日期(Date)。 其功能与'today（） - 1'相同。

SELECT yesterday()

 

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

SELECT toYYYYMM(now())

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

SELECT toYYYYMMDD(now())

--将Date或DateTime转换为包含年份和月份编号的UInt64类型的数字（YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss）

SELECT toYYYYMMDDhhmmss(now())

 

 

-- addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters¶

-- 函数将一段时间间隔添加到Date/DateTime，然后返回Date/DateTime。例如：

WITH

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

toDateTime('2018-01-01 00:00:00') AS date_time

SELECT

addYears(date, 1) AS add_years_with_date,

addMonths(date_time, 1) AS add_years_with_date_time

 

 

-- subtractYears, subtractMonths, subtractWeeks, subtractDays, subtractHours, subtractMinutes, subtractSeconds, subtractQuarters¶

-- 函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime

WITH

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

toDateTime('2019-01-01 00:00:00') AS date_time

SELECT

subtractYears(date, 1) AS subtract_years_with_date,

subtractDays(date_time, 1) AS subtract_years_with_date_time

 

--dateDiff('unit', t1, t2, [timezone])

-- 返回以'unit'为单位表示的两个时间之间的差异，例如'hours' : t2 - t1

--'t1'和't2'可以是Date或DateTime，如果指定'timezone'，它将应用于两个参数。如果不是，则使用来自数据类型't1'和't2'的时区。如果时区不相同，则结果将是未定义的。

SELECT dateDiff('year', toDate('2019-11-01'), toDate('2021-02-01'))

 

--对于空字符串返回1，对于非空字符串返回0。 结果类型是UInt8。 如果字符串包含至少一个字节，则该字符串被视为非空字符串，即使这是一个空格或空字符。 该函数也适用于数组。

SELECT empty('1')

--对于空字符串返回0，对于非空字符串返回1。 结果类型是UInt8。 该函数也适用于数组。

SELECT notEmpty('1')

--返回字符串的字节长度。 结果类型是UInt64。 该函数也适用于数组。

SELECT length('afdffg')

 

 

--将字符串中的ASCII转换为大写

SELECT upper('afdffg杜海')

--将字符串中的ASCII转换为小写

SELECT lower('AFDFFG杜海')

 

-- 反转字符串。

SELECT reverse('AFDFFG')

 

-- 将参数中的多个字符串拼接，不带分隔符。

SELECT concat('A','B')

 

-- substring(s, offset, length), mid(s, offset, length), substr(s, offset, length)

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

-- 中文好像占三位

SELECT substring('你好啊，时间', 1, 9)

 

--与‘substring’相同，但其操作单位为Unicode字符，函数假设字符串是以UTF-8进行编码的文本。如果不是则可能返回一个预期外的结果（不会抛出异常）。

SELECT substringUTF8('你好啊，时间', 1, 2)

 

 

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

SELECT appendTrailingCharIfAbsent('get me ab', 'd')

 

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

SELECT endsWith('getmeab', 'd')

 

-- 返回是否以指定的前缀开头。如果字符串以指定的前缀开头，则返回1，否则返回0

SELECT startsWith('getmeab', 'd')

 

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

SELECT trimLeft(' getmeab ')

 

-- 返回一个字符串，用于删除右侧的空白字符。

SELECT trimRight(' getmeab ')

 

-- 返回一个字符串，用于删除任一侧的空白字符。

SELECT trimBoth(' getm eab ')

 

-- 对于不区分大小写的搜索，请使用函数positionCaseInsensitive。

-- 在字符串haystack中搜索子串needle。 返回子串的位置（以字节为单位），从1开始，如果未找到子串，则返回0。

SELECT position(' getm 世界,你好 eab ','你好')

 

-- 对于不区分大小写的搜索，请使用函数positionCaseInsensitiveUTF8

-- 与position相同，但位置以Unicode字符返回。此函数工作在UTF-8编码的文本字符集中。如非此编码的字符集，则返回一些非预期结果（他不会抛出异常）

SELECT positionUTF8(' getm 世界,你好 eab ','你好')

 

 

------------------------------------字符串替换函数

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

SELECT replaceOne('世界,你好','你好','hello')

 

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

SELECT replaceAll('世界,你好! click hourse, 你好','你好','hello')

 

 

 

 

------------------------------------条件函数

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

SELECT if(1>2,'y','n')

 

--multiIf(cond_1, then_1, cond_2, then_2...else)multiIf允许您在查询中更紧凑地编写CASE运算符。

SELECT multiIf(1>2,'y',2>3,'k','n')

 

 

---------------------------------------取整函数

 

-- 返回小于或等于x的最大舍入数。该函数使用参数乘1/10N，如果1/10N不精确，则选择最接近的精确的适当数据类型的数。

-- ‘N’是一个整数常量，可选参数。默认为0，这意味着不对其进行舍入。 ‘N’可以是负数。

SELECT floor(123.45, 1), floor(123.45, -1)

 

-- 返回大于或等于'x'的最小舍入数。在其他方面，它与'floor'功能相同（见上文）。

SELECT ceil(123.45, 1), ceil(123.45, -1)

 

 

 

-- 返回一个UInt32类型的随机数字，所有UInt32类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。

SELECT rand()

 

-- 返回一个UInt64类型的随机数字，所有UInt64类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。

SELECT rand64()

 

 

-- 生成一个UUID

SELECT generateUUIDv4()

 

 

-- 如果第一个参数为“NULL”，则返回第二个参数的值。

SELECT ifNull(null,'a')

 

 

-- 返回a和b中的最小值。

SELECT least(8, 11)

 

-- 返回a和b的最大值。

SELECT greatest(8, 11)

 

 

SELECT CASE

WHEN `Hour` > 8 THEN '大于8'

WHEN `Hour` > 5 THEN '大于5,小于8'

ELSE '小于5--'

END

FROM visits

 

-- 连接运算符

SELECT concat('s1', 's2')