ClickHouse函数 4.数组函数

clickhouse

发布日期: 2021-09-10

1. empty

判断数组是否为空
为空：1
不为空：0

SELECT empty([]), empty([1,2,3]);

┌─empty(array())─┬─empty([1, 2, 3])─┐
│              1 │                0 │
└────────────────┴──────────────────┘

2. notEmpty

判断数组是否不为空
为空：0
不为空：1

SELECT notEmpty([]), notEmpty([1,2,3]);

┌─notEmpty(array())─┬─notEmpty([1, 2, 3])─┐
│                 0 │                   1 │
└───────────────────┴─────────────────────┘

3. length

计算数组长度，返回数组中的元素个数。
结果类型是UInt64。
该函数也适用于字符串。

SELECT length([]),length(['a','b','c']),length([1,2,3]); 

┌─length(array())─┬─length(['a', 'b', 'c'])─┬─length([1, 2, 3])─┐
│               0 │                       3 │                 3 │
└─────────────────┴─────────────────────────┴───────────────────┘

-- length(), -- 出现异常
-- length([true, false]), -- 异常
-- length([1,2,,4]), --出现异常！

4. emptyArray*

并返回适当类型的空数组
不接受任何参数

-- UInt8的空数组
SELECT emptyArrayUInt8() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayUInt8())─┐
│ []  │ Array(UInt8)                  │
└─────┴───────────────────────────────┘

-- UInt16的空数组
SELECT emptyArrayUInt16() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayUInt16())─┐
│ []  │ Array(UInt16)                  │
└─────┴────────────────────────────────┘

-- UInt32的空数组
SELECT emptyArrayUInt32() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayUInt32())─┐
│ []  │ Array(UInt32)                  │
└─────┴────────────────────────────────┘

-- UInt64的空数组
SELECT emptyArrayUInt64() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayUInt64())─┐
│ []  │ Array(UInt64)                  │
└─────┴────────────────────────────────┘

-- Date的空数组
SELECT emptyArrayDate() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayDate())─┐
│ []  │ Array(Date)                  │
└─────┴──────────────────────────────┘

-- DateTime的空数组
SELECT emptyArrayDateTime() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayDateTime())─┐
│ []  │ Array(DateTime)                  │
└─────┴──────────────────────────────────┘

-- Int8的空数组
SELECT emptyArrayInt8() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayInt8())─┐
│ []  │ Array(Int8)                  │
└─────┴──────────────────────────────┘

-- Int16的空数组
SELECT emptyArrayInt16() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayInt16())─┐
│ []  │ Array(Int16)                  │
└─────┴───────────────────────────────┘

-- Int32的空数组
SELECT emptyArrayInt32() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayInt32())─┐
│ []  │ Array(Int32)                  │
└─────┴───────────────────────────────┘

-- Int64的空数组
SELECT emptyArrayInt64() as arr,toTypeName(arr);

┌─arr─┬─toTypeName(emptyArrayInt64())─┐
│ []  │ Array(Int64)                  │
└─────┴───────────────────────────────┘

5. emptyArrayToSingle

返回空数组的默认值
接受一个空数组并返回一个仅包含一个默认值元素的数组

-- 返回 Array(Int32)  类型的默认值
SELECT emptyArrayToSingle(emptyArrayInt32());

┌─emptyArrayToSingle(emptyArrayInt32())─┐
│ [0]                                   │
└───────────────────────────────────────┘

-- 返回 Array(UInt32)  类型的默认值
SELECT emptyArrayToSingle(emptyArrayUInt32());

┌─emptyArrayToSingle(emptyArrayUInt32())─┐
│ [0]                                    │
└────────────────────────────────────────┘

-- 返回 Array(Date)  类型的默认值
SELECT emptyArrayToSingle(emptyArrayDate());

┌─emptyArrayToSingle(emptyArrayDate())─┐
│ ['1970-01-01']                       │
└──────────────────────────────────────┘

-- 返回 Array(DateTime)  类型的默认值
SELECT emptyArrayToSingle(emptyArrayDateTime());

