clickhouse 三(clickhouse函数使用总结)
clickhouse函数
- 1、日期类函数
- 1.1 时间日期函数
- 1.2 生成日期、时间戳
- 2、类型转化函数
- 3.字符串处理
- 4.取整函数(四舍五入)
- 5.NULL值处理函数
- 6.取记录最新一条或最早一条
- 7.数组函数
- 8 位图函数
- 8.1 位图的构建与转化
- 8.2 位图的计算操作
- 9 hash函数
- 10 随机函数
- 11 类型转换函数
- 11.1 cast 将’x’转换为’t’数据类型。还支持语法cast(x AS t)
- 11 其他函数
1、日期类函数
1.1 时间日期函数
toHour() → 获取时间日期的小时
toMinute() → 获取时间日期的分钟
toSecond() → 获取时间日期的秒
toTime() → 将时间日期的日期固定到某一天,保留原始时间
SELECT
toHour(now()),
toMinute(now()),
toSecond(now()),
toTime(now())
┌─toHour(now())─┬─toMinute(now())─┬─toSecond(now())─┬───────toTime(now())─┐
│ 11 │ 37 │ 14 │ 1970-01-02 11:37:14 │
└───────────────┴─────────────────┴─────────────────┴─────────────────────┘
toDayOfMonth → 获取日期或时间日期的天(1-31)
toDayOfWeek → 获取日期或时间日期的星期数值(1-7)
toYear → 获取日期或时间日期的年份
toMonth → 取日期或时间日期的月份
SELECT
toDayOfMonth(now()),
toDayOfWeek(now()),
toYear(now()),
toMonth(now()),
now()
┌─toDayOfMonth(now())─┬─toDayOfWeek(now())─┬─toYear(now())─┬─toMonth(now())─┬───────────────now()─┐
│ 26 │ 4 │ 2020 │ 11 │ 2020-11-26 11:33:49 │
└─────────────────────┴────────────────────┴───────────────┴────────────────┴─────────────────────┘
toDate() → 将字符日期或时间戳转化为日期
toDateTime() → 将字符时间戳转化为时间戳
SELECT
toDate('2020-11-25 19:58:10'),
toDateTime('2020-11-25 19:58:10')
┌─toDate('2020-11-25 19:58:10')─┬─toDateTime('2020-11-25 19:58:10')─┐
│ 2020-11-25 │ 2020-11-25 19:58:10 │
└───────────────────────────────┴───────────────────────────────────┘
1.2 生成日期、时间戳
now() → 生成当前时间戳
today() → 生成当前日期
yesterday() → 生成当前日期的前一天
SELECT
now(),today(),yesterday()
┌───────────────now()─┬────today()─┬─yesterday()─┐
│ 2020-11-25 19:58:10 │ 2020-11-25 │ 2020-11-24 │
└─────────────────────┴────────────┴─────────────┘
2、类型转化函数
toDecimal32() → 将数值型或者含有非数字的字符串进行精度保留
toInt8OrZero() → 将整数字符型转化为整数型,否则返回0
toFloat32OrZero() → 将数值字符串型转化为数值型
SELECT
toDecimal32(83.5222191, 6),
toDecimal32('-23.12291', 3),
toInt8OrZero('23'),
toInt8OrZero('-123'),
toFloat32OrZero('-123'),
toFloat32OrZero('123.123')
┌─toDecimal32(83.5222191, 6)─┬─toDecimal32('-23.12291', 3)─┬─toInt8OrZero('23')─┬─toInt8OrZero('-123')─┬─toFloat32OrZero('-123')─┬─toFloat32OrZero('123.123')─┐
│ 83.522219 │ -23.122 │ 23 │ -123 │ -123 │ 123.123 │
└────────────────────────────┴─────────────────────────────┴────────────────────┴──────────────────────┴─────────────────────────┴────────────────────────────┘
3.字符串处理
upper() → 大写转换
lower() → 小写转换
concat() → 字符串拼接
substring() → 字符串截取
splitByString() → 字符串拆分
SELECT
upper('abc'),
lower('ABC'),
concat('ab', 'cd'),
substring('abcde', 1, 2),
splitByString(',', 'a,b,c')
┌─upper('abc')─┬─lower('ABC')─┬─concat('ab', 'cd')─┬─substring('abcde', 1, 2)─┬─splitByString(',', 'a,b,c')─┐
│ ABC │ abc │ abcd │ ab │ ['a','b','c'] │
└──────────────┴──────────────┴────────────────────┴──────────────────────────┴─────────────────────────────┘
4.取整函数(四舍五入)
floor([x, N]) → 向下取数
ceil([x, N]) → 向上取数
round([x, N]) → 四舍五入
SELECT
floor(123.883, 1),floor(123.883, -1),
ceil(123.883, 1),ceil(123.883, -1),
round(123.883, 1),round(123.883, -1)
┌─floor(123.883, 1)─┬─floor(123.883, -1)─┬─ceil(123.883, 1)─┬─ceil(123.883, -1)─┬─round(123.883, 1)─┬─round(123.883, -1)─┐
│ 123.8 │ 120 │ 123.9 │ 130 │ 123.9 │ 120 │
└───────────────────┴────────────────────┴──────────────────┴───────────────────┴───────────────────┴────────────────────┘
5.NULL值处理函数
isNull() → 检查参数是否为NULL ,为NULL返回1,否则0
isNotNull() → 检查参数是否不为 NULL,为NULL返回0,否则1
ifNull() → 如果第一个参数为NULL,则返回第二个参数的值。
NullIf() → 如果参数相等,返回NULL,参数不相等,返回第一个值
SELECT
isNull('1'),isNull(NULL),
isNotNull('1'),isNotNull(NULL),
ifNull(NULL, 2),ifNull(1, 2),
NullIf(1, 1),NullIf(1, 0)
┌─isNull('1')─┬─isNull(NULL)─┬─isNotNull('1')─┬─isNotNull(NULL)─┬─ifNull(NULL, 2)─┬─ifNull(1, 2)─┬─NullIf(1, 1)─┬─NullIf(1, 0)─┐
│ 0 │ 1 │ 1 │ 0 │ 2 │ 1 │ ᴺᵁᴸᴸ │ 1 │
└─────────────┴──────────────┴────────────────┴─────────────────┴─────────────────┴──────────────┴──────────────┴──────────────┘
6.取记录最新一条或最早一条
argMin(arg,val) → 计算最小值的arg值。如果val的最小值有几个不同的arg值,则遇到的第一个值是输出。
argMax(arg,val) → 计算最大值的arg值。如果存在多个不同的arg值来表示val的最大值,则遇到的第一个值是输出。
# 测试数据,求最低薪水的员工
┌─user─────┬─salary─┐
│ director │ 5000 │
│ manager │ 3000 │
│ worker │ 1000 │
└──────────┴────────┘
# 计算最低薪水的员工
SELECT argMin(user, salary) FROM salary
┌─argMin(user, salary)─┐
│ worker │
└──────────────────────┘
7.数组函数
1.arrayJoin
将数组行变列
SELECT arrayJoin([2, 5, 7, 8, 9]) AS arrayJoin
┌─arrayJoin─┐
│ 2 │
│ 5 │
│ 7 │
│ 8 │
│ 9 │
└───────────┘
2.arraySort(升序),arrayReverseSort(降序)
对数组进行排序
SELECT arrayJoin(arrayReverseSort([2, 5, 7, 8, 9])) AS arrayReverseSort
┌─arrayReverseSort─┐
│ 9 │
│ 8 │
│ 7 │
│ 5 │
│ 2 │
└──────────────────┘
3.arrayFilter
过滤出数组中满足条件的数据
过滤获取大于6的数值
SELECT arrayJoin(arrayFilter(x -> (x > 6), [2, 5, 7, 8, 9])) AS arrayFilter
┌─arrayFilter─┐
│ 7 │
│ 8 │
│ 9 │
└─────────────┘
4.arrayEnumerate
返回数据的下标
SELECT arrayEnumerate([2, 5, 7, 8, 9]) AS arrayEnumerate
┌─arrayEnumerate─┐
│ [1,2,3,4,5] │
└────────────────┘
5.arrayReduce
对数组进行聚合操作,min 、max、avg 、sum、count
SELECT arrayReduce(‘sum’, [2, 5, 7, 8, 9]) AS arrayReduce
┌─arrayReduce─┐
│ 31 │
└─────────────┘
6.hasAny(包含某个值),hasAll(包含全部值)
判断数组中是否包含某些值,包含返回1,否则返回0
SELECT
hasAny([2, 5, 7, 8, 9], [6, 9]) AS hasAny,
hasAll([2, 5, 7, 8, 9], [6, 9]) AS hasAll
┌─hasAny─┬─hasAll─┐
│ 1 │ 0 │
└────────┴────────┘
7.arrayDistinct
对数组进行去重
SELECT arrayDistinct([2, 2, 4, 4, 5, 7, 8, 9]) AS arrayDistinct
┌─arrayDistinct─┐
│ [2,4,5,7,8,9] │
└───────────────┘
8 位图函数
位图函数用于对两个位图对象进行计算,对于任何一个位图函数,它都将返回一个位图对象,例如and,or,xor,not等。位图对象有两种构造方法。一个是由聚合函数groupBitmapState构造的,另一个是由Array Object构造的。同时还可以将位图对象转化为数组对象。我们使用RoaringBitmap实际存储位图对象,当基数小于或等于32时,它使用Set保存。当基数大于32时,它使用RoaringBitmap保存。这也是为什么低基数集的存储更快的原因。
8.1 位图的构建与转化
- bitmapBuild → 将无符号整数数组构建位图对象。
- bitmapToArray → 将位图转换为整数数组。
bitmapSubsetInRange → 将位图指定范围(不包含range_end)转换为另一个位图。
语法格式:bitmap – 位图对象,range_start – 范围起始点(含),range_end – 范围结束点(不含)
bitmapSubsetInRange(bitmap, range_start, range_end)
bitmapSubsetLimit → 将位图指定范围(起始点和数目上限)转换为另一个位图。
语法格式:bitmap – 位图对象,range_start – 范围起始点(含),limit – 子位图基数上限
bitmapSubsetLimit(bitmap, range_start, limit)
测试:
WITH [toUInt32(1), toUInt32(2), toUInt32(5), toUInt32(7), toUInt32(35)] AS arr
SELECT
bitmapBuild(arr) AS bitmapBuild,
bitmapToArray(bitmapBuild(arr)) AS bitmapToArray,
bitmapToArray(bitmapSubsetInRange(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetInRange,
bitmapToArray(bitmapSubsetLimit(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetLimit
┌─bitmapBuild─┬─bitmapToArray─┬─bitmapSubsetInRange─┬─bitmapSubsetLimit─┐
│ # │ [1,2,5,7,35] │ [2,5] │ [2,5,7,35] │
└─────────────┴───────────────┴─────────────────────┴───────────────────┘
- arrayJoin → 将数组转化为明细数据,行转列
测试:
SELECT arrayJoin(bitmapToArray(bitmapBuild([1, 2, 3, 4, 5]))) AS uid
┌─uid─┐
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
└─────┘
groupBitmapState → 聚合函数,可将id列值压缩bitmap
测试:SELECT bitmapToArray(groupBitmapState(arrayJoin([1, 2, 3, 4, 5]))) AS groupBitmapState
┌─groupBitmapState─┐
│ [1,2,3,4,5] │
└──────────────────┘
8.2 位图的计算操作
- bitmapHasAny(bitmap1,bitmap2) → 如果位图有任何公共元素则返回1,否则返回0。对于空位图,返回0。
- bitmapHasAll(bitmap1,bitmap2) → 如果第一个位图包含第二个位图的所有元素,则返回1,否则返回0。如果第二个参数是空位图,则返回1。
测试:
# 语法格式
WITH
bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
SELECT
bitmapHasAny(bitmap1, bitmap2) AS bitmapHasAny,
bitmapHasAll(bitmap1, bitmap2) AS bitmapHasAll
┌─bitmapHasAny─┬─bitmapHasAll─┐
│ 1 │ 0 │
└──────────────┴──────────────┘
- bitmapContains(bitmap, needle) → 检查位图是否包含指定元素。
- bitmapAnd(bitmap1,bitmap2) → 两个位图对象进行与操作(相当于取交集),返回一个新的位图对象。
- bitmapOr(bitmap1,bitmap2) → 为两个位图对象进行或操作,返回一个新的位图对象,如果参数是多个的情况下,可以尝试使用groupBitmapMergeState。
- bitmapXor(bitmap1,bitmap2) → 为两个位图对象进行异或操作,返回一个新的位图对象。
- bitmapAndnot(bitmap1,bitmap2) → 计算两个位图的差异,返回一个新的位图对象。
测试:
WITH
bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
SELECT
bitmapContains(bitmap1, toUInt32(5)) AS bitmapContains,
bitmapToArray(bitmapAnd(bitmap1, bitmap2)) AS bitmapAnd,
bitmapToArray(bitmapOr(bitmap1, bitmap2)) AS bitmapOr,
bitmapToArray(bitmapXor(bitmap1, bitmap2)) AS bitmapXor,
bitmapToArray(bitmapAndnot(bitmap1, bitmap2)) AS bitmapAndnot
┌─bitmapContains─┬─bitmapAnd─┬─bitmapOr────────────┬─bitmapXor───────┬─bitmapAndnot─┐
│ 1 │ [4,5] │ [1,2,3,4,5,9,17,35] │ [1,2,3,9,17,35] │ [1,2,3] │
└────────────────┴───────────┴─────────────────────┴─────────────────┴──────────────┘
- bitmapCardinality(bitmap) → 返回一个UInt64类型的数值,表示位图的大小。
- bitmapMin(bitmap) → 返回一个UInt64类型的数值,表示位图中的最小值。如果位图为空则返回UINT32_MAX。
- bitmapMax(bitmap) → 返回一个UInt64类型的数值,表示位图中的最大值。如果位图为空则返回0。
测试:
WITH bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap
SELECT
bitmapCardinality(bitmap) AS bitmapCardinality,
bitmapMin(bitmap) AS bitmapMin,
bitmapMax(bitmap) AS bitmapMax
┌─bitmapCardinality─┬─bitmapMin─┬─bitmapMax─┐
│ 5 │ 4 │ 35 │
└───────────────────┴───────────┴───────────┘
- bitmapOrCardinality(bitmap1,bitmap2) → 为两个位图进行或运算,返回结果位图的基数。
- bitmapXorCardinality(bitmap1,bitmap2) → 为两个位图进行异或运算,返回结果位图的基数。
- bitmapAndnotCardinality(bitmap1,bitmap2) → 计算两个位图的差异,返回结果位图的基数。
测试:
WITH
bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
SELECT
bitmapOrCardinality(bitmap1, bitmap2) AS bitmapOrCardinality,
bitmapXorCardinality(bitmap1, bitmap2) AS bitmapXorCardinality,
bitmapAndnotCardinality(bitmap1, bitmap2) AS bitmapAndnotCardinality
┌─bitmapOrCardinality─┬─bitmapXorCardinality─┬─bitmapAndnotCardinality─┐
│ 8 │ 6 │ 3 │
└─────────────────────┴──────────────────────┴─────────────────────────┘
9 hash函数
可以用于将元素不可逆的伪随机打乱。
1 计算字符串的md5值,这两个函数比较低效,如果可以请使用sipHash64函数。
SELECT
halfMD5('123'),
lower(hex(MD5('123')))
┌──────halfMD5('123')─┬─lower(hex(MD5('123')))───────────┐
│ 2318431741638412123 │ 202cb962ac59075b964b07152d234b70 │
└─────────────────────┴──────────────────────────────────┘
2 计算字符串的SipHash
SELECT
sipHash64('123'),
lower(hex(sipHash128('123')))
┌─────sipHash64('123')─┬─lower(hex(sipHash128('123')))────┐
│ 10015141997369857857 │ b1f8870810764950f06fd4808598b5da │
└──────────────────────┴──────────────────────────────────┘
3 计算整型的hash码
SELECT
intHash32(123),
intHash64(123)
┌─intHash32(123)─┬───────intHash64(123)─┐
│ 1465837921 │ 12585326385145454095 │
└────────────────┴──────────────────────┘
4 计算字符串的JavaHash,返回Int32
SELECT javaHash('123')
┌─javaHash('123')─┐
│ 48690 │
└─────────────────┘
10 随机函数
所有随机函数都只接受一个参数或不接受任何参数。可以向它传递任何类型的参数,但传递的参数将不会使用在任何随机数生成过程中。此参数的唯一目的是防止公共子表达式消除,以便在相同的查询中使用相同的随机函数生成不同的随机数。
- rand, rand32 → 返回一个UInt32类型的随机数字,所有UInt32类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。
- rand64 → 返回一个UInt64类型的随机数字,所有UInt64类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。
randConstant → 返回一个UInt32类型的随机数字,该函数不同之处在于仅为每个数据块参数一个随机数。
SELECT
rand(),
rand(123),
rand32(),
rand32(123),
rand64(),
randConstant()
┌─────rand()─┬──rand(123)─┬───rand32()─┬─rand32(123)─┬─────────────rand64()─┬─randConstant()─┐
│ 3117928134 │ 2712052414 │ 3534229488 │ 3279101485 │ 11232216995783696794 │ 2151891090 │
└────────────┴────────────┴────────────┴─────────────┴──────────────────────┴────────────────┘
11 类型转换函数
11.1 cast 将’x’转换为’t’数据类型。还支持语法cast(x AS t)
SELECT
'2020-11-15 23:00:00' AS timestamp,
CAST(timestamp, 'DateTime') AS datetime,
CAST(timestamp, 'Date') AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string
┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string────────┐
│ 2020-11-15 23:00:00 │ 2020-11-15 23:00:00 │ 2020-11-15 │ 2020-11-15 23:00:00 │ 2020-11-15 23:00:00 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘
11 其他函数
rowNumberInAllBlocks() → 返回行所在结果集中的序列号。此函数仅考虑受影响的Block。
SELECT
rowNumberInAllBlocks() AS rowNumberInAllBlocks,
partition
FROM system.parts
LIMIT 5┌─rowNumberInAllBlocks─┬─partition──┐
│ 0 │ 2020-07-22 │
│ 1 │ 2020-07-23 │
│ 2 │ 2020-07-27 │
│ 3 │ 2020-07-28 │
│ 4 │ 2020-05-01 │
└──────────────────────┴────────────┘
未完待续!
参考文章:
ClickHouse官方文档
还没有评论,来说两句吧...