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

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

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
• 创建聚合
• 创建函数
• PL/pgSQL
• 解除嵌套

Answer 2

接受的答案对我不起作用。我就是这样解决的。

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;

Answer 3

现在回答这个问题有点晚了，但也许有人会需要它，所以我决定分享我写的东西，因为没有找到任何数量数组的交集的现成解决方案。所以就是这样。该函数接收数组的数组，如果它只是单个数组，函数返回第一个数组，如果有2个数组，函数与2个数组相交并返回结果，如果多于2个数组，函数取2个第一个数组的交集，并将其存储在某些变量中并循环遍历所有其他数组，将每个下一个数组与存储的结果相交并将结果存储在变量中。如果结果为 null，则它与 null 一起存在。在 和 变量中存储数组以及从函数返回的交互数据。

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