PostgreSQL中多个阵列的交集 [英] Intersection of multiple arrays in PostgreSQL

问题描述

我有一个观点定义为：

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

这是可能的话，还是有更好的方式来实现我想要什么？

Is this at all possible, or is there a better way to achieve what I want?

推荐答案

最接近数组十字路口，我能想到的是这样的：

The closest thing to an array intersection that I can think of is this:

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

这假定 A1 和 A2 是一维数组与同一类型的元素。你可以换，最多的功能是这样的：

This assumes that a1 and a2 are single dimension arrays with the same type of elements. You could wrap that up in a function something like this:

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;

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

Then you could do things like this:

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

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

Note that this doesn't guarantee any particular order in the returned array but you can fix that if you care about it. Then you could create your own aggregate function:

-- 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的滑稽和有些kludgey的事情。我们需要一个行为像全集，我们可以用NULL的（是的，这闻起来有点过，但我想不出任何东西把我的头顶部越好）聚集的初始值。

And now we see why array_intersect does funny and somewhat kludgey things with NULLs. We need an initial value for the aggregation that behaves like the universal set and we can use NULL for that (yes, this smells a bit off but I can't think of anything better off the top of my head).

在这一切就绪，你可以做这样的事情：

Once all this is in place, you can do things like this:

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

不完全是简单或有效，但可能是合理的起点，比什么都没有要好。

Not exactly simple or efficient but maybe a reasonable starting point and better than nothing at all.

有用的参考资料：

• <$c$c>array_agg


• 创制综合


• 创建函数


• PL / pgSQL的


• <$c$c>unnest

这篇关于PostgreSQL中多个阵列的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！