Determine how similar two arrays are in PostgreSQL-CodePudding

I'm aware you can compare two arrays in PostgreSQL to see if the elements in one are contained in the elements of another like so,

SELECT ARRAY[1,2] <@ ARRAY[1,2,3] --> true

Is there any way to get # of matches or say "if matches 2 of 3" ??

SELECT ARRAY[1,2] ?? ARRAY[1,2,3] --> 2/3 or 66.6666%

I'm open to interesting solutions.. I want to take an array and ultimately say it must match 2 of 3 elements from another array in an inline query.. or >= 66% or something of that nature.

Ideally like this..

SELECT * FROM SOMETABLE WHERE ARRAY[1,2] ?? ARRAY[1,2,3] >= 66.66666666666667

Thanks in advance.

CodePudding user response：

From here Array functions

with match as 
    (select count(a) as match_ct from unnest(ARRAY[1,2] ) as a 
join 
    (select * from  unnest(ARRAY[2,1,3]) as b)  t on a=t.b) 
select 
     match_ct/total_ct::numeric from match,  
     (select count(*) as total_ct from unnest(ARRAY[1,2], ARRAY[2,1,3]) as t(a, b)) as total ;
        ?column?        
------------------------
 0.66666666666666666667

CodePudding user response：

You could create a function for that:

CREATE FUNCTION array_similarity(anyarray, anyarray)
   RETURNS double precision
   LANGUAGE sql
   IMMUTABLE STRICT AS
$$SELECT 100.0 * count(*) / cardinality($2)
FROM unnest($1) AS a1(e1)
WHERE ARRAY[e1] <@ $2$$;