Menu de navegação

[airoospParticipante]

Boa tarde,

Tem uma tabela com 179 mil registros e preciso descobrir quais registros o conteúdo do campo nome é o mesmo.

Pesquisando na internet encontrei alguns exemplos (com rowid, ROW_NUMBER() OVER (PARTITION BY ORDER BY ROWID) mas não entendi direito como funcionam.

Criei uma consulta com base no que encontrei, mas retornou alguns registros que realmente estão repetidos, mostrando mais de 1 linha, e para outros registros mostra apenas 1 linha, sendo que estes também estão repetidos.

Esta é a consulta que foi criada:

select empresa, id_funcionario, nome, cargo, rg, cracha
FROM funcionarios
WHERE ROWID IN (
SELECT rid
FROM (
SELECT ROWID rid,
ROW_NUMBER() OVER (PARTITION BY nome ORDER BY ROWID) rn
FROM funcionarios
)
WHERE rn <> 1
)

order by nome

Outra coisa, é possível fazer uma consulta que retorne os nomes que são parecidos, por exemplo:

Nome RG
Jaqueline Cardoso de Oliveira Nunes 56289713
Jaqueline Cardoso de O Nunes 56289713
Jaqueline C de O Nunes 56289713

No caso acima, é a mesma pessoa mas foi cadastrada de forma diferente.

Se alguém tiver alguma dica, agradeço.

Obrigado.

Airton

[José Laurindo ChiappaModerador]

ENtão : o primeiro conceito aqui é que, já que vc tem uma coluna “chave”, uma coluna que vc quer consultar se há duplicidade, o procedimento para isso na linguagem SQL (nem falo de Oracle aqui, é conceito ** geral ** de banco de dados relacional) é vc AGRUPAR por essa coluna, e o comando para isso é GROUP BY – veja o exemplo com a tabela demo EMPLOYEES no schema HR do RDBMS Oracle :

SQL> select first_name, count() from employees group by first_name having count() > 1 order by 1;

FIRST_NAME COUNT(*)

---

Alexander 2
David 3
James 2
Jennifer 2
John 3
Julia 2
Karen 2
Kevin 2
Michael 2
Peter 3
Randall 2
Steven 2
William 2

13 linhas selecionadas.

SQL>

Blz ?? Isso te diz quais “chaves” contém valores duplicados….. Fosse isso apenas, taria pronto mas ao que entendi vc precisa também obter informações de FORA do grupo, vc quer o resto do registro aonde a “chave” está duplicada, e isso o GROUP BY não traz….
Pra vc obter o resto, entre muitas outras Possibilidades (vc pode agrupar via Analytics, por exemplo) existe a possibilidade de sub-query, ie, simplesmente pegar o resultset acima e fazer uma nova leitura na tabela trazendo os registros correspondentes à cada chave, tipo :

SQL> select first_name, last_name, employee_id, salary, email from EMPLOYEES
2 where first_name in (select first_name from employees group by first_name having count() > 1)
3 order by 1;

FIRST_NAME LAST_NAME EMPLOYEE_ID SALARY EMAIL

---

Alexander Khoo 115 3100 AKHOO
Alexander Hunold 103 9000 AHUNOLD
David Bernstein 151 9500 DBERNSTE
David Lee 165 6800 DLEE
David Austin 105 4800 DAUSTIN
James Marlow 131 2500 JAMRLOW
James Landry 127 2400 JLANDRY
Jennifer Whalen 200 4400 JWHALEN
Jennifer Dilly 189 3600 JDILLY
John Seo 139 2700 JSEO
John Russell 145 14000 JRUSSEL
John Chen 110 8200 JCHEN
Julia Nayer 125 3200 JNAYER
Julia Dellinger 186 3400 JDELLING
Karen Partners 146 13500 KPARTNER
Karen Colmenares 119 2500 KCOLMENA
Kevin Feeney 197 3000 KFEENEY
Kevin Mourgos 124 5800 KMOURGOS
Michael Hartstein 201 13000 MHARTSTE
Michael Rogers 134 2900 MROGERS
Peter Tucker 150 10000 PTUCKER
Peter Hall 152 9000 PHALL
Peter Vargas 144 2500 PVARGAS
Randall Perkins 191 2500 RPERKINS
Randall Matos 143 2600 RMATOS
Steven Markle 128 2200 SMARKLE
Steven King 100 24000 SKING
William Smith 171 7400 WSMITH
William Gietz 206 8300 WGIETZ

29 linhas selecionadas.

SQL>

Okdoc ? Eu escrevi em SQL ** exatamente ** o que escrevi em Português, ie, me traga todos os registros onde o FIRST_NAME (minha coluna “chave”) está contido no resultset que me traz os FIRST_NAMEs duplicados….. Mais fácil que isso *** ABSOLUTAMENTE NÃO FICA ***…

==> Porém, como QUASE sempre em TI, o fácil Não É performático : o “problema” é que estou fazendo DOIS acessos na tabela, se ela for grande a performance cai…. Com centos e poucos mil registros eu ** ACHO ** que isso não vai ser problema pra vc (esse volume é café pequeno prim servidor de Produção) mas para registro, a técnica para que vc agrupe uma tabela ao mesmo tempo em que vai lendo os dados (um table scan só, portanto) é Analytics….
Aplicando a sintaxe de função Analítica o SQL ia ficar tipo :

SQL> select first_name, last_name, employee_id, salary, email,
2* count(*) over (partition by first_name) QTD from EMPLOYEES;

FIRST_NAME LAST_NAME EMPLOYEE_ID SALARY EMAIL QTD

---

Adam Fripp 121 8200 AFRIPP 1
Alana Walsh 196 3100 AWALSH 1
Alberto Errazuriz 147 12000 AERRAZUR 1
Alexander Hunold 103 9000 AHUNOLD 2
Alexander Khoo 115 3100 AKHOO 2
Danielle Greene 163 9500 DGREENE 1
David Austin 105 4800 DAUSTIN 3
David Bernstein 151 9500 DBERNSTE 3
…..

==> OU SEJA, o RDBMS em memória montou um Grupo para cada FIRST_NAME cfrme foi lendo cada registro…. Para eu poder especificar essa coluna QTD numa cláusula WHERE só preciso colocar numa sub-query :

SQL> SELECT * FROM (select first_name, last_name, employee_id, salary, email,count(*) over (partition by first_name) QTD from EMPLOYEES)
2 WHERE QTD > 1;

FIRST_NAME LAST_NAME EMPLOYEE_ID SALARY EMAIL QTD

---

Alexander Hunold 103 9000 AHUNOLD 2
Alexander Khoo 115 3100 AKHOO 2
David Austin 105 4800 DAUSTIN 3
David Bernstein 151 9500 DBERNSTE 3
David Lee 165 6800 DLEE 3
James Marlow 131 2500 JAMRLOW 2
James Landry 127 2400 JLANDRY 2
Jennifer Whalen 200 4400 JWHALEN 2
Jennifer Dilly 189 3600 JDILLY 2
John Seo 139 2700 JSEO 3
John Russell 145 14000 JRUSSEL 3
John Chen 110 8200 JCHEN 3
Julia Dellinger 186 3400 JDELLING 2
Julia Nayer 125 3200 JNAYER 2
Karen Colmenares 119 2500 KCOLMENA 2
Karen Partners 146 13500 KPARTNER 2
Kevin Feeney 197 3000 KFEENEY 2
Kevin Mourgos 124 5800 KMOURGOS 2
Michael Hartstein 201 13000 MHARTSTE 2
Michael Rogers 134 2900 MROGERS 2
Peter Hall 152 9000 PHALL 3
Peter Vargas 144 2500 PVARGAS 3
Peter Tucker 150 10000 PTUCKER 3
Randall Matos 143 2600 RMATOS 2
Randall Perkins 191 2500 RPERKINS 2
Steven King 100 24000 SKING 2
Steven Markle 128 2200 SMARKLE 2
William Gietz 206 8300 WGIETZ 2
William Smith 171 7400 WSMITH 2

29 linhas selecionadas.

SQL>

Legal ??? Taí a primeira resposta (ie, detectar duplicidade em cima de um valor-chave), agora vamos para a segunda…. Na sua segunda pergunta, vc quer fazer pesquisa por similaridade ao que entendo (ie, encontrar strings com ** algumas posições ** diferentes entre si) : em sendo banco Oracle 11g em versão relativamente recente vc pode usar para isso a built-in UTL_MATCH, veja alguns exemplo em http://dbaora.com/utl_match-string-similaritymatching-11g/, http://stackoverflow.com/questions/29935017/how-to-get-almost-matching-string-from-oracle-table e https://oracle-base.com/articles/11g/utl_match-string-matching-in-oracle ….
Fique ** ciente **, porém, que :

a) a função *** não faz *** matching por som, ie, coisas como LUIS e LUIZ ou JAQUELIN, JACQUELINE e JAQUELINE(letras diferentes com mesmo som e/ou letras mudas) Certamente vão dar uma “distância”, um “peso de match” razoavelmente diferente