┌─emptyArrayToSingle(emptyArrayDateTime())─┐
│ ['1970-01-01 08:00:00']                  │
└──────────────────────────────────────────┘

6. range

range(end)
- 返回从0到N-1的数字数组
- 元素从0开始增长，步长尾1
- 如果在数据块中创建总长度超过100,000,000个元素的数组，则抛出异常
range([start, ] end [, step])
- start— 开始值（包含）
- end — 结束值（不包含）
- step— 增量步长

SELECT range(10);

┌─range(10)─────────────┐
│ [0,1,2,3,4,5,6,7,8,9] │
└───────────────────────┘

SELECT range(1, 4);
┌─range(1, 4)─┐
│ [1,2,3]     │
└─────────────┘

SELECT range(1,10,3);
┌─range(1, 10, 3)─┐
│ [1,4,7]         │
└─────────────────┘

-- range(5.5), -- 出现异常，N为Int8的数据类型，正整数
-- range(-10), -- 出现异常，DB::Exception: Illegal type Int8 of argument of

7. array

用于创建一个新的数组的函数
array(1,2,3) 类似于[1,2,3]
新建数组中的元素的数据类型需保持一致

SELECT array(1,2,2,3,4);

┌─array(1, 2, 2, 3, 4)─┐
│ [1,2,2,3,4]          │
└──────────────────────┘

8. arrayConcat

arrayConcat(arrays)
合并参数中传递的所有数组
不会自动去重，不会自动排序
所有数组中的数据类型必须一致

SELECT arrayConcat(array(1,2),array(2,3),array(3,4));

┌─arrayConcat(array(1, 2), array(2, 3), array(3, 4))─┐
│ [1,2,2,3,3,4]                                      │
└────────────────────────────────────────────────────┘

9. arrayElement

获取指定位置元素
从数组arr中获取索引为 n 的元素，n 必须是任何整数类型。
数组中的索引从 1 开始，支持负索引。
索引为负数时，它选择从末尾开始编号的相应元素。例如，arr [-1]是数组中的最后一项。
如果索引超出数组的边界，则返回默认值（数字为0，字符串为空字符串等）.

SELECT arrayElement(array(10,20,30), 1),arrayElement(array(10,20,30), 4);

┌─arrayElement(array(10, 20, 30), 1)─┬─arrayElement(array(10, 20, 30), 4)─┐
│                                10 │                                 0 │
└───────────────────────────────────┴───────────────────────────────────┘

SELECT arrayElement(array(10,20,30), 0),arrayElement(array(10,20,30), -1);

┌─arrayElement(array(10, 20, 30), 0)─┬─arrayElement(array(10, 20, 30), -1)─┐
│                                  0 │                                  30 │
└────────────────────────────────────┴─────────────────────────────────────┘

10. has

has(arr, elem)
检查 arr 数组是否具有 elem 元素。
包含：1
不包含：0

SELECT has([1,2,3], 2), has(array(1,2,3),4);
┌─has([1, 2, 3], 2)─┬─has(array(1, 2, 3), 4)─┐
│                 1 │                      0 │
└───────────────────┴────────────────────────┘

select has([1,2,NULL], NULL);

┌─has([1, 2, NULL], NULL)─┐
│                       1 │
└─────────────────────────┘

11. hasAny

hasAny(arr1, arr2)
表示arr1和arr2是否存在交集
存在：1
不存在：0
“ NULL “ 作为数组中的元素值进行处理
忽略两个数组中的元素值的顺序

SELECT hasAny(array(1,2,3), array(1)),hasAny(array(1,2,3), array());

┌─hasAny(array(1, 2, 3), array(1))─┬─hasAny(array(1, 2, 3), array())─┐
│                                1 │                               0 │
└──────────────────────────────────┴─────────────────────────────────┘

-- 空数组跟null不是一样的对象
select hasAny([1,NULL],[]),hasAny([1,NULL],[NULL,2]);

┌─hasAny([1, NULL], array())─┬─hasAny([1, NULL], [NULL, 2])─┐
│                          0 │                            1 │
└────────────────────────────┴──────────────────────────────┘

