Inicio > SQL > Normalización de cadenas de texto en PL/SQL

Normalización de cadenas de texto en PL/SQL

La normalización de cadenas de texto es uno de los temas recurrentes en el desarrollo de procesos ETL. Cuando se tratan datos anagráficos, es muy común encontrarse con duplicados debidos a errores tipográficos y de data entry.

Consideremos el siguiente ejemplo:

Ejemplo Datos Duplicados

Se puede ver que los dos registros se refieren a la misma persona física, pero hay elementos que impiden considerar los registros como duplicados perfectos. El NIF ha sido transcrito mal. Es presumible que el error venga de una introducción manual de los datos ya que la N y la H están muy cerca en el teclado español. Tanto el nombre como el domicilio aparecen en dos formatos distintos, mientras que en el caso de la población es el acento el que hace que no coincidan las dos cadenas.

¿Qué herramientas tenemos en PL/SQL para matchear los dos registros y eliminar los duplicados?

La función SOUNDEX devuelve una representación fonética de una cadena. El resultado es la codificación de cómo se pronunciaría un texto en inglés. A pesar de que estemos trabajando con texto en otro idioma, la función puede resultar útil.

Si la aplicamos por ejemplo a la población, tendremos:

SELECT POBLACIO, SOUNDEX(POBLACIO)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

Ejemplo SOUNDEX

En este caso, el código resultante es el mismo y podríamos utilizarlo para considerar que tienen igual valor.

En otros casos, como el nombre, el resultado de la función SOUNDEX no nos ayuda.

SELECT NOMDES, SOUNDEX(nomdes)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

El package UTL_MATCH contiene unas funciones desarrolladas para facilitar la detección de duplicados. Hay cuatro funciones:

  • EDIT_SIMILARITY: la función calcula la distancia de Levenshtein. Esta medida debe el nombre al científico ruso que desarrolló un algoritmo para medir la distancia entre dos cadenas de texto s1 y s2. La distancia se calcula como numero de inserciones, cambios, cancelaciones de caracteres que permiten pasar de la cadena s1 a la cadena s2. La función devuelve un número que representa la distancia entre las dos cadenas: 0 indica dos cadenas idénticas. En el ejemplo, si asumimos que el formato correcto es “NOMBRE APELLIDO”, la función devuelve:
SELECT nomdes, utl_match.edit_distance(NOMDES, 'SCOTT TIGER')
FROM clientes
WHERE NOMDES LIKE '%SCOTT%'Ejemplo EDIT_DISTANCE

La distancia entre las dos cadenas no es muy grande y podríamos considerar las dos cadenas como coincidentes.

  • EDIT_DISTANCE_SIMILARITY: actúa como la función EDIT_SIMILARITY pero devuelve un valor normalizado entre 100 (cadenas coincidentes) y 0 (total discordancia).
  • JARO_WINKLER: esta función utiliza el algoritmo de Jaro-Winkler que calcula un índice de similitud entre dos cadenas, para intentar tener en cuenta posibles errores tipográficos. El valor 1 representa dos cadenas coincidentes.
    Ejemplo JARO-WINKLER
  • JARO_WINKLER_SIMILARITY: la función utiliza el algoritmo de Jaro-Winkler y normaliza los resultados entre 0 y 100.

Las funciones del paquete UTL_MATCH permiten definir un umbral de aceptabilidad para establecer si dos cadenas coinciden “lógicamente” y pueden resultar una herramienta muy útil en el proceso de data cleansing.

Categorías:SQL Etiquetas: , ,
  1. Fernando Galindo
    octubre 16, 2013 en 21:16

    Muchas Gracias por el post Cristina, justamente esta semana pasada me tocó investigar sobre este tema.

    Seguro que me sirve de ayuda!

  2. Joan
    octubre 16, 2013 en 22:26

    Molt bó!

  3. Javier
    octubre 31, 2014 en 22:41

    Muy explicito tu articulo y util, gracias, aunque me gustaria poder saber cuando es mas conveniente uno u otro algoritmo, Levenshtein o Jaro-Winkler

    • noviembre 2, 2014 en 02:08

      Hola Javier, cada una de las funciones explicadas evoluciona o perfecciona un poco más las anteriores.

      Dependiendo del objetivo concreto de cada ocasión, resultará más conveniente una o u otra función, ya que en el fondo … tanto Levenshtein como Jaro-Winkler permitirían responder a las mismas preguntas, con simplemente invertir la condición.

      Si necesitas determinar si dos cadenas son diferentes, edit_distance_similarity (Levenshtein), mientras que si intentas determinar si son iguales, Jaro-Winkler.

      • Javier
        noviembre 5, 2014 en 15:12

        gracias por responder, me fue de mucha utilidad, con esa informacion ya puedo implementar mejor la solucion que estoy buscando.
        gracias otra vez
        saludos javier

  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: