在 SQLite FTS3 搜索中包含部分匹配

发布于 2024-11-17 18:39:36 字数 644 浏览 2 评论 0原文

我的简单 SQLite FTS3 实现当前将用户查询与存储的结果进行匹配，并返回它们，只要这些结果包含用户查询中的所有单词（换句话说，FTS 表是使用简单的标记生成器创建的）。

现在，我想让搜索更加智能，我希望它根据查询中与结果匹配的单词数对结果进行排名/排序。例如，

SELECT name FROM nametable WHERE name MATCH 'fast food restaurant'

当前返回的内容

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT
CHIPOTLE FAST FOOD
PIZZA FAST FOOD
GOOD FOOD OUTLET

应该按该顺序。换句话说，搜索输出不应限于必须包含用户查询的所有单词的结果，而是将包含所有单词的结果放置在列表中较高的位置，同时为返回部分匹配的结果腾出空间。

一种简单的方法是使用用户输入的所有排列来运行多个查询，并按所需的顺序对结果进行 UNION，但这效率相当低。

原文

My simple SQLite FTS3 implementation currently matches user queries with stored results and returns them as long as these results contain all the words in the user query (in other words, the FTS table was created using a simple tokenizer).

Now, I want to make the search more intelligent in that, I want it to rank/order results based on the number of words in the query that match results. For instance,

SELECT name FROM nametable WHERE name MATCH 'fast food restaurant'

which currently returns

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT

should return

SUBWAY FAST FOOD RESTAURANT
MCDONALDS FAST FOOD RESTAURANT
CHIPOTLE FAST FOOD
PIZZA FAST FOOD
GOOD FOOD OUTLET

in that order. In other words, the search output should not be restricted to results which necessarily contain all words of user queries, rather, place results that contain all words higher up in the list while making room for results that return a partial match.

One simple way of doing this is to run multiple queries using all permutations of the user's input and UNION the results in the required order, but that would be rather inefficient.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最冷一天 2024-11-24 18:39:36

更容易做到：

SELECT name FROM nametable WHERE name MATCH 'fast OR food OR restaurant'

无需处理多个查询。

More easily just do:

SELECT name FROM nametable WHERE name MATCH 'fast OR food OR restaurant'

No need to handle multiple queries.

回复收藏 0 原文

镜花水月 2024-11-24 18:39:36

这是一个未经测试的想法。

SELECT name
FROM (
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'fast'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'food'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'restaurant'
  )
GROUP BY name
ORDER BY SUM(matched) DESC, name

Here is an untested idea.

SELECT name
FROM (
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'fast'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'food'
    UNION ALL
      SELECT name, 1 as matched
      FROM nametable
      WHERE name MATCH 'restaurant'
  )
GROUP BY name
ORDER BY SUM(matched) DESC, name

回复收藏 0 原文

~没有更多了~