SQL Remove Duplicates: Métodos abrangentes e práticas recomendadas

Registros duplicados são um problema comum que pode comprometer a integridade dos dados e o desempenho do banco de dados. A remoção dessas duplicatas é essencial para manter a precisão dos dados, otimizar o armazenamento e melhorar o desempenho das consultas. Neste artigo, exploraremos várias técnicas para remover linhas duplicadas no SQL, adaptadas a vários casos de uso e sistemas de gerenciamento de banco de dados.

Para começar, recomendo que você faça os cursos Introduction to SQL e Learn SQL da DataCamp para aprender os conhecimentos básicos de extração e análise de dados usando SQL. Além disso, acho que o SQL Basics Cheat Sheet, que você pode baixar, é uma referência útil porque contém todas as funções SQL mais comuns.

Entendendo as linhas duplicadas no SQL

As linhas duplicadas no SQL referem-se a registros em uma tabela que contêm valores idênticos em todas as colunas ou em colunas selecionadas. As causas comuns de linhas duplicadas no SQL incluem o seguinte:

• Chaves primárias ausentes: Quando as tabelas não têm uma chave primária definida ou uma restrição exclusiva, não há mecanismo para impedir a inserção de dados duplicados. Isso pode ocorrer quando uma tabela não está normalizada e/ou há problemas de dependência transitiva.
• Problemas de integração de dados: Ao mesclar conjuntos de dados de fontes diferentes, junções inadequadas ou inconsistências nos formatos de dados podem introduzir duplicatas acidentalmente.
• Erros de entrada manual de dados: Erro humano, como inserir o mesmo registro várias vezes, é outra causa comum de linhas duplicadas.

No restante do artigo, veremos como remover duplicatas no SQL e dividiremos o artigo em dois blocos. Na primeira seção, abordaremos como remover duplicatas nos dados que você está recuperando para um relatório ou painel; na segunda seção, veremos como remover duplicatas no banco de dados.

Métodos para remover duplicatas nos dados que você recupera

Existem diferentes métodos para remover duplicatas durante a recuperação de registros no SQL. Cada método depende do DBMS, como SQL Server, MySQL e PostgreSQL. Nesta seção, examinaremos os métodos de remoção de duplicatas e destacaremos qualquer consideração especial para cada banco de dados. Lembre-se de que esses métodos filtram os dados e retornam registros exclusivos e não modificam a tabela subjacente.

Usando a palavra-chave DISTINCT

A palavra-chave DISTINCT é usada em uma instrução SELECT para recuperar linhas exclusivas. A sintaxe da palavra-chave DISTINCT para remover duplicatas é semelhante para os bancos de dados MySQL, PostgreSQL e SQL Server. A consulta abaixo recuperará nomes exclusivos de clientes da tabela customers.

SELECT DISTINCT Name 
FROM customers;Tecnologia

Usando GROUP BY com funções de agregação

A cláusula GROUP BY, combinada com outras funções de agregação como MAX(), MIN() ou COUNT(), pode ajudar a remover registros duplicados das tabelas. A cláusula GROUP BY ajuda a selecionar registros específicos a serem mantidos e a excluir outras duplicatas.

Suponha que você queira excluir registros de clientes duplicados, mas manter o que tem o ID mais alto. Você usará a cláusula GROUP BY com a função MAX(), conforme mostrado abaixo.

-- Delete duplicate rows from the 'customers' table (aliased as c1)
DELETE c1
FROM customers c1
-- Find the maximum ID for each unique Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Match rows based on 'Name' and keep the row with the maximum ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;Tecnologia

O MySQL e o SQL Server suportam a sintaxe acima de GROUP BY com funções agregadas e a cláusula JOIN. 

Uso de ROW_NUMBER() com expressões de tabela comuns (CTE)

Com a função ROW_NUMBER() combinada com uma expressão de tabela comum (CTE), você pode filtrar as duplicatas com base em seus critérios. A função ROW_NUMBER, quando usada com as cláusulas PARTITION BY e ORDER BY, atribui um número sequencial exclusivo a cada linha. Esse método permite filtrar as linhas que não atendem aos critérios exigidos.