b) o algoritmo é (na forma básica) começar pela primeira posição da string A e ver se a primeira posição em B é igual, se não for ele adiciona 1 na contagem de diferenças e vai pra próxima posição, assim vai…. PORTANTO, sub-strings iguais mas em posições diferentes (como a string CARDOSO em “Jaqueline Pereira Cardoso de Oliveira” e Jaqueline P. Cardoso) vão com certeza trazer pesos bem diferentes…..

Faça seus testes e veja se essa built-in te atende….

[]s

Chiappa

[José Laurindo ChiappaModerador]

Só por completude, vou colocar um Exemplo possível de utilização do UTL_MATCH em cima do que vc propôs : EVIDENTEMENTE, este case de tabela de Pessoas ** absolutamente ** não é apropriado (pois NECESSARIAMENTE uma Pessoa tem um documento único, no cenário proposto bastaria se ter uma Chave Única adicional em RG para evitar a mesma pessoa ser cadastrada com o mesmo RG com nomes em grafias diferentes) mas ignore-se isso em prol de se ter uma massa de exemplo, fictícia…

Observo ** apenas ** que, como eu Disse Antes, iirc a UTL_MATCH foi introduzida no 11g (e usei 11gR2 XE no meu exemplo) – não garanto que vc vai conseguir usar ela se seu banco é mais antigo que isso, aí vc vai ter que fazer a lógica de comparação MANUALMENTE, creio eu…

