clickhouse 三(clickhouse函数使用总结)

亦凉 2021-05-10 11:13 3065阅读 0赞

在这里插入图片描述

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() → 将时间日期的日期固定到某一天,保留原始时间

  1. SELECT
  2. toHour(now()),
  3. toMinute(now()),
  4. toSecond(now()),
  5. toTime(now())
  6. ┌─toHour(now())─┬─toMinute(now())─┬─toSecond(now())─┬───────toTime(now())─┐
  7. 11 37 14 1970-01-02 11:37:14
  8. └───────────────┴─────────────────┴─────────────────┴─────────────────────┘

toDayOfMonth → 获取日期或时间日期的天(1-31)
toDayOfWeek → 获取日期或时间日期的星期数值(1-7)
toYear → 获取日期或时间日期的年份
toMonth → 取日期或时间日期的月份

  1. SELECT
  2. toDayOfMonth(now()),
  3. toDayOfWeek(now()),
  4. toYear(now()),
  5. toMonth(now()),
  6. now()
  7. ┌─toDayOfMonth(now())─┬─toDayOfWeek(now())─┬─toYear(now())─┬─toMonth(now())─┬───────────────now()─┐
  8. 26 4 2020 11 2020-11-26 11:33:49
  9. └─────────────────────┴────────────────────┴───────────────┴────────────────┴─────────────────────┘

toDate() → 将字符日期或时间戳转化为日期
toDateTime() → 将字符时间戳转化为时间戳

  1. SELECT
  2. toDate('2020-11-25 19:58:10'),
  3. toDateTime('2020-11-25 19:58:10')
  4. ┌─toDate('2020-11-25 19:58:10')─┬─toDateTime('2020-11-25 19:58:10')─┐
  5. 2020-11-25 2020-11-25 19:58:10
  6. └───────────────────────────────┴───────────────────────────────────┘

1.2 生成日期、时间戳

now() → 生成当前时间戳
today() → 生成当前日期
yesterday() → 生成当前日期的前一天

  1. SELECT
  2. now(),today(),yesterday()
  3. ┌───────────────now()─┬────today()─┬─yesterday()─┐
  4. 2020-11-25 19:58:10 2020-11-25 2020-11-24
  5. └─────────────────────┴────────────┴─────────────┘

2、类型转化函数

toDecimal32() → 将数值型或者含有非数字的字符串进行精度保留
toInt8OrZero() → 将整数字符型转化为整数型,否则返回0
toFloat32OrZero() → 将数值字符串型转化为数值型

  1. SELECT
  2. toDecimal32(83.5222191, 6),
  3. toDecimal32('-23.12291', 3),
  4. toInt8OrZero('23'),
  5. toInt8OrZero('-123'),
  6. toFloat32OrZero('-123'),
  7. toFloat32OrZero('123.123')
  8. ┌─toDecimal32(83.5222191, 6)─┬─toDecimal32('-23.12291', 3)─┬─toInt8OrZero('23')─┬─toInt8OrZero('-123')─┬─toFloat32OrZero('-123')─┬─toFloat32OrZero('123.123')─┐
  9. 83.522219 -23.122 23 -123 -123 123.123
  10. └────────────────────────────┴─────────────────────────────┴────────────────────┴──────────────────────┴─────────────────────────┴────────────────────────────┘

3.字符串处理

upper() → 大写转换
lower() → 小写转换
concat() → 字符串拼接
substring() → 字符串截取
splitByString() → 字符串拆分

  1. SELECT
  2. upper('abc'),
  3. lower('ABC'),
  4. concat('ab', 'cd'),
  5. substring('abcde', 1, 2),
  6. splitByString(',', 'a,b,c')
  7. ┌─upper('abc')─┬─lower('ABC')─┬─concat('ab', 'cd')─┬─substring('abcde', 1, 2)─┬─splitByString(',', 'a,b,c')─┐
  8. ABC abc abcd ab ['a','b','c']
  9. └──────────────┴──────────────┴────────────────────┴──────────────────────────┴─────────────────────────────┘

4.取整函数(四舍五入)

floor([x, N]) → 向下取数
ceil([x, N]) → 向上取数
round([x, N]) → 四舍五入

  1. SELECT
  2. floor(123.883, 1),floor(123.883, -1),
  3. ceil(123.883, 1),ceil(123.883, -1),
  4. round(123.883, 1),round(123.883, -1)
  5. ┌─floor(123.883, 1)─┬─floor(123.883, -1)─┬─ceil(123.883, 1)─┬─ceil(123.883, -1)─┬─round(123.883, 1)─┬─round(123.883, -1)─┐
  6. 123.8 120 123.9 130 123.9 120
  7. └───────────────────┴────────────────────┴──────────────────┴───────────────────┴───────────────────┴────────────────────┘

5.NULL值处理函数

