Funciones de Rango

Hoy quiero hablar de las funciones de rango que existen en Oracle a partir de la versión 8i, y en Microsoft SQL Server a partir de la versión 9 (SQL Server 2005). Estas funciones se usan para asignar a cada registro un rango, una jerarquía, de acuerdo a un criterio determinado.

Las funciones que voy a mencionar hoy son RANK() y DENSE_RANK().

1. Referencias.

No quiero centrarme en la definición formal de las funciones, sino más bien en cómo sacarle provecho. Las definiciones de las funciones pueden encontrarse en:

Para Microsoft SQL Server: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Para Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions123.htm

2. Contexto de Ejemplo

Supongamos que tenemos una base de datos de un colegio, que cuenta con las tablas ALUMNO, MATERIA, CURSO, SECCION, CORTE, EVALUACION. Los alumnos pertenecen a un curso, por ejemplo: Primero, Segundo, Tercero, Cuarto... (Imaginemos que son cursos de la ESO en España, o de Primaria en Venezuela)

Cada curso tiene varias Secciones, o grupos de alumnos. Digamos que las Secciones son A, B, C... Cada alumno pertenece a una sección de un curso. Además, los profesores dictan Materias. Para cada curso hay varias materias, y una materia pertenece a un solo curso. Un Curso se compone de varios Cortes, y en cada corte se evalúan todas y cada una de las materias del curso. Los resultados de la evaluación de cada alumno en cada corte se guardan en la tabla Evaluación.

Alumno

Alumno_Id

Nombre

Seccion_Actual_Id

Materia

Materia_Id

Nombre

Curso_Id

Curso

Curso_Id

Nombre

Seccion

Seccion_Id

Curso_Id

Nombre

Corte

Corte_Id

Fecha

Evaluacion

Alumno_Id

Materia_Id

Corte_Id

Califiacion

3. Cómo obtener los primeros N registros.

Supongamos que queremos obtener los tres alumnos que han obtenido las mejores notas en un corte:

select

Alumno.Nombre,

Materia.Nombre,

O.Calificacion,

O.Puesto
from
(

select

Alumno_Id,

Materia_Id,

Corte_Id

Calificacion,

rank() over(order by calificacion desc) Puesto

from Evaluacion

where Corte_Id = 1
) O
join Alumno
on Alumno.Alumno_Id = O.Alumno_Id
join Materia
on Materia.Materia_Id = O.Materia_Id
where O.Puesto <=3

4. Obtener los primeros N registros de cada grupo.

Ahora supongamos que nos interesan los tres alumnos que han obtenido las notas más altas en cada materia, por ejemplo: los tres mejores de Matemáticas, los tres mejores de Deporte (jajaja)...

select

Alumno.Nombre,

Materia.Nombre,

O.Calificacion,

O.Puesto
from
(

select

Alumno_Id,

Materia_Id,

Corte_Id

Calificacion,

rank() over(partition by Materia_Id order by calificacion desc) Puesto

from Evaluacion

where Corte_Id = 1
) O
join Alumno
on Alumno.Alumno_Id = O.Alumno_Id
join Materia
on Materia.Materia_Id = O.Materia_Id

La función RANK() no es excluyente, eso significa que si dos alumnos tienen la misma calificación, RANK() devuelve el mismo valor para ambos. Además, RANK() deja espacios vacíos, es decir, si hay un empate en el primer lugar, no habrá nadie ocupando el segundo lugar, por ejemplo:

Juanito	Matemáticas	99	1
Pepito	Química	99	1
Sandrita	Matemáticas	98	3

Si no queremos espacios vacíos, podemos usar la función DENSE_RANK(), y si no queremos "empates", tendremos que darle a la función más argumentos que permitan desempatar:

rank() over (partition by Materia_Id order by Calificacion desc, Materia_Id asc, Alumno_Id desc)