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:
- Less_important_field &
- 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.
精彩评论