SQL Eliminar duplicados: Métodos exhaustivos y buenas prácticas

Los registros duplicados son un problema común que puede comprometer la integridad de los datos y el rendimiento de la base de datos. Eliminar estos duplicados es esencial para mantener la exactitud de los datos, optimizar el almacenamiento y mejorar el rendimiento de las consultas. En este artículo, exploraremos varias técnicas para eliminar filas duplicadas en SQL, adaptadas a diversos casos de uso y sistemas de gestión de bases de datos.

Para empezar, recomiendo encarecidamente seguir los cursos Introducción a SQL y Aprende SQL de DataCamp para adquirir conocimientos básicos sobre la extracción y el análisis de datos mediante SQL. Además, considero que la Hoja de trucos de SQL Básico, que puedes descargar, es una referencia útil porque contiene todas las funciones SQL más comunes.

Comprender las filas duplicadas en SQL

Las filas duplicadas en SQL se refieren a los registros de una tabla que contienen valores idénticos en todas las columnas o en algunas seleccionadas. Las causas habituales de las filas duplicadas en SQL son las siguientes:

• Faltan claves primarias: Cuando las tablas carecen de una clave primaria definida o de una restricción única, no existe ningún mecanismo para impedir la inserción de datos duplicados. Esto puede ocurrir cuando una tabla no está normalizada y/o hay problemas de dependencia transitiva.
• Problemas de integración de datos: Al fusionar conjuntos de datos de distintas fuentes, las uniones inadecuadas o las incoherencias en los formatos de los datos pueden introducir accidentalmente duplicados.
• Errores de introducción manual de datos: Los errores humanos, como introducir el mismo registro varias veces, son otra causa común de las filas duplicadas.

En el resto del artículo, veremos cómo eliminar duplicados en SQL, y dividiremos el artículo en dos bloques. En la primera sección, veremos cómo eliminar duplicados en los datos que estás recuperando para un informe o cuadro de mando; en la segunda sección, veremos cómo eliminar duplicados en la base de datos.

Métodos para eliminar duplicados en los datos que recuperas

Existen distintos métodos para eliminar duplicados al recuperar registros en SQL. Cada método depende del SGBD, como SQL Server, MySQL y PostgreSQL. En esta sección, veremos los métodos para eliminar duplicados, destacando al mismo tiempo cualquier consideración especial para cada base de datos. Ten en cuenta que estos métodos filtran los datos y devuelven registros únicos y no modifican la tabla subyacente.

Utilizar la palabra clave DISTINCT

La palabra clave DISTINCT se utiliza en una sentencia SELECT para recuperar filas únicas. La sintaxis de la palabra clave DISTINCT para eliminar duplicados es similar para las bases de datos MySQL, PostgreSQL y SQL Server. La siguiente consulta recuperará nombres de clientes únicos de la tabla customers.

SELECT DISTINCT Name 
FROM customers;

Utilizar GROUP BY con funciones agregadas

La cláusula GROUP BY, combinada con otras funciones de agregación como MAX(), MIN(), o COUNT(), puede ayudar a eliminar registros duplicados de las tablas. La cláusula GROUP BY ayuda a seleccionar determinados registros para conservarlos mientras se eliminan otros duplicados.

Supón que quieres eliminar los registros de clientes duplicados, pero conservar el que tenga el ID más alto. Utilizarás la cláusula GROUP BY con la función MAX(), como se muestra a continuación.

-- 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;

MySQL y SQL Server admiten la sintaxis anterior de GROUP BY con funciones agregadas y la cláusula JOIN. 

Utilizar ROW_NUMBER() con expresiones comunes de tabla (CTE)

Con la función ROW_NUMBER() combinada con una Expresión Común de Tabla (ETC), puedes filtrar los duplicados según tus criterios. La función ROW_NUMBER, cuando se utiliza con las cláusulas PARTITION BY y ORDER BY, asigna un número secuencial único a cada fila. Este método permite filtrar las filas que no cumplen los criterios requeridos.

La siguiente consulta identifica los duplicados y elimina todos menos el primero.

-- 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;

Este método funciona bien con las versiones modernas de SQL Server, MySQL y PostgreSQL. Es útil para conjuntos de datos más grandes o condiciones más complejas, ya que te permite especificar exactamente qué duplicado conservar.

Eliminar duplicados mediante auto-JOIN

Una autounión te permite comparar una tabla consigo misma, lo que la hace útil para identificar y eliminar filas duplicadas comparando registros en función de criterios específicos. El siguiente ejemplo utiliza la autounión para eliminar la fila con el ID más alto, manteniendo sólo la primera aparición de cada nombre.

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

El método anterior funciona en las principales bases de datos, como SQL server, MySQL y PostgreSQL. Consulta nuestro curso de SQL Intermedio para saber más sobre el uso de funciones agregadas y uniones para filtrar datos.

Métodos para eliminar duplicados en la base de datos

Aunque puedes eliminar registros duplicados mediante consultas, también puedes eliminarlos permanentemente de la base de datos. Este enfoque es importante para mantener la calidad de los datos. Los siguientes métodos se utilizan para eliminar duplicados de la base de datos.

Utilizar ROW_NUMBER() y DELETE

