集合函数

最近更新时间:2024-05-11 14:37:42

我的收藏

ARRAY

函数语法:
ARRAY(<e1> T, ..., <en> T)
支持引擎:SparkSQL、Presto。
使用说明:根据给定元素生成数组
返回类型:array<T>。
示例:
> SELECT array(1, 2, 3);
[1,2,3]

FILTER

函数语法:
FILTER(<expr> array<T>, <predicate> function(T[, integer])->boolean)
支持引擎:SparkSQL。
使用说明:使用给定谓词过滤输入数组。
返回类型:array<T>。
示例:
> SELECT `filter`(array(1, 2, 3), x -> x % 2 == 1);
[1,3]
> SELECT `filter`(array(0, 2, 3), (x, i) -> x > i);
[2,3]
> SELECT `filter`(array(0, null, 2, 3, null), x -> x IS NOT NULL);
[0,2,3]

TRANSFORM

函数语法:
TRANSFORM(<expr> array<T>, <func> function(T[, integer])->U)
支持引擎:SparkSQL
使用说明:使用func变换数组中的元素。
返回类型:array<U>
示例:
> SELECT transform(array(1, 2, 3), x -> x + 1);
[2,3,4]
> SELECT transform(array(1, 2, 3), (x, i) -> x + i);
[1,3,5]

ZIP_WITH

函数语法:
ZIP_WITH(<left> array<T>, <right> array<U>, <func> function(T, U)->R)
支持引擎:SparkSQL。
使用说明:使用函数将两个给定数组按元素合并为单个数组。如果一个数组较短,则在应用函数之前,在末尾追加null以匹配较长数组的长度。
返回类型:array<R>。
示例:
> SELECT zip_with(array(1, 2, 3), array('a', 'b', 'c'), (x, y) -> (y, x));
[{"y":"a","x":1},{"y":"b","x":2},{"y":"c","x":3}]
> 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"]

FORALL

函数语法:
FORALL(<expr> array<T>, <pred> function(T)->boolean)
支持引擎:SparkSQL。
使用说明:测试谓词是否适用于数组中的所有元素。
返回类型:boolean。
示例:
> SELECT forall(array(1, 2, 3), x -> x % 2 == 0);
false
> SELECT forall(array(2, 4, 8), x -> x % 2 == 0);
true
> SELECT forall(array(1, null, 3), x -> x % 2 == 0);
false
> SELECT forall(array(2, null, 8), x -> x % 2 == 0);
NULL

AGGREGATE

函数语法:
AGGREGATE(<expr> array<T>, <start> U, <merge> function(U, T)->U, <finish> function(U)->R)
支持引擎:SparkSQL。
使用说明:聚合数组 expr 中的元素。
返回类型:R。
示例:
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x);
6
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x, acc -> acc * 10);
60

EXISTS

函数语法:
EXISTS(<expr> array<T>, <pred> function(T)->boolean)
支持引擎:SparkSQL。
使用说明:测试谓词是否适用于数组中的一个或多个元素。
返回类型:boolean。
示例:
> SELECT exists(array(1, 2, 3), x -> x % 2 == 0);
true> SELECT exists(array(1, 2, 3), x -> x % 2 == 10);
false
> SELECT exists(array(1, null, 3), x -> x % 2 == 0);
NULL
> SELECT exists(array(0, null, 2, 3, null), x -> x IS NULL);
true
> SELECT exists(array(1, 2, 3), x -> x IS NULL);
false

ARRAY_CONTAINS

函数语法:
ARRAY_CONTAINS(<expr> array<T>, <value> T)
支持引擎:SparkSQL、Presto。
使用说明:如果数组包含 value,则返回 true。
返回类型:boolean。
示例:
> SELECT array_contains(array(1, 2, 3), 2);
true

ARRAYS_OVERLAP

函数语法:
ARRAYS_OVERLAP(<a> array<T>, <b> array<U>)
支持引擎:SparkSQL、Presto。
使用说明:如果 a 至少包含 b 中也存在的非 null 元素,则返回 true。如果数组没有公共元素,并且它们都是非空的,并且其中任何一个包含 null 元素,则返回 null,否则返回 false。
返回类型:boolean。
示例:
> SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
true

ARRAY_INTERSECT

函数语法:
ARRAY_INTERSECT(<a> array<T>, <b> array<T>)
支持引:SparkSQL、Presto。
使用说:返回 a 和 b 相交处的元素数组,无重复项。
返回类:array<T>。
示例:
> SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
[1,3]

ARRAY_JOIN