12. hasAll

hasAll(set, subset)
检查一个数组是否是另一个数组的子集。
set – 具有一组元素的任何类型的数组。
subset – 任何类型的数组，其元素应该被测试为set的子集。
是：1，表示set包含subset中所有的元素
不是：0
空数组是任何数组的子集。
“NULL” 作为数组中的元素值进行处理。
忽略两个数组中的元素值的顺序。

SELECT hasAll([1,2,3], [1,2]),hasAll([1,2,3], [3,4]);
┌─hasAll([1, 2, 3], [1, 2])─┬─hasAll([1, 2, 3], [3, 4])─┐
│                         1 │                         0 │
└───────────────────────────┴───────────────────────────┘

13. indexOf

indexOf(arr, x)
数组指定元素的索引
返回数组中第一个‘x’元素的索引（从1开始），
如果 ‘x’ 元素不存在在数组中，则返回0

SELECT indexOf([1, 2, 3], 3),indexOf([1, 3, 3], 3),indexOf([1, 2, 3], 4);

┌─indexOf([1, 2, 3], 3)─┬─indexOf([1, 3, 3], 3)─┬─indexOf([1, 2, 3], 4)─┐
│                     3 │                     2 │                     0 │
└───────────────────────┴───────────────────────┴───────────────────────┘

14. countEqual

countEqual(arr, x)
数组中包含指定元素的个数
返回数组中等于x的元素的个数
相当于arrayCount（elem - > elem =x，arr）
null值将作为单独的元素值处理

SELECT countEqual([1, 2, 2, 2, 3], 2), countEqual([2, NULL, NULL], NULL);

┌─countEqual([1, 2, 2, 2, 3], 2)─┬─countEqual([2, NULL, NULL], NULL)─┐
│                              3 │                                 2 │
└────────────────────────────────┴───────────────────────────────────┘

15. arrayEnumerate

arrayEnumerate(arr)
返回 Array [1, 2, 3, …, length (arr) ] 此功能通常与ARRAY JOIN一起使用。
它允许在应用 ARRAY JOIN 后为每个数组计算一次。

SELECT arrayEnumerate([1,20,20,3]); 

┌─arrayEnumerate([1, 20, 20, 3])─┐
│ [1,2,3,4]                      │
└────────────────────────────────┘

SELECT arrayEnumerate(array(11,20,13));

┌─arrayEnumerate(array(11, 20, 13))─┐
│ [1,2,3]                           │
└───────────────────────────────────┘

SELECT arrayEnumerate(array(11,20,13,NULL));
┌─arrayEnumerate(array(11, 20, 13, NULL))─┐
│ [1,2,3,4]                               │
└─────────────────────────────────────────┘

16. arrayEnumerateUniq

arrayEnumerateUniq(arr)
返回元素在数组中出现的次数
返回与源数组大小相同的数组，其中每个元素表示与其下标对应的源数组

SELECT arrayEnumerateUniq([1,1,2,2]);
┌─arrayEnumerateUniq([1, 1, 2, 2])─┐
│ [1,2,1,2]                        │
└──────────────────────────────────┘

17. arrayPop*

删除数组的元素
arrayPopBack(arr) 删除数组的最后一项
arrayPopFront(arr) 删除数组的第一项

-- 删除数组array的最后一项
SELECT arrayPopBack(array(1,2,3,4));

┌─arrayPopBack(array(1, 2, 3, 4))─┐
│ [1,2,3]                         │
└─────────────────────────────────┘

-- 删除数组第一项
SELECT arrayPopFront(array(1,2,3,4));

┌─arrayPopFront(array(1, 2, 3, 4))─┐
│ [2,3,4]                          │
└──────────────────────────────────┘

18. arrayPush*

向数组添加元素
arrayPushFront(array, single_value) ：向数组头部添加元素
arrayPushBack(array, single_value) ：向数组尾部添加元素
single_value 是单个值

SELECT arrayPushBack([1,2,3], 0);

