Presto--数组函数和运算符

Dear 丶 2022-01-29 09:45 846阅读 0赞

# 数组函数和运算符 #

## 脚标运算符：\[\] ##

`[]`用于访问一个数组的元素，并从1开始索引：

SELECT  my_array [ 1 ]  AS  first_element

## 连接运算符：|| ##

`||`用来连接相同类型的数组或者元素：

SELECT  ARRAY  [ 1 ]  ||  ARRAY  [ 2 ];  -  [1,2] 
    SELECT  ARRAY  [ 1 ]  ||  2 ;  -  [1,2] 
    SELECT  2  ||  ARRAY  [ 1 ];   -  [2,1]

## 数组函数 ##

*  `array_distinct`（*x* ） →数组
    
    从数组中删除重复的值`x`。
 *  `array_intersect`（*x*，*y* ） →数组
    
    返回`x`和`y`的交集中的不重复元素。
 *  `array_union`（*x*，*y* ） →数组
    
    返回`x`和`y`的并集中的不重复元素。
 *  `array_except`（*x*，*y* ） →数组
    
    返回属于`x`但不属于`y`的不重复元素。（差集）
 *  `array_join`（*x*，*delimiter*，*null\_replacement* ） →varchar
    
    使用`delimiter`界定符连接`x`给定数组的元素并且用`null_replacement`可选字符串来填充数组里面的空值。
 *  `array_max`（*x* ） →x
    
    返回输入数组的最大值。
 *  `array_min`（*x* ） →x
    
    返回输入数组的最小值。
 *  `array_position`（*x*，*element*） →bigint
    
    返回数组`x`中第一次出现元素`element`的位置（数字）（如果未找到，则返回0）。
 *  `array_remove`（*x*，*元素*） →数组
    
    删除数组`x`中和`element`相同的所有元素。
 *  `array_sort`（*x* ） →数组
    
    排序`x`并返回数组。`x`的元素必须是可排序的。空元素将放置在返回数组的末尾。
 *  `array_sort`（*array（T）*，*function（T*，*T*，*int）） - > array（T* ）
    
    `array`根据给定的比较函数`function`排序并返回。比较函数采用两个可空的参数来表示`array`两个可以为空的元素。当第一个可空元素小于，等于或大于第二个可空元素时，它返回-1,0或1。如果比较函数返回其他值（包括`NULL`），则查询将失败并引发错误

SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- [5, 3, 2, 2, 1]
    SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- ['dc', 'bc', 'ab']
    SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null first with descending order
                      (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); -- [null, null, 5, 3, 2, 2, 1]
    SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null last with descending order
                      (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); -- [5, 3, 2, 2, 1, null, null]
    SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], -- sort by string length
                      (x, y) -> IF(length(x) < length(y),
                                   -1,
                                   IF(length(x) = length(y), 0, 1))); -- ['a', 'abc', 'abcd']
    SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], -- sort by array length
                      (x, y) -> IF(cardinality(x) < cardinality(y),
                                   -1,
                                   IF(cardinality(x) = cardinality(y), 0, 1))); -- [[1, 2], [2, 3, 1], [4, 2, 1, 4]]

*  `arrays_overlap`（*x*，*y* ） →布尔值
    
    测试数组x和y是否有任何共同的非空元素。如果没有公共的非空元素，但是两个数组都包含null，则返回null。。
 *  `cardinality`（*x* ） →bigint
    
    返回数组的基数（大小）。
 *  `concat`（*array1*，*array2*，*…*，*arrayN* ） →数组
    
    连接数组`array1`，`array2`，`...`，`arrayN`。此函数提供与SQL标准串联运算符（`||`）相同的功能。
 *  `contains`（*x*，*元素*） →布尔值
    
    如果数组`x`包含元素 `element`，则返回true。
 *  `element_at`（*数组（E）*，*索引*） →E
    
    返回`array`给定的元素是否在给定的索引`index`。如果`index`> 0，则此函数提供与SQL标准下标运算符（`[]`）相同的功能。如果`index`<0，则`element_at`从最后到第一个的元素访问到第一个元素。
 *  `filter`（*array（T）*，*function（T*，*boolean）） - > array（T* ）
    
    把函数`function`返回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* ） →数组  
    Flattens an `array(array(T))` to an `array(T)` by concatenating the contained arrays.
 *  `ngrams`(array(T), n) -> array(array(T))  
    Returns `n`\-grams for the `array`:

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]]

*  `reduce`(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R)) → R  
    Returns a single value reduced from `array`. `inputFunction` will be invoked for each element in `array` in order. In addition to taking the element, `inputFunction` takes the current state, initially `initialState`, and returns the new state. `outputFunction` will be invoked to turn the final state into the result value. It may be the identity function (`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], -- calculates arithmetic average: 10.25
                  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));

*  `repeat`（*元素*，*计数*） →数组
    
    重复元素`element`的`count`次数。
 *  `reverse`（*x* ） →数组
    
    返回一个具有相反数组顺序的数组`x`。
 *  `sequence`（*开始*，*停止） - >数组（bigint* ）
    
    从`start`to`stop` 生成一个整数序列，如果开始数小于等于停止数 每次自增1，否则自增 -1
 *  `sequence`（*开始*，*停止*，*步骤） - >数组（bigint* ）
    
    同上，根据指定步进值自增。
 *  `sequence`（*开始*，*停止） - >数组（日期*）
    
    生成`start`日期到`stop`日期的日期序列，如果`start`日期小于或等于`stop`日期，则按1天递增，否则按-1天递增。
 *  `sequence`（*开始*，*停止*，*步骤） - >数组（日期*）
    
    生成日期序列从`start`到`stop`，通过`step`递增。类型`step`可以是`INTERVAL DAY TO SECOND`或者`INTERVAL YEAR TO MONTH`
 *  `sequence`（*开始*，*停止*，*步骤） - >数组（时间戳*）
    
    生成时间戳序列，`start`到`stop`递增`step`。类型`step`可以是`INTERVAL DAY TO SECOND`或`INTERVAL YEAR TO MONTH`
 *  `shuffle`（*x* ） →数组
    
    打乱数组元素。
 *  `slice`（*x*，*start*，*length* ） →数组
    
    数组切片，和python\[::\]类似，如果start为负数，则从后面开始切。
 *  `transform`（*array（T）*，*function（T*，*U）） - > array（U* ）
    
    返回一个数组，该数组是应用`function`到每个元素的结果：

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*)
    
    Merges the given arrays, element-wise, into a single array of rows. The M-th element of the N-th argument will be the N-th field of the M-th output element. If the arguments have an uneven length, missing values are filled with `NULL`.
    
        SELECT zip(ARRAY[1, 2], ARRAY['1b', null, '3b']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]
 *  `zip_with`(*array(T)*, *array(U)*, *function(T*, *U*, *R)) -> array(R*)
    
    Merges the two given arrays, element-wise, into a single array using `function`. If one array is shorter, nulls are appended at the end to match the length of the longer array, before applying `function`:
    
        SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); -- [ROW('a', 1), ROW('b', 3), ROW('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']