如何按大多数加权匹配排序

Question

我在下表中试图找到有匹配的爱或需求的用户。

create table info (id integer, love text[], want text[]);
insert into info (id, love, want) values (1, '{run, bike, swim}', '{dance, bike. sit}');
insert into info (id, love, want) values (2, '{sit, bike, dance}', '{run}');
insert into info (id, love, want) values (3, '{jump. hike, swim}', '{bike}');
select * from info where '{sit, hike, drive}' && love or '{bike, eat, draw}' && want;

我想按比赛次数顺序退货，但爱的重量是想要的重量的2倍。这意味着，如果在有3个匹配的愿望但有2个匹配的爱的情况下找到匹配，则具有2个匹配爱的匹配的权重会更高。我该怎么做？

此外，是否可以对每个加权级别的结果进行混洗？例如，如果5行具有3的权重，8行具有4的权重，则我将在具有3的加权的5行之前返回具有4的加权的8行，但是8行和5行的顺序将是随机的。

Answer 1

如果我理解正确，你想通过<nr love matches> * <love weight> + <nr want matches> * <want weight>订购

PostgreSQL中没有计算数组交集的原生函数，但您可以很容易地使用之类的函数来添加它

CREATE FUNCTION array_intersect(anyarray, anyarray)
RETURNS anyarray
language sql
as $function$
SELECT ARRAY(SELECT unnest($1) INTERSECT SELECT unnest($2));
$function$;

之后，您可以像一样查询表格

SELECT
*,
cardinality(array_intersect('{sit, hike, drive}', love)) * 2 
+ cardinality(array_intersect('{sit, hike, drive}', want)) 
AS weight
FROM info
WHERE
'{sit, hike, drive}' && love
OR '{bike, eat, draw}' && want
ORDER BY
weight DESC,
RANDOM()

注意：对于大型数据集，这可能会变得非常低效。