Функции и операторы массивов#

Функции и операторы массивов используют тип ARRAY. Создайте массив с помощью конструктора типа данных.

Создайте массив целых чисел:

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

Создайте массив символьных значений:

SELECT ARRAY['foo', 'bar', 'bazz'];
-- [foo, bar, bazz]

Элементы массива должны иметь один и тот же тип, либо должна быть возможность привести значения к общему типу. В следующем примере используются целые и десятичные значения, и итоговый массив содержит десятичные числа:

SELECT ARRAY[1, 1.2, 4];
-- [1.0, 1.2, 4.0]

Значения null допускаются:

SELECT ARRAY[1, 2, NULL, -4, NULL];
-- [1, 2, NULL, -4, NULL]

Оператор индексации: []#

Оператор [] используется для доступа к элементу массива; индексация начинается с единицы:

SELECT my_array[1] AS first_element

В следующем примере создается массив, после чего извлекается второй элемент:

SELECT ARRAY[1, 1.2, 4][2];
-- 1.2

Оператор конкатенации: ||#

Оператор || используется для объединения массива с массивом или с элементом того же типа:

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, если функция-предикат возвращает NULL для одного или более элементов и true для всех остальных элементов.

any_match(array(T), function(T, boolean)) → boolean#: Возвращает, соответствуют ли какие-либо элементы массива заданному предикату. Возвращает true, если предикату соответствует один или более элементов; false, если не соответствует ни один элемент (частный случай - когда массив пуст); NULL, если функция-предикат возвращает NULL для одного или более элементов и false для всех остальных элементов.

array_distinct(x) → array#: Удаляет повторяющиеся значения из массива x.

array_intersect(x, y) → array#: Возвращает массив элементов, входящих в пересечение x и y, без дубликатов.

array_union(x, y) → array#: Возвращает массив элементов, входящих в объединение x и y, без дубликатов.

array_except(x, y) → array#: Возвращает массив элементов, которые есть в x, но отсутствуют в y, без дубликатов.

array_first(array(E)) → E#: Возвращает первый элемент array. Если массив пуст, функция возвращает NULL, тогда как оператор индексации в таком случае завершится ошибкой.

array_first(array(E), function(E, boolean)) → E: Возвращает первый элемент array, который соответствует предикату. Если массив пуст или совпадений нет, функция возвращает NULL.

array_histogram(x) → map<K, bigint>#

Возвращает map, где ключами являются уникальные элементы входного массива x, а значениями - количество появлений каждого элемента в x. Значения null игнорируются.

SELECT array_histogram(ARRAY[42, 7, 42, NULL]);
-- {42=2, 7=1}

Возвращает пустой map, если во входном массиве нет ненулевых элементов.

SELECT array_histogram(ARRAY[NULL, NULL]);
-- {}

array_join(x, delimiter) → varchar#: Конкатенирует элементы заданного массива с использованием разделителя. Элементы null в результате пропускаются.

array_join(x, delimiter, null_replacement) → varchar: Конкатенирует элементы заданного массива с использованием разделителя и необязательной строки для замены null.

array_last(array(E)) → E#: Возвращает последний элемент array. Если массив пуст, функция возвращает NULL, тогда как оператор индексации в таком случае завершится ошибкой.

array_max(x) → x#: Возвращает максимальное значение входного массива.

array_min(x) → x#: Возвращает минимальное значение входного массива.

array_position(x, element) → bigint#: Возвращает позицию первого вхождения element в массиве x (или 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)

Сортирует и возвращает array на основе заданной функции сравнения function. Функция сравнения принимает два nullable-аргумента, представляющих два nullable-элемента array. Она возвращает -1, 0 или 1, если первый nullable элемент меньше, равен или больше второго nullable элемента. Если функция сравнения возвращает другие значения (включая 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) → boolean#: Проверяет, есть ли у массивов x и y общие ненулевые элементы. Возвращает null, если общих ненулевых элементов нет, но хотя бы один из массивов содержит null.

cardinality(x) → bigint#: Возвращает кардинальность (размер) массива x.

