数组函数和运算符¶

下标运算符: []¶

使用 [] 运算符访问数组元素，从 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]

数组函数¶

all_match(array(T), function(T,boolean)) -> boolean()¶

返回数组中所有元素是否与给定谓词匹配。如果所有元素与谓词匹配（特殊情况是数组为空），则返回 true；如果一个或多个元素不匹配，则返回 false；如果谓词函数对一个或多个元素返回 NULL 且对所有其他元素返回 true，则返回 NULL。

any_match(array(T), function(T,boolean)) -> boolean()¶

返回数组中是否存在与给定谓词匹配的元素。如果一个或多个元素与谓词匹配，则返回 true；如果所有元素都不匹配（特殊情况是数组为空），则返回 false；如果谓词函数对一个或多个元素返回 NULL 且对所有其他元素返回 false，则返回 NULL。

array_average(array(double)) -> double()¶

返回 array 中所有非空元素的平均值。如果没有非空元素，则返回 null。

array_cum_sum(array(T)) -> array(T)¶

返回一个数组，该数组的元素是输入数组的累积和，即 result[i] = input[1]+input[2]+…+input[i]。如果数组中有空元素，则该元素之后的累积和为 null。

SELECT array_cum_sum(ARRAY [1, 2, null, 3]) -- array[1, 3, null, null]

array_distinct(x) -> array()¶

从数组 x 中删除重复值。此函数使用 IS DISTINCT FROM 来确定不同的元素。

