gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-03 22:48:31 24 4
gpt4 key购买 nike

使用 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(Relevance)... 然后在 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

最佳答案

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)

关于sql-server - 返回列最大值百分比范围内的行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7673643/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com