数组函数和运算符
下标操作符：[]
描述：下标操作符用于访问数组中的元素，并从1开始建立索引。
select myarr[5] from (values array [1,4,6,78,8,9],array[2,4,6,8,10,12]) as t(myarr);
_col0
-------
8
10
Concatenation Operator : ||
|| 操作符用于将相同类型的数组或数值串联起来。
SELECT ARRAY[1] || ARRAY[2]; _col0 -------- [1, 2] (1 row) SELECT ARRAY[1] || 2; _col0 -------- [1, 2] (1 row) SELECT 2 || ARRAY[1]; _col0 -------- [2, 1] (1 row)
Array函数
下标运算符: []
下标运算符 [] 用于获取数组中对应位置的值。
SELECT ARRAY[5,3,41,6][1] AS first_element; -- 5
Concatenation Operator: ||
The || operator is used to concatenate an array with an array or an element of the same type:
SELECT ARRAY[1]||ARRAY[2];-- [1, 2] SELECT ARRAY[1]||2; -- [1, 2] SELECT 2||ARRAY[1];-- [2, 1]
- array_contains(x, element) → boolean
描述：如果数组x中包含element，则返回true。
select array_contains (array[1,2,3,34,4],4); -- true
- all_match(array(T), function(T, boolean)) → boolean
描述：返回是否数组的所有元素满足给定的断言函数。如果都满足断言函数或者数组为空时，返回true, 如果有一个或者多个元素不满足断言函数，则返回false。当断言函数对于一个或者多个元素的结果是NULL时，返回结果也是NULL：
select all_match(a, x-> true) from (values array[]) t(a); -- true select all_match(a, x-> x>2) from (values array[4,5,7]) t(a); -- true select all_match(a, x-> x>2) from (values array[1,5,7]) t(a); -- false select all_match(a, x-> x>1) from (values array[NULL, NULL ,NULL]) t(a); --NULL
- any_match(array(T), function(T, boolean)) → boolean
描述：返回数组是否存在满足断言的元素。当一个或多个元素满足断言时，返回true。都不满足断言或者数组为空时，返回false。当断言函数对于一个或者多个元素的结果是NULL时，返回结果也是NULL：
select any_match(a, x-> true) from (values array[]) t(a); -- false select any_match(a, x-> x>2) from (values array[0,1,2]) t(a); -- false select any_match(a, x-> x>2) from (values array[1,5,7]) t(a); -- true select any_match(a, x-> x>1) from (values array[NULL, NULL ,NULL]) t(a); -- NULL
- array_distinct(x) → array
描述：输出去重后的数组x。
select array_distinct(array [1,1,1,1,1,1,3,3,3,33,4,5,6,6,6,6]);-- [1, 3, 33, 4, 5, 6]
- array_intersect(x, y) → array
描述：返回两个数组去重后的交集。
select array_intersect(array [1,3,5,7,9],array [1,2,3,4,5]); _col0 ----------- [1, 3, 5] (1 row)
- array_union(x, y) → array
描述：返回两个数组的并集。
select array_union(array [1,3,5,7,9],array [1,2,3,4,5]); _col0 ----------------------- [1, 3, 5, 7, 9, 2, 4] (1 row) - array_except(x, y) → array
描述：返回去重后的在x中但不在y中的元素数组。
select array_except(array [1,3,5,7,9],array [1,2,3,4,5]); _col0 -------- [7, 9] (1 row)
- array_join(x, delimiter, null_replacement) → varchar
描述：使用分隔符来连接给定数组x的元素，并用可选字符替换x中的null值。
select array_join(array[1,2,3,null,5,6],'|','0');-- 1|2|3|0|5|6
- array_max(x) → x
描述：返回数组x的最大值。
select array_max(array[2,54,67,132,45]); -- 132
- array_min(x) → x
描述：返回数组x的最小值。
select array_min(array[2,54,67,132,45]); -- 2
- array_position(x,element) → bigint
描述：返回数组x中element第一次出现的位置，没找到则返回0。
select array_position(array[2,3,4,5,1,2,3],3); -- 2
- array_remove(x, element) → array
描述：移除数组x中的值为element的元素并返回。
select array_remove(array[2,3,4,5,1,2,3],3); -- [2, 4, 5, 1, 2]
- array_sort(x) → array
排序并返回数组x。 x的元素必须是可排序的。 空元素将放置在返回数组的末尾。
select array_sort(array[2,3,4,5,1,2,3]); -- [1, 2, 2, 3, 3, 4, 5]
- array_sort(array(T), function(T, T, int))
描述：根据给定的比较器函数对数组进行排序并返回。比较器将使用两个可为空的参数，表示数组的两个可为空的元素。当第一个可为空的元素小于，等于或大于第二个可为空的元素时，它将返回-1、0或1。如果比较器函数返回其他值（包括NULL），则查询将失败并引发错误。
SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); _col0 -------- [5, 3, 2, 2, 1] (1 row) SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); _col0 -------- [dc, bc, ab] (1 row) -- null值排在前，其余值按降序排列 SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], (x, y) -> CASE WHEN x IS NULL THEN -1 WHEN y IS NULL THEN 1 WHEN x < y THEN 1 WHEN x = y THEN 0 ELSE -1 END); _col0 -------- [null, null, 5, 3, 2, 2, 1] (1 row) -- null值在后的降序排列 SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], (x, y) -> CASE WHEN x IS NULL THEN 1 WHEN y IS NULL THEN -1 WHEN x < y THEN 1 WHEN x = y THEN 0 ELSE -1 END); _col0 -------- [5, 3, 2, 2, 1, null, null] (1 row) -- 按字符串长度排序 SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], (x, y) -> IF(length(x) < length(y), -1, IF(length(x) = length(y), 0, 1))); _col0 -------- [a, abc, abcd] (1 row) -- 按数组长度排序 SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], (x, y) -> IF(cardinality(x) < cardinality(y), -1, IF(cardinality(x) = cardinality(y), 0, 1))); _col0 -------- [[1, 2], [2, 3, 1], [4, 2, 1, 4]] (1 row) - arrays_overlap(x, y)
描述：如果两个数组有共同的非null元素，则返回true。否则返回false。
select arrays_overlap(array[1,2,3],array[3,4,5]);-- true select arrays_overlap(array[1,2,3],array[4,5,6]);-- false
- cardinality(x)
描述:返回数组x的容量。
select cardinality(array[1,2,3,4,5,6]); --6
- concat(array1, array2, ..., arrayN)
描述：此函数提供与sql标准连接运算符(||)相同的功能。
- combinations(array(T), n) -> array(array(T))
描述：返回输入数组的n个元素子组。 如果输入数组没有重复项，则组合将返回n个元素的子集。
SELECT combinations(ARRAY['foo', 'bar', 'baz'], 2); -- [[foo, bar], [foo, baz], [bar, baz]] SELECT combinations(ARRAY[1, 2, 3], 2); -- [[1, 2], [1, 3], [2, 3]] SELECT combinations(ARRAY[1, 2, 2], 2); -- [[1, 2], [1, 2], [2, 2]]
子组以及子组中的元素，虽未指明，都是有序的。参数n必须不大于5，且产生的子组个数最大不超过100000。
- contains(x, element)
描述：如果数组x中包含element，则返回true。
select contains(array[1,2,3,34,4],4); -- true
- element_at(array(E), index)
描述：返回给定索引处数组的元素。 如果index> 0，则此函数提供与SQL标准下标运算符（[]）相同的功能，但在访问大于数组长度的索引时该函数返回NULL，且下标运算符在这种情况下将失败。 如果index <0，则element_at从最后到第一个访问元素。
select element_at(array['a','b','c','d','e'],3); -- c
- filter(array(T), function(T, boolean)) -> array(T)
描述：删选出按函数运算结果为true的元素构成的数组。
SELECT filter(ARRAY [], x -> true); -- [] SELECT filter(ARRAY [5, -6, NULL, 7], x -> x > 0); -- [5, 7] SELECT filter(ARRAY [5, NULL, 7, NULL], x -> x IS NOT NULL); -- [5, 7]
- flatten(x)
描述：以串联的方式将array(array(T)) 展开为array(T)。
- ngrams(array(T), n) -> array(array(T))
描述：返回数组的n元语法（相邻n个元素的子序列）。结果中n元语法的顺序未指定。
SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 2); -- [[foo, bar], [bar, baz], [baz, foo]] SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 3); -- [[foo, bar, baz], [bar, baz, foo]] SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 4); -- [[foo, bar, baz, foo]] SELECT ngrams(ARRAY['foo', 'bar', 'baz', 'foo'], 5); -- [[foo, bar, baz, foo]] SELECT ngrams(ARRAY[1, 2, 3, 4], 2); -- [[1, 2], [2, 3], [3, 4]]
- none_match(array(T), function(T, boolean))
描述：返回数组是否没有元素与给定谓词匹配。如果没有元素与谓词匹配，则返回true（特殊情况是当数组为空时）。如果一个或多个元素匹配，则为false；如果谓词函数对一个或多个元素返回NULL，而对所有其他元素返回false，则为NULL。
- reduce(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R))
返回从数组减少的单个值。将按顺序为数组中的每个元素调用inputFunction。除了获取元素之外，inputFunction还获取当前状态，最初为initialState，然后返回新状态。将调用outputFunction将最终状态转换为结果值。它可能是恒等函数（i-> i）。
SELECT reduce(ARRAY [], 0, (s, x) -> s + x, s -> s); -- 0 SELECT reduce(ARRAY [5, 20, 50], 0, (s, x) -> s + x, s -> s); -- 75 SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + x, s -> s); -- NULL SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> s + COALESCE(x, 0), s -> s); -- 75 SELECT reduce(ARRAY [5, 20, NULL, 50], 0, (s, x) -> IF(x IS NULL, s, s + x), s -> s); -- 75 SELECT reduce(ARRAY [2147483647, 1], CAST (0 AS BIGINT), (s, x) -> s + x, s -> s); -- 2147483648 SELECT reduce(ARRAY [5, 6, 10, 20], CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)), (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)), s -> IF(s.count = 0, NULL, s.sum / s.count)); -- 10.25 - repeat(element, count)
描述：将element重复输出count次，填充到数组中。
select repeat(4,5);-- [4, 4, 4, 4, 4]
- reverse(x)
描述：以相反顺序将数组元素填充到返回的新数组中。
select reverse(array[1,2,3,4,5]); --[5, 4, 3, 2, 1]
- sequence(start, stop)
描述：输出一个从start开始，到stop结束的数组。start不大于stop时，每次递增1，否则，每次递减1。
start和stop的数据类型还可以是date或者timstamp类型，按1天递增或递减。
select sequence(5,1); _col0 ----------------- [5, 4, 3, 2, 1] (1 row) select sequence(5,10); _col0 --------------------- [5, 6, 7, 8, 9, 10] (1 row) - sequence(start, stop, step)→ array
描述：以步长step从start输出到stop。
select sequence(1,30,5);-- [1, 6, 11, 16, 21, 26]
- shuffle(x)→ array
描述：根据给的数组随机排列获得一个新的数组。
select shuffle(array[1,2,3,4,5]);-- [1, 5, 4, 2, 3] select shuffle(array[1,2,3,4,5]);-- [2, 1, 3, 5, 4]
- size(x) → bigint
描述：返回arrayx的容量。
select size(array[1,2,3,4,5,6]); --6
- slice(x, start, length) → array
描述：子集数组x从索引开头开始（如果start为负，则从结尾开始），长度为length。
select slice(array[1,2,3,4,5,6],2,3);-- [2, 3, 4]
- sort_array(x)
- trim_array(x, n) → array
描述：移除数组末尾n个元素。
SELECT trim_array(ARRAY[1, 2, 3, 4], 1); -- [1, 2, 3] SELECT trim_array(ARRAY[1, 2, 3, 4], 2); -- [1, 2]
- transform(array(T), function(T, U)) -> array(U)
描述：返回一个数组，该数组是将函数应用于数组的每个元素的结果。
SELECT transform(ARRAY [], x -> x + 1); -- [] SELECT transform(ARRAY [5, 6], x -> x + 1); -- [6, 7] SELECT transform(ARRAY [5, NULL, 6], x -> COALESCE(x, 0) + 1); -- [6, 1, 7] SELECT transform(ARRAY ['x', 'abc', 'z'], x -> x || '0'); -- [x0, abc0, z0] SELECT transform(ARRAY [ARRAY [1, NULL, 2], ARRAY[3, NULL]], a -> filter(a, x -> x IS NOT NULL)); -- [[1, 2], [3]]
- zip(array1, array2[, ...]) -> array(row)
描述：将给定数组按元素合并到单个行数组中。第N个自变量的第M个元素将是第M个输出元素的第N个字段。如果参数长度不均匀，则缺少的值将填充为NULL。
SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [{1, 1b}, {2, NULL}, {NULL, 3b}] - zip_with(array(T), array(U), function(T, U, R)) -> array(R)
描述：使用函数将两个给定的数组逐个元素合并到单个数组中。如果一个数组较短，则在应用函数之前，将在末尾添加空值以匹配较长数组的长度。
SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); -- [{a, 1}, {b, 3}, {c, 5}] SELECT zip_with(ARRAY[1, 2], ARRAY[3, 4], (x, y) -> x + y); -- [4, 6] SELECT zip_with(ARRAY['a', 'b', 'c'], ARRAY['d', 'e', 'f'], (x, y) -> concat(x, y)); -- [ad, be, cf] SELECT zip_with(ARRAY['a'], ARRAY['d', null, 'f'], (x, y) -> coalesce(x, y)); -- [a, null, f]
