根据田间组合权重排序结果 [英] ordering results based on combine weightage of fields

问题描述

大家都知道任何基于电子商务的购物车的主要要求之一是一个很好的搜索。

几天前，我开始了解MySQL全文搜索，我必须说它很安静。

我想在搜索查询中领先一步，并且需要为字段提供权重。这些权重将允许对搜索结果进行排序。

例如：我在产品表中有以下字段

  productid 
名称
描述
价格
  

'name'和'description'字段为全文索引。

对于任何搜索关键字，查询都应该查看两个字段：name，description。但是名称字段应该比描述字段有更多的权重。

Incase，如果您还没有很好地理解我的问题，那么以下示例应该有所帮助。 b
$ b

  productid名称描述价格
 1个漂亮的键盘这是一个漂亮的键盘10 
 2个漂亮的键盘，这是一个不错的键盘键盘10 
 3键盘这是一个不错的键盘10 
  

当搜索关键字为'nice'时，'name'字段的权重为10，'description'字段的权重为1：1 b

对于productid：1，发现1（名字） 2（两次）描述。所以结合权重为（1 * 10 + 2 * 1 = 10 + 2 = 12）

为productid：2，发现2（两次）名字，2 （两次）描述。所以结合权重为（2 * 10 + 2 * 1 = 20 + 2 = 22）

为productid：3，发现很好0（无）名字，1 （一次）描述。所以组合权重是（0 * 10 + 1 * 1 = 0 + 1 = 1）

2个问题

（1）我如何提供特定领域的权重？我需要再次创建全文索引来提供权重吗？

（2）我需要查询，它可以用desc输出结果。

解决方案

全文搜索可以为您提供搜索的相对相关性：

  SELECT * FROM 
（
 SELECT id，
（MATCH（name）AGAINST（'nice'）* 10 ）+ MATCH（描述）AGAINST（'nice'）AS相关性
 FROM产品
匹配（名称）反对（'nice'）或
 MATCH（描述）反对（'nice'） 
）ORDER BY revelence DESC 
  

通过将名称相关性乘以10，我已经偏离了相关性得分与名字的比值

I have a very interesting question for all you SQL masters out there.

As you all know that one of the prime requirement of any e-commerce based shopping cart is a good search.

Few days back, I got to know about MySQL full-text search, and I must say it's quiet interesting.

I want to go one step ahead in search query, and need to provide weightage to fields. These weightage would allow sorting of search result.

For example: I have following fields in product table

productid
name
description
price

'name' and 'description' fields are full-text indexed.

For any search keyword, query should look into both fields: name, description. But name field should have more weightage compared to description field.

Incase, if you haven't understand my question well, then following example should help.

 productid             name                   description                price
 1                  nice keyboard      this is a nice nice keyboard     10                
 2                  nice nice keyboard this is a nice nice keyboard     10                
 3                  keyboard           this is a nice keyboard     10                

when search keyword is 'nice', and 'name' field have weightage: 10, and 'description' field have weightage: 1

for productid: 1, nice was found 1 (once) in name, 2 (twice) in description. so combine weightage is (1*10 + 2*1 = 10 + 2 = 12)

for productid: 2, nice was found 2 (twice) in name, 2 (twice) in description. so combine weightage is (2*10 + 2*1 = 20 + 2 = 22)

for productid: 3, nice was found 0 (none) in name, 1 (once) in description. so combine weightage is (0*10 + 1*1 = 0 + 1 = 1)

2 questions

(1) How can I provide weightage to a particular field? Do I need to create full-text index again to provide weightage?

(2) I need query, which can output results in desc. order of combine weightage.

解决方案

The full text search can give you the relative relevance of the search:

SELECT * FROM
(
    SELECT id,
    (MATCH(name) AGAINST ('nice')*10 ) + MATCH(description) AGAINST('nice') AS relevance
    FROM products
    WHERE MATCH(name) AGAINST ('nice') OR
    MATCH(description) AGAINST ('nice')
) ORDER BY revelence DESC

By multiplying the name relevance by 10 I have skewed the relevance score towards that of names

这篇关于根据田间组合权重排序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！