How can I calculate the overlap count between an array passed as argument and a column of type array.
SELECT id1, overlap_count(ary, '{32,56,72,...,996}') FROM tbl
where it checks the ary field for how many of the elements match with the array passed as argument.
My question is two fold. My table is something like this : ID1,ID2,ary I want to use multidimensional arrays if possible (where every array can be any dimensions), but suspect it would be more complex or very slow.
if not I would have to use duplicate records with one-dim array, like this :
| ID1 | ID2 | ary |
| 5 | 7 | {...} |
| 43 | 611 | {...} |
| 5 | 7 | {...} |
Here is a simple function for the job.
create or replace function overlap_count(arr_a integer[], arr_b integer[])
returns integer language sql immutable as
$$
select count(*)::integer from unnest(arr_a) a inner join unnest(arr_b) b on a = b;
$$;