A consulta a seguir identifica as duplicatas e remove todas, exceto a primeira ocorrência.

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Select only the unique records where RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;Tecnologia

Esse método funciona bem com as versões modernas do SQL Server, MySQL e PostgreSQL. É útil para conjuntos de dados maiores ou condições mais complexas, pois permite que você especifique exatamente qual duplicata deve ser mantida.

Remoção de duplicatas usando o self-JOIN

Uma autojunção permite que você compare uma tabela com ela mesma, o que a torna útil para identificar e remover linhas duplicadas, comparando registros com base em critérios específicos. O exemplo a seguir usa o self-join para excluir a linha com o ID mais alto, mantendo apenas a primeira ocorrência de cada nome.

-- Delete duplicate rows using self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;Tecnologia

O método acima funciona nos principais bancos de dados, incluindo SQL Server, MySQL e PostgreSQL. Confira nosso curso de SQL intermediário para saber mais sobre o uso de funções agregadas e junções para filtrar dados.

Métodos para remover duplicatas no banco de dados

Embora seja possível remover registros duplicados usando consultas, você também pode excluí-los permanentemente do banco de dados. Essa abordagem é importante para manter a qualidade dos dados. Os métodos a seguir são usados para remover duplicatas do banco de dados.

Usando ROW_NUMBER() e DELETE

A função ROW_NUMBER() atribui um número sequencial às linhas em uma partição definida. Quando usado com a instrução DELETE, ele ajuda a identificar duplicatas classificando as linhas com base em colunas específicas e removendo registros indesejados. Esse método se aplica às versões modernas do MySQL (a partir da versão 8.0), PostgreSQL e SQL Server.

Suponha que você queira remover registros duplicados de clientes com base na coluna Name, mantendo apenas a primeira ocorrência (menor ID):

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Delete rows from the 'customers' table where the row number is greater than 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);Tecnologia

Usando DELETE com subconsulta

Às vezes, uma simples operação DELETE usando uma subconsulta pode remover duplicatas do banco de dados. Esse método é adequado para versões mais antigas do MySQL ou do PostgreSQL, nas quais o site ROW_NUMBER() pode não estar disponível.

A consulta abaixo exclui as linhas da tabela customers em que ID não é o mínimo para cada Name, mantendo apenas a linha com o menor ID para cada Name exclusivo.

-- Delete rows from the 'customers' table
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery to find the minimum ID for each unique Name
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);Tecnologia

Usando GROUP BY com a cláusula HAVING

Quando você precisa verificar se há valores duplicados em colunas específicas, a cláusula GROUP BY combinada com a cláusula HAVING pode ser usada para identificar duplicatas. Esse método permite que você exclua linhas específicas com base nos critérios fornecidos. Esse método é compatível com o SQL Server, o MySQL e o PostgreSQL.

A consulta a seguir exclui linhas da tabela customers em que o ID pertence a um grupo de duplicatas.

-- Delete rows from the 'customers' table where there are duplicates
DELETE FROM customers
WHERE ID IN (
    -- Subquery to find IDs of duplicate rows
    SELECT ID
    FROM customers
    GROUP BY ID
    HAVING COUNT(*) > 1
);Tecnologia

Uso de tabelas temporárias para processamento em lote

As tabelas temporárias são eficientes para processamento em lote e remoção de duplicatas em grandes conjuntos de dados. Esse método é útil quando consultas únicas podem causar problemas de desempenho. A consulta a seguir cria uma tabela temporária para armazenar o mínimo de ID para cada customer_name e excluir linhas da tabela customers onde o ID não está na tabela temp_customers.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(customer_id) AS ID, customer_name
FROM customers
GROUP BY customer_name;
DELETE FROM customers
WHERE customer_id NOT IN (SELECT ID FROM temp_customers);Tecnologia

A sintaxe acima usando CREATE TEMPORARY TABLE é compatível apenas com os bancos de dados MySQL e PostgreSQL. 

