Então eu tenho uma tabela com Nome, Endereço e CEP sem dados de registro anexados; e eu tenho uma tabela que tem tudo igual, mas tem mais informações e preciso de uma forma de mesclar as tabelas quando elas não correspondem 100%.
Como faço para combiná-los se eles não são idênticos? Sou novato em SQL, mas sei que eles não combinam na maior parte e não posso ser o único com esse problema. No entanto, o software que fará isso provou ser difícil.
Escrever software para fazer isso seria ainda pior do que ter que fazê-lo em primeiro lugar.
Eu sei que posso fazer isso no Excel; mais ou menos, mas com a quantidade de registros que tenho, está provando ser difícil passar de um milhão.
Responder1
Eu trabalhava em uma empresa de marketing de banco de dados (desculpe por enviar lixo eletrônico para você). Era nosso trabalho descobrir se "Robert Jones 671 Kimbrough SPFD MO 65802" é o mesmo que "Bobbie Joanes 671 Kimbrough St. Sprinfield MO 65809" Se não encontrássemos uma correspondência, corríamos o risco de enviar correspondência duplicada para um cliente em potencial o que faria nossos clientes parecerem idiotas e também desperdiçariam seu dinheiro.
Nossa abordagem foi decompor o problema em domínios menores e aplicar critérios diferentes para responder é AprovavelmenteB. Uma regra de correspondência muito rígida e você não detectará duplicatas. Uma regra de correspondência muito frouxa e você descartará clientes em potencial.
Tínhamos três domínios com os quais uma entidade poderia corresponder: Nome, Método de contato, Relacionamento. Uma correspondência só era permitida se correspondêssemos em dois dos domínios.
Método de contato
Um método de contato foi correio, e-mail ou telefone.
Endereços
O primeiro passo é padronizar um endereço fornecido. O objetivo final é pegar seu endereço de entrada e corrigi-lo para oUSPSpadrão. No exemplo anterior, ambos os endereços provavelmente receberiam correspondência, mas apenas porque o transportador postal entendeu a intenção do remetente. O verdadeiro endereço seria
671 S KIMBROUGH AVE SPRINGFIELD MO 65806-3342
Depois de ter um endereço consistente, a correspondência de endereços será um problema muito mais fácil de resolver. Você ainda precisa se preocupar com endereços que não podem ser corrigidos, bem como com quais são as regras para locais com vários locatários (Suíte 200, Apto B, etc.), mas isso faz parte do ajuste fino que você precisa trabalhar com o proprietário da empresa . Ah, e mesmo que os +4 dígitos sejam úteis para entrega, não deixe que isso influencie sua lógica de correspondência de endereço. É muito mais provável que eles mudem do que o código postal de 5 dígitos.
Outra coisa a ter em mente é que as pessoas se mudam para que você possa obter informações de encaminhamento de endereço (NCOA - Mudança Nacional de Endereço) dos últimos X períodos, se for importante que você tenha dados de endereço atuais. Quando você se muda, a papelada de encaminhamento de endereço só é válida por um determinado período de tempo e qualquer pessoa que enviar uma correspondência para você após essa janela receberá um retorno ao remetente, não neste endereço de devolução. NCOA'ing o e-mail antes de enviá-lo garantiria que você tivesse o endereço atual, mesmo que o encaminhamento tenha expirado.
Nossa abordagem foi criar um hash do endereço padronizado (linha 1 + código postal) e usá-lo como chave de comparação.
Telefone
A única coisa complicada em relação ao telefone era se eles tinham um código de área associado. Nós os armazenamos sem separadores ou formatação e quaisquer extensões foram armazenadas separadamente. Isso se resumia a um número de telefone de 7 ou 10 dígitos. Se tivéssemos um endereço, existe um software que podegeralmentepreencha o código de área. À medida que os códigos de área são divididos, geralmente há um período de carência em que um local pode ser atendido por 2 (ou mais) códigos de área.
De modo geral, um endereço de e-mail corresponde ou não. Quando estávamos realmente desesperados para tentar igualar, limpávamos nossos dados. Isso envolveu examinar os domínios e garantir que eles existissem e adicionar o domínio de nível superior, caso não existissem. Se víssemos joan@aol, era seguro apostar que eles queriam dizer @aol.com. O outro truque que você pode usar para aumentar a correspondência de e-mail é quando eles usam + em seus endereços. Alguns provedores, como o Google, permitem[e-mail protegido]para ser entregue no endereço base. Acho que é uma maneira útil de associar um endereço de e-mail a um site específico no qual me registrei. Se o lixo eletrônico começar a chegar nessa conta, então saberei quem posso mastigar. Mas, para fins de correspondência, você pode descartar o conteúdo de + para @
Nomes
"O que há em um nome? Aquilo que chamamos de Jones por qualquer outra grafia pode ser a mesma pessoa"
William Matchspeare
Descobrimos que havia dois tipos diferentes de correspondência que precisávamos realizar em Nomes. Nome da empresa ou entidade e nome de um indivíduo. Um nome dos EUA pode ter um prefixo (Sr., Sra., Dr, Fr, Sen, Sgt, etc), um nome, nome do meio, um segundo nome do meio ou sobrenome paterno, sobrenome/sobrenome materno, geracional (Jr, Sr, IV ), profissional/honorário/acadêmico (MBA, JD, PhD, esq, etc). Não é divertido?
Geralmente não é tão ruim, desde que os dados tenham sido capturados nas partes individuais. Caso contrário, você poderá obter resultados estranhos se presumir que pode dividir os espaços em branco para determinar as partes do nome, como meu amigo com sobrenome "de los santos" pode atestar.
Nomes de empresas, bem, geralmente é isso que eles fornecem. Coisas que você deve estar ciente são como fazer negócios com DBA. "Megacorporação sem alma LLC DBA Preservação de cachorrinhos fofinhos felizes" Isso pode precisar corresponder a "Preservação de cachorrinhos fofinhos felizes" e/ou "Megacorporação sem alma"
Correspondência de nome
Uma primeira tentativa de correspondência de nomes pessoais seriasomex. Geralmente está disponível em um RDBMS e pode ser aceitável com base nos seus dados de entrada. O problema com o soundex é que ele só serve para um subconjunto de nomes europeus. Uma abordagem fonética mais inteligente e que usamos foi aMetafone Duploalgoritmo. Isso forneceu um resultado muito melhor para correspondência de strings.
Em nosso exemplo acima, uma correspondência exata de Jones para Joanes falhará, mas uma correspondência fonética deverá funcionar. O problema, porém, é que temos Bobbie para Robert. Nenhum esforço de imaginação fará com que os dois pareçam iguais, mas os clientes insistiram que estávamos perdendo correspondências, então adicionamos outro conjunto de verificações para expandir os apelidos de volta ao seu valor total e, em seguida, repetimos as comparações.
Nas comparações de nomes de empresas, descobrimos que era útil compilar uma lista de "parar palavras" - lixo sem sentido que aparece em nomes, mas deve ser ignorado para fins de correspondência (a, of, the, LLC, corp, univ, university)
Estávamos então recebendo feedback de que erros de digitação "simples", transposição ou omissão de letras resultavam em entidades não correspondentes. À medida que essa resposta se alongava, também recebemos feedback sobre a falha na correspondência do nome da empresa em entidades como "Celeiro de pneus usados Johns" para "Celeiro de pneus mega usados Johns". Acabamos implementando umcomparação de n-gramase um algoritmo de comparação de tokens para ajudar a resolver esses cenários. Desde então, conversei com outras pessoas do setor e elas eram proponentes do usoDistância Levenshteinpara determinar a correspondência de strings.
Relação
Um relacionamento era basicamente outra coisa que sabíamos ser verdade. Uma empresa fez uma promoção em que os vendedores se animavam com o fato de os clientes preencherem cartões de resposta comerciais. Tínhamos uma lista de funcionários do "celeiro de pneus usados de John" e precisávamos correlacionar dados de nomes incompletos com esse conjunto de referência. Eu só falo aqui para completar. Para o seu problema, você verá correspondências de Nome e MoC.
Faça isso já
As especificidades da sua implementação dependerão da aparência dos seus dados e de quanto tempo e dinheiro você deseja investir no problema.
Minha abordagem geral começaria importando os dois conjuntos de dados para o seu banco de dados. Os dados que possuem todos os atributos são o seu conjunto de referência. O conjunto menor de dados é o seu conjunto de candidatos. Na tabela candidata, adicione uma coluna que contenha o identificador do conjunto de referências. O seguinte carece de normalização, mas isso é pretendido
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
Etapa 1, correspondências diretas. Em qualquer lugar onde exista uma correspondência exata entre Candidato e Referência, registre isso em Candidate.reference_id e agora será excluído do processo.
Etapa 2, correspondências diretas com expansão de apelido e/ou substituição de palavras de parada
Etapa 3, correspondências de endereço com correspondência de nome difusa (metafone duplo + ngram + distância mínima de edição)
Etapa 4, correspondências de endereço com expansão difusa de apelidos e/ou correspondência de substituição de palavras de parada (metafone duplo + ngram + distância mínima de edição)
Etapa 5: examinar o conjunto de candidatos restantes para correspondência manual
SSIS
A Enterprise Edition do SSIS fornece recursos de lógica difusa. Basicamente, ele fará praticamente o mesmo listado na abordagem TSQL, sem a necessidade de você montar sua própria estrutura para correspondência de nomes e tudo mais.
A versão 2012 do SSIS também fornece serviços de qualidade de dados que abordariam a limpeza de seus endereços, bem como a divisão de nomes em partes.