┌─arrayPushBack([1, 2, 3], 0)─┐
│ [1,2,3,0]                   │
└─────────────────────────────┘

SELECT arrayPushFront([1,2,3], 0);

┌─arrayPushFront([1, 2, 3], 0)─┐
│ [0,1,2,3]                    │
└──────────────────────────────┘

19. arrayResize

更改数组的长度
arrayResize(arr, size[, extender])
如果arr的长度 > size,则会对arr截取size的长度；
如果arr的长度 < size，则其余位置用对应数据类型的默认值填充。
extender含义是扩展元素的值。
- 如果没有指定extender，则默认按照对应的数据类型的默认值进行赋值。
- 否则按照extender进行填充。

SELECT arrayResize([1,2,3], 5),arrayResize([1,2,3], 2);

┌─arrayResize([1, 2, 3], 5)─┬─arrayResize([1, 2, 3], 2)─┐
│ [1,2,3,0,0]               │ [1,2]                     │
└───────────────────────────┴───────────────────────────┘

20. arraySlice

arraySlice(array, offset[, length])
截取数组的部分元素，得到一个新的子数组
array：数组
offset：数组的偏移。正值表示左侧的偏移量，负值表示右侧的缩进值。数组下标从1开始。
length：子数组的长度。如果指定负值，则该函数返回[offset，array_length - length。如果

SELECT arraySlice([1,2,3,4], 0, 3),arraySlice([1,2,NULL,5], 2, 3);

┌─arraySlice([1, 2, 3, 4], 0, 3)─┬─arraySlice([1, 2, NULL, 5], 2, 3)─┐
│ []                             │ [2,NULL,5]                        │
└────────────────────────────────┴───────────────────────────────────┘

21. arraySort

arraySort([func,] arr, ……)
数组排序
arraySort是高阶函数。可以将lambda函数作为第一个参数传递给它。在这种情况下，排序顺序由lambda函数的调用结果决定。

SELECT arraySort(['a','',NULL,'c','b']);
┌─arraySort(['a', '', NULL, 'c', 'b'])─┐
│ ['','a','b','c',NULL]                │
└──────────────────────────────────────┘

SELECT arraySort(array('ac','ab','bc','ad',NULL));
┌─arraySort(array('ac', 'ab', 'bc', 'ad', NULL))─┐
│ ['ab','ac','ad','bc',NULL]                     │
└────────────────────────────────────────────────┘

-- -inf,inf,nan,NULL 的排序顺序
select arraySort(array(NULL, NaN, Inf,-Inf));
┌─arraySort(array(NULL, nan, inf, -inf))─┐
│ [-inf,inf,nan,NULL]                    │
└────────────────────────────────────────┘

SELECT arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf]);
┌─arraySort([1, nan, 2, NULL, 3, nan, -4, NULL, inf, -inf])─┐
│ [-inf,-4,1,2,3,inf,nan,nan,NULL,NULL]                     │
└───────────────────────────────────────────────────────────┘

-- 降序：（高阶函数用法）
SELECT arraySort(x -> -x, [5,4,3,1,2,3]); 

┌─arraySort(lambda(tuple(x), negate(x)), [5, 4, 3, 1, 2, 3])─┐
│ [5,4,3,3,2,1]                                              │
└────────────────────────────────────────────────────────────┘

-- 升序：（高阶函数用法）
SELECT arraySort(x -> x, [5,4,3,1,2,3]);
┌─arraySort(lambda(tuple(x), x), [5, 4, 3, 1, 2, 3])─┐
│ [1,2,3,3,4,5]                                      │
└────────────────────────────────────────────────────┘

22. arrayReverse

arrayReverse([func,] arr, ……)
数组翻转
如果是NULL的话在排序的过程中，根据数组的数据类型进行默认值填充。

