Hoje aprenderemos como ELIMINAR DADOS DUPLICADOS no SQL Server, assunto que recentemente enfrentei na vida prática (mais uma vez) e que também é cobrado nas provas de certificação da Microsoft.

Dados duplicados no SQL Server

Claro que se o banco fosse bem definido em termos de chaves primárias, estrangeiras, únicas, etc… você não teria esse problema, mas a vida é bandida certas horas, e quando lidamos com certos bancos de dados…

 

Um dia você terá de enfrentar um problema de dados duplicados em um banco de dados, seja SQL ou outro. Talvez ainda não tenha ocorrido com você, mas fique tranquilo, vai acontecer! Kkkkk

 

Essa situação é tão comum que cai até nas provas de certificação, e eu mesmo me deparei com ela a menos de 1 mês atrás.

 

Eliminando dados duplicados no SQL

Eliminar dados duplicados pode ser bem complexo, eu mesmo já tive situações bastante dramáticas, então hoje venho compartilhar com você algumas querys que me ajudaram tremendamente no troubleshooting e resolução desse problema.

 

A query mais básica, é claro, não poderia ser outra se não o select *.

 

Quando são poucos dados, em geral ele (o select *) resolve a parada, mas quase sempre não é assim. Na ocasião que mencionei a você (problema que enfrentei a menos de 1 mês), os dados duplicados eram em uma tabela com mais de 10 mil registros.

 

Nesses casos, o que temos de fazer é agrupar os problemas, e para isso eu uso as querys que apresentei na aula de hoje:

 

[sociallocker id=”5114″]

--------------------------------------------------------
-- Trabalhando com dados duplicados
--------------------------------------------------------
-----------------------------------------
-- Dados de teste
-----------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null drop database curso
go
create database curso
go
use curso
go
-- Cadastro de amigos em tabela com PK
if object_id('amigos1') is not null drop table amigos1
create table amigos1 (id int identity(1,1) primary key, nome varchar(100), email varchar(100))
insert into amigos1 (nome, email) values 
	-- e-mail duplicado
	('João', '[email protected]'),
	('João DBA', '[email protected]'),
	-- e-mail e nome triplicados
	('Maria', '[email protected]'),
	('Maria', '[email protected]'),
	('Maria', '[email protected]'),
	-- dado não duplicado misturado com dados duplicados
	('Josué', '[email protected]')