O exemplo :

==> primeiro crio a tabela e a populo com dados :

SQL> create table FUNCIONARIOS (empresa NUMBER(1), id_funcionario number, nome varchar2(40), cargo varchar2(25), rg varchar2(15), cracha number);

Tabela criada.

SQL> insert into FUNCIONARIOS values (1, 100, ‘ALEXANDRE MOURA’, ‘FAZNADA’, ‘111234560’, 1000);

1 linha criada.

SQL> insert into FUNCIONARIOS values (1, 200, ‘Jaqueline Cardoso de Oliveira Nunes’, ‘FAZNADA’, ‘101234560’, 2000);

1 linha criada.

SQL> insert into FUNCIONARIOS values (1, 300, ‘Jaqueline C. de Oliveira Nunes’, ‘FAZNADA’, ‘101234560’, 2000);

1 linha criada.

SQL> insert into FUNCIONARIOS values (1, 400, ‘Jaqueline C de O. Nunes’, ‘FAZNADA’, ‘101234560’, 2000);

1 linha criada.

SQL> insert into FUNCIONARIOS values (1, 500, ‘José Laurindo’, ‘DBA’, ‘1801234560’, 3000);

1 linha criada.

SQL> select * from FUNCIONARIOS;

EMPRESA ID_FUNCIONARIO NOME CARGO RG CRACHA

---

  1            100 ALEXANDRE MOURA                          FAZNADA   111234560       1000
  1            200 Jaqueline Cardoso de Oliveira Nunes      FAZNADA   101234560       2000
  1            300 Jaqueline C. de Oliveira Nunes           FAZNADA   101234560       2000
  1            400 Jaqueline C de O. Nunes                  FAZNADA   101234560       2000
  1            500 José Laurindo                            DBA       1801234560      3000

SQL> commit;

Commit concluído.

=> OK, a função Exige que vc forneça duas strings a comparar, então a lógica é pegar o valor da coluna a comparar em cada registro da tabela e comparar com o valor presente em TODOS os outros registros presentes exceto o registro original , para obter o grau de similaridade da coluna string em cada registro com todos os outros….
Para que vc leia a mesma tabela duas vezes (uma vez estamos percorrendo a tabela para pegar o valor da coluna em cada registro, e esse valor vai ser comparado com o resultado de uma Outra leitura) vc simplesmente coloca a mesma tabela duas vezes no FROM, assim :

SQL> SELECT a.NOME search,
2 b.nome compare,
3 utl_match.jaro_winkler_similarity( a.NOME, b.NOME ) similarity
4 FROM FUNCIONARIOS a,
5 FUNCIONARIOS b
6 WHERE a.rowid != b.rowid
7* ORDER BY utl_match.jaro_winkler_similarity( a.NOME, b.NOME ) desc;

SEARCH COMPARE SIMILARITY

---

Jaqueline C. de Oliveira Nunes Jaqueline C de O. Nunes 90
Jaqueline Cardoso de Oliveira Nunes Jaqueline C. de Oliveira Nunes 90
Jaqueline C. de Oliveira Nunes Jaqueline Cardoso de Oliveira Nunes 90
Jaqueline C de O. Nunes Jaqueline C. de Oliveira Nunes 90
Jaqueline C de O. Nunes Jaqueline Cardoso de Oliveira Nunes 87
Jaqueline Cardoso de Oliveira Nunes Jaqueline C de O. Nunes 87
Jaqueline Cardoso de Oliveira Nunes José Laurindo 60
José Laurindo Jaqueline Cardoso de Oliveira Nunes 60
José Laurindo Jaqueline C de O. Nunes 54
Jaqueline C de O. Nunes José Laurindo 54
José Laurindo Jaqueline C. de Oliveira Nunes 52
Jaqueline C. de Oliveira Nunes José Laurindo 52
Jaqueline C de O. Nunes ALEXANDRE MOURA 40
ALEXANDRE MOURA Jaqueline C de O. Nunes 40
ALEXANDRE MOURA Jaqueline C. de Oliveira Nunes 40
Jaqueline C. de Oliveira Nunes ALEXANDRE MOURA 40
Jaqueline Cardoso de Oliveira Nunes ALEXANDRE MOURA 39
ALEXANDRE MOURA Jaqueline Cardoso de Oliveira Nunes 39
ALEXANDRE MOURA José Laurindo 25
José Laurindo ALEXANDRE MOURA 25

