Calidad del dato: Usar PL/SQL con cadenas de texto para estandarizar

La estandarización de cadenas de texto es uno de los temas recurrentes en el desarrollo de procesos ETL. Cuando se tratan datos que definen a personas físicas o jurídicas, es muy común encontrarse con duplicados debidos a errores tipográficos y de entradas de datos (y mas si son nomtbres de procedencia extranjera, árabe, chino, ...).

Consideremos el siguiente ejemplo:

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 Y y la T están muy cerca en el teclado español. Tanto el nombre como la dirección es el acento el que hace que no coincidan las dos cadenas , mientras que en el caso de la población aparecen en dos formatos distintos.

¿Qué herramientas tenemos en PL/SQL para comparar 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 DIRECCION, SOUNDEX(DIRECCION) FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';

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

• EDIT_DISTANCE: 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 Nombre, utl_match.edit_distance(Nombre, 'Rafael Montesinos')
FROM personas_ejemplos WHERE Nombre LIKE '%Rafael%';

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.

SELECT NOMBRE, utl_match.JARO_WINKLER(NOMBRE, 'Rafael Montesinos')

FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';

• JARO_WINKLER_SIMILARITY: la función utiliza el algoritmo de Jaro-Winkler y normaliza los resultados entre 0 y 100.

SELECT NOMBRE, utl_match.JARO_WINKLER_SIMILARITY(NOMBRE, 'Rafael Montesinos')

FROM personas_ejemplos WHERE NOMBRE LIKE '%Rafael%';

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 para limpiar datos en ETL.