开发者

Precedence/weight to a column using FREETEXTTABLE in dymnamic TSQL

I have dynamic sql that perform paging and a full text search using CONTAINSTABLE which works fine. Problem is I would like to use FREETEXTTABLE but weight the rank of some colums over others

Here is my orginal sql and the ranking weight I would like to integrate (I have changed names for privacy reasons)

SELECT * FROM 
    (SELECT TOP 10 Things.ID, ROW_NUMBER() 
        OVER(ORDER BY KEY_TBL.RANK DESC ) AS Row FROM [Things]
    INNER JOIN 
        CONTAINSTABLE([开发者_如何学运维Things],(Features,Description,Address),
            'ISABOUT("cow" weight (.9), "cow" weight(.1))') AS KEY_TBL 
    ON [Properties].ID = KEY_TBL.[KEY] 
    WHERE TypeID IN (91, 48, 49, 50, 51, 52, 53) 
        AND
        dbo.FN_CalcDistanceBetweenLocations(51.89249, -8.493376, 
        Latitude, Longitude) <= 2.5 
    ORDER BY KEY_TBL.RANK DESC ) x 
WHERE x.Row BETWEEN 1 AND 10

Here is what I would like to integrate

select sum(rnk) as weightRankfrom
From
(select 
    Rank * 2.0 as rnk, 
    [key] 
from freetexttable(Things,Address,'cow')
union all 
select 
    Rank * 1.0 as rnk, 
    [key] 
from freetexttable(Things,(Description,Features),'cow')) as t 
group by [key] 
order by weightRankfrom desc 


Unfortunately, the algorithm used by the freetext engine (FREETEXTTABLE) has no way to specify the significance of the various input columns. If this is critical, you may need to consider using a different product for your freetext needs.


You can create a column with the concatenation of:

  1. Less_important_field &
  2. More_important_field & More_important_field (2x)

This might look really stupid, but it's actually what BM25F does to simulate structured documents. The only downside of this hack-implementation is that you can't actually dynamically change the weight. It bloats up the table a bit, but not necessarily the index, which should only need counts.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