SELECT
arrayReverse(array('a','b','c',NULL)) AS hasOneNull, -- ['','c','b','a']
arrayReverse(array('ac','ab','bc','ad',NULL)) AS hasNull, --
['','ad','bc','ab','ac']
--网格视图： ['[NULL]','ad','bc','','ab','[NULL]','','ac']；文本视图 ：
['','ad','bc','','ab','','','ac']
arrayReverse(array('ac','',NULL,'ab','','bc','ad',NULL)) AS hasNullEmpty,
arrayReverse(array(NULL, 3, NULL, 2, 1)),-- [1,2,0,3,0]
arrayReverse([1,2,3,4]);-- [4,3,2,1]

23. arrayUniq

arrayUniq(arr,……)
统计数组中不重复元素的个数。
如果传递一个参数，则计算数组中不同元素的数量。
如果传递了多个参数，则它计算多个数组中相应位置的不同元素元组的数量

SELECT 
  arrayUniq([1,2,3]),
  arrayUniq([1,2,2,2,3]);

┌─arrayUniq([1, 2, 3])─┬─arrayUniq([1, 2, 2, 2, 3])─┐
│                    3 │                          3 │
└──────────────────────┴────────────────────────────┘

SELECT
  arrayUniq([1,2,3],[2,3,4]),
  arrayUniq([1,2,2],[1,3,3]);
┌─arrayUniq([1, 2, 3], [2, 3, 4])─┬─arrayUniq([1, 2, 2], [1, 3, 3])─┐
│                               3 │                               2 │
└─────────────────────────────────┴─────────────────────────────────┘

24. arrayJoin

arrayJoin(arr)
此函数将数组作为参数，并将该行在结果集中复制数组元素个数

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src;
┌─dst─┬─'Hello'─┬─src─────┐
│   1 │ Hello   │ [1,2,3] │
│   2 │ Hello   │ [1,2,3] │
│   3 │ Hello   │ [1,2,3] │
└─────┴─────────┴─────────┘

-- 每个元素扩大两倍；
SELECT arrayJoin([1,2,3]) * 2;
┌─multiply(arrayJoin([1, 2, 3]), 2)─┐
│                                 2 │
│                                 4 │
│                                 6 │
└───────────────────────────────────┘

-- 每个元素缩小两倍
SELECT arrayJoin([-4,-2,0,2,4]) / 2;
┌─divide(arrayJoin([-4, -2, 0, 2, 4]), 2)─┐
│                                      -2 │
│                                      -1 │
│                                       0 │
│                                       1 │
│                                       2 │
└─────────────────────────────────────────┘

25. arrayDifference

arrayDifference(arr)
返回一个数组，其中包含所有相邻元素对之间的差值


SELECT arrayDifference([1,2,3,4]);
┌─arrayDifference([1, 2, 3, 4])─┐
│ [0,1,1,1]                     │
└───────────────────────────────┘

SELECT arrayDifference([1,3,10,50]);
┌─arrayDifference([1, 3, 10, 50])─┐
│ [0,2,7,40]                      │
└─────────────────────────────────┘

26. arrayDistinct

arrayDistinct(arr)
返回一个包含所有数组中不同元素的数组.
类似于java的Set集合，对list集合进行去重。

SELECT arrayDistinct(array(0,1,NULL,3,4,4,4));
┌─arrayDistinct(array(0, 1, NULL, 3, 4, 4, 4))─┐
│ [0,1,3,4]                                    │
└──────────────────────────────────────────────┘

27. uniqArray

uniqArray(arr)
数组去重统计元素个数

SELECT uniqArray([1,2,3,1,2,3]);
┌─uniqArray([1, 2, 3, 1, 2, 3])─┐
│                             3 │
└───────────────────────────────┘

SELECT uniq(arrayJoin([1,2,3,1,2,3]));
┌─uniq(arrayJoin([1, 2, 3, 1, 2, 3]))─┐
│                                   3 │
└─────────────────────────────────────┘

28. sumArray

sumArray(arr)
数组累加

SELECT sumArray([1,2,3,4,5]);
┌─sumArray([1, 2, 3, 4, 5])─┐
│                        15 │
└───────────────────────────┘

SELECT sum(arraySum([1,2,3,4,5]));
┌─sum(arraySum([1, 2, 3, 4, 5]))─┐
│                             15 │
└────────────────────────────────┘

29. arrayEnumerateDense

