SQL-Server 2005/2008: Eliminar duplicados con funciones de ranqueo

Escrito por Administrator. Publicado en artículos técnicos

Un uso interesante de las funciones de rankeo.

Con la introduccion de las funciones de posicionamiento o "ranking' en Microsoft (C) SQL Server 2005, ahora es posible crear conjuntos de datos (mediante sentencias select), que incluyen un numero consecutivo basado en el orden de dicho conjunto.

Esto se puede aprovechar para eliminar datos duplicados en una forma sencilla.

Considere por ejemplo la siguiente lista de datos

A
A
B
B
C
D
D
E

Pongamos estos datos en una tabla: este codigo crea una variable de tipo tabla, de modo que lo puede ejecutar sin afectar ninguna de sus bases de datos:

declare @AlphaList table(AlphaKey char);

insert into @AlphaList(AlphaKey) values('A');
insert into @AlphaList(AlphaKey) values('A');
insert into @AlphaList(AlphaKey) values('B');
insert into @AlphaList(AlphaKey) values('B');
insert into @AlphaList(AlphaKey) values('C');
insert into @AlphaList(AlphaKey) values('D');
insert into @AlphaList(AlphaKey) values('D');
insert into @AlphaList(AlphaKey) values('E');

select AlphaKey from @AlphaList orderby1;

Si ahora usamos la funcion ROW_NUMBER, podemos obtener una lista de @AlphaList con el numero secuencial de fila, de este modo:

selectROW_NUMBER()over(orderby AlphaKey)as RowNumber, AlphaKey from @AlphaList;

1 A
2 A
3 B
4 B
5 C
6 D
7 D
8 E

Si ahora agregamos un RANK a nuestra consulta, obtendremos una columna adicional con el posicionamiento de nuestros datos.

Por ejemplo, los valores "A" se posiciona como numero 1, los "B" como numero 2, etc.  Esta es una forma sencilla de asignar un valor por clase o categoría dependiendo de como ordenemos nuestros datos.  En este caso, el orden alfabético es el utilizado:

select RANK()over(orderby AlphaKey)as Rank
,ROW_NUMBER()over(orderby AlphaKey)as RowNumber
, AlphaKey
from @AlphaList;

Nuestro conjunto final de datos:

1 1 A
1 2 A
3 3 B
3 4 B
5 5 C
6 6 D
6 7 D
8 8 E

De aqui podemos ver que las filas de nuestra tabla que contienen el mismo valor para RANK y para ROW_NUMBER son a su vez la primera ocurrencia de cada dato independiente.

O lo que es lo mismo: si seleccionamos únicamente dichas filas, tendremos los valores únicos.

Esto se logra gracias a que los datos son clasificados y categorizados simultá¡neamente así que los valores iguales son adyacentes unos a otros.

Es importante notar que esto funciona porque:

  • La cláusula ORDER BY se usa para obtener los datos en un orden específico.
  • La función ROW_NUMBER asigna numeros secuenciales a cada fila del conjunto de datos obtenido.
  • La función RANK asigna un valor a cada bloque de datos conforme con un cierto criterio.
with AlphaRank(Rank, RowNumber, AlphaKey)as(
select RANK()over(orderby AlphaKey)as Rank
,ROW_NUMBER()over(orderby AlphaKey)as RowNumber
, AlphaKey
from @AlphaList
)
select AlphaKey
from AlphaRank
where Rank=RowNumber

Esta ultima versión de la consulta retorna estos valores:

A
B
C
D
E

COMENTARIO FINAL

La sentencia aprovecha nuevas caracterí­sticas del SQL Server 2005 y 2008 para resolver con eficiencia un problema.  La obtención de alrededor de 8,000 filas de una tabla de 63 millones de filas ejecutó en nuestras pruebas en menos de 3 minutos.  Otras estrategias como usar un SELECT DISTINCT en una tabla temporal, o usar un GROUP BY "ficticio", fueron todas mucho menos eficientes.

Ademas, el codigo es sencillo (requiere un par de lineas adicionales) y se puede adaptar a muchas necesidades.

La última sugerencia: en consultas repetitivas en tablas de gran tamaño, serí­a ideal contar con un índice basado en las columnas de su clausula SORT para una mayor eficiencia.

  • Paquetes WEB
  • Aplicaciones
  • Apoyo Técnico
  • Sistemas de Pesaje