Obtener la MODA de un grupo

En el desarrollo de nuestro último proyecto nos hemos encontrado ante la necesidad de saber para cada día del mes a qué hora ha habido el mayor número de llamadas, o lo que es lo mismo, cuál es la hora punta de llamadas de cada día.

En el fondo, si lo tratàramos como una muestra estadística sería obtener que hora es la Moda para cada día.

Supongamos que tenemos la siguiente consulta:
Select DiaLlamada, HoraLlamada, Count(*) as Total
From Llamadas
Where EjercicioLlamada = 2016 and MesLlamada = 9
Group by DiaLlamada, HoraLlamada
Order by 1, 3 desc

El resultado es el número total de llamadas por día y hora ordenado por día y número de llamada. Es decir, para cada bloque de días, el primer valor siempre nos devuelve la hora en la que hubo mayor número de llamadas.

Como lo que queremos es el primer registro de cada una de las categorías, no nos sirve añadir una clausula tipo TOP a la sentencia anterior, puesto que nos devolvería los n primeros resultados que serían siempre del mismo día.

Si hubieramos ordenado por número de llamadas independientemente del día tampoco podríamos utilizar la clausula TOP puesto que podrían haber registros del mismo día (si el mismo día hubiera habido muchas llamadas a varias horas) 

Solución
Para obtener el primer registro de cada una de las categorías de nuestra consulta tenemos que usar la función de categorización ROW_NUMBEREsta función devuelve el número secuencial de una fila dentro de una partición de un conjunto de resultados, comenzando con 1 para la primera fila de cada partición.

En otras palabras, numera las filas reiniciando el contador cada vez que encuentra una nueva categoría. En nuestro caso la agrupación la haremos sobre el campo DiaLlamada. Como la consulta se ordena por el campo Total, la hora que tenga más llamadas tendrá el número 1, la siguiente el 2 y así sucesivamente hasta la hora que menos llamadas haya tenido.

Por lo tanto, añadiendo la función Row_number a nuestra consulta:
Select DiaLlamada, HoraLlamada, count(*) as NumLlamadas, 
ROW_NUMBER() OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS RN 
From Llamadas 
Where EjercicioLlamada = 2016 and MesLlamada = 9 
Group by DiaLlamada, HoraLlamada

Tendremos la lista ordenada y en el campo RN el número de orden dentro de la categoría, por lo que únicamente nos quedará seleccionar de esta lista los elementos que tengan el valor 1 en el campo RN.

With Lista AS
(
Select DiaLlamada, HoraLlamada, count(*) as NumLlamadas,
ROW_NUMBER() OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS RN
From Llamadas
Where EjercicioLlamada = 2016 and MesLlamada = 9   
Group by DiaLlamada, HoraLlamada
)
Select * 
FROM Lista
Where RN = 1;


Es posible que dos franjas horarias tengan el mismo número de llamadas. En nuestro caso no importaba cual de ellas obtener. Si fuese importante, se debería modificar la consulta para ordenar según el criterio necesario.

Otras funciones de categorización

Además de Row_number, existen otras alternativas para ordenar y categorizar resultados, os dejamos la definición y un ejemplo de utilización de cada una de ellas.

ROW_NUMBER es una función de categoría y como tal, es una función no determinista. Si la expresión de ordenación no es única, cada vez que se ejecute la consulta podrá asignar posiciones diferentes para cualquier fila donde la expresión de ordenación sea la misma. En cambio, si esa expresión de ordenación es única, cada fila obtendrá una número de fila única.

RANK - A diferencia de ROW_NUMBER, no asigna una numeración única cuando dos registros comparten la misma posición. Es decir, podrán haber tantas posiciones 1ª, 2ª,... como registros compartan esa posición.

DENSE_RANK - Su funcionamiento es similar a RANK, aunque a diferencia de ese, sí asigna números contiguos.

NTILE - Según el parámetro que le asignemos dividirá los registros en tantos grupos, repitiendo la numeración.

Podéis comprobar sus diferencias en el resultado de la consulta que hemos estado utilizando:

Select DiaLlamada, HoraLlamada, count(*) as NumLlamadas,
ROW_NUMBER() OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS "Row Number",
RANK() OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS "Rank",
DENSE_RANK() OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS "Dense Rank",
NTILE (4) OVER (PARTITION BY DiaLlamada ORDER BY count(*) DESC) AS "Periodo"
From Llamadas
Where EjercicioLlamada = 2016 AND MesLlamada = 9 AND CodigoGrupo = 2
Group By DiaLlamada, HoraLlamada


Podéis obtener más información en los siguientes enlaces:

Funciones de categoría (Transact-SQL)
ROW_NUMBER (Transact-SQL)
ROW_NUMBER, o cómo obtener la enésima fila de un conjunto de resultados
Get top 1 row of each group
Adding Row Number to SQL SELECT result

0 comentarios :

Publicar un comentario

Lo más visto