Нечеткое соответствие SQL

Надеюсь, я не повторяю этот вопрос. Я сделал поиск здесь и google перед публикацией здесь.

Я запускаю eStore с SQL Server 2008R2 с включенным полным текстом.

Мои требования,

  • Существует таблица продуктов, в которой есть название продукта, коды OEM, модель, в которую входит этот продукт. Все в тексте.
  • Я создал новый столбец TextSearch. Это имеет объединенные значения Название продукта, OEM-код и модель, в которые входит этот продукт. Эти значения разделяются запятой.
  • Когда клиент вводит ключевое слово, мы запускаем поиск в столбце TextSearch для соответствия продуктам. См. Соответствующую логику ниже.

Я использую Hybrid Fulltext и обычно люблю делать поиск. Это дает более релевантные результаты. Все запросы, выполненные в таблицу temp и отдельные элементы, были возвращены.

Логика соответствия,

  • Запустите следующий SQL, чтобы получить соответствующий продукт, используя полный текст. Но @Keywords будут предварительно обработаны. Скажем, CLC 2200 будет изменен на 'CLC * AND 2200 *'

    SELECT Id FROM dbo.Product WHERE CONTAINS (TextSearch, @Keywords)

  • Другой запрос будет выполняться с использованием обычного типа. Таким образом, CLC 2200 будет предварительно обработан в TextSearch, например,% clc% AND TextSearch, например% 2200%. Это просто потому, что полнотекстовый поиск не будет искать шаблоны перед ключевыми словами. Например, он не вернет "pclc 2200".

    SELECT Id FROM dbo.Product WHERE TextSearch, например "% clc%" и "TextSearch", например "% 2200%"

  • Если шаги 1 и 2 не вернули записи, будет выполнен следующий поиск. Значение 135 было настроено мной, чтобы вернуть более релевантные записи.

    SELECT p.id FROM dbo.Product AS p INNER JOIN FREETEXTTABLE (продукт, TextSearch, @Keywords) AS r ON p.Id = r. [KEY] WHERE r.RANK > 135

Все вышеперечисленные комбинированные работы отлично работают с разумной скоростью и возвращают соответствующие продукты для ключевых слов.

Но я ищу для дальнейшего улучшения, когда продукта нет.

Скажем, если клиент ищет "CLC 2200npk" , и этого продукта там не было, мне нужно было показать рядом очень близко "CLC 2200" .

До сих пор я пытался использовать функцию Soundex(). Купите значение soundex для каждого слова в столбце TextSearch и сравнивайте его с значением судекса ключевого слова. Но это возвращает слишком много записей и замедляет работу.

Например, "CLC 2200npk" вернет такие продукты, как "CLC 1100" и т.д. Но это не будет хорошим результатом. Поскольку он не близок к CLC 2200npk

Здесь есть еще одна хорошая . но это использует функции CLR. Но я не могу установить CLR-функции на сервере.

Итак, моя логика должна быть,

если "CLC 2200npk" не найден, нажмите "CLC 2200" , если "CLC 2200" не найден, рядом с ним следует "CLC 1100"

Вопросы

  • Возможно ли совпадение, как предлагается?
  • Если мне понадобится исправление орфографии и поиск, что было бы хорошим способом? Весь наш список продуктов находится на английском языке.
  • Есть ли UDF или SP для соответствия текстам, например, моим предложениям?

Спасибо.

Ответы

Ответ 1

Скорее быстрое решение для конкретного домена может заключаться в вычислении сходства строк с использованием SOUNDEX и числового расстояния между двумя строками. Это действительно поможет, когда у вас будет много кодов продуктов.

Используя простой UDF, как показано ниже, вы можете извлечь числовые символы из строки, чтобы затем получить 2200 из "CLC 2200npk" и 1100 из "CLC 1100", чтобы теперь можно было определить близость на основе вывода SOUNDEX каждого входа, а также близость числовой составляющей каждого входа.

CREATE Function [dbo].[ExtractNumeric](@input VARCHAR(1000))
RETURNS INT
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @input) > 0
    BEGIN
        SET @input = STUFF(@input, PATINDEX('%[^0-9]%', @input), 1, '')
    END
    IF @input = '' OR @input IS NULL
        SET @input = '0'
    RETURN CAST(@input AS INT)
END
GO

Что касается алгоритмов общего назначения, то есть пара, которая может помочь вам с разной степенью успеха в зависимости от размера набора данных и требований к производительности. (обе ссылки имеют реализацию TSQL)

  • Двойной метафон - Этот алгоритм даст вам лучшее совпадение, чем soundex за счет скорости, но это действительно хорошо для коррекции орфографии.
  • Расстояние Левенштейна - это будет подсчитывать, сколько нажатий клавиши, чтобы перевести одну строку в другую, например, для перехода с CLC 2200npk на 'CLC 2200' 3, а от "CLC 2200npk" до "CLC 1100" - 5.

Здесь - интересная статья, в которой используются оба альгоса, которые могут дать вам несколько идей.

Ну, надеюсь, некоторые из них немного помогают.

EDIT: Здесь - это намного более быстрая частичная реализация Levenshtein Distance (прочитайте сообщение, которое он не вернет точно так же, как и обычный). В моей тестовой таблице из 125000 строк она работает через 6 секунд по сравнению с 60 секундами для первой, с которой я связан.