如何比较两个数组并选择不匹配的元素在PostgreSQL中

Question

我该如何从两个数组中仅选取不匹配的元素。

示例：

base_array [12,3,5,7,8]
temp_array [3,7,8]

我想要比较这两个数组，并从基础数组中删除匹配的元素。

现在，基础数组应该是这样的：[12,5]

- ggvvkk

Answer 1

我会使用数组操作符来解决这个问题。

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

如果：arr1和：arr2没有交集，使用array_agg()会导致空值。

- Denis de Bernardy

---

数组的顺序在这里产生了不同的结果：select array(select unnest(ARRAY['1']) except select unnest(ARRAY['1','2'])) 返回空列表，但是 select array(select unnest(ARRAY['1','2']) except select unnest(ARRAY['1'])) 返回 {2}。 - Brady Holt

@Brady：这应该是对的，不是吗？{1} - {1,2} = {}，{1,2} - {1} = {2}。 - Denis de Bernardy

只是想强调一下，这个函数不稳定，:arr1 中的顺序不会被保留。感谢分享这个一行代码。 - jlandercy

在循环中使用 explain analyse 进行大约 99990 次迭代，可以明显看出这种解决方案更快（消耗了 array_agg 解决方案时间的 ~80%）。关于结果，它能够正常工作。 - Peter Krauss

Answer 2

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

- user330315

Answer 3

我已经构建了一组特别处理这些问题的函数：https://github.com/JDBurnZ/anyarray

最好的事情是这些函数适用于所有数据类型，而不仅仅是像 intarray 一样仅限于整数。

从GitHub加载这些SQL文件中定义的函数之后，你所需要做的就是：

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]

- Joshua Burns

Answer 4

让我们尝试使用unnest() / 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微秒内消除一个包含哈希和其他东西的相关子查询...

- bobflux

Answer 5

contrib/intarray 模块提供了这个功能——至少对于整数数组而言是如此。对于其他数据类型，您可能需要编写自己的函数（或修改 intarray 提供的函数）。

- Jonathan Hall

---

嗨，intarray很好而且快速...但是这个项目的bigint版本在哪里呢？即使是PostgreSQL上的一个简单的count(*)也是bigint数据类型，而不是int。 - Peter Krauss

Answer 6

一个对Denis的回答进行扩展的方法，返回两个数组的差异，不管哪个数组先输入。这并不是最简洁的查询方式，也许有更整洁的方法。

select array_cat(
   (select array(select unnest(a.b::int[]) except select unnest(a.c::int[]))),
   (select array(select unnest(a.c::int[]) except select unnest(a.b::int[]))))
from (select '{1,2}'::int[] b,'{1,3}'::int[] c) as a;

返回：

{2,3}

- MichaelG

Answer 7

我会创建一个函数，使用与 @a_horse_with_no_name 描述的相同的 except 逻辑。

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

---

使用 @Denis 更快的解决方案，仅使用 SQL，我们可以将通用函数表示为

CREATE FUNCTION array_subtract(anyarray,anyarray) RETURNS anyarray AS $f$
  SELECT array(
    SELECT unnest($1)
    EXCEPT
    SELECT unnest($2)
  )
$f$ language SQL IMMUTABLE;

- danjuggler

---

“dt”是什么作用？在别名之前使用的那个。我注意到如果不使用它，数组的每个元素都存储在括号内，即()。 - Karan Parikh

"dt" 别名为表格，"e" 别名为列。如果不包括 "dt"，则选择的是表格行作为单个列。 - danjuggler

Answer 8

这是我的建议 - 它与其他答案有以下几点不同：

• 它是一个可重复使用的函数
• 最重要的是，它保持原始数组的元素顺序

CREATE OR REPLACE FUNCTION array_difference_ordered(array1 anyarray, array2 anyarray)
    RETURNS anyarray
    LANGUAGE sql
    IMMUTABLE AS
$$
SELECT ARRAY_AGG(array1_ordered_row.element ORDER BY array1_ordered_row.ordering)
FROM UNNEST(array1) WITH ORDINALITY AS array1_ordered_row(element, ordering)
WHERE NOT EXISTS
    (
        SELECT 1
        FROM UNNEST(array2) AS array2_row(element)
        WHERE array1_ordered_row.element = array2_row.element
    )
$$;

- Kamil Kalinowski