concat(array1, array2, ..., arrayN) → array: Конкатенирует массивы array1, array2, ..., arrayN. Эта функция предоставляет ту же функциональность, что и стандартный SQL оператор конкатенации (||).

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

Возвращает подгруппы из n элементов входного массива. Если во входном массиве нет дубликатов, combinations возвращает подмножества из n элементов.

SELECT combinations(ARRAY['foo', 'bar', 'baz'], 2);
-- [['foo', 'bar'], ['foo', 'baz'], ['bar', 'baz']]

SELECT combinations(ARRAY[1, 2, 3], 2);
-- [[1, 2], [1, 3], [2, 3]]

SELECT combinations(ARRAY[1, 2, 2], 2);
-- [[1, 2], [1, 2], [2, 2]]

Порядок подгрупп детерминирован, но не определен. Порядок элементов внутри подгруппы детерминирован, но не определен. n не должен быть больше 5, а общий размер создаваемых подгрупп должен быть меньше 100,000.

contains(x, element) → boolean#: Возвращает true, если массив x содержит element.

contains_sequence(x, seq) → boolean#: Возвращает true, если массив x содержит весь массив seq как подпоследовательность (все значения в одном и том же последовательном порядке).

element_at(array(E), index) → E#: Возвращает элемент array по заданному index. Если index > 0, эта функция предоставляет ту же функциональность, что и стандартный SQL оператор индексации ([]), за исключением того, что функция возвращает NULL при обращении к index, превышающему длину массива, тогда как оператор индексации в таком случае завершится ошибкой. Если 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) путем конкатенации содержащихся массивов.

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

Возвращает n-граммы (подпоследовательности из соседних n элементов) для 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, если функция-предикат возвращает NULL для одного или более элементов и false для всех остальных элементов.

reduce(array(T), initialState S, inputFunction(S, T, S), outputFunction(S, R)) → R#

Возвращает одно значение, свернутое из array. 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], BIGINT '0',
              (s, x) -> s + x,
              s -> s);
-- 2147483648

-- calculates arithmetic average
SELECT reduce(ARRAY[5, 6, 10, 20],
              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));
-- 10.25

repeat(element, count) → array#: Повторяет element count раз.

reverse(x) → array: Возвращает массив, в котором порядок элементов массива x обращен.

sequence(start, stop)#: Генерирует последовательность целых чисел от start до stop, увеличивая на 1, если start меньше или равен stop, иначе на -1.

sequence(start, stop, step): Генерирует последовательность целых чисел от start до stop, увеличивая на step.

sequence(start, stop): Генерирует последовательность дат от даты start до даты stop, увеличивая на 1 день, если дата start меньше или равна дате stop, иначе на -1 день.

sequence(start, stop, step): Генерирует последовательность дат от start до stop, увеличивая на step. Тип step может быть либо INTERVAL DAY TO SECOND, либо INTERVAL YEAR TO MONTH.

sequence(start, stop, step): Генерирует последовательность timestamp от start до stop, увеличивая на step. Тип step может быть либо INTERVAL DAY TO SECOND, либо INTERVAL YEAR TO MONTH.

shuffle(x) → array#: Генерирует случайную перестановку заданного массива x.

slice(x, start, length) → array#: Возвращает подмассив массива x, начиная с индекса start (или начиная с конца, если start отрицательный), длиной 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]]

euclidean_distance(array(double), array(double)) → double#

Вычисляет евклидово расстояние:

SELECT euclidean_distance(ARRAY[1.0, 2.0], ARRAY[3.0, 4.0]);
-- 2.8284271247461903

dot_product(array(double), array(double)) → double#

Вычисляет скалярное произведение:

SELECT dot_product(ARRAY[1.0, 2.0], ARRAY[3.0, 4.0]);
-- 11.0

zip(array1, array2[, ...]) -> array(row)#

Объединяет заданные массивы поэлементно в один массив строк. M-й элемент N-го аргумента будет N-м полем M-го выходного элемента. Если аргументы имеют разную длину, недостающие значения заполняются 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. Если один массив короче, в его конец добавляются null до длины более длинного массива перед применением 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']