PostgreSQL中多个数组的交集

Question

我有一个如下定义的视图：

 CREATE VIEW View1 AS 
 SELECT Field1, Field2, array_agg(Field3) AS AggField 
 FROM Table1 
 GROUP BY Field1, Field2;

我想要做的是获取AggField数组中的交集，类似于以下方式：

SELECT intersection(AggField) FROM View1 WHERE Field2 = 'SomeValue';

这有可能吗？或者有更好的方法来实现我想要的吗？

- DanielGibbs

Answer 1

我能想到的最接近数组交集的方法是这样的：

select array_agg(e)
from (
    select unnest(a1)
    intersect
    select unnest(a2)
) as dt(e)

假设a1和a2是具有相同类型元素的单维数组。您可以将其封装在以下类似函数中：

create function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
    ret int[];
begin
    -- The reason for the kludgy NULL handling comes later.
    if a1 is null then
        return a2;
    elseif a2 is null then
        return a1;
    end if;
    select array_agg(e) into ret
    from (
        select unnest(a1)
        intersect
        select unnest(a2)
    ) as dt(e);
    return ret;
end;
$$ language plpgsql;

然后，您可以做这样的事情：

=> select array_intersect(ARRAY[2,4,6,8,10], ARRAY[1,2,3,4,5,6,7,8,9,10]);
 array_intersect 
-----------------
 {6,2,4,10,8}
(1 row)

请注意，这并不保证返回数组中的任何特定顺序，但如果您关心顺序，您可以解决这个问题。然后，您可以创建自己的聚合函数：

-- Pre-9.1
create aggregate array_intersect_agg(
    sfunc    = array_intersect,
    basetype = int[],
    stype    = int[],
    initcond = NULL
);

-- 9.1+ (AFAIK, I don't have 9.1 handy at the moment
-- see the comments below.
create aggregate array_intersect_agg(int[]) (
    sfunc = array_intersect,
    stype = int[]
);

现在我们可以看到为什么array_intersect在处理NULL时会表现出奇怪和有点笨拙的行为。我们需要一个初始值来聚合，它的行为类似于通用集合，我们可以使用NULL来实现这一点（是的，这有点问题，但我无法想到更好的方法）。

一旦所有这些都就位了，你可以做这样的事情：

> select * from stuff;
    a    
---------
 {1,2,3}
 {1,2,3}
 {3,4,5}
(3 rows)

> select array_intersect_agg(a) from stuff;
 array_intersect_agg 
---------------------
 {3}
(1 row)

并不是非常简单或高效，但可能是一个合理的起点，总比什么都没有要好。

有用的参考资料：

• array_agg
• create aggregate
• create function
• PL/pgSQL
• unnest

- mu is too short

---

在Postgres 9.1上，agg函数需要像这样："""CREATE AGGREGATE array_intersect_agg(integer[]) ( SFUNC=array_intersect, STYPE=int4[] );""" - Rescommunes

@Rescommunes：我添加的9.1版本正确吗？我手头没有9.1来测试。 - mu is too short

是的，这很好。不确定差异是否在9.1+版本中（可能更早）。 - Rescommunes

或者安装intarray扩展程序，该程序提供交集运算符(&)。 - user330315

什么是 dt(e)？ - smaiakov

@smaiakov 这是派生表的别名，以便查询知道名为 dt 的“表”是指 UNION，并且它的“列”名为 e。 - mu is too short

Answer 2

回答这个问题可能有点晚了，但也许有人会需要，所以我决定分享一些我写的东西，因为没有找到任何准备好的解决方案来交叉任意数量的数组。所以在这里。这个函数接收数组的数组，如果只有单个数组，函数返回第一个数组，如果有2个数组函数交叉2个数组并返回结果，如果超过2个数组，函数取前2个数组的交集，将其存储在某个变量中，并循环遍历所有其他数组，将每个下一个数组与存储的结果相交，并将结果存储在变量中。如果结果为空，则退出空。最后，存储包含与函数返回的交互数据的数组的变量。

CREATE OR REPLACE FUNCTION array_intersected(iarray bigint[][])
  RETURNS bigint[] AS
$BODY$
    declare out_arr bigint[]; set1 bigint[]; set2 bigint[];
    BEGIN
        --RAISE NOTICE '%', array_length(iarray, 1);
        if array_length(iarray, 1) = 1 then
            SELECT ARRAY(SELECT unnest(iarray[1:1])) into out_arr;
        elseif array_length( iarray, 1) = 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
        elseif array_length(iarray, 1) > 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            --exit if no common numbers exists int 2 first arrays
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
            if out_arr = NULL then
                EXIT;
                END IF;
            FOR i IN 3 .. array_upper(iarray, 1)
            LOOP
               set1 := iarray[i:i];
               SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(out_arr))into out_arr;
               if out_arr = NULL then
                EXIT;
                   END IF;
            END LOOP;
        end if;

    return out_arr;

    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE;

这里是验证代码，确保它能正常工作。

select array_intersected(array[[1, 2]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3], [2, 4]]::bigint[][]);

select array_intersected(array[[1, 2, 3, 4],[null, null, 4, 3], [3, 1, 4, null]]::bigint[][]);

- genichm

Answer 3

被接受的答案对我没有用。这是我修复它的方法。

create or replace function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
  ret int[];
begin
  -- RAISE NOTICE 'a1 = %', a1;
  -- RAISE NOTICE 'a2 = %', a2;
  if a1 is null then
    -- RAISE NOTICE 'a1 is null';
    return a2;
  -- elseif a2 is null then
  --    RAISE NOTICE 'a2 is null';
  --    return a1;
  end if;
  if array_length(a1,1) = 0 then
    return '{}'::integer[];
  end if;
  select array_agg(e) into ret
  from (
    select unnest(a1)
    intersect
    select unnest(a2)
  ) as dt(e);
  if ret is null then
    return '{}'::integer[];
  end if;
  return ret;
end;
$$ language plpgsql;

- Martin Velez