Как быстро развернуть 2d-массив в 1d-массив в PostgreSQL?

У меня действительно большой массив, который я вычислил с помощью Apache Madlib, и я хотел бы применить операцию к каждому отдельному массиву в этом 2d-массиве.

Я нашел код, который может помочь мне удалить его из этого связанного ответа. Однако код очень медленно работает с этим действительно большим 2-мерным массивом (150 000+ 1-мерных массивов с плавающей запятой). В то время как unnest() запуск занимает всего несколько секунд, даже после ожидания в течение нескольких минут код не завершен.

Конечно, должен быть более быстрый способ раскрутить большой 2d массив в меньшие 1d массивы? Бонус, если это решение использует Apache Madlib. Я нашел один свинец, похороненный в документации под названием deconstruct_2d_arrayОднако, когда я пытаюсь вызвать эту функцию в матрице, она завершается с ошибкой:

ОШИБКА: функция "deconstruct_2d_array(double precision[])": неверное преобразование типа. Внутренний составной тип имеет больше элементов, чем внутренний составной тип.

Источник

2 ответа

Решение

Функция, которую вы нашли в моем старом ответе, не подходит для больших массивов. Я никогда не думал о массивах вашего размера, которые, вероятно, должны быть набором (таблицей).

Как бы то ни было, эта функция plpgsql заменяет функцию в ссылочном ответе. Требуется Postgres 9.1 или более поздняя версия.

CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
  RETURNS SETOF ANYARRAY AS
$func$
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql IMMUTABLE STRICT;

В 40 раз быстрее в моем тесте на большом 2d-массиве в Postgres 9.6.

STRICT чтобы избежать исключения для ввода NULL (как прокомментировал IamIC):

ОШИБКА: выражение FOREACH не должно быть нулевым

Источник

Теперь для этого есть встроенная функция MADlib - array_unnest_2d_to_1d, которая была представлена ​​в выпуске 1.11: http://madlib.incubator.apache.org/docs/latest/array__ops_8sql__in.html

Вот пример использования:

CREATE TABLE test1 (pid int, points double precision[]);
INSERT INTO test1 VALUES
(100,  '{{1.0, 2.0, 3.0}, {4.0, 5.0, 6.0}, {7.0, 8.0, 9.0}}'),
(101,  '{{11.0, 12.0, 13.0}, {14.0, 15.0, 16.0}, {17.0, 18.0, 19.0}}'),
(102,  '{{21.0, 22.0, 23.0}, {24.0, 25.0, 26.0}, {27.0, 28.0, 29.0}}');
SELECT * FROM test1;

производит

 pid |               points               
-----+------------------------------------
 100 | {{1,2,3},{4,5,6},{7,8,9}}
 101 | {{11,12,13},{14,15,16},{17,18,19}}
 102 | {{21,22,23},{24,25,26},{27,28,29}}
(3 rows)

Затем вызовите функцию unnest:

SELECT pid, (madlib.array_unnest_2d_to_1d(points)).* 
FROM test1 ORDER BY pid, unnest_row_id;

производит

pid | unnest_row_id | unnest_result 
-----+---------------+---------------
 100 |             1 | {1,2,3}
 100 |             2 | {4,5,6}
 100 |             3 | {7,8,9}
 101 |             1 | {11,12,13}
 101 |             2 | {14,15,16}
 101 |             3 | {17,18,19}
 102 |             1 | {21,22,23}
 102 |             2 | {24,25,26}
 102 |             3 | {27,28,29}
(9 rows)

где unnest_row_id - индекс в двумерном массиве

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