如何在全文搜索中将权重分配给不同的列？ [英] How do I assign weights to different columns in a full text search?

问题描述

在我的全文搜索查询中，我想为特定列指定更高的权重。考虑这个查询：

pre $ SELECT Key_Table.RANK，FT_Table。* FROM Restaurants AS FT_Table
INNER JOIN FREETEXTTABLE（Restaurants， *，'chilly chicken'）AS Key_Table
ON FT_Table.RestaurantID = Key_Table。[KEY]
ORDER BY Key_Table.RANK DESC


现在，我希望名称列在结果中具有更高的权重（名称，关键字和位置为全文索引）。目前，如果结果在三列中的任何一列中找到，则排名不受影响。

例如，我想要一个名为Chilly Chicken的行的排名高于关键字Chilly Chicken的排名，但是是另一个名字。

编辑：

我不急于使用ContainsTable，因为那意味着将短语（Chilly和Chicken，等等），这将涉及我不得不搜索所有可能的组合 - 寒冷和鸡，寒冷或鸡等我想FTS引擎自动找出哪些结果匹配最好，我认为FREETEXT这样做很好。

如果我误解了CONTAINS / CONTAINSTABLE的工作原理，道歉。

解决方案

最好的解决方案是使用ContainsTable。使用联合来创建一个查询，该查询会搜索所有3列，并添加一个整数，用于指示搜索了哪一列。按照整数对结果进行排序，然后排名desc。

排名是sql server的内部数据，不是您可以调整的。

您还可以通过将整数除以整数（名称除以1，关键字和位置2或更高）来操纵返回的级别。这会导致出现不同的排名。

下面是一些示例sql
：
- 建议使用开始更改跟踪并启动后台updateindex（请参阅在线书籍）
$ b

 
 SELECT 1 AS ColumnLocation，Key_Table.Rank，FT_Table。* FROM Restaurants AS FT_Table 
 INNER JOIN ContainsTable（Restaurant ，Name，'chilly chicken'）AS Key_Table ON 
 FT_Table.RestaurantId = Key_Table。[Key] 
 
 UNION SELECT 2 AS ColumnLocation，Key_Table.Rank，FT_Table。* FROM Restaurants AS FT_Table 
 INNER JOIN ContainsTable（Restaurant，Keywords，'chilly chicken'）AS Key_Table ON 
 FT_Table.RestaurantId = Key_Table。[Key] 
 
 UNION SELECT 3 AS ColumnLocation，Key_Table.Rank， FT_Table。* FROM Restaurants AS FT_Table 
 INNER JOIN ContainsTable（Restaurant，Location，'chilly chicken'）AS Key_Table ON 
 FT_Table.RestaurantId = Key_Table。[Key] 
 
 ORDER BY ColumnLocation，Rank DESC 
 

在生产环境中，我会将查询的输出插入到表变量中，以便在返回结果之前执行任何其他操作（在这种情况下可能不需要）。另外，请避免使用*，只列出您真正需要的列。

编辑：您使用ContainsTable是正确的，您必须将关键字修改为''寒冷*和鸡* *，我使用标记输入短语的过程来做到这一点。如果你不想这样做，只需用FreeTextTable替换上面的ContainsTable的每个实例，查询仍然可以工作。

In my full text search query, I want to assign particular columns a higher weightage. Consider this query:

SELECT Key_Table.RANK, FT_Table.* FROM Restaurants AS FT_Table
INNER JOIN FREETEXTTABLE(Restaurants, *, 'chilly chicken') AS Key_Table
ON FT_Table.RestaurantID = Key_Table.[KEY]
ORDER BY Key_Table.RANK DESC

Now, I want the Name column to have a higher weightage in the results (Name, Keywords and Location are full-text indexed). Currently, if the result is found in any of the three columns, the ranks are not affected.

For example, I'd like a row with Name "Chilly Chicken" to have higher rank than one with Keywords "Chilly Chicken", but another name.

Edit:

I'm not eager to use ContainsTable, because that would mean separating the phrases (Chilly AND Chicken, etc.), which would involve me having to search all possible combinations - Chilly AND Chicken, Chilly OR Chicken, etc. I would like the FTS engine to automatically figure out which results match best, and I think FREETEXT does a fine job this way.

Apologies if I've misunderstood how CONTAINS/CONTAINSTABLE works.

解决方案

The best solution is to use ContainsTable. Use a union to create a query that searches all 3 columns and adds an integer used to indicate which column was searched. Sort the results by that integer and then rank desc.

The rank is internal to sql server and not something you can adjust.

You could also manipulate the returned rank by dividing the rank by the integer (Name would be divided by 1, Keyword and Location by 2 or higher). That would cause the appearance of different rankings.

Here's some example sql
: --Recommend using start change tracking and start background updateindex (see books online)

    SELECT 1 AS ColumnLocation, Key_Table.Rank, FT_Table.* FROM Restaurants AS FT_Table
 INNER JOIN ContainsTable(Restaurant, Name, 'chilly chicken') AS Key_Table ON 
FT_Table.RestaurantId = Key_Table.[Key]

UNION SELECT 2 AS ColumnLocation, Key_Table.Rank, FT_Table.* FROM Restaurants AS FT_Table
 INNER JOIN ContainsTable(Restaurant, Keywords, 'chilly chicken') AS Key_Table ON 
FT_Table.RestaurantId = Key_Table.[Key]

UNION SELECT 3 AS ColumnLocation, Key_Table.Rank, FT_Table.* FROM Restaurants AS FT_Table
 INNER JOIN ContainsTable(Restaurant, Location, 'chilly chicken') AS Key_Table ON 
FT_Table.RestaurantId = Key_Table.[Key]

ORDER BY ColumnLocation, Rank DESC

In a production environment, I would insert the output of the query into a table variable to perform any additional manipulation before returning the results (may not be necessary in this case). Also, avoid using *, just list the columns you really need.

Edit: You're right about using ContainsTable, you would have to modify the keywords to be '"chilly*" AND "chicken*"', I do this using a process that tokenizes an input phrase. If you don't want to do that, just replace every instance of ContainsTable above with FreeTextTable, the query will still work the same.

这篇关于如何在全文搜索中将权重分配给不同的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！