Работа с массивами#

Данный документ описывает операторы и функции для работы с типом данных 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_cum_sum(x) → array#: Returns an array of the partial (running) sums of the elements in the source BIGINT array.

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_histogram(x) → map<K, bigint>#

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

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

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

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

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

array_join(x, delimiter, null_replacement) → varchar: Объединяет элементы заданного массива с использованием разделителя и необязательной строки для замены значений 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)

Сортирует и возвращает массив на основе заданной функции-компаратора. Компаратор принимает два аргумента, допускающих 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]]

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

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

Создает массив из тех элементов исходного массива, для которых функция возвращает 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 смежных элементов) для массива. Порядок 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#

Возвращает единственное значение, полученное в результате свертки массива. Функция 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], 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): Генерирует последовательность временных меток от 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 к каждому элементу массива:

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

Объединяет заданные массивы поэлементно в единый массив строк (rows). 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 для выравнивания длины с более длинным массивом перед применением функции.

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