Entonces tengo una tabla con Nombre, Dirección y Código Postal sin datos de registro adjuntos; y tengo una tabla que tiene todo lo mismo, pero tiene más información y necesito una manera de fusionar las tablas cuando no coinciden al 100%.
¿Cómo los emparejo si no son idénticos? Soy un novato en SQL, pero sé que no coincidirán en su mayor parte y no puedo ser el único con este problema. Sin embargo, el software que haga esto ha demostrado ser difícil.
Escribir software para hacer esto sería incluso peor que tener que hacerlo en primer lugar.
Sé que puedo hacer esto en Excel; Un poco, pero con la cantidad de discos que tengo, resulta difícil superar el millón.
Respuesta1
Solía trabajar en una empresa de marketing de bases de datos (perdón por enviarte correo basura). Nuestro trabajo era averiguar si "Robert Jones 671 Kimbrough SPFD MO 65802" es lo mismo que "Bobbie Joanes 671 Kimbrough St. Sprinfield MO 65809". Si no coincidíamos, corríamos el riesgo de enviar correo duplicado a un cliente potencial. lo que haría que nuestros clientes parecieran tontos y desperdiciarían su dinero.
Nuestro enfoque fue descomponer el problema en dominios más pequeños y aplicar diferentes criterios para responder es A.probablementeB. Una regla de coincidencia demasiado rígida no detectará duplicados. Una regla de coincidencia demasiado laxa desperdiciará clientes potenciales.
Teníamos tres dominios en los que una entidad podía coincidir: nombre, método de contacto, relación. Solo se permitía una coincidencia si coincidíamos en dos de los dominios.
Método de contacto
Un método de contacto fue el correo postal, el correo electrónico o el teléfono.
Direcciones
El primer paso es estandarizar una dirección proporcionada. El objetivo final es tomar su dirección de entrada y corregirla alUSPSestándar. En el ejemplo anterior, probablemente se les entregaría el correo a ambas direcciones, pero sólo porque el cartero entendió la intención del remitente. La verdadera dirección sería
671 S KIMBROUGH AVE SPRINGFIELD MO 65806-3342
Una vez que tenga una dirección coherente, la coincidencia de direcciones será un problema mucho más fácil de resolver. Aún debe preocuparse por las direcciones que no se pueden corregir, así como por las reglas para las ubicaciones de múltiples inquilinos (Suite 200, Apt B, etc.), pero eso es parte del ajuste que necesitaría trabajar con el propietario de la empresa. . Ah, y aunque los +4 dígitos son útiles para la entrega, no permita que influyan en la lógica de coincidencia de direcciones. Es mucho más probable que cambien que el código postal de 5 dígitos.
Otra cosa a tener en cuenta es que las personas se mudan para que pueda obtener información de reenvío de direcciones (NCOA - Cambio Nacional de Dirección) de los últimos X períodos de tiempo si es importante que tenga datos de dirección actuales. Cuando se muda, la documentación de reenvío de dirección solo es válida por un período de tiempo determinado y cualquier persona que le envíe correo después de esa ventana recibirá una devolución al remitente, no un rebote en esta dirección. NCOA enviar el correo antes de enviarlo garantizará que tenga la dirección actual, incluso si el reenvío ha expirado.
Nuestro enfoque fue hacer un hash a partir de la dirección estandarizada (línea 1 + código postal) y lo usaríamos como clave de comparación.
Teléfono
Lo único complicado con respecto al teléfono era si tenían un código de área asociado. Los almacenamos sin separadores ni formato y las extensiones se almacenaron por separado. Esto se reducía a un número de teléfono de 7 o 10 dígitos. Si tuviéramos una dirección, hay un software que puedegeneralmenterellene el código de área. A medida que los códigos de área se dividen, generalmente hay un período de gracia en el que una ubicación podría recibir servicio con 2 (o más) códigos de área.
Correo electrónico
En términos generales, una dirección de correo electrónico coincide o no. Cuando estábamos realmente desesperados por intentar emparejar, limpiábamos nuestros datos. Esto implicó mirar los dominios y asegurarse de que existan y agregar el dominio de nivel superior si no existieran. Si vimos a joan@aol, era una apuesta segura, se referían a @aol.com. El otro truco que puedes usar para aumentar la coincidencia de correo electrónico es cuando usan + en su dirección. Algunos proveedores, como Google, permiten[correo electrónico protegido]para ser entregado en la dirección base. Considero que es una forma útil de asociar una dirección de correo electrónico a un sitio específico en el que me he registrado. Si el correo basura comienza a llegar a esa cuenta, entonces sé quién es el trasero que puedo masticar. Pero, para fines de coincidencia, es posible que pueda descartar el contenido de + a @
Nombres
"¿Qué hay en un nombre? Lo que llamamos Jones con cualquier otra ortografía podría ser la misma persona"
William Matchspeare
Descubrimos que había dos tipos diferentes de coincidencias que necesitábamos realizar en Nombres. Nombre de la empresa o entidad y nombre de una persona física. Un nombre estadounidense puede tener un prefijo (Sr., Sra., Dr., Fr, Sen, Sgt, etc.), un nombre, segundo nombre o apellido paterno, apellido/apellido materno, generacional (Jr, Sr, IV). ), profesional/honorario/académico (MBA, JD, PhD, esq, etc). ¿No es divertido?
Por lo general, no es tan malo siempre que los datos se hayan capturado en las partes individuales. De lo contrario, puede obtener resultados extraños si asume que puede dividir los espacios en blanco para determinar las partes del nombre, como puede atestiguar mi amigo con el apellido "de los santos".
Nombres de empresas, bueno, normalmente eso es justo lo que te dan. Lo que hay que tener en cuenta es que los DBA hacen negocios. "Soulless megacorporation LLC DBA Happy Cuddly Puppy Preserve" Es posible que tenga que coincidir con "Happy Cuddly Puppy Preserve" y/o "Soulless Megacorporation"
Coincidencia de nombres
Un primer paso en la coincidencia de nombres personales seríasonidoex. Generalmente está disponible en un RDBMS y puede ser aceptable según sus datos de entrada. El problema con Soundex es que sólo sirve para un subconjunto de nombres europeos. Un enfoque fonético más inteligente que utilizamos fue elDoble metáfonoalgoritmo. Esto proporcionó un resultado mucho mejor para la coincidencia de cadenas.
En nuestro ejemplo anterior, una coincidencia exacta de Jones con Joanes fallará, pero una coincidencia fonética debería funcionar. Sin embargo, el problema es que tenemos a Bobbie y a Robert. Ningún esfuerzo de imaginación hará que esos dos suenen iguales, pero los clientes insistieron en que nos faltaban coincidencias, por lo que agregamos otro conjunto de comprobaciones para expandir los apodos a su valor total y luego volvimos a realizar las comparaciones.
En las comparaciones de nombres de empresas, nos pareció útil compilar una lista de "Para las palabras" - información sin sentido que aparece en los nombres pero que debe ignorarse a efectos de coincidencia (a, of, the, LLC, corp, univ, college)
Luego recibimos comentarios de que errores tipográficos, transposiciones u omisiones "simples" de letras daban como resultado entidades no coincidentes. A medida que esta respuesta se alarga, también recibimos comentarios sobre la falla en la coincidencia de nombres de empresas en entidades como "Johns used tire barn" con "Johns mega used tire barn". Terminamos implementando uncomparación de n-gramasy un algoritmo de comparación de tokens para ayudar a abordar esos escenarios. Desde entonces he hablado con otros en la industria y ellos eran partidarios del usodistancia de Levenshteinpara determinar la coincidencia de cadenas.
Relación
Una relación era básicamente algo más que sabíamos que era verdad. Una empresa realizó una promoción en la que los vendedores se pusieron elegantes al pedirle al cliente que llenara tarjetas de respuesta comercial. Teníamos la lista de empleados del "granero de neumáticos usados de John" y necesitábamos correlacionar los datos de nombres incompletos con ese conjunto de referencia. Sólo hablo de aquí para completar. Para su problema, observará las coincidencias de Nombre y MoC.
Hazlo ya
Los detalles de su implementación dependerán de cómo se ven sus datos y de cuánto tiempo y dinero desea invertir en el problema.
Mi enfoque general comenzaría importando ambos conjuntos de datos a su base de datos. Los datos que tienen todos los atributos son su conjunto de referencia. El conjunto de datos más pequeño es su conjunto de candidatos. En la tabla de candidatos, agregue una columna que contenga su identificador de conjunto de referencia. Lo siguiente carece de normalización, pero eso es lo que se pretende
CREATE TABLE
dbo.reference
(
reference_id int identity(1,1) NOT NULL PRIMARY KEY
, name_prfix varchar(50) NULL
, name_first varchar(50) NOT NULL
, name_middle varchar(50) NULL
, name_last varchar(50) NOT NULL
, name_suffix varchar(20) NULL
, company_name varchar(100) NULL
, address_line1 varchar(70) NULL
, address_line2 varchar(50) NULL
, address_city varchar(50) NULL
, address_state varchar(20) NULL
, address_postalcode varchar(10) NULL
, address_zip4 char(4) NULL
, phone_number varchar(10) NULL
)
CREATE TABLE
dbo.candidate
(
candidate_id int identity(1,1) NOT NULL PRIMARY KEY
, name_prfix varchar(50) NULL
, name_first varchar(50) NOT NULL
, name_middle varchar(50) NULL
, name_last varchar(50) NOT NULL
, name_suffix varchar(20) NULL
, company_name varchar(100) NULL
, address_line1 varchar(70) NULL
, address_line2 varchar(50) NULL
, address_city varchar(50) NULL
, address_state varchar(20) NULL
, address_postalcode varchar(10) NULL
, address_zip4 char(4) NULL
, reference_id int
)
TSQL iterativo
Paso 1, coincidencias directas. En cualquier lugar donde exista una coincidencia exacta entre Candidato y Referencia, regístrelo en Candidate.reference_id y ahora quedará excluido del proceso.
Paso 2, coincidencias directas con expansión del apodo y/o reemplazo de palabras vacías
Paso 3, coincidencias de direcciones con coincidencia aproximada de nombres (doble metafono + ngram + distancia mínima de edición)
Paso 4, coincidencias de direcciones con expansión difusa del apodo y/o coincidencia de reemplazo de palabras vacías (doble metáfono + ngram + distancia mínima de edición)
Paso 5: examine el grupo de candidatos restante para realizar una comparación manual
SSIS
La Enterprise Edition de SSIS proporciona capacidades de lógica difusa. Básicamente, hará lo mismo que se enumera en el enfoque TSQL sin la necesidad de que usted cree su propio marco para la coincidencia de nombres y todo eso.
La versión 2012 de SSIS también proporciona servicios de calidad de datos que abordarían la limpieza de sus direcciones y la división de nombres en partes.