更新时间:2024-07-02 GMT+08:00
分享

数组函数和运算符

下标操作符:[]

描述:下标操作符用于访问数组中的元素,并从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)

    参考array_sort(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]

相关文档