Полнотекстовый поиск SQL Server для точного соответствия с резервным

Во-первых, похоже, что нет способа получить точное соответствие, используя полнотекстовый поиск. По-видимому, это очень обсуждаемая проблема при использовании полнотекстового метода поиска, и для достижения желаемого результата существует множество различных решений, однако большинство из них кажется очень неэффективным. Поскольку я вынужден использовать полнотекстовый поиск из-за объема моей базы данных, мне недавно пришлось реализовать одно из этих решений, чтобы получить более точные результаты.

Я не мог использовать результаты ранжирования из полнотекстового поиска из-за того, как он работает. Например, если вы искали фильм под названием Toy Story, а также фильм под названием The Story Behind Toy Story, который появился бы вместо точного соответствия, потому что он нашел слово Story дважды и Toy.

Я отслеживаю свои собственные рейтинги, которые я называю "Популярность" каждый раз, когда пользователь получает доступ к записи, номер увеличивается. Я использую этот datapoint для взвешивания моих результатов, чтобы определить, что пользователь может искать.

У меня также есть проблема, когда иногда приходится возвращаться к поиску LIKE и не возвращать точное соответствие. То есть поиск Goonies должен возвращать The Goonies (самый популярный результат)

Итак, вот пример моей текущей хранимой процедуры для этого:

DECLARE @Title varchar(255)
SET @Title = '"Toy Story"'
--need to remove quotes from parameter for LIKE search
DECLARE @Title2 varchar(255)
SET @Title2 = REPLACE(@title, '"', '')

--get top 100 results using full-text search and sort them by popularity
SELECT TOP(100) id, title, popularity As Weight into #TempTable FROM movies WHERE CONTAINS(title, @Title) ORDER BY [Weight] DESC

--check if exact match can be found
IF EXISTS(select * from #TempTable where Title = @title2)
--return exact match
SELECT TOP(1) * from #TempTable where Title = @title2
ELSE
--no exact match found, try using like with wildcards
SELECT TOP(1) * from #TempTable where Title like '%' + @title2 + '%'
DROP TABLE #TEMPTABLE

Эта хранимая процедура выполняется около 5000 раз в минуту, и она достаточно сумасшедшая, чтобы не довести мой сервер до колен. Но я действительно хочу знать, был ли более эффективный подход к этому? Спасибо.

Ответ 1

Вы должны использовать полнотекстовый поиск CONTAINSTABLE, чтобы найти результаты 100 лучших (возможно 200) кандидатов, а затем упорядочить результаты, найденные вами, используя ваши собственные критерии.

Похоже, вы хотели бы ORDER BY

точное совпадение фразы (=)
полностью соответствующая фраза (LIKE)
более высокое значение для столбца Popularity
Rank из CONTAINSTABLE

Но вы можете поиграть с точным заказом, который вы предпочитаете.

В SQL, который выглядит примерно так:

DECLARE @title varchar(255)
SET @title = '"Toy Story"'
--need to remove quotes from parameter for LIKE search
DECLARE @title2 varchar(255)
SET @title2 = REPLACE(@title, '"', '')

SELECT
    m.ID,
    m.title,
    m.Popularity,
    k.Rank
FROM Movies m
INNER JOIN CONTAINSTABLE(Movies, title, @title, 100) as [k]
    ON m.ID = k.[Key]
ORDER BY 
  CASE WHEN m.title = @title2 THEN 0 ELSE 1 END,
  CASE WHEN m.title LIKE @title2 THEN 0 ELSE 1 END,
  m.popularity desc,
  k.rank

См. SQLFiddle

Ответ 2

Это даст вам фильмы, содержащие точную фразу "История игрушек", упорядоченная по их популярности.

SELECT
    m.[ID],
    m.[Popularity],
    k.[Rank]
FROM [dbo].[Movies] m
INNER JOIN CONTAINSTABLE([dbo].[Movies], [Title], N'"Toy Story"') as [k]
    ON m.[ID] = k.[Key]
ORDER BY m.[Popularity]

Обратите внимание, что вышеизложенное также даст вам "Возвращение Goonies", если вы искали "The Goonies".

Ответ 3

Если у вас возникло ощущение, что вам не очень нравится нечеткая часть полнотекстового поиска, но вам нравится роль производительности.

Возможно, это путь: если вы настаиваете на том, чтобы получить соответствие EXACT перед взвешенным совпадением, вы можете попытаться изменить значение. Например, "История игрушек" → принести в нижний регистр → игрушечный рассказ → Хеш в 4de2gs5sa (с любым хэшем, который вам нравится) и выполнить поиск на хэше.

Ответ 4

В Oracle я использовал UTL_MATCH для аналогичных целей. (http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_match.htm)

Хотя использование алгоритма Jaro Winkler, например, может занять некоторое время, если вы сравните столбец заголовка из таблицы 1 и таблицы 2, вы можете повысить производительность, если вы частично присоединитесь к 2 таблицам. В некоторых случаях я сопоставлял имена людей в таблице 1 с таблицей 2 с использованием Jaro Winkler, но ограничивал результаты не только над определенным порогом Jaro Winkler, но также и именами между двумя таблицами, где первая буква одинакова. Например, я бы сравнил Альберта с Аденом, Альфонсо и Альберто, используя Яро Винклера, но не Альберта и Фрэнка (ограничение числа ситуаций, когда нужно использовать алгоритм).

Яро Винклер может быть действительно подходящим для названий фильмов. Хотя вы используете SQL-сервер (не можете использовать пакет utl_match), похоже, что имеется бесплатная библиотека под названием "SimMetrics", которая имеет алгоритм Jaro Winkler среди других показателей сравнения строк. Вы можете найти подробную информацию об этом и инструкции здесь: http://anastasiosyal.com/POST/2009/01/11/18.ASPX?#simmetrics