函数语法:
ARRAY_JOIN(<a> ARRAY<T>, <delimiter> string[, <nullReplacement> string])
支持引擎:SparkSQL、Presto。
使用说明:使用分隔符和可选字符串连接给定数组的元素以替换 null。如果没有为 nullReplacement 设置值,则会过滤所有 null 值。
返回类型:string。
示例:
> SELECT array_join(array('hello', 'world'), ' ');
hello world
> SELECT array_join(array('hello', null ,'world'), ' ');
hello world
> SELECT array_join(array('hello', null ,'world'), ' ', ',');
hello , world

ARRAY_POSITION

函数语法:
ARRAY_POSITION(<a> array<T>, <element> T)
支持引擎:SparkSQL、Presto。
使用说明:返回数组第一个元素的(从1开始计数)索引。
返回类型:integer。
示例:
> SELECT array_position(array(3, 2, 1), 1);
3

ARRAY_SORT

函数语法:
ARRAY_SORT(<a> array<T>[, <func> function(T, T)->integer])
支持引擎:SparkSQL、Presto。
使用说明:对输入数组排序。如果省略 func,则按升序排序。
返回类型:array<T>。
示例:
> SELECT array_sort(array(5, 6, 1), (left, right) -> case when left < right then -1 when left > right then 1 else 0 end);
[1,5,6]
> SELECT array_sort(array('bc', 'ab', 'dc'), (left, right) -> case when left is null and right is null then 0 when left is null then -1 when right is null then 1 when left < right then 1 when left > right then -1 else 0 end);
["dc","bc","ab"]
> SELECT array_sort(array('b', 'd', null, 'c', 'a'));
["a","b","c","d",null]

ARRAY_EXCEPT