SELECT array_distinct(ARRAY [1, 2, null, null, 2]) -- ARRAY[1, 2, null]
SELECT array_distinct(ARRAY [ROW(1, null), ROW (1, null)] -- ARRAY[ROW(1, null)

array_duplicates(array(T)) -> array(bigint/varchar)¶

返回 array 中出现次数超过一次的元素集合。如果任何元素是包含空值的行或数组，则会抛出异常。

SELECT array_duplicates(ARRAY[1, 2, null, 1, null, 3]) -- ARRAY[1, null]
SELECT array_duplicates(ARRAY[ROW(1, null), ROW(1, null)]) -- "map key cannot be null or contain nulls"

array_except(x, y) -> array()¶

返回一个数组，其中包含在 x 中但在 y 中不存在的元素，不包含重复项。此函数使用 IS NOT DISTINCT FROM 来确定哪些元素是相同的。

SELECT array_except(ARRAY[1, 3, 3, 2, null], ARRAY[1,2, 2, 4]) -- ARRAY[3, null]

array_frequency(array(E)) -> map(E, int)¶

返回一个映射：键是 array 中的唯一元素，值是键出现的次数。忽略空元素。空数组返回空映射。

array_has_duplicates(array(T)) -> boolean()¶

返回一个布尔值：array 是否有任何元素出现超过一次。如果任何元素是包含空值的行或数组，则会抛出异常。

SELECT array_has_duplicates(ARRAY[1, 2, null, 1, null, 3]) – true SELECT array_has_duplicates(ARRAY[ROW(1, null), ROW(1, null)]) – “map key cannot be null or contain nulls”

array_intersect(x, y) -> array()¶

返回 x 和 y 交集中的元素数组，不包含重复元素。此函数使用 IS NOT DISTINCT FROM 来确定哪些元素相同。

SELECT array_intersect(ARRAY[1, 2, 3, 2, null], ARRAY[1,2, 2, 4, null]) -- ARRAY[1, 2, null]

array_intersect(array(array(E))) -> array(E)¶

返回给定数组中所有数组交集中的元素数组，不包含重复元素。此函数使用 IS NOT DISTINCT FROM 来确定哪些元素相同。

SELECT array_intersect(ARRAY[ARRAY[1, 2, 3, 2, null], ARRAY[1,2,2, 4, null], ARRAY [1, 2, 3, 4 null]])  -- ARRAY[1, 2, null]

array_join(x, delimiter, null_replacement) -> varchar()¶

使用分隔符和可选字符串将给定数组的元素连接在一起，以替换空值。

array_least_frequent(array(T)) -> array(T)¶

返回数组中最不频繁的非空元素。如果有多个元素具有相同的频率，则函数将返回最小的元素。如果数组包含多个元素，并且任何元素都是包含空字段的 ROWS 或包含空元素的 ARRAYS，则会返回异常。

SELECT array_least_frequent(ARRAY[1, 0 , 5])  -- ARRAY[0]
select array_least_frequent(ARRAY[1, null, 1]) -- ARRAY[1]
select array_least_frequent(ARRAY[ROW(1,null), ROW(1, null)]) -- "map key cannot be null or contain nulls"

array_least_frequent(array(T), n) -> array(T)¶

返回数组中最不频繁的 n 个非空元素。这些元素按频率升序排列。如果两个元素具有相同的频率，则较小的元素将先出现。如果数组包含多个元素，并且任何元素都是包含空字段的 ROWS 或包含空元素的 ARRAYS，则会返回异常。

SELECT array_least_frequent(ARRAY[3, 2, 2, 6, 6, 1, 1], 3) -- ARRAY[3, 1, 2]
select array_least_frequent(ARRAY[1, null, 1], 2) -- ARRAY[1]
select array_least_frequent(ARRAY[ROW(1,null), ROW(1, null)], 2) -- "map key cannot be null or contain nulls"

array_max(x) -> x()¶

返回输入数组的最大值。

array_min(x) -> x()¶

返回输入数组的最小值。

array_max_by(array(T), function(T, U)) -> T()¶

将提供的函数应用于每个元素，并返回产生最大值的元素。 U 可以是任何可排序类型。

SELECT array_max_by(ARRAY ['a', 'bbb', 'cc'], x -> LENGTH(x)) -- 'bbb'

array_min_by(array(T), function(T, U)) -> T()¶

将提供的函数应用于每个元素，并返回产生最小值的元素。 U 可以是任何可排序类型。

SELECT array_min_by(ARRAY ['a', 'bbb', 'cc'], x -> LENGTH(x)) -- 'a'

array_normalize(x, p) -> array()¶

通过将每个元素除以数组的 p 范数来规范化数组 x。它等效于 TRANSFORM(array, v -> v / REDUCE(array, 0, (a, v) -> a + POW(ABS(v), p), a -> POW(a, 1 / p))，但 reduce 部分只执行一次。如果数组为空或包含空数组元素，则返回空值。

array_position(x, element) -> bigint()¶

返回 element 在数组 x 中首次出现的位置（如果未找到，则返回 0）。

array_position(x, element, instance) -> bigint()¶

如果 instance > 0，则返回 element 在数组 x 中第 instance 次出现的位置。如果 instance < 0，则返回 element 在数组 x 中倒数第 instance 次出现的位置。如果未找到匹配的元素实例，则返回 0。

array_remove(x, element) -> array()¶

从数组 x 中删除所有等于 element 的元素。

array_sort(x) -> array()¶

对数组 x 进行排序并返回。 x 的元素必须可排序。空元素将放在返回数组的末尾。

array_sort(array(T), function(T, T, int)) -> array(T)¶

根据给定的比较器 function 对 array 进行排序并返回。比较器将采用两个可空参数，分别代表 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]]

array_sort_desc(x) -> array()¶

返回按降序排序的 array。 array 的元素必须可排序。空元素将放在返回数组的末尾。

SELECT array_sort_desc(ARRAY [100, 1, 10, 50]); -- [100, 50, 10, 1]
SELECT array_sort_desc(ARRAY [null, 100, null, 1, 10, 50]); -- [100, 50, 10, 1, null, null]
SELECT array_sort_desc(ARRAY [ARRAY ["a", null], null, ARRAY ["a"]); -- [["a", null], ["a"], null]

array_split_into_chunks(array(T), int) -> array(array(T))¶

返回一个 array 数组，将输入 array 分成给定长度的块。如果数组的长度不是块长度的整数倍，则最后一个块将比块长度短。忽略空输入，但不忽略元素。

SELECT array_split_into_chunks(ARRAY [1, 2, 3, 4], 3); – [[1, 2, 3], [4]] SELECT array_split_into_chunks(null, null); – null SELECT array_split_into_chunks(array[1, 2, 3, cast(null as int)], 2]); – [[1, 2], [3, null]]

array_sum(array(T)) -> bigint/double()¶

返回 array 中所有非空元素的总和。如果没有非空元素，则返回 0。行为类似于聚合函数 sum()。

T 必须可以强制转换为 double。如果 T 可以强制转换为 bigint，则返回 bigint。否则，返回 double。

array_top_n(array(T), int) -> array(T)¶

根据其自然降序，返回给定 array 中的前 n 个元素的数组。如果 n 小于给定 array 的大小，则返回的列表将与输入的大小相同，而不是 n。

SELECT array_top_n(ARRAY [1, 100, 2, 5, 3], 3); -- [100, 5, 3]
SELECT array_top_n(ARRAY [1, 100], 5); -- [100, 1]
SELECT array_top_n(ARRAY ['a', 'zzz', 'zz', 'b', 'g', 'f'], 3); -- ['zzz', 'zz', 'g']

arrays_overlap(x, y) -> boolean()¶

测试数组 x 和 y 是否有共同的非空元素。如果两个数组都没有共同的非空元素，但其中一个数组包含空值，则返回空值。如果 ROW 或 ARRAY 类型的元素包含空值，则抛出 NOT_SUPPORTED 异常。

SELECT arrays_overlap(ARRAY [1, 2, null], ARRAY [2, 3, null]) -- true
SELECT arrays_overlap(ARRAY [1, 2], ARRAY [3, 4]) -- false
SELECT arrays_overlap(ARRAY [1, null], ARRAY[2]) -- null
SELECT arrays_overlap(ARRAY[ROW(1, null)], ARRAY[1, 2]) -- "ROW comparison not supported for fields with null elements"

array_union(x, y) -> array()¶

返回 x 和 y 的并集的元素数组，不包含重复项。此函数使用 IS NOT DISTINCT FROM 来确定哪些元素相同。

SELECT array_union(ARRAY[1, 2, 3, 2, null], ARRAY[1,2, 2, 4, null]) -- ARRAY[1, 2, 3, 4 null]

cardinality(x) -> bigint()¶

返回数组 x 的基数（大小）。

concat(array1, array2, ..., arrayN) -> array()

连接数组 array1、array2、...、arrayN。此函数提供了与 SQL 标准连接运算符（||）相同的功能。

combinations(array(T), n) -> array(array(T))¶

返回输入数组的 n 元素组合。如果输入数组没有重复项，则 combinations 返回 n 元素子集。子组的顺序是确定的，但未指定。子组内元素的顺序是确定的，但未指定。 n 必须不大于 5，并且生成的子组的总大小必须小于 100000

SELECT combinations(ARRAY['foo', 'bar', 'boo'],2); --[['foo', 'bar'], ['foo', 'boo']['bar', 'boo']]
SELECT combinations(ARRAY[1,2,3,4,5],3); --[[1,2,3], [1,2,4], [1,3,4], [2,3,4]]
SELECT combinations(ARRAY[1,2,2],2); --[[1,2],[1,2],[2,2]]

contains(x, element) -> boolean()¶

如果数组 x 包含 element，则返回 true。

element_at(array(E), index) -> E()¶

返回给定 index 处的 array 元素。如果 index > 0，此函数提供了与 SQL 标准下标运算符（[]）相同的功能。如果 index < 0，则 element_at 从最后一个元素访问元素到第一个元素。

filter(array(T), function(T, boolean)) -> array(T)¶

从 array 中的那些元素构造一个数组，这些元素的 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) -> array()¶

