返回列的最大值的百分比范围内的行

Question

使用SQL Server 2005全文搜索，我想返回值的最大相关结果的百分比内的搜索。

 SELECT 
 A.ActivityID,
 KEY_TBL.Rank as Relevance, 
 DENSE_RANK() OVER (ORDER BY  Rank DESC) as SearchRank
 FROM Activity A 
 INNER JOIN FREETEXTTABLE(vwActivitySearch, FTS,'My search expression') AS KEY_TBL ON A.ActivityID = KEY_TBL.[KEY]

的回报:

    ActivityID  Relevance   SearchRank
    ----------- ----------- --------------------
    89378       242         1
    89406       242         1
    88083       236         2
    88214       236         2
    84007       197         3
    83434       197         3
    13017       172         4
    89247       164         5
    89346       164         5

我希望返回的值大于最大相关性的90%或任意百分比，而不是按排名返回，因此在本例中

 WHERE Relevance>(242*0.9). 

我相信有一个简单的方法可以做到这一点，但是我看不出来。

一些约束-

• 查询是UDF中的CTE表达式。
• 我可以很容易地运行一个初始查询获得@MAXRelevance= SELECT MAX(相关性)…然后在WHERE子句中使用Max(Relevance)，但全文搜索不保证对重复搜索的相关性结果返回相同的绝对值。

现有功能:

 CREATE FUNCTION [dbo].[xxActivitySearch] (@SearchTerm varchar(255)='',@ResultDepth int)
 RETURNS @ReturnTable Table (ActivityID int,Relevance int,SearchRank int)
 AS
 BEGIN
 WITH T AS (
 SELECT 
   A.ActivityID,
   KEY_TBL.Rank as Relevance, 
   DENSE_RANK() OVER (ORDER BY  Rank DESC) as SearchRank
 FROM Activity A 
INNER JOIN FREETEXTTABLE(vwActivitySearch, FTS,@SearchTerm) AS KEY_TBL ON A.ActivityID=KEY_TBL.[KEY])
INSERT @ReturnTable SELECT * FROM T WHERE (SearchRank<=@ResultDepth)
RETURN
END 

Answer 1

WITH T AS
(
SELECT A.ActivityID,
       KEY_TBL.Rank                           as Relevance,
       DENSE_RANK() OVER (ORDER BY Rank DESC) as SearchRank,
       MAX(KEY_TBL.Rank) OVER() AS MaxRelevance
FROM   Activity A
       INNER JOIN 
         FREETEXTTABLE(vwActivitySearch, FTS, 'My search expression') AS KEY_TBL
         ON A.ActivityID = KEY_TBL.[KEY]  
)
SELECT ActivityID,
       Relevance,
       SearchRank
FROM T
WHERE Relevance>(MaxRelevance*0.9)