函数语法:
ARRAY_EXCEPT(<a> array<T>, <b> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:返回 a 中但不在 b 中的元素数组,不包含重复项。
返回类型:array<T>。
示例:
> SELECT array_except(array(1, 2, 3), array(1, 3, 5));
[2]

ARRAY_UNION

函数语法:
ARRAY_UNION(<a> array<T>, <b> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:返回 a 和 b 并集中的元素数组,不包含重复项。
返回类型:array<T>。
示例:
> SELECT array_union(array(1, 2, 3), array(1, 3, 5));
[1,2,3,5]

NAMED_STRUCT

函数语法:
NAMED_STRUCT(name1 K, val1 V, ...)
支持引擎:SparkSQL。
使用说明:使用给定的字段名和值创建 struct。
返回类型:struct。
示例:
> SELECT named_struct("a", 1, "b", 2, "c", 3);
{"a":1,"b":2,"c":3}

STRUCT

函数语法:
STRUCT(<col1> T1, <col2> T2, ...)
支持引擎:SparkSQL、Presto。
使用说明:使用给定的字段名和值创建 struct。
返回类型:struct。
示例:
> SELECT struct('a', 'b', 'c');
{"col1":"a","col2":"b","col3":"c"}

> SELECT struct('a', 'b', 'c', 1, 2);
{"col1":"a","col2":"b","col3":"c","col4":1,"col5":2}

SLICE

函数语法:
SLICE(<a> array<T>, <start> integer, <length> integer)
支持引擎:SparkSQL、Presto。
使用说明:返回数组 a 从索引 start 开始(数组索引从1开始,如果开始为负,则从结尾开始),长度为的具有指定长度 length 的子集。
返回类型:array<T>。
示例:
> SELECT slice(array(1, 2, 3, 4), 2, 2);
[2,3]
> SELECT slice(array(1, 2, 3, 4), -2, 2);
[3,4]

ARRAYS_ZIP

函数语法:
ARRAYS_ZIP(<a1> array<T>, ...)
支持引擎:SparkSQL。
使用说明:返回合并后的数组,元素为 struct 类型,其中第 N 个 struct 包含每个输入数组的第 N 个值。
返回类型:array<struct<string, T>>。
示例:
> SELECT arrays_zip(array(1, 2, 3), array(2, 3, 4));
[{"0":1,"1":2},{"0":2,"1":3},{"0":3,"1":4}]
> SELECT arrays_zip(array(1, 2), array(2, 3), array(3, 4));
[{"0":1,"1":2,"2":3},{"0":2,"1":3,"2":4}]

SORT_ARRAY

函数语法:
SORT_ARRAY(<a> array<T>[, ascendingOrder boolean])
支持引擎:SparkSQL、Presto。
使用说明:按升序或降序对输入数组排序。对于 double/float 类型,NaN 大于任何非 NaN 元素。Null 元素将按升序放置在返回数组的开头,或按降序放置在返回数组的末尾。
返回类型:array<T>。
示例:
> SELECT sort_array(array('b', 'd', null, 'c', 'a'), true);
[null,"a","b","c","d"]

SHUFFLE

函数语法:
SHUFFLE(<a> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:返回给定数组的随机排列。
返回类型:array<T>。
示例:
> SELECT shuffle(array(1, 20, 3, 5));
[3,1,5,20]
> SELECT shuffle(array(1, 20, null, 3));
[20,null,3,1]

ARRAY_MAX

函数语法:
ARRAY_MAX(<a> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:返回数组中的最大值。对于 double/float 类型,NaN 大于任何非 NaN 元素。跳过空元素。
返回类型:array<T>。
示例:
> SELECT array_max(array(1, 20, null, 3));
20

ARRAY_MIN

函数语法:
ARRAY_MIN(<a> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:返回数组中的最小值。对于 double/float 类型,NaN 大于任何非 NaN 元素。跳过空元素。
返回类型:array<T>。
示例:
> SELECT array_min(array(1, 20, null, 3));
1

FLATTEN

函数语法:
FLATTEN(<aa> array<array<T>>
支持引擎:SparkSQL、Presto。
使用说明:将二位数组转换为一维数组。
返回类型:array<T>。
示例:
> SELECT flatten(array(array(1, 2), array(3, 4)));
[1,2,3,4]

SEQUENCE

函数语法:
SEQUENCE(<start> integer|date|timestamp, end integer|date|timestamp[, step integer|interval])
支持引擎:SparkSQL、Presto
使用说明:生成从 start 到 end(包含 end)的数组,并逐步递增。返回元素的类型与 start 与 end 的类型相同。 start 和 stop 表达式必须解析为同一类型。如果开始和停止表达式解析为 date 或 timestamp 类型,则 step 必须解析为 interval 或 year-month interval 或 day-time interval 类型,否则解析为与 start 与 end 相同的类型。
返回类型: 与start相同
示例:
> SELECT sequence(1, 5);
[1,2,3,4,5]
> SELECT sequence(5, 1);
[5,4,3,2,1]
> SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
[2018-01-01,2018-02-01,2018-03-01]
> SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval '0-1' year to month);
[2018-01-01,2018-02-01,2018-03-01]

ARRAY_REPEAT

函数语法:
ARRAY_REPEAT(<element> T, <count> integer)
支持引擎:SparkSQL、Presto。
使用说明:返回包含 count 个 element 的数组。
返回类型:array<T>。
示例:
> SELECT array_repeat('123', 2);
["123","123"]

ARRAY_REMOVE

函数语法:
ARRAY_REMOVE(<a> array<T>, <element> T)
支持引擎:SparkSQL、Presto。
使用说明:从数组中删除所有等于元素的元素。
返回类型:array<T>。
示例:
> SELECT array_remove(array(1, 2, 3, null, 3), 3);
[1,2,null]

ARRAY_DISTINCT

函数语法:
ARRAY_DISTINCT(<a> array<T>)
支持引擎:SparkSQL、Presto。
使用说明:从数组中删除重复值。
返回类型:array<T>。
示例:
> SELECT array_distinct(array(1, 2, 3, null, 3));
[1,2,3,null]

ELEMENT_AT

函数语法:
ELEMENT_AT(<a> array<T>, <index> integer)
ELEMENT_AT(<m> map<K, V>, <key> K)
支持引擎:SparkSQL、Presto
使用说明:返回给定(从1开始计数)索引处的数组元素或返回给定键的值。
返回类型:T,V
示例:
> SELECT element_at(array(1, 2, 3), 2);
2
> SELECT element_at(map(1, 'a', 2, 'b'), 2);
b

MAP

函数语法:
MAP(<k1> K, <v1> V, ...)
支持引擎:SparkSQL、Presto。
使用说明:根据给定元素生成 map。
返回类型:MAP<K, V>。
示例:
> SELECT map(1.0, '2', 3.0, '4');
{1.0:"2",3.0:"4"}

MAP_FROM_ARRAYS

函数语法:
MAP_FROM_ARRAYS(<keys> array<K>, <values> array<V>)
支持引擎:SparkSQL、Presto。
使用说明:使用一对给定的 key/value 数组创建 map。keys 中的所有元素都不应为 null。
返回类型:map<K, V>。
示例:
> SELECT map_from_arrays(array(1.0, 3.0), array('2', '4'));
{1.0:"2",3.0:"4"}

MAP_KEYS

函数语法:
MAP_KEYS(<m> map<K, V>)
支持引擎:SparkSQL、Presto。
使用说明:返回包含 map 键的无序数组。
返回类型:array<K>。
示例:
> SELECT map_keys(map(1, 'a', 2, 'b'));
[1,2]

MAP_VALUES

函数语法:
MAP_VALUES(<m> map<K, V>)
支持引擎:SparkSQL、Presto。
使用说明:返回包含 map 值的无序数组。
返回类型:array<V>。
示例:
> SELECT map_values(map(1, 'a', 2, 'b'));
["a","b"]

MAP_ENTRIES

函数语法:
MAP_ENTRIES(<m> map<K, V>)
支持引擎:SparkSQL、Presto。
使用说明:返回给定 map 中所有项的无序数组。
返回类型:array<struct<K, V>>。
示例:
> SELECT map_entries(map(1, 'a', 2, 'b'));
[{"key":1,"value":"a"},{"key":2,"value":"b"}]

MAP_FROM_ENTRIES

函数语法:
MAP_FROM_ENTRIES(<entries> array<struct<K, V>>)
支持引擎:SparkSQL、Presto。
使用说明:返回从给定的条目数组创建的映射。
返回类型:map<K, V>。
示例:
> SELECT map_from_entries(array(struct(1, 'a'), struct(2, 'b')));
{1:"a",2:"b"}

MAP_CONCAT

函数语法:
MAP_CONCAT(map1 map<K, V>, ...)
支持引擎:SparkSQL、Presto。
使用说明:返回所有给定映射的并集。
返回类型:map<K, V>。
示例:
> SELECT map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));
{1:"a",2:"b",3:"c"}

MAP_FILTER

函数语法:
MAP_FILTER(<m> map<K, V>, <func> function(K, V)->boolean)
支持引擎:SparkSQL。
使用说明:使用 func 过滤 m 中的条目。
返回类型:map<K, V>。
示例:
> SELECT map_filter(map(1, 0, 2, 2, 3, -1), (k, v) -> k > v);
{1:0,3:-1}

MAP_ZIP_WITH

函数语法:
MAP_ZIP_WITH(<map1> map<K, V1>, <map2> map<K, V2>, <func> function(K, V1, V2)->R)
支持引擎:SparkSQL。
使用说明:通过 func 将两个给定映射合并为单个映射。对于仅在一个映射中显示的键,值将被置为 NULL。如果输入映射包含重复键,则仅将重复键的第一个条目传递给 func。
返回类型:MAP<K, R>。
示例:
> SELECT map_zip_with(map(1, 'a', 2, 'b'), map(1, 'x', 2, 'y'), (k, v1, v2) -> concat(v1, v2));
{1:"ax",2:"by"}

TRANSFORM_KEYS

函数语法:
TRANSFORM_KEYS(<m> map<K, V>, <func> function(K, V)->R)
支持引擎:SparkSQL。
使用说明:使用 func 变换 map 中的 keys。
返回类型:map<R, V>。
示例:
> SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + 1);
{2:1,3:2,4:3}
> SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + v);
{2:1,4:2,6:3}

TRANSFORM_VALUES

函数语法:
TRANSFORM_VALUES(<m> map<K, V>, <func> function(K, V)->R)
支持引擎:SparkSQL。
使用说明:使用 func 变换 map 中的 values。
返回类型:map<K, R>。
示例:
> SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> v + 1);
{1:2,2:3,3:4}
> SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -> k + v);
{1:2,2:4,3:6}

