sql - 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
• create aggregate
• create function
• PL/pgSQL
• unnest