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.

Como medir el tiempo de ejecucion de una consulta SQL

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.

Este es mi primer post en serio desde que los de byethost tumbaron mi antiguo blog, cuando tenga tiempo iré rescatando los post mas interesantes de ese antiguo blog...

Hay ciertas situaciones en las que uno tiende a crear procedimientos almacenados(stored procedures) un poco extensos y son:

• Cuando la logica del negocio es muy compleja.
• Cuando la empresa en la que trabajas quiere que le entregues todo para ayer.
• Cuando uno es muy novato en la programación de sql(mas exactamente Transact-sql).

El segundo punto se los puedo sustentar porque cuando se programa a las carreras, especialmente en transact-sql se tiende a NO modularizar el código, entonces uno hace todo el codigo de manera secuencial y por esto los procedures quedan un poco extensos.
Siendo este mi caso de ya hace unas semanas, tuve que hacer un procedimiento un poco complicado y lo terminé quedando de 1000 y pico lineas, obviamente con uno que otro comentario, entonces pensando en el rendimiento de este me dije: "tengo que medir el tiempo de ejecución de este mamotrete porque sino cuando lo vayan a usar los usuarios se van a poner alzados...", de esta situacion se ha inspirado este post, por eso estoy aqui expliandoles como hice para medir el rendimiento de esta piedra...

Tuve tres posibles soluciones para medir esto...
La primera se las resumo facilmente con las siguientes lineas de codigo: 

CREATE PROCEDURE dbo.PruebaDeRendimiento
    @PARAMS BIGINT,
    ....
AS
BEGIN
    DECLARE @InicioTiempo DATETIME
    SET @InicioTiempo = GETDATE()
   
    --------------------------------------------
    -- El codigo del procedimiento va aqui
    --------------------------------------------
   
    IF @DuracionEjecucion = 1
    BEGIN
        SELECT 'Duracion aproximada de la ejecucion: ' +
               CAST(DATEDIFF(MS,@InicioTiempo,GETDATE()) AS VARCHAR(20)) + '[ms]'
        RETURN
    END
END
GO

Como pueden observar se crea una variable tipo DATETIME para asignarle la fecha y hora actual(podemos tambien usar CURRENT_TIMESTAMP), para que al final podamos restarle la fecha y hora en ese instante(cuando termine) usando la función DATEDIFF con el parametro MS para que nos entregue los milisegundos de duración de la ejecución, como pueden ver es muy facil y creeria yo que da un tiempo aproximado de duración.

La segunda opción es usar las DMVs en ingles "Dynamic Management Views" ó en español "Vistas Dinamicas de Administración" que tiene el SQL Server y que usan los verdaderos DBAs xD, algunas de estas vistas requieren permisos en el servidor, una de las que vamos a manejar requiere el permiso VIEW SERVER STATE, mas especificamente me refiero a sys.dm_exec_query_stats que como dice la documentación ofrece información acerca del rendimiento de las consultas o comandos agregadas a la caché para los planes de ejecución, es muy util esta DMV porque nos muestra por ejemplo cuantas veces se ejecutó una determinada consulta, cuando se demoró, cuando fue la ultima vez que se ejecutó, el número de lecturas fisicas y logicas que se necesitaron, el número de escrituras fisicas y logicas, en fin, hay bastante información sobre la ejecución.Normalmente esta DMV nos permite ver la suficiente información, pero las instrucciones ejecutadas no nos las muestra, es por ello que nos toca utilizar una funcion llamada sys.dm_exec_sql_text que nos retorna una tabla, la cual nos muesta las batchs de SQL que hemos ejecutado, por medio de un manejador o handle, podemos buscar una determinada consulta, para mostrar las ultimas consultas hechas podemos utilizar algo como lo siguiente (basado en ejemplo MSDN):

SELECT query_stats.query_hash AS HashConsulta,
    query_stats.statement_text AS ConsultaSQL,
    (CAST(query_stats.total_worker_time AS FLOAT) / 1000) /
    query_stats.execution_count AS DuracionPromedioEjecucionMS,
    query_stats.last_execution_time AS HoraUltimaEjecucion,
    query_stats.execution_count AS NumeroVecesEjecutado
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
WHERE  query_stats.last_execution_time > DATEADD(MI,-20,GETDATE())
    AND query_stats.execution_count > 0
ORDER BY HoraUltimaEjecucion DESC

Lo cual nos muestra las ultimas consultas hechas desde hace 20 minutos, con esto podemos ver algo como:

Este metodo es mas preciso ya que obtiene la duracion de las ejecuciones en microsegundos por esta razon vemos valores con coma decimal, ademas podemos agregar mas filtros en el where por si tenemos muchos registros o incluso incluir el plan de ejecución usado en cada consulta "enganchando" otra funcion (sys.dm_exec_query_plan), en fin, hay muchas posibilidades.

Recomiendo antes de hacer las pruebas de ejcucion que corran el siguiente comando:

DBCC FREEPROCCACHE

Esto para limpiar la cache de ejecucion y poder consultar mas facil y rapidamente...

La tercera opcion es tambien muy sencilla, utilizando una de los parametros del SQL server llamado STATISTICS TIME podemos ver casi que directamente el tiempo de analisis, compilacion y ejecucion de alguna de las consultas que queramos, por ejemplo: 

SET STATISTICS TIME ON
GO
-- Esta es mi consulta de prueba
SELECT TerceroId
FROM Tercero
WHERE TipoTerceroId = 13
GO
SET STATISTICS TIME OFF
GO

como pueden ver es sencillamente activar el parametro, luego realizar la consulta y si desean lo pueden volver a desactivar, mostrandonos lo siguiente:

Conclusiones

• En SQL Server es muy facil obtener información acerca de la ejecución y del rendimiento de una consulta.
• Hay otros metodos para obtener esta información que no se vieron en este post.
• Estos tiempos son aproximados por obvias razones, asi que no van a ver el tiempo real de las consultas ya que es casi imposible, pero la aproximación que nos muestra es muy acertada, no obstante pueden ver diferencias entre la primera ejecución y las posteriores de una misma consulta, esto debido a diversos factores, asi que no se confundan.