Postgres重叠一列中的数组 [英] Postgres overlap arrays from one column

问题描述

我有一个表 A，其中有一列 col1，其中每条记录是一个整数数组。

I have a Table "A" with one column "col1" where each record is a array of integers.

col1
-----
{1,2,3,4}
{1,2,6,7}
{1,2,3,8,9}

我希望有一行结果包含 col1中所有数组的重叠或相交。

I like to have one row as result which contains the overlap or intersect of all arrays in "col1".

select overlap(col1) from A;

result
-----
{1,2}

推荐答案

您应该为此定义自定义聚合：

You should to define custom aggregate for this purpose:

CREATE OR REPLACE FUNCTION public.overlap_array_aggregate(anyarray, anyarray)
 RETURNS anyarray
 LANGUAGE plpgsql STRICT
AS $function$
BEGIN
  RETURN ARRAY(SELECT unnest($1) INTERSECT SELECT unnest($2));
END;
$function$

CREATE AGGREGATE array_overlap_agg (
   basetype = anyarray,
   sfunc =  overlap_array_aggregate,
   stype = anyarray );

然后它按预期运行：

postgres=# SELECT * FROM foo;
┌─────────────┐
│      a      │
╞═════════════╡
│ {1,2,3,4}   │
│ {1,2,6,7}   │
│ {1,2,3,8,9} │
└─────────────┘
(3 rows)

postgres=# SELECT array_overlap_agg(a) FROM foo;
┌───────────────────┐
│ array_overlap_agg │
╞═══════════════════╡
│ {1,2}             │
└───────────────────┘
(1 row)

这篇关于Postgres重叠一列中的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！