Есть ли что-то вроде функции zip() в PostgreSQL, которая объединяет два массива?

У меня есть два значения массива одинаковой длины в PostgreSQL:

{a,b,c} а также {d,e,f}

и я хотел бы объединить их в

{{a,d},{b,e},{c,f}}

Есть способ сделать это?

Источник

2 ответа

Решение

Postgres 9.3 или старше

Простой почтовый индекс ()

Рассмотрим следующую демонстрацию для Postgres 9.3 или более ранней версии:

SELECT ARRAY[a,b] AS ab
FROM  (
   SELECT unnest('{a,b,c}'::text[]) AS a
         ,unnest('{d,e,f}'::text[]) AS b
    ) x;

Результат:

  ab
-------
 {a,d}
 {b,e}
 {c,f}

Обратите внимание, что оба массива должны иметь одинаковое количество элементов для параллельного размещения, иначе вы получите перекрестное соединение.

Вы можете заключить это в функцию, если хотите:

CREATE OR REPLACE FUNCTION zip(anyarray, anyarray)
  RETURNS SETOF anyarray LANGUAGE SQL AS
$func$
SELECT ARRAY[a,b] FROM (SELECT unnest($1) AS a, unnest($2) AS b) x;
$func$;

Вызов:

SELECT zip('{a,b,c}'::text[],'{d,e,f}'::text[]);

Тот же результат.

zip () в многомерный массив:

Теперь, если вы хотите объединить этот новый набор массивов в один двумерный массив, это становится более сложным.

SELECT ARRAY (SELECT ...)

или же:

SELECT array_agg(ARRAY[a,b]) AS ab
FROM  (
   SELECT unnest('{a,b,c}'::text[]) AS a
         ,unnest('{d,e,f}'::text[]) AS b
    ) x

или же:

SELECT array_agg(ARRAY[ARRAY[a,b]]) AS ab
FROM  ...

все приведёт к одному и тому же сообщению об ошибке (протестировано с pg 9.1.5):

ОШИБКА: не удалось найти тип массива для текста типа данных []

Но есть способ обойти это, поскольку мы работали над этим тесно связанным вопросом.
Создайте собственную функцию агрегирования:

CREATE AGGREGATE array_agg_mult (anyarray) (
    SFUNC    = array_cat
   ,STYPE    = anyarray
   ,INITCOND = '{}'
);

И используйте это так:

SELECT array_agg_mult(ARRAY[ARRAY[a,b]]) AS ab
FROM  (
   SELECT unnest('{a,b,c}'::text[]) AS a
         ,unnest('{d,e,f}'::text[]) AS b
    ) x

Результат:

{{a,d},{b,e},{c,f}}

Обратите внимание на дополнительные ARRAY[] слой! Без этого и просто

SELECT array_agg_mult(ARRAY[a,b]) AS ab
FROM ...

Ты получаешь:

{a,d,b,e,c,f}

Что может быть полезно для других целей.

Бросить другую функцию:

CREATE OR REPLACE FUNCTION zip2(anyarray, anyarray)
  RETURNS SETOF anyarray LANGUAGE SQL AS
$func$
SELECT array_agg_mult(ARRAY[ARRAY[a,b]])
FROM (SELECT unnest($1) AS a, unnest($2) AS b) x;
$func$;

Вызов:

SELECT zip2('{a,b,c}'::text[],'{d,e,f}'::text[]); -- or any other array type

Результат:

{{a,d},{b,e},{c,f}}

Postgres 9.4+

Использовать ROWS FROM построить или обновленный unnest() который принимает несколько массивов для параллельного размещения. Каждый может иметь разную длину. Вы получаете ( за документацию):

[...] число строк результата в этом случае соответствует числу наибольшего результата функции, с меньшими результатами, дополненными нулевыми значениями для соответствия.

Используйте этот более чистый и простой вариант:

SELECT ARRAY[a,b] AS ab
FROM   unnest('{a,b,c}'::text[] 
            , '{d,e,f}'::text[]) x(a,b);

Postgres 9,5+

судов array_agg(array expression):

Function                Argument Type(s)   Return Type
array_agg(expression)   any array type     same as argument data type  

Description
input arrays concatenated into array of one higher dimension
(inputs must all have same dimensionality, and cannot be empty or NULL)

Это замена для моей пользовательской агрегатной функции array_agg_mult() реализовано в C, что значительно быстрее. Используй это.

Источник

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

CREATE OR REPLACE FUNCTION zip(array1 anyarray, array2 anyarray) RETURNS text[]
AS $$
SELECT array_agg_mult(ARRAY[ARRAY[array1[i],array2[i]]])
FROM generate_subscripts(
  CASE WHEN array_length(array1,1) >= array_length(array2,1) THEN array1 ELSE array2 END,
  1
) AS subscripts(i)
$$ LANGUAGE sql;

regress=> SELECT zip('{a,b,c}'::text[],'{d,e,f}'::text[]);
         zip         
---------------------
 {{a,d},{b,e},{c,f}}
(1 row)


regress=> SELECT zip('{a,b,c}'::text[],'{d,e,f,g}'::text[]);
             zip              
------------------------------
 {{a,d},{b,e},{c,f},{NULL,g}}
(1 row)

regress=> SELECT zip('{a,b,c,z}'::text[],'{d,e,f}'::text[]);
             zip              
------------------------------
 {{a,d},{b,e},{c,f},{z,NULL}}
(1 row)

Если вы хотите отсеять лишнее значение вместо заполнения нулями, просто измените >= длина теста до <= вместо.

Эта функция не обрабатывает довольно причудливую функцию PostgreSQL, в которой массивы могут иметь элемент, отличный от 1, но на практике никто не использует эту функцию. Например, с 3-элементным массивом с нулевым индексом:

regress=> SELECT zip('{a,b,c}'::text[], array_fill('z'::text, ARRAY[3], ARRAY[0]));
          zip           
------------------------
 {{a,z},{b,z},{c,NULL}}
(1 row)

Когда код Эрвина работает с такими массивами, и даже с многомерными массивами (путем их выравнивания), но не работает с массивами различной длины.

Массивы немного особенные в PostgreSQL, они слишком гибки с многомерными массивами, настраиваемым индексом происхождения и т. Д.

В 9.4 вы сможете написать:

SELECT array_agg_mult(ARRAY[ARRAY[a,b])
FROM unnest(array1) WITH ORDINALITY as (o,a)
NATURAL FULL OUTER JOIN
unnest(array2) WITH ORDINALITY as (o,b);

что будет намного приятнее, особенно если будет проведена оптимизация для сканирования функций вместе, а не для сортировки и объединения.

Источник
Другие вопросы по тегам