Funciones de categoria (RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER(), OVER())

Importante: Debo dejar claro que NINGUNO de los métodos, procesos, procedimientos e invenciones aquí expuestas fueron creadas, ideadas o hechas en el trascurso de mi trabajo ni con el uso de los alguno de recursos que me provee la empresa que me emplea, los nombres, tablas, código y ejemplos NO son sacados de ninguna porción de código creada en alguno de los objetos de trabajo de alguno de mis empleadores ya que estos son propiedad del empleador correspondiente.

Las funciones de categoría en Microsoft SQL Server son funciones que para cada fila devuelven un número correspondiente a la clasificación de la fila dentro de un grupo definido, es decir son funciones no determinísticas.

ROW_NUMBER()

Sintaxis:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

La función ROW_NUMBER() devuelve como su nombre lo indica el numero de fila de un grupo de registros en una consulta.

<partition_by_clause>
Esta clausula divide la consulta en particiones según las columnas especificadas en su sintaxis y les aplica la función ROW_NUMBER(), es opcional ya que por defecto se particiona desde la primera fila hasta la ultima...

<order_by_clause>
No es más que la típica clausula ORDER BY especificando las columnas por las cuales se debe ordenar la consulta o cada partición, esta clausula es obligatoria...

Ejemplo:

Tenemos la siguiente tabla de clientes:

Vamos a ver el uso de la función ROW_NUMBER para esta pequeña tabla, primeramente vamos a enumerar todas las filas, para esto utilizaríamos la siguiente consulta:

SELECT ROW_NUMBER() OVER(ORDER BY Nombre) AS NF,
    Identificacion, Nombre, TipoCliente
FROM Clientes

Esta consulta nos da como resultado lo siguiente:

Como pueden ver la función ha enumerado las columnas ordenándolas por la columnas Nombre, también puede ser útil para realizar paginados en base de datos con un rendimiento bastante aceptable...

Ahora veamos cómo se utilizaría ROW_COUNT con la cláusula de partición "PARTITION BY", la consulta sería la siguiente para la misma tabla:

SELECT ROW_NUMBER() OVER(PARTITION BY TipoCliente ORDER BY Nombre) AS NF,
    Identificacion, Nombre, TipoCliente
FROM Clientes

Obteniendo los siguientes resultados:

Como podemos ver, la consulta particionó los registros en dos grupos, ya que especificamos que la clausula "PARTITION BY" lo hiciera por la columna TipoCliente y solo hay dos tipos de cliente, si hubiesen tres tipos de cliente, se hubiera particionado en tres, luego vemos que el ROW_NUMBER() fue aplicado según el orden que especificamos, es decir por Nombre, también podemos ver que ROW_NUMBER() actúa como lo habíamos esperado, volviendo a iniciar desde 1 en cada partición.

Para terminar, para filtrar ciertas filas o hacer otro tipo de filtro con la columna generada por el ROW_NUMBER() debemos volverla una subconsulta, más o menos de la siguiente forma:

SELECT R.*
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY TipoCliente ORDER BY Nombre) AS NF,
        Identificacion, Nombre, TipoCliente
    FROM Clientes
) AS R
WHERE R.NF % 2 = 0

En esta consulta filtramos los impares, dejando únicamente las filas con números pares...

ROW_NUMBER() en conjunto con "PARTITION BY" puede ser útil en reportes en donde nos piden por ejemplo: "Necesitamos un informe con los clientes que compraron nuestro producto x por segunda vez y lo hayan hecho en el mes de Abril"...

RANK()

Sintaxis:

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Esta función es muy similar a ROW_NUMBER(), la diferencia es que cuando encuentra un registro repetido en la columna especificada en la clausula ORDER BY el valor mostrado no aumenta pero si lo aumenta internamente, entonces por ejemplo si tenemos tres registros y los primeros dos son iguales, entonces mostrara en su columna la siguiente serie (1, 1, 3), en conclusión aumenta 1 mas el numero de rangos anteriores a la fila actual...

Entonces para ver cómo funciona, agregue tres registros mas a la tabla utilizada en la explicación anterior, la consulta con RANK() quedaría mas o menos así:

SELECT RANK() OVER(ORDER BY TipoCliente) AS RF,
    Identificacion, Nombre, TipoCliente
FROM Clientes

Lo cual nos daría el siguiente resultado:

Nótese en la imagen como los últimos elementos son consecutivos empezando desde el 9 y las filas siguientes al 5 tienen el mismo valor...

Con la clausula "PARTITION BY" podemos de alguna forma emular el ROW_NUMBER(con PARTITION BY), la consulta quedaría mas o menos así:

SELECT RANK() OVER(PARTITION BY TipoCliente ORDER BY Nombre) AS NF,
    Identificacion, Nombre, TipoCliente
FROM Clientes

Con lo cual el resultado sería el siguiente:

Bien, a excepción que en el caso de que haya un nombre repetido, nos mostraría como si fuera un partición aparte, aunque no lo es.
Todavía no se me ha ocurrido una aplicación para esta función, pero yo creo que la debe haber, así que si alguno de los lectores sabe de una o tiene un ejemplo en donde es recomendable usar RANK() por favor me avisa con un comentario...

DENSE_RANK()

Sintaxis:

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Esta función hace algo muy similar a lo que hace RANK(), la única diferencia es que no aumenta su contador cuando hay valores repetidos en la columna especificada en el ORDER BY, para un mejor entendimiento un ejemplo:

SELECT DENSE_RANK() OVER(ORDER BY TipoCliente) AS NF,
    Identificacion, Nombre, TipoCliente
FROM Clientes

Cuyo resultado sería el siguiente:

Esta función es útil cuando nos piden un reporte tipo "clasifíqueme" o "renquéeme los vendedores de o desde", si se utiliza con PARTITION BY esta clasificación es aplicada de forma independiente en cada partición, en fin...

NTILE()

Sintaxis:

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Esta función tiene como objetivo dividir una partición o grupo de filas en el numero especificado por el programador, es decir si se especifica por ejemplo un 3, la función divide el grupo de filas en 3 partes iguales si es posible y numera los grupos con un numero consecutivo, si no es posible dividir las filas en grupos iguales los primeros grupos serán los que tengan las filas adicionales.
Para mejor entendimiento un ejemplo, tenemos la siguiente consulta con la tabla que hemos utilizado desde el principio en este post, es algo como lo siguiente:

SELECT NTILE(3) OVER(ORDER BY TipoCliente) AS NF,
     Identificacion, Nombre, TipoCliente
FROM Clientes

En este caso estamos especificando a NTILE que nos haga 3 grupos en el conjunto de registros, y que estos registros deben ordenarse por TipoCliente, entonces los resultados serian los siguientes:

Como se puede ver en la imagen se dividieron las filas en 3 grupos pero los primeros dos grupos quedaron con una fila adicional debido a que no es posible dividir 11 en tres partes iguales, cada grupo se enumero con su respectivo numero, si se hubiera utilizado PARTITION BY particionaría las filas según lo especifiquemos por ejemplo según el tipo de cliente y a estos grupos les aplicaría la división de registros en grupos, si es posible...

OVER

Para funciones de categoría

OVER ( [ PARTITION BY value_expression , ... [ n ] ] <ORDER BY_Clause> )

Para funciones de agregado

OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

Ya vimos como se utilizan las funciones de agregado y también como se utiliza la clausula OVER en ellas, ahora como pueden ver OVER es una clausula independiente, y puede ser utilizada en dos tipos de funciones, las funciones de categoría que fueron las que acabamos de ver, y las funciones de agregado como COUNT(), SUM(), AVG()...etc.

Como pueden observar en la sintaxis, para funciones de agregado no hay clausula ORDER BY, pero si tenemos la clausula PARTITION BY.
Vamos a cambiar nuestra acostumbrada tabla y le vamos a agregar una columna llamada por ejemplo "TotalCompras" en donde registraremos las compras totales de cada cliente, la tabla quedaría de la siguiente forma:

Ahora supongamos que nos piden un reporte o informe que clasifique los clientes según su tipo y que por cada fila en cada clasificación nos muestre el porcentaje de compras que representa dicho cliente, saldría una consulta más o menos así:

SELECT (TotalCompras * 100) / R.Total, R.*
FROM (
    SELECT SUM(TotalCompras) OVER(PARTITION BY TipoCliente) AS Total,
        Identificacion, Nombre, TipoCliente, TotalCompras
    FROM Clientes
) AS R

Como se puede observar ha salido una consulta apenas de 7 líneas de código, imagínense cuantas gastarían si no lo hicieran con la clausula OVER, el resultado de la consulta sería el siguiente:

Con esto podemos ver su funcionalidad, para mostrar mejor los porcentajes podemos usar ROUND...

Conclusiones

• Las funciones de categoría son muy útiles en determinados casos, como por ejemplo, reportes o paginados.
• Si no hubiesen agregado estas funciones de categoría, probablemente estaríamos haciendo consultas más complejas y de menor rendimiento para cumplir con el objetivo.
• Algunas funciones como RANK() parecen haber sido hechas para objetivos muy específicos.
• La clausula OVER no solamente es útil en funciones de categoría sino también en funciones de agregado, aunque no es muy conocida su utilización en estas funciones, en algunos casos brinda buenos resultados.

adew.