通过连接包含的数组，将 array(array(T)) 展平为 array(T)。

find_first(array(E), function(T,boolean)) -> E()¶

返回 array 中第一个对 function(T,boolean) 返回 true 的元素，如果返回的元素为 NULL，则抛出异常。如果不存在这样的元素，则返回 NULL。

find_first(array(E), index, function(T,boolean)) -> E()¶

返回 array 中第一个对 function(T,boolean) 返回 true 的元素，如果返回的元素为 NULL，则抛出异常。如果不存在这样的元素，则返回 NULL。如果 index > 0，则从位置 index 开始搜索元素，直到数组的末尾。如果 index < 0，则从最后一个位置 abs(index) 开始搜索元素，直到数组的开头。

SELECT find_first(ARRAY[3, 4, 5, 6], 2, x -> x > 0); -- 4
SELECT find_first(ARRAY[3, 4, 5, 6], -2, x -> x > 0); -- 5
SELECT find_first(ARRAY[3, 4, 5, 6], 2, x -> x < 4); -- NULL
SELECT find_first(ARRAY[3, 4, 5, 6], -2, x -> x > 5); -- NULL

find_first_index(array(E), function(T,boolean)) -> BIGINT()¶

返回 array 中第一个对 function(T,boolean) 返回 true 的元素的索引。如果不存在这样的元素，则返回 NULL。

