sql - 按相关性查询和排序

Question

我在这样的查询中有多个条件:

SELECT * FROM image WHERE name LIKE '%text%' AND group_id = 10 LIMIT 1

WHERE 语句由 3 个条件组成:

• 文本匹配
• 外键匹配

如果我想按相关性对结果进行排序，那么取决于:

1. 文本的匹配程度
2. 满足多少条件(例如文本匹配和外键)

这是两个合而为一的问题，但我认为有时这些问题结合起来会派上用场。我指的是我以前的帖子 ( Way to try multiple SELECTs till a result is available? ) 提出的问题。

提前致谢!

Answer 1

要了解文本匹配的准确程度，您需要 fuzzystrmatch PostgreSQL 模块。它提供了 difference 和 levenshtein 函数，可以让您估计两个字符串之间的相似度。

然后，您可以使用以下条件构建查询:

    SELECT *
      FROM image
     WHERE name LIKE '%text%' 
       AND ( group_id = 10 
             OR second_field = 4
             OR thirth_field = 5
           )
    ORDER BY ( (group_id=10)::int
               + (second_field=4)::int
               + (thirth_field=5)::int
             ) * weight_1
             + (strlen(name) - levenshtein('text',name)) * weight_2

您可以调整 weight_1 和 weight_2 以优先考虑文本距离或满足条件的数量。