Funciones de ordenación: Row_Number, Rank, Dense_Rank y Ntile

Row_Number es una función de ordenación con base en un orden determinado dentro de una partición. El valor inicial es 1 y continúa con su asignación incrementando en 1 estos valores hasta el final y lo realiza por cada partición.

Rank es una función de clasificación con base en las posiciones de los elementos del set de datos. Si aparece un elemento con el mismo valor, este se clasifica en la misma posición y al siguiente elemento con diferente valor, lo clasifica como la posición siguiente según la cantidad total de elementos.

Dense_Rank es una función de clasificación con base en las posiciones de los elementos del set de datos parecida a la función Rank. Si aparece un elemento con el mismo valor, este se clasifica en la misma posición y al siguiente elemento con diferente valor, lo clasifica como la posición siguiente según la numeración de las posiciones.

Ntile es una función de clasificación con base en el número de particiones ordenadas, esta función clasifica la posición de los elementos según el número del grupo al que pertenece el elemento.

La sintaxis para utilizar estas funciones es:

función () OVER
    ([PARTITION BY <expresion_valor_2> [,<expresion_valor_3>, ...]])
    ORDER BY <expresion_valor_1> [ASC|DESC] 
    [NULLS FIRST | NULLS LAST] [,…])
Ejemplos:

En la tabla HumanResources.Employee hay un total de 290 filas.

Sin utilización de la expresión Partition By el motor divide la totalidad de filas en 29 grupos con max 10 (290/29) items por grupo. sin basarse en alguna columna.

select

NTILE(29) OVER( Order By BusinessEntityID) Posicion,

BusinessEntityID,

[NationalIDNumber],

JobTitle

from [AdventureWorks2016].[HumanResources].[Employee]

.…

Con la utilización de la expresión Partition By el motor divide la según el valor de la comuna escogida en la sentencia Partition By y con base en el número especificado se dividen los items del grupo.

select

NTILE(3) OVER(partition by jobtitle Order By BusinessEntityID) Posicion,

BusinessEntityID,

[NationalIDNumber],

JobTitle

from [AdventureWorks2016].[HumanResources].[Employee]

……

Ejemplo con Row_Number:

En este ejemplo no se define la sentencia partition by. En este caso se enumeran cada una de las filas.

select

Row_number() OVER(Order By BusinessEntityID) Posicion,

BusinessEntityID,

[NationalIDNumber],

JobTitle

from [AdventureWorks2016].[HumanResources].[Employee]

En este ejemplo se utiliza la sentencia partition by. En esta se enumera cada uno de los items por cada categoría en la consulta.

select

Row_number() OVER(partition by jobtitle Order By BusinessEntityID) Posicion,

BusinessEntityID,

[NationalIDNumber],

JobTitle

from [AdventureWorks2016].[HumanResources].[Employee]