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 中数组的交集，例如:
What I would like to do is get the intersection of the arrays in AggField with something like:
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 做有趣且有点笨拙的事情.我们需要一个行为类似于通用集的聚合的初始值，我们可以为此使用 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.
有用的参考:
这篇关于PostgreSQL 中多个数组的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
