clickhouse的函数列表

最新推荐文章于 2024-05-28 09:55:27 发布

林鸟鸟

最新推荐文章于 2024-05-28 09:55:27 发布

阅读量2.2k

点赞数 1

分类专栏： clickhouse 文章标签： clickhouse

本文链接：https://blog.csdn.net/weixin_57205390/article/details/129313568

版权

clickhouse 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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 │
└─────────────────────┴──────────────────────┴─────────────────────────┘

林鸟鸟

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
clickhouse的函数列表

argMax(arg，val) → 计算最大值的arg值。argMin(arg，val) → 计算最小值的arg值。isNotNull() → 检查参数是否不为 NULL，为NULL返回0，否则1。NullIf() → 如果参数相等，返回NULL,参数不相等，返回第一个值。isNull() → 检查参数是否为NULL ，为NULL返回1，否则0。ifNull() → 如果第一个参数为NULL，则返回第二个参数的值。toTime() → 将时间日期的日期固定到某一天，保留原始时间。
复制链接

扫一扫

专栏目录