数据湖计算 DLC 集合函数-SQL 语法-文档中心-腾讯云

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
﻿
集合函数

本页目录：

ARRAY

FILTER

TRANSFORM

ZIP_WITH

FORALL

AGGREGATE

EXISTS

ARRAY_CONTAINS

ARRAYS_OVERLAP

ARRAY_INTERSECT

ARRAY_JOIN

ARRAY_POSITION

ARRAY_SORT

ARRAY_EXCEPT

ARRAY_UNION

NAMED_STRUCT

STRUCT

SLICE

ARRAYS_ZIP

SORT_ARRAY

SHUFFLE

ARRAY_MAX

ARRAY_MIN

FLATTEN

SEQUENCE

ARRAY_REPEAT

ARRAY_REMOVE

ARRAY_DISTINCT

ELEMENT_AT

MAP

MAP_FROM_ARRAYS

MAP_KEYS

MAP_VALUES

MAP_ENTRIES

MAP_FROM_ENTRIES

MAP_CONCAT

MAP_FILTER

MAP_ZIP_WITH

TRANSFORM_KEYS

TRANSFORM_VALUES

SIZE

CARDINALITY

ANY_MATCH / FORALL