Remover duplicatas no SQL Server

O SQL Server oferece diferentes métodos para remover registros duplicados do banco de dados. Esses métodos incluem o uso de DISTINCT com INTO, ROW_NUMBER() e tabelas temporárias.

Usando DISTINCT com INTO

Você pode usar a palavra-chave DISTINCT em uma instrução SELECT para criar uma nova tabela com registros exclusivos. Você pode eliminar a tabela antiga depois de verificar se a nova tabela tem os registros especificados. O exemplo a seguir cria a tabela unique_customers com registros exclusivos da tabela customers.

-- Select distinct rows from 'customers' and create a new table 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Drop the original 'customers' table to remove it from the database
DROP TABLE customers;
-- Rename the 'unique_customers' table to 'customers' to replace the original table
EXEC sp_rename 'unique_customers', 'customers';Tecnologia

Usando ROW_NUMBER()

Você também pode usar a função ROW_NUMBER() para remover registros duplicados do SQL Server. Suponha que você tenha uma tabela Customers com linhas duplicadas com base na coluna CustomerName e queira excluir todas as ocorrências de cada grupo duplicado, exceto a primeira.

-- Common Table Expression (CTE) to assign a row number to each customer 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Delete rows from the CTE
DELETE FROM CTE
WHERE RowNum > 1;Tecnologia

Uso de tabela temporária

Como o SQL Server não é compatível com a função CREATE TEMPORARY TABLE, você usa a função SELECT INTO. As tabelas temporárias no SQL Server usam # como um prefixo para o nome da tabela.

-- Create a temporary table
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Delete rows from the 'customers' table where the ID is not in the temporary table
DELETE FROM customers
WHERE CustomerIDNOT IN (SELECT ID FROM #temp_customers);
-- Optionally drop the temporary table after use
DROP TABLE #temp_customers;Tecnologia

Sugiro que você experimente o nosso curso de habilidades SQL Server Fundamentals para aprimorar suas habilidades de união de tabelas e análise de dados. A carreira de Desenvolvedor do SQL Server equipará você com as habilidades para escrever, solucionar problemas e otimizar suas consultas usando o SQL Server.  

Práticas recomendadas

As linhas duplicadas são um problema comum que afeta a qualidade dos dados e o desempenho do banco de dados. Considere as seguintes práticas recomendadas para evitar que registros duplicados sejam inseridos no seu banco de dados.

• Use chaves primárias: A coluna de chave primária garante que cada registro contenha informações exclusivas, evitando que valores duplicados entrem na tabela.
• Implemente restrições exclusivas: A aplicação de restrições exclusivas a qualquer coluna garante que não haja duplicatas em colunas de chave não primária, como endereços de e-mail ou números de telefone.
• Design e normalização adequados do banco de dados: O design eficaz do esquema e a normalização do banco de dados ajudam a reduzir a redundância e a duplicação de dados. Essa abordagem garante que cada registro seja armazenado em tabelas específicas.
• Use índices exclusivos: Use índices exclusivos para garantir que determinadas combinações de colunas sejam exclusivas sem exigir restrições completas no nível da tabela em todo o conjunto de dados.
• Auditorias regulares de dados: Realize auditorias regulares de dados executando consultas para identificar possíveis duplicatas com base em suas regras comerciais.

Conclusão

Identificar e remover linhas duplicadas é importante para manter a eficiência do banco de dados e a precisão dos dados. É sempre uma prática recomendada fazer backup de seus dados antes de fazer modificações para garantir que não ocorra nenhuma perda acidental de dados.

Se você estiver interessado em se tornar um analista de dados proficiente, confira nosso curso de carreira Associate Data Analyst in SQL para aprender as habilidades necessárias. O curso Reporting in SQL também é adequado se você quiser aprender a criar painéis profissionais usando SQL. Por fim, recomendo que você obtenha a Certificação SQL Associate para demonstrar que domina o uso do SQL para análise de dados e se destacar entre outros profissionais de dados.