La función ROW_NUMBER() asigna un número secuencial a las filas dentro de una partición definida. Cuando se utiliza con la sentencia DELETE, ayuda a identificar duplicados clasificando las filas en función de columnas específicas y eliminando los registros no deseados. Este método se aplica a las versiones modernas de MySQL (a partir de la 8.0), PostgreSQL y SQL Server.

Supongamos que quieres eliminar registros de clientes duplicados basándote en la columna Name, manteniendo sólo la primera aparición (la más pequeña 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);

Utilizar DELETE con subconsulta

A veces, una simple operación DELETE utilizando una subconsulta puede eliminar duplicados de la base de datos. Este método es adecuado para versiones antiguas de MySQL o PostgreSQL en las que ROW_NUMBER() podría no estar disponible.

La siguiente consulta elimina las filas de la tabla customers en las que el ID no es el mínimo para cada Name, manteniendo sólo la fila con el ID más pequeño para cada Name único.

-- 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
);

Uso de la cláusula GROUP BY con HAVING

Cuando necesites comprobar si hay valores duplicados en columnas concretas, puedes utilizar la cláusula GROUP BY combinada con la cláusula HAVING para identificar duplicados. Este método te permite eliminar filas concretas en función de los criterios indicados. Este método es compatible con SQL Server, MySQL y PostgreSQL.

La siguiente consulta elimina las filas de la tabla customers en las que el ID pertenece a un grupo de duplicados.

-- 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
);

Utilizar tablas temporales para el procesamiento por lotes

Las tablas temporales son eficaces para el procesamiento por lotes y la eliminación de duplicados en grandes conjuntos de datos. Este método es útil cuando las consultas individuales pueden causar problemas de rendimiento. La siguiente consulta crea una tabla temporal para almacenar el ID mínimo de cada customer_name y eliminar filas de la tabla customers en las que el ID no esté en la tabla 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);

La sintaxis anterior utilizando CREATE TEMPORARY TABLE sólo es compatible con las bases de datos MySQL y PostgreSQL. 

Eliminar duplicados en SQL Server

SQL Server ofrece distintos métodos para eliminar registros duplicados de la base de datos. Estos métodos incluyen el uso de DISTINCT con INTO, ROW_NUMBER(), y tablas temporales.

Utilizar DISTINCT con INTO

Puedes utilizar la palabra clave DISTINCT en una sentencia SELECT para crear una nueva tabla con registros únicos. Puedes eliminar la tabla antigua una vez que compruebes que la tabla nueva tiene los registros especificados. El siguiente ejemplo crea la tabla unique_customers con registros únicos de la tabla 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';

Utilizar ROW_NUMBER()

También puedes utilizar la función ROW_NUMBER() para eliminar registros duplicados del Servidor SQL. Supongamos que tienes una tabla Customers con filas duplicadas basadas en la columna CustomerName, y quieres eliminar todas menos la primera aparición de cada grupo duplicado.

-- 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;

Utilizar tabla temporal

Como SQL Server no admite la función CREATE TEMPORARY TABLE, utiliza la función SELECT INTO. Las tablas temporales en SQL Server utilizan # como prefijo para el nombre de la tabla.

-- 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;

Te sugiero que pruebes nuestro curso de Fundamentos de SQL Server para mejorar tus habilidades de unión de tablas y análisis de datos. La carrera de Desarrollador de SQL Server te dotará de los conocimientos necesarios para escribir, solucionar problemas y optimizar tus consultas utilizando SQL Server.  

Buenas prácticas

Las filas duplicadas son un problema común que afecta a la calidad de los datos y al rendimiento de la base de datos. Ten en cuenta las siguientes buenas prácticas para evitar que se inserten registros duplicados en tu base de datos.

• Utiliza claves primarias: La columna de clave primaria garantiza que cada registro contenga información única, evitando que entren valores duplicados en la tabla.
• Implementar Restricciones Únicas: Aplicar restricciones únicas a cualquier columna garantiza que no existan duplicados en columnas de clave no primaria, como direcciones de correo electrónico o números de teléfono.
• Diseño y normalización adecuados de la base de datos: Un diseño eficaz del esquema y la normalización de la base de datos ayudan a reducir la redundancia y los datos duplicados. Este enfoque garantiza que cada registro se almacene en tablas específicas.
• Utiliza índices únicos: Utiliza índices únicos para garantizar que determinadas combinaciones de columnas sean únicas sin necesidad de restricciones completas a nivel de tabla en todo el conjunto de datos.
• Auditorías periódicas de datos: Realiza auditorías de datos periódicas ejecutando consultas para identificar posibles duplicados en función de tus normas empresariales.

Conclusión

Identificar y eliminar las filas duplicadas es importante para mantener la eficacia de la base de datos y la exactitud de los datos. Siempre es una buena práctica hacer una copia de seguridad de tus datos antes de realizar modificaciones, para asegurarte de que no se produce ninguna pérdida accidental de datos.

Si estás interesado en convertirte en un analista de datos competente, consulta nuestro itinerario profesional de Analista de Datos Asociado en SQL para aprender las habilidades necesarias. El curso Informes en SQL también es adecuado si quieres aprender a crear cuadros de mando profesionales utilizando SQL. Por último, recomiendo obtener la Certificación de Asociado SQL para demostrar tu dominio del uso de SQL para el análisis de datos y destacar entre los demás profesionales de datos.