Возвращает строки в процентах от максимального значения для столбца

#sql-server #sql-server-2005 #full-text-search

#sql-server #sql-server-2005 #полнотекстовый поиск

Вопрос:

Используя полнотекстовый поиск 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).

Я уверен, что есть простой способ добиться этого, но я его не вижу.

Некоторые ограничения —

Запрос представляет собой выражение CTE в UDF.
Я мог бы легко запустить начальный запрос, чтобы получить @MAXRelevance= SELECT MAX(релевантность)… затем используйте Max(релевантность) в предложении WHERE, но полнотекстовый поиск не гарантирует возврата одних и тех же абсолютных значений для результатов релевантности при повторных поисках.

Существующая функция:

  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

1. Не могли бы вы добавить второй CTE, который выполнял бы SELECT ActivityId, Relevance, SearchRank ИЗ FirstCTE, ГДЕ релевантность> (242 * 0.9) и использовал это в качестве CTE для основного запроса?

2. Хороший момент, Уэйн, который помог мне переориентироваться на это. В результате я решил непосредственную проблему, выполнив нечто очень похожее. Однако я все еще думаю, что должен быть какой-то способ достичь этого в одном запросе, и я просто упускаю трюк. С нетерпением ожидая будущих проектов, я подозреваю, что новые аналитические функции SQL Server Denali msdn.microsoft.com/en-us/library/hh213234 (v= SQL.110).aspx будет большим подспорьем в решении проблем такого типа.

3. Я уверен, что есть и такое. Я продолжу проводить некоторые исследования, поскольку мне интересно решить аналогичную задачу.

Ответ №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)