arrayEnumerateDense(arr)
返回与源数组大小相同的数组，指示每个元素首次出现在源数组中的位置

SELECT arrayEnumerateDense([10,20,20,10,30]) AS numArrEnumDense;
┌─numArrEnumDense─┐
│ [1,2,2,1,3]     │
└─────────────────┘

select arrayEnumerateDense([10,10,2,12,3,10,12,NULL,NULL]) as arrEnumDenseHasNull;
┌─arrEnumDenseHasNull─┐
│ [1,1,2,3,4,1,3,5,5] │
└─────────────────────┘

30. arrayIntersect

arrayIntersect(arr,……)
返回所有数组元素的交集
如果arr的数目只有一个，则返回它本身；如果有多个数组,则返回所有数组中元素的交集
最后得到的数组元素的顺序。（有什么影响吗？）

SELECT
arrayIntersect(['one','two'],['one','two','three']) as uniStrArr1;
┌─uniStrArr1────┐
│ ['two','one'] │
└───────────────┘

31. arrayReduce

arrayReduce(agg_func, arr1, …)
agg_func 为聚合函数，传入到数组当中。
将聚合函数应用于数组并返回其结果.如果聚合函数具有多个参数，则此函数可应用于相同大小的多个数组。

SELECT 
  arrayReduce('max', [1,2,3]) AS minNum,
  arrayReduce('min', [1,2,3]) AS maxNum,
  arrayReduce('sum', [1,2,3]) AS sumNum;

┌─minNum─┬─maxNum─┬─sumNum─┐
│      3 │      1 │      6 │
└────────┴────────┴────────┘

32. arrayFilter

过滤出数组中满足条件的数据

select arrayFilter(x->x%2=0, [1,2,3,4,5,6]) as result;
┌─result──┐
│ [2,4,6] │
└─────────┘
1
2
3
4
5
6
7
8
9
10
11
12



## 33. arrayStringConcat

将数组元素按照给定分隔符进行拼接，返回拼接后的字符串

```sql
SELECT arrayStringConcat( ['a','b','c'] , '-');
┌─arrayStringConcat(['a', 'b', 'c'], '-')─┐
│ a-b-c                                   │
└─────────────────────────────────────────┘




## 34. arrayCompact

对数组内数据实现相邻去重

```sql
SELECT arrayCompact([1, 2, 2, 3, 2, 3, 3]);
┌─arrayCompact([1, 2, 2, 3, 2, 3, 3])─┐
│ [1,2,3,2,3]                         │
└─────────────────────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16



## 35. runningDifference

计算某一列前后数值的差值

```sql
select a, runningDifference(a) from (SELECT arrayJoin( [1,3,7,13,21] ) as a,'u' as b);
┌──a─┬─runningDifference(a)─┐
│  1 │                    0 │
│  3 │                    2 │
│  7 │                    4 │
│ 13 │                    6 │
│ 21 │                    8 │
└────┴──────────────────────┘




## 36. arrayWithConstant

生成一个指定长度的数组

```sql
SELECT arrayWithConstant( 3, 'a');
┌─arrayWithConstant(3, 'a')─┐
│ ['a','a','a']             │
└───────────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28



## 37. neighbor

获取某一列前后相邻的数据，第二个参数控制前后相邻的距离


```sql
-- 获取当前行与前一行的值
SELECT a, neighbor(a,-1) from (SELECT arrayJoin([1,2,3,4,5]) as a,'u' as b);
┌─a─┬─neighbor(a, -1)─┐
│ 1 │               0 │
│ 2 │               1 │
│ 3 │               2 │
│ 4 │               3 │
│ 5 │               4 │
└───┴─────────────────┘

-- 获取当前行与后两行的值
SELECT a, neighbor(a,2) from (SELECT arrayJoin([1,2,3,4,5]) as a,'u' as b);
┌─a─┬─neighbor(a, 2)─┐
│ 1 │              3 │
│ 2 │              4 │
│ 3 │              5 │
│ 4 │              0 │
│ 5 │              0 │
└───┴────────────────┘