20 linhas selecionadas.

Taí o que queríamos, cada string comparada com as outras Todas da tabela exceto ela mesma… Com esse resultado vc vê que algumas strings (como a “José Laurindo” e a a “ALEXANDRE MOURA”) não são nem de longe parecidas com as outras da tabela, então retornaram um fator de similaridade lá em baixo…
Para filtrarmos essa query trazendo apenas aqueles que são efetivamente parecidos entre si, novamente usamos a técnica de sub-query :

SQL> select * from (
2 SELECT a.NOME search,
3 b.nome compare,
4 utl_match.jaro_winkler_similarity( a.NOME, b.NOME ) similarity
5 FROM FUNCIONARIOS a,
6 FUNCIONARIOS b
7 WHERE a.rowid != b.rowid
8 ORDER BY utl_match.jaro_winkler_similarity( a.NOME, b.NOME ) desc
9 )
10 where similarity > 80;

SEARCH COMPARE SIMILARITY

---

Jaqueline Cardoso de Oliveira Nunes Jaqueline C. de Oliveira Nunes 90
Jaqueline C. de Oliveira Nunes Jaqueline Cardoso de Oliveira Nunes 90
Jaqueline C. de Oliveira Nunes Jaqueline C de O. Nunes 90
Jaqueline C de O. Nunes Jaqueline C. de Oliveira Nunes 90
Jaqueline C de O. Nunes Jaqueline Cardoso de Oliveira Nunes 87
Jaqueline Cardoso de Oliveira Nunes Jaqueline C de O. Nunes 87

6 linhas selecionadas.

SQL>

Blz ??? Dá uma boa estudada nas refs que te indiquei que Certamente vc deverá ser capaz de adaptar este meu teste pras suas consições reais aí…

[]s

Chiappa

[Jefferson de Almeida CostaParticipante]

AiroSP,

Você também tem a função Soundex que pode lhe ajudar na busca de registros próximos.

Complementando o que o jlchiappa, segue um pequeno exemplo que lhe dará as informações mais próximas:

SELECT * FROM TABELA
WHERE SOUNDEX(CAMPO) = SOUNDEX(‘EXPRESSAO’)
ORDER BY utl_match.EDIT_DISTANCE(CAMPO,’EXPRESSAO’)

Abraço

[Renato José Correia LimaParticipante]

Pelo que eu vi o campo RG acaba sendo a chave da sua tabela, eu utilizaria ele como o identificador, conforme o select abaixo:

select a.*
from funcionarios a,
(select RG, count(nome) total
from funcionarios
group by RG
having count(nome) > 1 ) b
where a.RG = b.RG

Att.

[José Laurindo ChiappaModerador]

Yep, se ele tem na tabela alguma coluna que não admite duplicação na digitação (por exemplo RG, além de não ter como vc abreviar RG ao contrário de nomes AInda por cima RG tem dígitos de verificação para evitar erros de datilografia/digitação) SEM DÚVIDA ele deveria a usar…
Inclusive, se a base dele tiversse RG como chave Única, ele já EVITARIA a situação que pelo que entendi ele quer detectar, ie, fichas de funcionários duplicadas, onde duas pessoas diferentes digitaram o RG do funcionário corretamente, mas uma cadastrou a pessoa como “Jaqueline Cardoso de Oliveira Nunes” e a outra cadastrou como “Jaqueline Cardoso de O Nunes”, ambas como o correto RG 56289713 ….. Óbvio, se RG fosse uma Chave Única, isso seria IMPOSSÍVEl de acontecer…
LOGICAMENTE, para que isso funcione, ele NÃO PODERIA ter uma coluna só RG, como (ERRADAMENTE!!!) ele tem hoje, mas sim tem que ter uma Combinação de colunas com UF_EMISSAO (o estado brasileiro que emitiu), ORGAO_EMISSOR, NUM_PRINCIPAL_RG e DIGITOS_VERIF , pois cada estado do Brasil tem sua numeração de RG, cada um tem suas regras de validação (por exemplo, alguns permitem caracteres nos dígitos de verificação, outros não)… Por isso que por princípio a MELHOR chave única para identificar pessoas é o CPF, que é único no país todo, mas nem todo mundo tem CPF….

[]s

Chiappa

[Autor]

Posts