Una de las nuevas funcionalidades que nos ofrece SQL Server 2005 es la de poder añadir expresiones de ranking a nuestra consulta. Esto viene muy bien en aplicaciones .NET para paginar y ordenar en un grid además de en cualquier otro escenario. Vamos a ver estas nuevas funcionalidades en la base de datos de ejemplo AdventureWorks.
ROW_NUMBER()
La función más básica de ranking es ROW_NUMBER(). Esta función devuelve una columna como una expresión que contiene el número de fila dentro del conjunto de resultados. Este número solo se usa en el contexto de la consulta, si el resultado de ella cambia, ROW_NUMBER cambiará. La expresión ROW_NUMBER toma una sentencia ORDER BY con la columna que queremos usar para contar las filas con un operador OVER como se muestra a continuación:
SELECT SalesOrderID, CustomerID, Row_Number() Over (Order By SalesOrderID) as RunningCount
FROM Sales.SalesOrderHeader
WHERE SalesOrderID > 10000
ORDER BY SalesOrderID
DevolverÍa el siguiente resultado:
SalesOrder ID Customer ID RunningCount
——– ———- ————–
43659 676 1
43660 117 2
43661 442 3
43662 227 4
43663 510 5
43664 397 6
43665 146 7
43666 511 8
43667 646 9
More
Alternativamente, si tenemos una clÁusula ORDER BY en nuestro conjunto de resultados diferente de la ORDER BY en nuestra expresión ROW_NUMBER()
Row_Number usando un valor único, diferente order by
SELECT SalesOrderID, CustomerID, Row_Number() Over (Order By SalesOrderID) as RunningCount
FROM Sales.SalesOrderHeader
WHERE SalesOrderID > 10000
Order By
CustomerID Diferente ORDER BY que en Row_NUMBER
Muestra lo siguiente:
SalesOrder ID Customer ID RunningCount
——– ———- ————–
43860 1 202
44501 1 843
45283 1 1625
46042 1 2384
46976 2 3318
47997 2 4339
49054 2 5396
More
Si elegimos la function ROW_NUMBER() contra una columna no única, se producirá un RunningCount de forma que no haya filas que tengan el mismo número. Por ejemplo, CUSTOMERID se puede repetir en este ejemplo:
SELECT SalesOrderID, CustomerID, Row_Number() Over (Order By CustomerID) as RunningCount
FROM Sales.SalesOrderHeader
WHERE SalesOrderID > 10000
Order By
CustomerID
Esta consulta muestra lo siguiente:
SalesOrder ID Customer ID RunningCount
——– ———- ————–
43860 1 1
44501 1 2
45283 1 3
46042 1 4
46976 2 5
47997 2 6
49054 2 7
50216 2 8
51728 2 9
57044 2 10
63198 2 11
69488 2 12
44124 3 13
. . . More
También podemos usar RANK como expresión en una cláusula WHERE. SQL Server devuelve un error cuando intentamos referirnos a una función de Ranking en una cláusula WHERE. Podemos solucionar esto usando una Common Table Expression.
- usar una common table expression si se quiere
- filtrar por una de las filas que contenga
- una función de ranking, ya que estas
- no están permitidas en cláusulas where o having
WITH NumberRows
AS
(
SELECT SalesOrderID, CustomerID, Row_Number() Over (Order By SalesOrderID) as RowNumber
FROM Sales.SalesOrderHeader
)
SELECT * FROM NumberRows
WHERE RowNumber BETWEEN 100 AND 200
ORDER BY SalesOrderID
El resultado se muestra a continuación:
SalesOrder ID Customer ID RunningCount
——– ———- ————–
43758 27646 100
43759 13257 101
43760 16352 102
43761 16493 103
43762 27578 104
43763 16525 105
43764 16612 106
43765 11010 107
43766 16518 108
43767 11001 109
43768 27649 110
43769 21659 111
43770 27614 112
�More
De la misma forma, podemos usar una CTE (Common Table Expression) si necesitamos establecer el rango por un agregado GROUP BY. En este caso, usaremos SUM() en una CTE y aplicaremos entonces ROW_NUMBER() despu�s del agregado.
�rango por la suma de totaldue
�necesitamos una CTE para hacer una suma
�de forma que este ejemplo tendr� un
�customerID sumado con todos de sus propios pedidos
With CustomerSum
As
(
Select
CustomerID, Sum(TotalDue) As totalamt
From
Sales.SalesOrderHeader
Group By
CustomerID
)
�a�ade un row_number al final de los resultados
Select
*, Row_Number() Over (Order By totalamt Desc) as RowNumber
From
CustomerSum
Resultado:
CustomerID totalamt RowNumber
���� ������� �������
678 1179857.4657 1
697 1179475.8399 2
170 1134747.4413 3
328 1084439.0265 4
514 1074154.3035 5
155 1045197.0498 6
72 1005539.7181 7
227 984324.0473 8
433 983871.933 9
166 979881.3491 10
146 964134.7777 11
670 946105.7121 12
506 937466.3027 13
�More
RANK()
RANK() trabaja de forma similar a ROW_NUMBER() excepto porque no deshace empates, es decir, no vamos a obtener un �nico valor en caso de empate.
Select
SalesOrderID, CustomerID, RANK() Over (Order By CustomerID) as RunningCount
From
Sales.SalesOrderHeader
Where
SalesOrderID > 10000
Order By
CustomerID
El resultado devuelto:
SalesOrderID CustomerID RunningCount
���� ���� �������
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 5
47997 2 5
49054 2 5
50216 2 5
51728 2 5
57044 2 5
63198 2 5
69488 2 5
44124 3 13
. . . More
El siguiente ejemplo redondea el campo TotalDue a la centena m�s cercana usando CTE y RANK sobre el campo derivado:
�rango por TotalDue, sumado y redondeado a la centena
�necesitamos una CTE para hacer la suma y redondear
�asi que este ejemplo obtendr� el total de todos
�los pedidos por CustomerID
With CustomerSum
As
(
Select
CustomerID, Round(Convert(Int, Sum(TotalDue))/100,8) *100 As totalamt
From
Sales.SalesOrderHeader
Group By
CustomerID
)
Select
*, Rank() Over (Order By totalamt Desc) as Rank
From
CustomerSum
La consulta mostrar� lo siguiente:
CustomerID totalamt Rank
���� ���� �������
678 1179800 1
697 1179400 2
170 1134700 3
328 1084400 4
514 1074100 5
155 1045100 6
72 1005500 7
227 984300 8
433 983800 9
166 979800 10
146 964100 11
670 946100 12
�More
DENSE_RANK() y NTILE(n)
DENSE_RANK() trabaja ex�ctamente como lo hace RANK() pero eliminando el salto entre n�meros en caso de empate.
Select
SalesOrderID, CustomerID, DENSE_RANK() Over (Order By CustomerID) as RunningCount
From
Sales.SalesOrderHeader
Where
SalesOrderID>10000
Order By
CustomerID
As� queda el resultado de la consulta:
SalesOrderID CustomerID RunningCount
���� ���� �������
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 2
57044 2 2
63198 2 2
69488 2 2
44124 3 3
. . . More
NTile(n) divide todos los resultados en partes aproximadamente iguales y asigna cada parte al mismo n�mero dentro del resultado de la consulta. Esto puede ser muy �til en porcentajes. Veamos un ejemplo:
Select
SalesOrderID, CustomerID, NTILE(10000) Over (Order By CustomerID) as RunningCount
From
Sales.SalesOrderHeader
Where
SalesOrderID > 10000
Order By
CustomerID
Nos dar�a algo as�:
SalesOrderID CustomerID RunningCount
���� ���� �������
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 3
57044 2 3
63198 2 3
69488 2 3
44124 3 4
. . . More
Un �ltimo ejemplo nos muestra c�mo usar todas estas funciones juntas en una sola consulta y de paso nos ense�a la diferencia entre las cuatro funciones de rango.
Select
SalesOrderID as OrderID,
CustomerID,
Row_Number() Over (Order By CustomerID) as RowNum,
RANK() Over (Order By CustomerID) as Rank,
DENSE_RANK() Over (Order By CustomerID) as DRank,
NTILE(10000) Over (Order By CustomerID) as NTile
From
Sales.SalesOrderHeader
Where
SalesOrderID > 10000
Order By
CustomerID
OrderID CustomerID RowNum Rank DRank NTile
���� ���� ��- �� �� ���
43860 1 1 1 1 1
44501 1 2 1 1 1
45283 1 3 1 1 1
46042 1 4 1 1 1
46976 2 5 5 2 2
47997 2 6 5 2 2
49054 2 7 5 2 2
50216 2 8 5 2 2
51728 2 9 5 2 3
57044 2 10 5 2 3
63198 2 11 5 2 3
69488 2 12 5 2 3
44124 3 13 13 3 4
44791 3 13 13 3 4
�More
PARTITION BY
Las funciones de rango tambi�n se pueden combinar con funciones de particionado. Este tipo de funciones divide un conjunto de resultados en particiones iguales basadas en los valores de una sentencia PARTITION BY en conjunci�n con la cl�usula OVER de la funci�n de rango. Es como aplicar un GROUP BY a nuestra funci�n de rango obteniendo un rango separado para cada partici�n. El siguiente ejemplo usa ROW_NUMBER con PARTITION BY para contar el n�mero de pedidos por fecha de pedido y por vendedor. Hacemos esto con un PARTITION BY SalesPersonID OVER OrderDate. Puede ser usado con cualquiera de las cuatro funciones de rango.
Select
SalesOrderID, SalesPersonID, OrderDate, Row_NUMBER() Over (Partition By SalesPersonID Order By OrderDate) as OrderRank
From
Sales.SalesOrderHeader
Where
SalesPersonID is not null
Resultado:
SalesOrderID SalesPersonID OrderDate OrderRank
���� ����- �������� ���
43659 279 2001-07-01 00:00:00.000 1
43660 279 2001-07-01 00:00:00.000 2
43681 279 2001-07-01 00:00:00.000 3
43684 279 2001-07-01 00:00:00.000 4
43685 279 2001-07-01 00:00:00.000 5
43694 279 2001-07-01 00:00:00.000 6
43695 279 2001-07-01 00:00:00.000 7
43696 279 2001-07-01 00:00:00.000 8
43845 279 2001-08-01 00:00:00.000 9
43861 279 2001-08-01 00:00:00.000 10
. . . More
48079 287 2002-11-01 00:00:00.000 1
48064 287 2002-11-01 00:00:00.000 2
48057 287 2002-11-01 00:00:00.000 3
47998 287 2002-11-01 00:00:00.000 4
48001 287 2002-11-01 00:00:00.000 5
48014 287 2002-11-01 00:00:00.000 6
47982 287 2002-11-01 00:00:00.000 7
47992 287 2002-11-01 00:00:00.000 8
48390 287 2002-12-01 00:00:00.000 9
48308 287 2002-12-01 00:00:00.000 10
. . . More
PARTITION BY soporta adem�s otras funciones de agregado incluyendo MIN y MAX.
* Traducci�n de Using Ranking and Windowing Functions in SQL Server 2005 de Stephen Forte publicada en www.sqljunkies.com