find_first_index(array(E), index, function(T,boolean)) -> BIGINT()¶

返回 array 中第一个对 function(T,boolean) 返回 true 的元素的索引。如果不存在这样的元素，则返回 NULL。如果 index > 0，则从位置 index 开始搜索元素，直到数组的末尾。如果 index < 0，则从最后一个位置 abs(index) 开始搜索元素，直到数组的开头。

SELECT find_first(ARRAY[3, 4, 5, 6], 2, x -> x > 0); -- 2
SELECT find_first(ARRAY[3, 4, 5, 6], -2, x -> x > 0); -- 3
SELECT find_first(ARRAY[3, 4, 5, 6], 2, x -> x < 4); -- NULL
SELECT find_first(ARRAY[3, 4, 5, 6], -2, x -> x > 5); -- NULL

ngrams(array(T), n) -> array(array(T))¶

返回 array 的 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)) -> boolean()¶

返回数组中是否没有元素与给定的谓词匹配。如果数组中没有元素与谓词匹配（一个特例是数组为空），则返回 true；如果一个或多个元素匹配，则返回 false；如果谓词函数对一个或多个元素返回 NULL，并且对所有其他元素返回 false，则返回 NULL。

reduce(array(T), initialState S, inputFunction(S,T,S), outputFunction(S,R)) -> R()¶

从 array 返回一个由 inputFunction 规约的单个值。 inputFunction 将按顺序对 array 中的每个元素进行调用。除了接收元素之外，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], -- 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));

remove_nulls(array(T)) -> array()¶

移除数组中的所有空元素。

repeat(element, count) -> array()¶

将 element 重复 count 次。

reverse(x) -> array()

返回一个数组，该数组的顺序与数组 x 的顺序相反。

sequence(start, stop) -> array(bigint)¶

生成一个从 start 到 stop 的整数序列，如果 start 小于或等于 stop，则递增 1，否则递增 -1。

sequence(start, stop, step) -> array(bigint)¶

生成一个从 start 到 stop 的整数序列，以 step 递增。

sequence(start, stop) -> array(date)¶

生成一个从 start 日期到 stop 日期 的日期序列，如果 start 日期小于或等于 stop 日期，则递增 1 天，否则递增 -1 天。

sequence(start, stop, step) -> array(date)¶

生成一个从 start 到 stop 的日期序列，以 step 递增。 step 的类型可以是 INTERVAL DAY TO SECOND 或 INTERVAL YEAR TO MONTH。

sequence(start, stop, step) -> array(timestamp)¶

生成一个从 start 到 stop 的时间戳序列，以 step 递增。 step 的类型可以是 INTERVAL DAY TO SECOND 或 INTERVAL YEAR TO MONTH。

shuffle(x) -> array()¶

生成给定数组 x 的随机排列。

slice(x, start, length) -> array()¶

从索引 start （如果 start 为负，则从末尾开始）开始，长度为 length 的子集数组 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)¶

返回一个数组，该数组是将 function 应用于 array 的每个元素的结果。

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']); -- [ROW(1, '1b'), ROW(2, null), ROW(null, '3b')]

zip_with(array(T), array(U), function(T, U, R)) -> array(R)¶

使用 function 将两个给定的数组按元素合并成一个单一的数组。如果一个数组比另一个数组短，则在应用 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']