isNull() → 检查参数是否为NULL ,为NULL返回1,否则0
isNotNull() → 检查参数是否不为 NULL,为NULL返回0,否则1
ifNull() → 如果第一个参数为NULL,则返回第二个参数的值。
NullIf() → 如果参数相等,返回NULL,参数不相等,返回第一个值

  1. SELECT
  2. isNull('1'),isNull(NULL),
  3. isNotNull('1'),isNotNull(NULL),
  4. ifNull(NULL, 2),ifNull(1, 2),
  5. NullIf(1, 1),NullIf(1, 0)
  6. ┌─isNull('1')─┬─isNull(NULL)─┬─isNotNull('1')─┬─isNotNull(NULL)─┬─ifNull(NULL, 2)─┬─ifNull(1, 2)─┬─NullIf(1, 1)─┬─NullIf(1, 0)─┐
  7. 0 1 1 0 2 1 ᴺᵁᴸᴸ 1
  8. └─────────────┴──────────────┴────────────────┴─────────────────┴─────────────────┴──────────────┴──────────────┴──────────────┘

6.取记录最新一条或最早一条

argMin(arg,val) → 计算最小值的arg值。如果val的最小值有几个不同的arg值,则遇到的第一个值是输出。
argMax(arg,val) → 计算最大值的arg值。如果存在多个不同的arg值来表示val的最大值,则遇到的第一个值是输出。

  1. # 测试数据,求最低薪水的员工
  2. ┌─user─────┬─salary─┐
  3. director 5000
  4. manager 3000
  5. worker 1000
  6. └──────────┴────────┘
  7. # 计算最低薪水的员工
  8. SELECT argMin(user, salary) FROM salary
  9. ┌─argMin(user, salary)─┐
  10. worker
  11. └──────────────────────┘

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

    1. hasAny([2, 5, 7, 8, 9], [6, 9]) AS hasAny,
    2. 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)

