clickhouse 三（clickhouse函数使用总结）

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

![在这里插入图片描述][20201203120310667.png]

### 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官方文档][ClickHouse]

[20201203120310667.png]: /images/20210505/75d1e4f211f845989becf442985413d0.png
[ClickHouse]: https://clickhouse.tech/docs/zh/