funciones - ¿Cómo crear una búsqueda difusa simple con Postgresql solamente?

postgresql pdf

Question

Tengo un pequeño problema con la funcionalidad de búsqueda en mi sitio basado en RoR. Tengo muchos Produts con algunos CÓDIGOS. Este código puede ser cualquier cadena como "AB-123-lHdfj". Ahora uso el operador ILIKE para encontrar productos:

Product.where("code ILIKE ?", "%" + params[:search] + "%")

Funciona bien, pero no puede encontrar productos con códigos como "AB123-lHdfj" o "AB123lHdfj".

¿Qué debo hacer para esto? ¿Puede ser postgresql tiene alguna función de normalización de cadenas, o algún otro método para ayudarme? :)

Answer 1

Pablo te habló de levenshtein() . Es una herramienta muy útil, pero también es muy lenta con tablas grandes. Tiene que calcular la distancia de levenshtein del término de búsqueda para cada fila, eso es caro.

En primer lugar, si sus requisitos son tan simples como lo indica el ejemplo, aún puede usar LIKE . Simplemente reemplace cualquiera - en su término de búsqueda con % para crear la cláusula WHERE

WHERE code LIKE "%AB%123%lHdfj%"

en lugar de

WHERE code LIKE "%AB-123-lHdfj%"

Si su problema real es más complejo y necesita algo más rápido, entonces, dependiendo de sus requisitos, hay varias opciones.

• Hay búsqueda de texto completo , por supuesto. Pero esto puede ser una exageración en su caso.

• Un candidato más probable es pg_trgm . Tenga en cuenta que puede combinar eso con LIKE en PostgreSQL 9.1. Ver esta entrada de blog por Depesz .
  También muy interesante en este contexto: la función de similarity() o el operador % de ese módulo. Más:

  • Variaciones del rendimiento de la consulta de PostgreSQL LIKE

• Por último, pero no menos importante, puede implementar una solución tejida a mano con una función para normalizar las cadenas a buscar. Por ejemplo, podría transformar AB1-23-lHdfj -> ab123lhdfj , guardarlo en una columna adicional y buscarlo con los términos de búsqueda que se han transformado de la misma manera.

  O use un índice en una expresión en lugar de la columna redundante. (Las funciones involucradas deben ser IMMUTABLE ). Y posiblemente combine eso con pg_tgrm desde arriba.

Visión general de las técnicas de comparación de patrones:

• La coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

Answer 2

Postgres proporciona un módulo con varias funciones de comparsión de cadenas como soundex y metaphone. Pero querrás usar la función de distancia de edición levenshtein .

Example:
test=# SELECT levenshtein(''GUMBO'', ''GAMBOL'');
levenshtein
-------------
2
(1 row)

El 2 es la distancia de edición entre las dos palabras. Cuando aplique esto contra varias palabras y ordene por el resultado de la distancia de edición, tendrá el tipo de coincidencias difusas que está buscando.

Pruebe este ejemplo de consulta: (con sus propios nombres de objetos y datos, por supuesto)

SELECT *
FROM some_table
WHERE levenshtein(code, ''AB123-lHdfj'') <= 3
ORDER BY levenshtein(code, ''AB123-lHdfj'')
LIMIT 10

Esta consulta dice:

Dame los 10 mejores resultados de todos los datos de some_table donde la distancia de edición entre el valor del código y la entrada ''AB123-lHdfj'' es menor que 3. Volverá todas las filas donde el valor del código tenga una diferencia de 3 caracteres a '' AB123-lHdfj ''...

Nota: si obtienes un error como:

function levenshtein(character varying, unknown) does not exist

Instala la extensión fuzzystrmatch usando:

test=# CREATE EXTENSION fuzzystrmatch;