测试:

  1. WITH [toUInt32(1), toUInt32(2), toUInt32(5), toUInt32(7), toUInt32(35)] AS arr
  2. SELECT
  3. bitmapBuild(arr) AS bitmapBuild,
  4. bitmapToArray(bitmapBuild(arr)) AS bitmapToArray,
  5. bitmapToArray(bitmapSubsetInRange(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetInRange,
  6. bitmapToArray(bitmapSubsetLimit(bitmapBuild(arr), toUInt32(2), toUInt32(7))) AS bitmapSubsetLimit
  7. ┌─bitmapBuild─┬─bitmapToArray─┬─bitmapSubsetInRange─┬─bitmapSubsetLimit─┐
  8. # │ [1,2,5,7,35] │ [2,5] │ [2,5,7,35] │
  9. └─────────────┴───────────────┴─────────────────────┴───────────────────┘
  • arrayJoin → 将数组转化为明细数据,行转列

测试:

  1. SELECT arrayJoin(bitmapToArray(bitmapBuild([1, 2, 3, 4, 5]))) AS uid
  2. ┌─uid─┐
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. └─────┘
  • 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。

测试:

  1. # 语法格式
  2. WITH
  3. bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
  4. bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
  5. SELECT
  6. bitmapHasAny(bitmap1, bitmap2) AS bitmapHasAny,
  7. bitmapHasAll(bitmap1, bitmap2) AS bitmapHasAll
  8. ┌─bitmapHasAny─┬─bitmapHasAll─┐
  9. 1 0
  10. └──────────────┴──────────────┘
  • bitmapContains(bitmap, needle) → 检查位图是否包含指定元素。
  • bitmapAnd(bitmap1,bitmap2) → 两个位图对象进行与操作(相当于取交集),返回一个新的位图对象。
  • bitmapOr(bitmap1,bitmap2) → 为两个位图对象进行或操作,返回一个新的位图对象,如果参数是多个的情况下,可以尝试使用groupBitmapMergeState。
  • bitmapXor(bitmap1,bitmap2) → 为两个位图对象进行异或操作,返回一个新的位图对象。
  • bitmapAndnot(bitmap1,bitmap2) → 计算两个位图的差异,返回一个新的位图对象。

测试:

  1. WITH
  2. bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
  3. bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
  4. SELECT
  5. bitmapContains(bitmap1, toUInt32(5)) AS bitmapContains,
  6. bitmapToArray(bitmapAnd(bitmap1, bitmap2)) AS bitmapAnd,
  7. bitmapToArray(bitmapOr(bitmap1, bitmap2)) AS bitmapOr,
  8. bitmapToArray(bitmapXor(bitmap1, bitmap2)) AS bitmapXor,
  9. bitmapToArray(bitmapAndnot(bitmap1, bitmap2)) AS bitmapAndnot
  10. ┌─bitmapContains─┬─bitmapAnd─┬─bitmapOr────────────┬─bitmapXor───────┬─bitmapAndnot─┐
  11. 1 [4,5] [1,2,3,4,5,9,17,35] [1,2,3,9,17,35] [1,2,3]
  12. └────────────────┴───────────┴─────────────────────┴─────────────────┴──────────────┘
  • bitmapCardinality(bitmap) → 返回一个UInt64类型的数值,表示位图的大小。
  • bitmapMin(bitmap) → 返回一个UInt64类型的数值,表示位图中的最小值。如果位图为空则返回UINT32_MAX。
  • bitmapMax(bitmap) → 返回一个UInt64类型的数值,表示位图中的最大值。如果位图为空则返回0。

测试:

  1. WITH bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap
  2. SELECT
  3. bitmapCardinality(bitmap) AS bitmapCardinality,
  4. bitmapMin(bitmap) AS bitmapMin,
  5. bitmapMax(bitmap) AS bitmapMax
  6. ┌─bitmapCardinality─┬─bitmapMin─┬─bitmapMax─┐
  7. 5 4 35
  8. └───────────────────┴───────────┴───────────┘
  • bitmapOrCardinality(bitmap1,bitmap2) → 为两个位图进行或运算,返回结果位图的基数。
  • bitmapXorCardinality(bitmap1,bitmap2) → 为两个位图进行异或运算,返回结果位图的基数。
  • bitmapAndnotCardinality(bitmap1,bitmap2) → 计算两个位图的差异,返回结果位图的基数。

测试:

  1. WITH
  2. bitmapBuild([toUInt32(1), toUInt32(2), toUInt32(3), toUInt32(4), toUInt32(5)]) AS bitmap1,
  3. bitmapBuild([toUInt32(4), toUInt32(5), toUInt32(9), toUInt32(17), toUInt32(35)]) AS bitmap2
  4. SELECT
  5. bitmapOrCardinality(bitmap1, bitmap2) AS bitmapOrCardinality,
  6. bitmapXorCardinality(bitmap1, bitmap2) AS bitmapXorCardinality,
  7. bitmapAndnotCardinality(bitmap1, bitmap2) AS bitmapAndnotCardinality
  8. ┌─bitmapOrCardinality─┬─bitmapXorCardinality─┬─bitmapAndnotCardinality─┐
  9. 8 6 3
  10. └─────────────────────┴──────────────────────┴─────────────────────────┘

9 hash函数

可以用于将元素不可逆的伪随机打乱。

1 计算字符串的md5值,这两个函数比较低效,如果可以请使用sipHash64函数。

  1. SELECT
  2. halfMD5('123'),
  3. lower(hex(MD5('123')))
  4. ┌──────halfMD5('123')─┬─lower(hex(MD5('123')))───────────┐
  5. 2318431741638412123 202cb962ac59075b964b07152d234b70
  6. └─────────────────────┴──────────────────────────────────┘

2 计算字符串的SipHash

  1. SELECT
  2. sipHash64('123'),
  3. lower(hex(sipHash128('123')))
  4. ┌─────sipHash64('123')─┬─lower(hex(sipHash128('123')))────┐
  5. 10015141997369857857 b1f8870810764950f06fd4808598b5da
  6. └──────────────────────┴──────────────────────────────────┘

3 计算整型的hash码

  1. SELECT
  2. intHash32(123),
  3. intHash64(123)
  4. ┌─intHash32(123)─┬───────intHash64(123)─┐
  5. 1465837921 12585326385145454095
  6. └────────────────┴──────────────────────┘

4 计算字符串的JavaHash,返回Int32

  1. SELECT javaHash('123')
  2. ┌─javaHash('123')─┐
  3. 48690
  4. └─────────────────┘

10 随机函数

所有随机函数都只接受一个参数或不接受任何参数。可以向它传递任何类型的参数,但传递的参数将不会使用在任何随机数生成过程中。此参数的唯一目的是防止公共子表达式消除,以便在相同的查询中使用相同的随机函数生成不同的随机数。

  • rand, rand32 → 返回一个UInt32类型的随机数字,所有UInt32类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。
  • rand64 → 返回一个UInt64类型的随机数字,所有UInt64类型的数字被生成的概率均相等。此函数线性同于的方式生成随机数。
  • randConstant → 返回一个UInt32类型的随机数字,该函数不同之处在于仅为每个数据块参数一个随机数。

    SELECT

    1. rand(),
    2. rand(123),
    3. rand32(),
    4. rand32(123),
    5. rand64(),
    6. 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)

  1. SELECT
  2. '2020-11-15 23:00:00' AS timestamp,
  3. CAST(timestamp, 'DateTime') AS datetime,
  4. CAST(timestamp, 'Date') AS date,
  5. CAST(timestamp, 'String') AS string,
  6. CAST(timestamp, 'FixedString(22)') AS fixed_string
  7. ┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string────────┐
  8. 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
  9. └─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘

11 其他函数

  • rowNumberInAllBlocks() → 返回行所在结果集中的序列号。此函数仅考虑受影响的Block。

    SELECT

    1. rowNumberInAllBlocks() AS rowNumberInAllBlocks,
    2. 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官方文档

发表评论

表情:
评论列表 (有 0 条评论,3065人围观)

还没有评论,来说两句吧...

相关阅读