Búsquedas complejas en Postgresql

La mayoría de las veces realizaremos búsquedas por patrones conocidos y fáciles de obtener, simples y repetitivos, búsquedas directas, o parciales utilizando el comando “like“. Pero otras veces la información que intentamos localizar no se encuentra formateada como esperamos, conteniendo diferentes símbolos o signos repartidos entre el texto, lo cual dificulta enormemente encontrar los datos que buscamos.

Postgres cuenta con la función “similarity“. Esta función elimina todos los caracteres extraños; guiones, comas, puntos, signos de admiración y exclamación, etc. Dejando el texto lo más limpio posible, y divide la cadena resultante en trigramas. Realizando la comparación entre estos trigramas se obtiene un porcentaje de similitud. Este procedimiento funciona realmente bien al realizar búsquedas con cadenas complejas pero el problema es que es realmente lento. En una tabla con 200.000 registros, realizar una búsqueda con similarity puede llevar de 6 a 10 segundos, incluso más.

Para utilizar similarity en Postgres, necesitamos instalar la extensión pg_trgm con el siguiente comando:

CREATE EXTENSION pg_trgm;

Podemos obtener el trigrama de una cadena con:

select show_trgm('A-Team Year?, the')

Obtendremos el siguiente array:

{”  a”,”  t”,”  y”,” a “,” te”,” th”,” ye”,”am “,”ar “,eam,ear,”he “,tea,the,yea}

Para realizar una busqueda con similarity utilizamos la siguiente consulta:

SELECT * FROM filmografia
WHERE similarity('A-Team Year?, the', titulo) > 0.90

Titulo, es el campo en el que realizamos la búsqueda. 0.90 significa el porcentaje de similitud que requerimos. Si la comparación es superior al porcentaje requerido, se incluirá en los resultados. Podemos jugar con diferentes porcentajes hasta encontrar el que mejor se adapte a las búsquedas requeridas.

Este método es muy efectivo al realizar búsquedas, aunque también es muy lento.

Búsquedas contranslate

Otro método de búsqueda que nos puede resultar útil es utilizar la función “translate“. Con translate podemos reemplazar una serie de caracteres por otros:

select lower(translate('A-Team Year?, the', ' ,-,?,(,)', ''))

Con la consulta anterior obtenemos: “ateamyearthe

Como vemos se han eliminado todos los caracteres superfluos y los espacios, e igualando la cadena de búsqueda con las cadenas a explorar, podremos realizar búsquedas más complejas con buenos resultados.

Aquí la utilizamos para realizar búsquedas eliminando todos los caracteres innecesarios:

SELECT * FROM filmografia
WHERE LOWER(translate(titulo, ' ,-,?,(,)', '')) = LOWER(translate('A-Team Year?, the', ' ,-,?,(,)', ''))

Búsquedas con “Expresiones regulares”

Al realizar determinadas búsquedas también podemos utilizar “expresiones regulares“. Nos permiten obtener resultados que de otra forma seria mucho más complicado o no seria posible.

Si por ejemplo queremos buscar todas las cadenas que comienzan por un número podemos utilizar la siguiente expresión:

SELECT * FROM filmografía
WHERE titulo ~ '^[0-9].*'

Obtendremos todas las cadenas que por lo menos el primer carácter sea un número.

Existen infinidad de formas en las que podemos obtener aquellos datos que necesitamos, aquí, solo hemos comentado algunas de ellas.



Si encuentras esta noticia interesante la puedes compartir en tus redes sociales, a tus seguidores les puede gustar. Utiliza los botones que tienes más abajo.

Share