Как сравнить два массива и выбрать только несоответствующие элементы В postgres

Ответ 1

select array_agg(elements)
from (
  select unnest(array[12,3,5,7,8])
  except
  select unnest(array[3,7,8])
) t (elements)

Ответ 2

Я бы применил это к оператору массива.

select array(select unnest(:arr1) except select unnest(:arr2));

Если: arr1 и: arr2 не пересекаются, использование array_agg() приводит к нулевому значению.

Ответ 3

Попробуйте отключить()/except:

EXPLAIN ANALYZE SELECT array(select unnest(ARRAY[1,2,3,n]) EXCEPT SELECT unnest(ARRAY[2,3,4,n])) FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n  (cost=0.00..62.50 rows=1000 width=4) (actual time=1.373..140.969 rows=10000 loops=1)
   SubPlan 1
     ->  HashSetOp Except  (cost=0.00..0.05 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=10000)
           ->  Append  (cost=0.00..0.04 rows=2 width=0) (actual time=0.002..0.008 rows=8 loops=10000)
                 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0) (actual time=0.002..0.003 rows=4 loops=10000)
                       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=4 loops=10000)
                 ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.003 rows=4 loops=10000)
                       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=4 loops=10000)
 Total runtime: 142.531 ms

И специальный оператор intarray:

EXPLAIN ANALYZE SELECT ARRAY[1,2,3,n] - ARRAY[2,3,4,n] FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n  (cost=0.00..15.00 rows=1000 width=4) (actual time=1.338..11.381 rows=10000 loops=1)
 Total runtime: 12.306 ms

Исходный уровень:

EXPLAIN ANALYZE SELECT ARRAY[1,2,3,n], ARRAY[2,3,4,n] FROM generate_series( 1,10000 ) n;
 Function Scan on generate_series n  (cost=0.00..12.50 rows=1000 width=4) (actual time=1.357..7.139 rows=10000 loops=1)
 Total runtime: 8.071 ms

Время на пересечение массива:

intarray -           :  0.4 µs
unnest() / intersect : 13.4 µs

Конечно, путь intarray намного быстрее, но я считаю удивительным, что postgres может заблокировать зависимый подзапрос (который содержит хэш и другие вещи) в 13.4 мкс...

Ответ 4

Я создал набор функций, специально предназначенных для решения этих проблем: https://github.com/JDBurnZ/anyarray

Самое главное, что эти функции работают во всех типах данных, а не в JUST целых числах, поскольку intarray ограничено.

После загрузки загрузки функций, определенных в этих файлах SQL из GitHub, все, что вам нужно сделать, это:

SELECT
  ANYARRAY_DIFF(
    ARRAY[12, 3, 5, 7, 8],
    ARRAY[3, 7, 8]
  )

Возвращает нечто похожее: ARRAY[12, 5]

Если вам также нужно вернуть отсортированные значения:

SELECT
  ANYARRAY_SORT(
    ANYARRAY_DIFF(
      ARRAY[12, 3, 5, 7, 8],
      ARRAY[3, 7, 8]
    )
  )

Возвращает точно: ARRAY[5, 12]

Ответ 5

Модуль contrib/intarray предоставляет эту функциональность - для массивов целых чисел. Для других типов данных вам, возможно, придется написать свои собственные функции (или изменить те, которые предоставляются с intarray).

Ответ 6

Я бы создал функцию, используя ту же логику, что и @a_horse_with_no_name:

CREATE FUNCTION array_subtract(a1 int[], a2 int[]) RETURNS int[] AS $$
DECLARE
    ret int[];
BEGIN
    IF a1 is null OR a2 is null THEN
        return a1;
    END IF;
    SELECT array_agg(e) INTO ret
    FROM (
        SELECT unnest(a1)
        EXCEPT
        SELECT unnest(a2)
    ) AS dt(e);
    RETURN ret;
END;
$$ language plpgsql;

Затем вы можете использовать эту функцию для изменения переменной base_array:

base_array := array_subtract(base_array, temp_array);