ClickHouse常见函数基本使用(二)

我在荒岛上迎接黎明，
太阳初升时，
忽然有十万支金喇叭齐鸣。
—王小波

13. 数组函数

1.数组非空判断相关函数（真为1，假为0）

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

2.数组长度 length() 返回数组中的元素个数。

**结果类型是UInt64。该函数也适用于字符串。

SELECT
-- length(), -- 出现异常
-- length([true, false]), -- 异常
-- length([1,2,,4]), --出现异常！
length([]), -- 0
length(['a','b','c']), -- 3
length([1,2,3]); -- 3

3.扩展判断非空的部分函数如下：不接受任何参数并返回适当类型的空数组

SELECT
emptyArrayUInt8(), -- UInt8的空数组
emptyArrayUInt16(),
emptyArrayUInt32(),
emptyArrayUInt64(),
emptyArrayDate(),
emptyArrayDateTime(),
emptyArrayInt8(),
emptyArrayInt16(),
emptyArrayInt32(),
emptyArrayInt64();
-- 接受一个空数组并返回一个仅包含一个默认值元素的数组。(以下是部分示例)
SELECT 
emptyArrayToSingle(emptyArrayInt32()), -- 0
emptyArrayToSingle(emptyArrayUInt32()), -- 0
emptyArrayToSingle(emptyArrayDate()), -- 0002-11-30
emptyArrayToSingle(emptyArrayDateTime()); --0002-11-30 08:00:00

4.生成一个含有N个元素的数组,元素从0开始增长，步长尾1.

range(N) 返回从0到N-1的数字数组。以防万一，如果在数据块中创建总长度超过100,000,000个元素的数组，则抛出异常

SELECT
range(10), -- [0,1,2,3,4,5,6,7,8,9]
range(2), -- [0,1]
-- range(5.5), -- 出现异常，N为Int8的数据类型，正整数
-- range(-10), -- 出现异常，DB::Exception: Illegal type Int8 of argument of function range
range(1); -- 0

5.新建一个数组的函数：array(x1,……) 类似于 直接[x1,……]

注意：新建数组的每个元素的数据类型需保持一致性。

SELECT
array(1,2,2,3,4) AS "array()函数",
-- [1,'hello',3], -- 出现异常，DB::Exception: There is no supertype for types UInt8, String, UInt8 because some of them are String/FixedString and some of them are not (version 19.10.1.5 (official build))
[1,2,3,4] AS "[ ]";

6.合并N个数组 arrayConcat(arrays) 合并参数中传递的所有数组

。跟java的数组差不多的合并，不会自动去重，不会自动排序

SELECT
arrayConcat(array(1,2),array(2,3),array(4,5)), -- [1,2,2,3,4,5]（第一种情况）
arrayConcat(array(1,1),array(2,2),array(3,3)), -- [1,1,2,2,3,3]
-- arrayConcat(array(1,2),['a','c'],array(3,3)), -- 出现异常，不能将不同类型的数组进行合并
arrayConcat(array(1,1),[2,3],array(4,5)); -- [1,1,2,3,4,5]

7.从数组arr中获取索引为“n”的元素

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

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

8.检查在数组中是否含有此元素

has(arr, elem) 包含此元素则返回1，否则返回0
has() 检查’arr’数组是否具有’elem’元素。如果元素不在数组中，则返回0;如果在，则返回1。
**hasAny(arr1, arr2) 返回1表示arr1和arr2存在交集。**否则返回0.
注意：特殊的定义：
① “NULL”作为数组中的元素值进行处理。
② 忽略两个数组中的元素值的顺序
hasAll(set, subset) 检查一个数组是否是另一个数组的子集。返回1，表示set包含subset中所有的元素
set – 具有一组元素的任何类型的数组。
subset – 任何类型的数组，其元素应该被测试为set的子集。
注意：特殊的定义：
① 空数组是任何数组的子集。
② “NULL”作为数组中的元素值进行处理。
③ 忽略两个数组中的元素值的顺序。

SELECT
has([1,2,3], 2), -- 1
has(array(1,2,3),2), -- 1
has([1,2,NULL], NULL), -- 1 (注意：null值的处理)
-- has([], 2), -- 出现异常，DB::Exception: Types of array and 2nd argument of function has must be identical up to nullability or numeric types or Enum and numeric type. Passed: Array(Nothing) and UInt8
has([1,2], 3); -- 0
SELECT
hasAll([], []), -- 1
hasAll([1,NULL,NULL], [NULL]), -- 1
hasAll([1,2,3], [1,2]), -- 1
hasAll([1,2,2,3], [2]), -- 1
hasAll(array(1,2,2,3), [2]), -- 1
hasAll([1,2,3], [4,5]); -- 0
-- 多重数组（如下的二维数组）。
SELECT hasAll([[1, 2], [3, 4]], [[1, 2], [3, 5]]); -- 0
SELECT
hasAny(array(1,2,3), array(1)), -- 1
hasAny(array(1,2,3), array(1,4,56,80)), -- 1
-- []与array()是一样的含义，本质上是一直的。只不过[]更加简便而已。
hasAny(array(), array()), -- 0
hasAny([],[]), -- 0
hasAny([1],[]), -- 0
-- 空数组跟null不是一样的对象
hasAny([1,NULL],[]), -- 0
hasAny([1,NULL],[NULL,2]);