Correspondência difusa SQL
estou a correr um eStore com o servidor SQL 2008R2 com o texto completo activo.
As minhas necessidades,
- existe uma tabela de produtos, que tem nome de produto, Códigos OEM, Modelo em que este produto se encaixa. Todos estão em texto. Criei uma nova coluna chamada TextSearch. Isto tem valores concatenados da Denominação do Produto, do Código OEM e do modelo em que este produto se encaixa. Estes valores estão separados por vírgulas.
- Quando um cliente entra numa palavra-chave, fazemos uma pesquisa na coluna TextSearch para corresponder aos produtos. Veja a lógica correspondente abaixo.
estou a usar um texto híbrido completo e normal gosto de pesquisar. Isto dá resultados mais relevantes. Todas as consultas executadas em uma mesa temporária e distinções foram devolvidas.
lógica de correspondência,
-
Execute após SQL para obter o produto relevante usando o texto completo. Mas @ Keywords serão pré-processados. Dizer "CLC 2200" será alterado para "CLC* e 2200*"
Seleccione o Id do dbo.Produto em que contém (TextSearch ,@Keywords)
-
outra consulta estará a correr usando o normal. Então o 'CLC 2200' será pré-processado para ' TextSearch como %clc% e TextSearch como %2200%'. Isto é simplesmente porque a pesquisa de texto completo não procura padrões antes das palavras-chave. por exemplo, ele não vai retornar 'pclc 2200'.
Seleccione o Id do dbo.Produto em que a pesquisa textual tipo "%clc% " e a pesquisa textual tipo '%2200%'
-
Se os passos 1 e 2 não devolverem nenhum registo, a seguir à pesquisa será executada. O valor 135 foi afinado por mim para devolver registos mais relevantes.
seleccionar p.id da dbo.Produto como p entrada interna FREETEXTTTABLE (produto,pesquisa textual,@Keywords) como R ON p.Id = R. [chave] em que r. RANK > 135
todos os produtos combinados acima funciona bem em uma velocidade razoável e retorna produtos relevantes para palavras-chave.
Mas eu estou procurando melhorar ainda mais quando há não foi encontrado nenhum produto. Digamos que se o cliente procura o CLC 2200npk e este produto não estava lá, eu precisava de mostrar próximo ao CLC 2200.até agora tentei usar a função Soundex(). Buy computing soundex value for each word in TextSearch column and comparing with soudex value of keyword. Mas isso retorna muitos registros e lento também.
por exemplo, o "CLC 2200npk" devolverá produtos como o "CLC 1100", etc. Mas isto não seria um bom resultado. Como it is not CLC 2200npk
Há outro bom aqui. mas isto usa funções CLR. Mas não posso instalar funções CLR no servidor. Então a minha lógica teria de ser:Se 'CLC 2200npk' não for encontrado, mostrar perto por 'CLC 2200' se o 'CLC 2200' não for encontrado, mostrar próximo perto de 'CLC 1100'
perguntas
-
É possível combinar como sugerido?
Se eu precisasse de fazer correcção ortográfica e procurar, qual seria a melhor maneira? Todo da nossa lista de produtos está em inglês.
Há alguma UDF ou SP para combinar textos como as minhas sugestões?
1 answers
Uma solução específica de domínio bastante rápida pode ser calcular uma semelhança de cadeia de caracteres usando o SOUNDEX e uma distância numérica entre 2 cadeias de caracteres. Isso só vai realmente ajudar quando você tem um monte de códigos de produto.
Se usar uma UDF simples como em baixo, poderá extrair os caracteres numéricos de uma cadeia de caracteres para que possa então obter 2200 de 'CLC 2200npk' e 1100 de 'CLC 1100' para que possa agora determinar a proximidade com base na saída de SOUNDEX de cada entrada, bem como a proximidade do valor numérico componente de cada entrada.
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
No que diz respeito aos algoritmos de propósito geral, há um casal que pode ajudá-lo com vários graus de sucesso, dependendo do tamanho do conjunto de dados e requisitos de desempenho. (ambas as ligações têm aplicações TSQL disponíveis)
- Dupla Metafona - Este item dar-lhe-á uma melhor correspondência do que o soundex ao custo da velocidade é realmente bom para correcção ortográfica.
- distância de Levenshtein - isto irá calcule quantas teclas seriam necessárias para transformar uma cadeia em outra, por exemplo, para passar de "CLC 2200npk" para "CLC 2200" é 3, enquanto de "CLC 2200npk" para "CLC 1100" é 5.
Aqui está um artigo interessante que aplica tanto algos juntos que pode lhe dar algumas idéias.
Espero que isso ajude um pouco.Editar: aqui é uma implementação de distância de Levenshtein parcial muito mais rápida (leia a publicação que não irá retornar exatamente o mesmo resultados como o normal). Na minha tabela de testes de 125000 linhas, ele corre em 6 segundos, em comparação com 60 segundos para o primeiro a que me conectei.