陣列函數和運算子¶

下標運算子：[]¶

[] 運算子用於存取陣列的元素，索引從一開始

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]。如果陣列中有空元素，則該元素及其之後的累計總和為空。

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 中出現多次的元素集合。如果任何元素是包含 null 的列或陣列，則會擲回例外狀況。

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)¶

回傳一個 map：鍵為 array 中不重複的元素，值為該鍵出現的次數。忽略 null 元素。空陣列會回傳空的 map。

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

回傳一個布林值：判斷 array 中是否有任何元素出現超過一次。如果任何元素是包含 null 的 rows 或陣列，則會拋出例外。

SELECT array_has_duplicates(ARRAY[1, 2, null, 1, null, 3]) – true SELECT array_has_duplicates(ARRAY[ROW(1, null), ROW(1, null)]) – “map 鍵不能為 null 或包含 null”

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()¶

使用分隔符號和可選的字串來替換 null，將給定陣列的元素串接起來。

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

回傳陣列中最不常出現的非 null 元素。如果有多個元素具有相同的頻率，則該函數會回傳最小的元素。如果陣列有多個元素，且任何元素是具有 null 欄位的 ROWS 或具有 null 元素的 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 個最不常出現的非 null 元素。這些元素會按照其頻率遞增排序。如果兩個元素具有相同的頻率，則較小的元素會先出現。如果陣列有多個元素，且任何元素是具有 null 欄位的 ROWS 或具有 null 元素的 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-norm 來正規化陣列 x。它等效於 TRANSFORM(array, v -> v / REDUCE(array, 0, (a, v) -> a + POW(ABS(v), p), a -> POW(a, 1 / p))，但 reduce 部分只會執行一次。如果陣列為 null 或有 null 陣列元素，則會回傳 null。

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 的元素必須是可排序的。Null 元素會放置在回傳陣列的末尾。

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

根據給定的比較器 function 排序並回傳 array。比較器會接收兩個可為 null 的引數，表示 array 的兩個可為 null 的元素。如果第一個可為 null 的元素小於、等於或大於第二個可為 null 的元素，則它會回傳 -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 的元素必須是可排序的。Null 元素會放置在回傳陣列的末尾。

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 分割成指定長度的區塊。如果陣列的長度不是區塊長度的整數倍，則最後一個區塊會比區塊長度短。忽略 null 輸入，但不忽略元素。

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 中所有非 null 元素的總和。如果沒有非 null 元素，則傳回 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 是否有任何非 null 的共同元素。如果沒有非 null 的共同元素，但任一陣列包含 null，則傳回 null。對於包含 null 值的 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-grams。

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 歸納出單一值。將依序對 array 中的每個元素調用 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], -- 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()¶

移除陣列中所有 null 元素。

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 為負數，則從結尾開始），截取陣列 x 中長度為 length 的子集。

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 之前，會在末尾附加 null 以匹配較長陣列的長度。

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