Contadores y Rangos o funcion Rank() en SQLServer

Contadores y Rangos o funcion Rank() en SQLServer
Microsoft SQL Server
hminguet 3 Octubre, 2011 - 14:40

Crear contadores en SQLSERVER, simple pero útil.

 

1. Contador de registros:

select num = (select count(*) from d_Category r where r.id_Category<=t.id_category), t.* from d_category t

 

1.1. Con la función Ranking (sólo a partir del SQLSERVER 2005):

select rank() over (order by t.id_Category asc) as Ranking, t.* from d_category t order by 1

 

2. Contador por la suma del valor de un campo (por ranking).

En este caso obtendremos los 10 mejores clientes en ventas del 2010

select rank=count(*), s1.Customer, Val=round(s1.Val,0) from (select Customer, Val=round(sum(importe),0) from f_sales where year=2010 group by Customer) s1, (select Customer, Val=round(sum(importe),0) from f_sales where year=2010 group by Customer) s2 where s1.Val<= s2.Val group by s1.Customer having count(*)<11 order by 1

 

2.1. Con la función Ranking (sólo a partir del SQLSERVER 2005):

select * from ( select rank() over (order by importe desc) as Ranking, a.* from (select customer, sum(importe) as importe from f_sales where year=2010 group by Customer ) a ) F_TABLE where Ranking<=10

y filtramos por los 10 primeros, por ejemplo.

 

 

Espero que os ayude.

Héctor Minguet.

 

Subido por Carlos el 15 Enero, 2016 - 21:19

Con respecto a la función rank() over.., en lugar de necesitar una ordenación de todos los registros para sacar los 10 primeros, como en el último ejemplo, podemos necesitar una ordenación parcial de grupos de registros. Por ejemplo, en lugar de los 10 clientes con más importe pueden interesarnos los 10 clientes de cada zona con mayor importe, que es algo más habitual.

Ordenar registros agrupados con rank() over (partition by campo1 order by campo2)

Para ordenar agrupaciones de registros y quedarnos con los que nos interese (los primeros, los últimos..) lo único que hay que hacer es añadir a la sentencia la clausula 'partition by' de SQLServer para que nos haga las agrupaciones, y devuelva así el ranking, pero dentro de cada grupo o partición. Con el ejemplo anterior sería algo así:

select * from
( 
  select rank() over (partition by zona order by importe desc) as Ranking, 
         a.*
  from (select zona, customer, sum(importe) as importe
        from f_sales where year=2010 group by zona, Customer ) a
) F_TABLE
where Ranking<=10

Con esta consulta obtendríamos los 10 mejores clientes de cada zona, rankeados del 1 al 10 por su importe.

 

Eliminar duplicados en SQL Server con rank over y partition by

Este tipo de sentencias también puede ser muy útil para eliminar duplicados, y quedarnos sólo con un registro de varios. Sería algo tan simple como agrupar con partition by por los campos que queremos que nos definan los registros únicos, y quedarnos sólo con el primero de los que nos devuelva la ordenación.

Si, por ejemplo, sabemos que tenemos clientes duplicados porque se han hecho altas de clientes cuando estos ya existían, y el cliente bueno es el de la última fecha de alta, podemos 'librarnos' de los obsoletos con algo así:

select * from
( 
  select rank() over (partition by customer order by fecha_alta desc) as Ranking, 
         a.*
  from (select customer, fecha_alta, sum(importe) as importe
        from f_sales where year=2010 group by Customer, fecha_alta ) a
) F_TABLE
where Ranking=1

 

Eliminar duplicados en SQL Server con row_number over y partition by

El método con rank() para eliminar registros duplicados sólo tiene un problemilla, y es que el rank, si en el campo por el que ordena se encuentra dos valores iguales, devuelve la misma posición o rango a los dos registros del grupo, es decir, que si un cliente se diera de alta dos veces el mismo día, y esa fuera la fecha de alta más reciente, el rank nos devolvería un 1 para los dos registros, y nos quedaríamos con los dos, comiéndonos el duplicado.

Para evitar que nos pase esto, que además puede costar de ver, y generar bastantes problemas si se nos cuela el registro duplicado, en lugar de rank() se puede utilizar row_number(), que devuelve un número para cada registro del grupo, aunque el valor de la ordenación sea el mismo.

Siguiendo con el ejemplo anterior, para asegurarnos de que nos quedamos sólo con uno de los registros de cliente que se han podido dar de alta en varias ocasiones, sólo tenemos que cambiar rank() por row_number():

select * from
( 
  select row_number() over (partition by customer order by fecha_alta desc) as fila, 
         a.*
  from (select customer, fecha_alta, sum(importe) as importe
        from f_sales where year=2010 group by Customer, fecha_alta ) a
) F_TABLE
where fila=1

La sentencia anterior sería para hacer la selección de los registros que nos interesan, descartando los duplicados, y después se pueden guardar en una tabla con un insert, por ejemplo.

Pero si queremos eliminar los registros duplicados existentes en una tabla la sentencia SQL a utilizar sería diferente. La subselect del ejemplo ya no tiene sentido, ahora crearemos una sentencia SQL más sencilla trabajando directamente sobre todos los registros de una tabla customer_table.

Además, contaremos con la ayuda del ;with de CTE para eliminar los duplicados que no interesan, tal como se sugiere en este tema sobre borrado de registros duplicados:

;with duplicado as 
( 
 select row_number() over (partition by customer order by fecha_alta desc) as fila, 
        c.*
 from customer_table c 
) 
delete from duplicado where fila>1

Bueno, pues así ya tenemos controlados contadores, rangos, agrupaciones y duplicados en SQL Server, y tenemos algunos ejemplos de la función rank() de SQLServer, que cuesta un poco de entender, pero una vez que se aprende a utilizar nos puede evitar muchos quebraderos de cabeza con nuestras consultas, que de otra manera se pueden complicar bastante, y más si además queremos deshacernos de registros duplicados.