用了一段时间 clickhouse，真不愧是 OLAP 的神器，提供的函数非常丰富多彩，可以满足各种各样的需求，今天我们就来介绍几个跟 Array 相关的函数。
range
见名知意，它就是用来生成一个数字序列的数组：
vm-10-183-120-218-centos :) select range(10);
SELECT range(10)
Query id: 91e3d3fd-78c3-4ef9-b0b2-ab4b4d6344eb
┌─range(10)─────────────┐│ [0,1,2,3,4,5,6,7,8,9] │└───────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
复制代码
更其他编程语言提供的函数一样，该函数调用也可以指定上界、下界、步长：
vm-10-183-120-218-centos :) select range(100,200,10);
SELECT range(100, 200, 10)
Query id: 4198444c-81b5-4187-9466-0d419afbb101
┌─range(100, 200, 10)───────────────────────┐│ [100,110,120,130,140,150,160,170,180,190] │└───────────────────────────────────────────┘
1 rows in set. Elapsed: 0.001 sec.
复制代码
该例子生成一个上界为 100，下界为 200（不包含），步长为 10 的数字序列。
arrayMap
咋一看，range 函数也没啥用啊，不就是生成一个数字序列，实际生产活动中貌似用不到。但是某一天突然蹦出一个需求：怎么初始化一个长度为 n，所有值都为 0 的数组呢？找了一遍，clickhouse 并没有提供这样的函数，那怎么实现呢？上面的 range 函数就用上了，还有我们现在要介绍的 arrayMap：
vm-10-183-120-218-centos :) select arrayMap(x -> (x * 0), range(10));
SELECT arrayMap(x -> (x * 0), range(10))
Query id: 4e6ed63e-93ed-44c3-a4d9-c3a3d90565c1
┌─arrayMap(lambda(tuple(x), multiply(x, 0)), range(10))─┐│ [0,0,0,0,0,0,0,0,0,0] │└───────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 9.676 sec.
复制代码
arrayMap 函数将 lamdba 表达式作用于数组中每个元素生成新的数组。该例子中首先调用 range(10)生成 0~10 的序列，然后调用 arrayMap 函数使数组中每个元素乘以 0，从而完成数组的初始化。
arrayConcat
如果表中某个字段 a 是长度小于 n 的整数数组，各行数组长度不一，想把数组用 0 补齐为长度为 n 的数组怎么办呢？用 arrayConcat：
vm-10-183-120-218-centos :) select arrayConcat([9,8,7] as a, arrayMap(x -> (x * 0), range(toUInt8(10 - length(a)))));
SELECT arrayConcat([9, 8, 7] AS a, arrayMap(x -> (x * 0), range(toUInt8(10 - length(a)))))
Query id: bf6da8ae-880e-49bd-b17c-bf083b8d3e93
┌─arrayConcat([9, 8, 7], arrayMap(lambda(tuple(x), multiply(x, 0)), range(toUInt8(minus(10, length([9, 8, 7]))))))─┐│ [9,8,7,0,0,0,0,0,0,0] │└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
复制代码
arrayConcat 将两个数合并成一个数组。我们的例子中把数组[9,8,7]用 0 补到长度为 10, arrayConcat 第二个参数中的数组就是用我们 2 中介绍的方法生成。
arrayEnumerate
该函数生成数组的下标数组：
vm-10-183-120-218-centos :) select [2,4,6] as a, arrayEnumerate(a) as row_num;
SELECT [2, 4, 6] AS a, arrayEnumerate(a) AS row_num
Query id: 35051c9c-3d8d-48d8-b646-b2ee2776e0ae
┌─a───────┬─row_num─┐│ [2,4,6] │ [1,2,3] │└─────────┴─────────┘
1 rows in set. Elapsed: 0.001 sec.
复制代码
arrayJoin
怎么把数组的每个元素转成一列分布到多行呢？arrayJoin 函数可以满足这个需求：
vm-10-183-120-218-centos :) select arrayJoin([2,4,6]);
SELECT arrayJoin([2, 4, 6])
Query id: ffed1183-7ebf-4c43-8a92-52beda88f22a
┌─arrayJoin([2, 4, 6])─┐│ 2 ││ 4 ││ 6 │└──────────────────────┘
3 rows in set. Elapsed: 0.001 sec.
复制代码
看到这里，会进一步想：怎么把行号也加上呢？这样做：
vm-10-183-120-218-centos :) select [2,4,6] as a, arrayJoin(arrayEnumerate(a)) as b, a[b];
SELECT [2, 4, 6] AS a, arrayJoin(arrayEnumerate(a)) AS b, a[b]
Query id: f37ecdb4-04a6-4c8d-bf65-815411faa28e
┌─a───────┬─b─┬─arrayElement([2, 4, 6], arrayJoin(arrayEnumerate([2, 4, 6])))─┐│ [2,4,6] │ 1 │ 2 ││ [2,4,6] │ 2 │ 4 ││ [2,4,6] │ 3 │ 6 │└─────────┴───┴───────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.001 sec.
复制代码
先用 arrayEnumerate(a)获取数组的下标，然后每个下标与原数组做 join，最后按行号从数组里面取数组元素。有没有发现，arrayJoin 和我们平常 sql 中的 join 语义是一样的，非常惊喜有没有！
都到这里了，我们再来个难度高一点的。假设有一个表 a：
现在想同时统计 c1 列有多少个元素、所有数组的元素个数。直接上代码：
vm-10-183-120-218-centos :) select count(), countIf(num = 1) arrayJoin([[2,4,6],[1,3,5]]) as a, arrayJoin(arrayEnumerate(a)) as b;
Syntax error: failed at position 43 ('('):
select count(), countIf(num = 1) arrayJoin([[2,4,6],[1,3,5]]) as a, arrayJoin(arrayEnumerate(a)) as b;
Expected one of: UNION, LIMIT, WHERE, HAVING, GROUP BY, INTO OUTFILE, OFFSET, PREWHERE, Comma, ORDER BY, SETTINGS, FROM, FORMAT, WITH, token
vm-10-183-120-218-centos :) select count(), countIf(b = 1) from (select arrayJoin([[2,4,6],[1,3,5]]) as a, arrayJoin(arrayEnumerate(a)) as b);
SELECT count(), countIf(b = 1)FROM ( SELECT arrayJoin([[2, 4, 6], [1, 3, 5]]) AS a, arrayJoin(arrayEnumerate(a)) AS b)
Query id: 4752fd1c-a4fa-472c-9245-d5e02f019fe1
┌─count()─┬─countIf(equals(b, 1))─┐│ 6 │ 2 │└─────────┴───────────────────────┘
1 rows in set. Elapsed: 0.016 sec.
复制代码
这里 arrayJoin([[2,4,6],[1,3,5]])是为了模拟包含多行的表，arrayJoin(arrayEnumerate(a))生成每个数组的行号后让行号与对应的数组做 join，结果是这样的：
┌─a───────┬─b─┐│ [2,4,6] │ 1 ││ [2,4,6] │ 2 ││ [2,4,6] │ 3 ││ [1,3,5] │ 1 ││ [1,3,5] │ 2 ││ [1,3,5] │ 3 │└─────────┴───┘
复制代码
接下来分别统计 count 和行号的 1 的 count 即可。
要实现该功能还有一种写法：
SELECT count(), countIf(b = 1)FROM (select arrayJoin([[1,3,5], [2,4,6]]) as a)ARRAY JOIN a, arrayEnumerate(a) AS b;
复制代码
arrayMax
求数组中的最大值：
vm-10-183-120-218-centos :) select arrayMax([2, 11, 7]);
SELECT arrayMax([2, 11, 7])
Query id: f1594905-ff56-474d-a515-a31679acb3d7
┌─arrayMax([2, 11, 7])─┐│ 11 │└──────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
复制代码
arrayReduce
clickhouse 提供了 arrayMax 求数组的最大值，需求来了：怎么得到一个整数数组的中位数呢？介绍一个非常有用的聚合函数 arrayReduce：
vm-10-183-120-218-centos :) select arrayReduce('median', [2, 11, 7]);
SELECT arrayReduce('median', [2, 11, 7])
Query id: 30ed3581-7c89-4b54-874b-f64c62b65417
┌─arrayReduce('median', [2, 11, 7])─┐│ 7 │└───────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
复制代码
这里的第一个参数可以是任何存在的聚合函数名称，强大吧！
就介绍到这里，想了解更多参考：https://clickhouse.com/docs/en/sql-reference/functions/array-functions/
评论