clickhouse-【总结卡】clickhouse数据库常用高级函数

一、分组聚合

1.groupArray:聚合为数组

select * from tmp_20230608;
1,a,aa
1,b,bb
1,a,aa
2,a,aa
2,b,bb
​
select id
,groupArray(type) -- 聚合为数组
from tmp_20230608
group by id;
2,"[a, b]"
1,"[a, b, a]"

2.groupUniqArray:聚合为去重数组

select \* from tmp\_20230608;
1,a,aa
1,b,bb
1,a,aa
2,a,aa
2,b,bb
​
select id
,groupUniqArray(type) -- 聚合为去重数组
from tmp\_20230608
group by id;
2,"\[a, b]"
1,"\[a, b]"

二、去重

1.arrayDistinct:对数组去重

select arrayDistinct(\[1,2,3,1,2])
\[1, 2, 3]

2.arrayUniq:统计数组中有多少个不重复的元素

select arrayUniq(\[1,2,3,1,2])
3

3.arrayCompact:对数组中相邻元素去重

select arrayCompact(\[1,2,3,3,2,1])
\[1, 2, 3, 2, 1]

三、数组属性

1.arrayJoin:将数组展开，行专列

select arrayJoin(\[1,2,3])
1
2
3

2.arrayFilter:筛选数组中的元素

select arrayFilter(x -> x >= 2,\[1,2,3])
\[2, 3]

3.arrayEnumerate:返回数组下标

select arrayEnumerate(\[11,22,33])
\[1, 2, 3]

4.arrayEnumerateDense:标记出数组中相同元素

select arrayEnumerateDense(\[11,22,33,22])
\[1, 2, 3, 2]

5.arrayReduce:对数组进行聚合操作，等价于arrayMin、arrayMax、arrayAvg

select arrayReduce('min',\[11,22,33]),arrayMin(\[11,22,33]) -- 11,11
select arrayReduce('max',\[11,22,33]),arrayMax(\[11,22,33]) -- 33,33
select arrayReduce('avg',\[11,22,33]),arrayAvg(\[11,22,33]) -- 22,22

6.hasAny:判断数组A中是否有数组B中任意一个元素hasAny(A,B)，是返回1，否返回0

select hasAny(\[11,22,33,22],\[44,55]),hasAny(\[11,22,33,22],\[33,55])
0,1

7.hasAll:判断数组A中是否有数组B中所有元素hasAll(A,B)，是返回1，否返回0

select hasAll(\[11,22,33,22],\[44,55]),hasAll(\[11,22,33,22],\[33,11])
0,1

8.arrayWithConstant:用常量生成一个指定长度的数组

select arrayWithConstant(5,2)
\[2, 2, 2, 2, 2]

9.has:判断数组中是否存在某元素

select has(\[11,33,22,44,55],11),has(\[11,33,22,44,55],111)
1,0

10.arrayExists:判断数组中是否存在满足条件的元素

select arrayExists(x -> x \* 2 < 50,\[11,33,22,44,55]),arrayExists(x -> x \* 2 > 500,\[11,33,22,44,55]);
1,0

11.arrayMap:按指定条件映射出新数组

select arrayMap(x -> x \* 10,\[11,33,22,44,55]);
\[110, 330, 220, 440, 550]

四、切割/拼接

1.arrayStringConcat:将数组元素按照指定分隔符拼接，若未指定分隔符，默认指定空字符串

select arrayStringConcat(\[1,2,3],'-')
1-2-3

2.arraySlice:对数组进行切片，后面两个参数分别是切割起始下标和元素个数

select arraySlice(\[11,22,33,44,55],2,3),arraySlice(\[11,22,33,44,55],-3,2)
\[22, 33, 44],\[33, 44]

五、排序

1.arraySort:对数组进行升序排列

select arraySort(\[11,33,22,44,55])
\[11, 22, 33, 44, 55]

2.arrayReverseSort:对数组进行降序排列

select arrayReverseSort()(\[11,33,22,44,55])
\[55, 44, 33, 22, 11]

六、添加/删除首尾元素

1.arrayPushFront:在数组头部添加元素

select arrayPushFront()(\[11,33,22,44,55],8)
\[8, 11, 33, 22, 44, 55]

2.arrayPushBack:在数组尾部添加元素

select arrayPushBack()(\[11,33,22,44,55],8)
\[11, 33, 22, 44, 55, 8]

3.arrayPopFront:删除数组头部元素

select arrayPopFront()(\[11,33,22,44,55])
\[33, 22, 44, 55]

4.arrayPopBack:删除数组尾部元素

select arrayPopBack()(\[11,33,22,44,55])
\[11, 33, 22, 44]

七、计算差值

1.arrayDifference:计算数组中相邻元素的差值，当前值减去前一位值（第一位不计算，默认为0）

select arrayDifference()(\[11,33,22,44,55])
\[0, 22, -11, 22, 11]

2.runningDifference:计算同一列中相邻元素的差值，当前行的值减去前一行的值（第一行不计算，默认为0）

select runningDifference(arrayJoin()(\[11,33,22,44,55]))
0
22
\-11
22
11

八、字段数据类型

1.toTypeName:获取表字段数据或常量的数据类型

select toTypeName(CAST('2023-01-01 01:02:03' AS DateTime))
DateTime

2.toColumnTypeName:获取表字段数据或常量实际存储的类型

select toColumnTypeName(CAST('2023-01-01 01:02:03' AS DateTime))
Const(UInt32)

3.defaultValueOfTypeName:获取该数据类型的默认值

select defaultValueOfTypeName('Date')
1970-01-01
-- 结合coalesce自动处理表中的null值
select coalesce(`${column},
    defaultValueOfTypeName(if(startsWith(toTypeName($`{
   column}), 'Nullable'),
                                substr(toTypeName(`${column}), 10,
                              char_length(toTypeName($`{
   column})) - 10),
                            toTypeName(`${column}))))
from $`{
   table};