sql - Postgres - 如何找到某一列的最大交集的行

Question

我有一个名为 Protocols 的表，其中包含一个名为 keyWords 的列，该列的类型为 TEXT[]。给定一个字符串数组，如何获取 keyWords 列与给定数组的最大交集的行？

Answer 1

使用函数(在其他地方也很有用):

create or replace function array_intersect(anyarray, anyarray)
returns anyarray language sql as $function$
    select case 
        when $1 is null then $2
        else
            array(
                select unnest($1)
                intersect
                select unnest($2)
            )
        end;
$function$;

查询:

with cte as (
    select 
        id, keywords,
        cardinality(array_intersect(keywords, '{a,b,d}')) as common_elements
    from protocols
    )
select * 
from cte
where common_elements = (select max(common_elements) from cte)

DbFiddle.

如果您不喜欢该功能:

with cte as (
    select id, count(keyword) as common_elements
    from protocols
    cross join unnest(keywords) as keyword
    where keyword = any('{a,b,d}')
    group by 1
    )
select id, keywords, common_elements
from cte
join protocols using(id)
where common_elements = (select max(common_elements) from cte);

DbFiddle.