Eliminando duplicados

Un factor de confusión y además un problema común con al trabajar con datos es la información duplicada. Puede presentarse en una tabla pobremente diseñada, en datos importados u otras situaciones, pero el problema sigue siendo el mismo, ¿como le dices a tu sistema que borre todo excepto una copia de cada fila? Afortunadamente, SQL Server tiene una herramienta que lo hace fácil: ROW_NUMBER()

En información duplicada, cada fila es lo mismo. Mientras puedes verlo y decidir que fila quieres borrar, el problema es puedes decirle a SQL Server cual fila no borrar. ROW_NUMBER() te permite agregar una columna que diferencia entre las filas y puedes borrar entonces solo las que tu deseas.

Digamos que tienes una tabla

CREATE TABLE DuplicateRow(
FName varchar(30),
LName varchar(30),
JobTitle varchar(30),
Age tinyint
)

Mantengamoslo sencillo para este ejemplo. Ahora digamos que consultas tu tabla y encuentras que cuando estabas haciendo los inserts te entusiasmaste de mas con el “execute”

Fname LName JobTitle Age
Mike      Klarm   Manager 37
Mike      Klarm   Manager 37
Mike      Klarm   Manager 37

Tu no quieres que este sujeto aparezca tres veces, pero si le mandas a SQL Server un DELETE basado en cualquiera de la información disponible, se borrarán los tres registros y tendrás que insertar de nuevo. Para mantener una fila y borrar las demás, puedes usar ROW_NUMBER(). Empezaremos con un SELECT para que puedas ver lo que está pasando

SELECT
ROW_NUMBER() OVER (PARTITION BY FName,LName, JobTitle, Age ORDER BY LName) R
, FName
, LName
, JobTitle
, Age
FROM DuplicateRow

esto te arrojará

R FName LName JobTitle Age
1 Mike      Klarm Manager 37
2 Mike      Klarm Manager 37
3 Mike      Klarm Manager 37

y ahora tu puedes borrar cualquier fila excepto donde R > 1

DELETE q
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY FName, LName, JobTitle, Age ORDER BY LName) R
,FName
,LName
,JobTitle
,Age
FROM DuplicateRow
) q
WHERE R > 1

No necesitas hacer el SELECT primero, pero es bueno ver que vas a borrar

Si hay otras filas en la base de datos, la primera instancia de cada conjunto de datos empezara con el número 1 y no sera borrada, pero cualquier registro que sea un duplicado si será borrado

Veamos ROW_NUMBER() un poco más de cerca. Toda la magia sucede en la clausula OVER() que le sigue a ROW_NUMBER(), especificamente la sección “PARTITION BY”. Aque es donde tu enlistas las columnas que esperas esten duplicadas. Ya que queremos borrar cualquier fila donde todas las columnas esten duplicadas, en esta sección enlistamos todas las columnas. “ORDER BY” no hace gran diferencia aqui ya que todas las filas son iguales, pero necesitas un valor aquí para que todo esto funcione.

Si tus datos son un poco diferentes, puedes usar ROW_NUMBER() para deshacerte de diferentes combinaciones de información. Digamos que Mike obtuvo una promoción el año pasado y alguién agrego una fila en lugar de actualizarla.

FName LName JobTitle Age
Mike      Klarm Manager 37
Mike      Klarm Manager 37
Mike      Klarm Manager 37
Mike      Klarm Operator 32
Mike      Klarm Operator 32
Mike      Klarm Intern 22

Dependiendo de que pongas en tu sección “PARTITION BY”, puedes decidir que borrar. Si tu quieres mantener una de cada conjunto, usa el mismo query que arriba y obtendras

R FName LName JobTitle Age
1 Mike      Klarm Manager 37
2 Mike      Klarm Manager 37
3 Mike      Klarm Manager 37
1 Mike      Klarm Operator 32
2 Mike      Klarm Operator 32
1 Mike      Klarm Intern       22

Se enumerará cada fila, empezando con uno para cada combinación unica de datos listados en la sección “PARTITION BY”. Si entonces corres la sentencia DELETE, te quedará lo siguiente

FName LName JobTitle Age
Mike      Klarm Manager  37
Mike      Klarm Operator 32
Mike      Klarm Intern       22

Pero digamos que solo quieres dejar el registro mas reciente. Podrías correr el query de arriba y también borrar cualquier registro donde Age <> 37, pero eso no funciona muy bien si tienes mucha información en la tabla, tendrías que especificar el caso de Mike Klarm para prevenir alguna perdida de información.

Una manera más fácil es modificando la clausula “PARTITION BY”

SELECT RO_NUMBER() OVER (PARTITION BY FName,LName ORDER BY Age DESC) R
,FName
,LName
,JobTitle
,Age
FROM DuplicateRow

Terminaras con algo como esto

R FName LName JobTitle Age
1 Mike      Klarm Manager  37
2 Mike      Klarm Manager  37
3 Mike      Klarm Manager  37
4 Mike      Klarm Operator 32
5 Mike      Klarm Operator 32
6 Mike      Klarm Intern       22

Entonces cuando borres donde R > 1 te quedraá el registro mas reciente. Puedes usar la clausula “PARTITION BY” para obtener muchos ordenes diferentes de la información solo parcialmente duplicada

 

Fuente

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s