SIZE

函数语法:
SIZE(<expr> array<T>|map<K, V>)
支持引擎:SparkSQL、Presto。
使用说明:返回数组或映射的大小。
返回类型:integer。
示例:
> SELECT size(array('b', 'd', 'c', 'a'));
4
> SELECT size(map('a', 1, 'b', 2));
2
> SELECT size(NULL);
-1

CARDINALITY

函数语法:
CARDINALITY(<expr> array<T>|map<K, V>)
支持引擎:SparkSQL、Presto。
使用说明:返回数组或映射的大小。
返回类型:integer。
示例:
> SELECT cardinality(array('b', 'd', 'c', 'a'));
4
> SELECT cardinality(map('a', 1, 'b', 2));
2
> SELECT cardinality(NULL);
-1

ANY_MATCH / FORALL

函数语法:
ANY_MATCH(<expr> array<T>, x -> lambda(x))
FORALL(<expr> array<T>, x -> lambda(x))
支持引擎:SparkSQL。
使用说明:对数组的每个元素,依次执行lambda表达式,只有1个返回true则函数返回true,否则返回false。
如果数组中有元素为NULL,则返回NULL。
返回类型:boolean
示例:
> SELECT any_match(array(1, 2, 3), x -> x % 2 == 0);
false
> SELECT any_match(array(2, 4, 8), x -> x % 2 == 0);
true
> SELECT any_match(array(1, null, 3), x -> x % 2 == 0);
NULL