Нормализовать индексы массивов для 1-мерного массива, чтобы они начинались с 1
PostgreSQL может работать со строками индексов, начиная с любого места.
Рассмотрим этот пример, который создает массив с 3 элементами с индексами от 5 до 7:
SELECT ('[5:7]={1,2,3}'::int[]);
Возврат:
[5:7]={1,2,3}
Смысл, например, в том, что вы получаете первый элемент с
SELECT ('[5:7]={1,2,3}'::int[])[5];
Я хочу нормализовать любой заданный 1-мерный массив до начать с индекса 1 массива.
Лучшее, что я мог придумать:
SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)]
Или, то же самое, проще читать:
WITH x(a) AS (
SELECT '[5:7]={1,2,3}'::int[]
)
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM x
Знаете ли вы более простой/более быстрый или, по крайней мере, более элегантный способ?
Benchmark
Для тестирования производительности я взвесил этот быстрый тест.
Таблица со 100k строками, простой целочисленный массив случайной длины между 1 и 11:
CREATE TEMP TABLE t (a int[]);
INSERT INTO t -- now with actually varying subscripts
SELECT ('[' || g%10 || ':' || 2*(g%10) || ']={1'
|| repeat(','||g::text, g%10) || '}')::int[]
FROM generate_series(1,100000) g;
EXPLAIN ANALYZE
SELECT
substring(a::text, '{.*$')::int[] -- Total runtime: 949.304 ms
-- a[-2147483648:2147483647] -- Total runtime: 283.877 ms
-- a[array_lower(a, 1):array_upper(a, 1)] -- Total runtime: 311.545 ms
FROM t
Итак, да, идея @Даниэля немного быстрее.
@Kevin преобразование текста работает тоже, но не зарабатывает много очков.
Любые другие идеи?
Ответы
Ответ 1
Существует более простой метод, который является уродливым, но я считаю технически правильным: извлечь максимально возможный фрагмент из массива, в отличие от точного среза с вычисленными границами.
Это позволяет избежать двух вызовов функций.
Пример:
select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];
приводит к:
int4
---------
{1,2,3}
Ответ 2
Не уверен, что это уже покрыто, но:
SELECT array_agg(v) FROM unnest('[5:7]={1,2,3}'::int[]) AS a(v);
Чтобы проверить производительность, мне пришлось добавить столбец id
в тестовую таблицу. Медленное.
Ответ 3
В итоге что-то более элегантное появилось с Postgres 9.6.
Руководство:
Можно опустить lower-bound
и/или upper-bound
фрагмента спецификатор; недостающая граница заменяется нижним или верхним пределом индексов массива. Например:
Итак, теперь просто:
SELECT ('[5:7]={1,2,3}'::int[])[:];
О той же производительности, что и решение Daniel с жестко закодированными индексами макс-массива - это еще один способ пойти с Postgres 9.5 или ранее.