-- Cadastro de amigos em tabela sem PK (copia de amigos 1
if object_id('amigos2') is not null drop table amigos2
select nome, email into amigos2 from amigos1

-- Cadastro de amigos com volume de dados
if object_id('amigos3') is not null drop table amigos3
create table amigos3 (id int not null identity(1, 1) primary key, nome varchar(100), email varchar(100))
;with cte_seq as (select 1 as nr_cont union all select nr_cont + 1 from cte_seq where nr_cont < 100) insert into amigos3 (nome, email) select 'amigo' + convert(varchar, nr_cont), 'amigo' + convert(varchar, nr_cont) + '@email.com' from cte_seq go -- Duplicar dados de amigos3 insert into amigos3 (nome, email) select nome, email from amigos3 insert into amigos3 (nome, email) select nome, email from amigos2 ----------------------------------------- -- Levantamento & Analise ----------------------------------------- -- Levantamento visual (fossem milhares seria pior...) select * from amigos1 -- delete from amigos1 where id in (2, 3, 4) select * from amigos2 select * from amigos3 -- Levantamento com group by select email, count(1) ocorrencias from amigos1 group by email order by 2 desc select email, count(1) ocorrencias from amigos3 group by email order by 2 desc select * from amigos3 -- Levantamento com group by + having select email, count(1) ocorrencias from amigos1 group by email having count(1) > 1
order by 2 desc


-- Levantamento do primeiro e último ID cadastrado para cada email:
select
	email,
	min(id) id_primeiro_cadastro,
	max(id) id_ultimo_cadastro
from amigos1
group by email
having count(1) > 1
select * from amigos1


-- Levantamento com distinct (quantidade de "nomes diferentes" associados a um "e-mail duplicado")
select 
	email,
	count(1) qtd_nomes_associados
from (select distinct nome, email from amigos1) cadastro
group by email
select * from amigos1


-- Levantamento de nomes diferentes vs. e-mails duplicados (Lista de nomes)
select 
	email,
	count(1) qtd_nomes_associados,
	stuff((
		select ' | ' + nome 
		from amigos2 a
		where a.email = cadastro.email
		for xml path('')
	), 1, 3, '') nomes_associados
from (select distinct nome, email from amigos1) cadastro
group by email
--having count(1) > 1 -- somente emails com mais de um nome associado


-- Levantamento de nomes diferentes vs. e-mails duplicados (XML completo)
select 
	email,
	count(1) qtd_nomes_associados,
	(
		select id, nome 
		from amigos1 a
		where a.email = cadastro.email
		for xml auto, root('cadastros')
	) cadastros_associados
from (select distinct nome, email from amigos1) cadastro
group by email
--having count(1) > 1


-- Levantamento (parecer geral)
select 
	cadastro.email,
	contagem_eml_dup.ocorrencias qtd_ocorrencias_na_tabela,
	count(1) qtd_nomes_diferentes_associados,
	(
		select id, nome 
		from amigos1 a
		where a.email = cadastro.email
		for xml auto, root('cadastros')
	) cadastros_associados
from (select distinct nome, email from amigos1) cadastro
join (select email, count(1) ocorrencias from amigos1 group by email) contagem_eml_dup on cadastro.email = contagem_eml_dup.email
group by cadastro.email, contagem_eml_dup.ocorrencias




-----------------------------------------
-- Resolvendo dados duplicados (amigos1)
-----------------------------------------
-- Ajustes manuais: Ajustando emails duplicados com nomes diferentes
update amigos1 set nome = 'João DBA' where email = '[email protected]'
select * from amigos1

-- Ajustes "automatizados": Excluindo a segunda ocorrência de registros duplicados
;with cte_duplicados as (
	select
		max(id) id_max,
		min(id) id_min,
		email
	from amigos1
	group by email
	having count(1) > 1
)
--delete cadastro
select * 
from amigos1 cadastro
join cte_duplicados cte on cadastro.id = cte.id_max


select * from amigos1


-- Ajustes "automatizados": Registros triplicados e afins...
;with 
cte_manter as (
	-- menor ID dos registros duplicados (ex: primeira ocorrência do João e da Maria)
	select min(id) manter_id, email manter_email from amigos1 group by email having count(1) > 1
	union all
	-- registros únicos da tabela (ex: Josué)
	select min(id), email from amigos1 group by email having count(1) = 1
), 
cte_excluir as (
	select email excluir_email from amigos1 group by email having count(1) > 1
)
--delete T
select * 
from amigos1 T
left join cte_manter M on T.id = M.manter_id
left join cte_excluir E on T.email = E.excluir_email
where M.manter_id is null -- se o email não existe na CTE Manter, exclui


-- Ajustes "automatizados": Alternativa a registros triplicados com Ranking (CTE cai na prova)
-- Levantamento
select 
	*
from (
	select 
		id, 
		nome, 
		email, 
		row_number() over (partition by email order by nome) as ranking 
	from amigos1) emails 
where ranking > 1 -- rankins maiores que 1 são duplicados

-- Exclusão (com join ao invés de CTE):
--delete cadastro
select * 
from amigos1 cadastro
join (
	select * from (select id, nome, email, row_number() over (partition by email order by nome) as ranking from amigos1) cadastro where ranking > 1
) duplicados on duplicados.id = cadastro.id


-----------------------------------------
-- Resolvendo dados duplicados (amigos2)
-----------------------------------------
-- Alternativa 1: Incluir coluna "ID" (chave primária) 
-- OBS: não é recomendado usar uniqueid como chave primária por motivos de performance
-- newid (SQL 2012-)
alter table amigos2 add id_newid uniqueidentifier default(newid()) with values
select * from amigos2

-- Sequence (SQL 2012+)
create sequence sAmigos2 as int start with 1 increment by 1
alter table amigos2 add id_seq int default(next value for sAmigos2) with values
select * from amigos2


-- Alternativa 2: Migrar dados com distinct
if object_id('amigos2migracao') is not null drop table amigos2migracao
select distinct nome, email into amigos2migracao from amigos2
select * from amigos2migracao
select * from amigos2

-----------------------------------------
-- Resolvendo dados duplicados (amigos3)
-----------------------------------------
-- Mesma abordagem de amigos1...



-----------------------------------------
-- Excluindo dados do teste
-----------------------------------------
/*
use master
if db_id('curso') is not null drop database curso
go
*/

[/sociallocker]

CONCLUSÃO

Eliminar dados duplicados no SQL Server pode ser uma grande dor de cabeça, mas usando os scripts certos o seu trabalho com certeza será reduzido dramaticamente!

 

Espero que também tenha gostado desse conteúdo.

 

Abraço do seu amigo Josué 🙂