Thiago Zavaschi R2 www.zavaschi.com

Repostagem – Ranking e Windowing

 

Essa repostagem eu não colocarei com data anterior, há muitos leitores que não conhecem esse recurso direito, mesmo estando presente desde o SQL Server 2005. Enjoy!

Utilizando Funções de Rank e Windowing

Hoje não vou falar de um recurso novo do SQL Server 2008, porém é bastante interessante e acredito que valha a pena um pequeno artigo sobre (principalmente em português já que não achei muita coisa em português).

As quatro funções T-SQL que vou me ater são: ROW_NUMBER(), RANK(), DENSE_RANK() e NTILE().
A base que utilizarei nos exemplos é a amplamente conhecida Northwind, que pode ser baixada gratuitamente do site da microsoft, mas para poupar o trabalho dos leitores e/ou possíveis mudanças no link, coloquei no meu SkyDrive: aqui.
Vamos começar dando uma pequena olhada nos dados.
Usamos a seguinte query SQL (limitada a 15 resultados e ordenados pelo preço unitário, do maior para o menor):

SELECT TOP 15 ProductID, ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC

Lembro que é uma boa prática utilizar os comandos SQL em caixa alta (todas as letras em maiúsculas).
Teremos o seguinte resultado:

ProductID   ProductName                              UnitPrice
----------- ---------------------------------------- ----------
38          Côte de Blaye                            263.50
29          Thüringer Rostbratwurst                  123.79
9           Mishi Kobe Niku                          97.00
20          Sir Rodney's Marmalade                   81.00
18          Carnarvon Tigers                         62.50
59          Raclette Courdavault                     55.00
51          Manjimup Dried Apples                    53.00
62          Tarte au sucre                           49.30
43          Ipoh Coffee                              46.00
28          Rössle Sauerkraut                        45.60
27          Schoggi Schokolade                       43.90
63          Vegie-spread                             43.90
8           Northwoods Cranberry Sauce               40.00
17          Alice Mutton                             39.00
12          Queso Manchego La Pastora                38.00

ROW_NUMBER():
O ROW_NUMBER é a função de ranking mais simples existente. Consiste de atribuir o valor de ranking igual a 1 para o primeiro registro (de acordo com o OVER, ver exemplo a seguir para facilitar), e para cada registro subsequente. Vejam o exemplo a seguir utilizando o ROW_NUMBER(). Percebam também que devido ao ORDER BY do OVER não é mais necessário usá-lo após o WHERE como na query anterior.

SELECT TOP 15 ProductID, ProductName, UnitPrice, ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS ROW_COUNT FROM Products

Teremos o seguinte resultado:

ProductID   ProductName              UnitPrice   ROW_NUMBER
----------- ------------------------ --------    -----------
38          Côte de Blaye             263.50          1
29          Thüringer Rostbratwurst   123.79          2
9           Mishi Kobe Niku           97.00           3
20          Sir Rodney's Marmalade    81.00           4
18          Carnarvon Tigers          62.50           5
59          Raclette Courdavault      55.00           6
51          Manjimup Dried Apples     53.00           7
62          Tarte au sucre            49.30           8
43          Ipoh Coffee               46.00           9
28          Rössle Sauerkraut         45.60          10
27          Schoggi Schokolade        43.90          11
63          Vegie-spread              43.90          12
8           Northwoods Cranberry Sauce40.00          13
17          Alice Mutton              39.00          14
12          Queso Manchego La Pastora 38.00          15

Bastante simples, como eu disse. Porém é bastante útil, eu particularmente a utilizo para fazer paginação nos casos em que ela é feita no banco de dados mesmo. Para tal basta adicionar ao final (por exemplo): WHERE ROW_NUMBER >= 10 AND ROW_NUMBER <= 21.

RANK():
Agora vamos utilizar a função RANK para ranquearmos os produtos do produto mais caro para o mais barato.
Com a função RANK podemos fazer isso, o comportamento do valor de ranking é o seguinte:
Caso haja empate, ambos recebem o mesmo valor de ranking. Só que o registro seguinte ao empate não é o número imediatamente subsequente, mas sim o numero de rank do empate acrescido do numero de registros empatados.

SELECT TOP 15 ProductID, ProductName, UnitPrice, RANK() OVER (ORDER BY UnitPrice ASC) AS RANK FROM Products

Dado o fato de que não há repetição de preços no exemplo inicial, então estou trocando da ordenação do decrescente pata a crescente (ao invés de DESC estou utilizando ASC).

ProductID   ProductName                     UnitPrice    RANK
----------- -----------------------         --------    ---------
33          Geitost                         2.50            1
24          Guaraná Fantástica              4.50            2
13          Konbu                           6.00            3
52          Filo Mix                        7.00            4
54          Tourtière                       7.45            5
75          Rhönbräu Klosterbier            7.75            6
23          Tunnbröd                        9.00            7
19          Teatime Chocolate Biscuits      9.20            8
47          Zaanse koeken                   9.50            9
45          Rogede sild                     9.50            9
41          Jack's New England Clam Chowder 9.65           11
21          Sir Rodney's Scones             10.00          12
3           Aniseed Syrup                   10.00          12
74          Longlife Tofu                   10.00          12
46          Spegesild                       12.00          15

O RANK trabalhou de modo semelhante ao ROW_NUMBER, mas no caso em que houveram registros com igual preço unitário (UnitPrice, utilizado no OVER) ele tratou com a mesma 'pontuação'. Para os correligionários do futebol, é algo semelhante às posições dos times nas tabelas de campeonatos, e etc.

Mas e se eu desejar que (segundo o exemplo acima) após o "rank 9" que foi repetido fosse o número 10 e não o 11? Para tal temos o:

DENSE_RANK():
O comportamento do é o mesmo do RANK(), o que muda é que não há "pulos", ou seja, se uma tupla retornada contém o rank de número 37, podemos garantir que todos os número do intervalo [1, 36] já apareceram.
A query para o nosso exemplo a seguir é a seguinte:

SELECT TOP 15 ProductID, ProductName, UnitPrice, DENSE_RANK() OVER (ORDER BY UnitPrice ASC) AS DENSE_RANK FROM Products

E a sua execução sobre a base Northwind resulta em:

ProductID   ProductName                     UnitPrice   DENSE_RANK
----------- -------------------------------- ---------- ---------
33          Geitost                           2.50      1
24          Guaraná Fantástica                4.50      2
13          Konbu                             6.00      3
52          Filo Mix                          7.00      4
54          Tourtière                         7.45      5
75          Rhönbräu Klosterbier              7.75      6
23          Tunnbröd                          9.00      7
19          Teatime Chocolate Biscuits        9.20      8
47          Zaanse koeken                     9.50      9
45          Rogede sild                       9.50      9
41          Jack's New England Clam Chowder   9.65      10
21          Sir Rodney's Scones               10.00     11
3           Aniseed Syrup                     10.00     11
74          Longlife Tofu                     10.00     11
46          Spegesild                         12.00     12

Conforme havia dito, após uma repetição (registros com ProductID 47 e 45, por exemplo), no caso do número 9, temos na sequência o número 10, e não o 11 como no RANK(). O último valor do DENSE_RANK é 12, então também sabemos que todos os números de 1 a 11 já apareceram (com ou sem repetição).

NTILE():
Ao contrário das outras funções que apresentei o valor para este rankeamento não é baseado no valor, mas sim na quantidade de registros. Se temos 12 registros e utilizamos um NTILE(N) (onde o N é um número natural), o SQL tenta agrupar as tuplas de retorno naquele número passado como paramêtro, atribuindo valores iguais para os elementos do mesmo grupo. Um exemplo prático pode ser obtido com a seguinte query SQL:

SELECT ProductID, ProductName, UnitPrice, NTILE(3) OVER (ORDER BY ProductID ASC) AS NTILE FROM Products WHERE ProductID <= 15

A query acima pode fazer você indagar: "Ué Zavaschi, porque você não utilizou o TOP 15 como já havia feito para os demais exemplos?".

Uma rápida explicação:
A questão é que o TOP é algo como um "truncador" de retornos. Uma query que originalmente retornaria 100 linhas de registros, ao executar um TOP 15, dizemos que queremos apenas as 15 primeiras, no entanto o SQL internamente trabalhou com as 100 linhas.
Ao eu executar um NTILE() e aplicar um TOP 15, o NTILE foi aplicado sobre toda a coleção e após isto eu recuperaria os 15 primeiros registros. Devido a tal comportamento, não seria possível mostrar o agrupamento que o NTILE faz, já que muito possivelmente todos os primeiros 15 registros estivessem no mesmo grupo (recebendo 1 como valor).

Como retorno a query apresentada temos a seguinte saída:

ProductID   ProductName                       UnitPrice  NTILE (3)
----------- --------------------------------- ---------- ----------
1           Chai                              18.00      1
2           Chang                             19.00      1
3           Aniseed Syrup                     10.00      1
4           Chef Anton's Cajun Seasoning      22.00      1
5           Chef Anton's Gumbo Mix            21.35      1
6           Grandma's Boysenberry Spread      25.00      2
7           Uncle Bob's Organic Dried Pears   30.00      2
8           Northwoods Cranberry Sauce        40.00      2
9           Mishi Kobe Niku                   97.00      2
10          Ikura                             31.00      2
11          Queso Cabrales                    21.00      3
12          Queso Manchego La Pastora         38.00      3
13          Konbu                             6.00       3
14          Tofu                              23.25      3
15          Genen Shouyu                      15.50      3

Gostaria que reparassem também que apesar do OVER, o resultado do NTILE() é baseado principalmente no número de resultados, caso não seja possível uma divisão exata, o SQL Server tentará agrupar da melhor maneira possível, fazendo que alguns conjuntos possuam um número de elementos maior que outros.
Alterando um poquinho a query para recuperarmos um registro a mais, mas mantendo o mesmo número de divisões:

SELECT ProductID, ProductName, UnitPrice, NTILE(3) OVER (ORDER BY ProductID ASC) AS NTILE FROM Products WHERE ProductID <= 16

Nos leva ao seguinte resultado, onde o grupo dos "1" possui um elemento a mais (6 elementos) do que o grupo dos "2" e "3" (5 elementos cada):

ProductID   ProductName                       UnitPrice NTILE (3)
----------- --------------------------------- --------- ----------
1           Chai                              18.00     1
2           Chang                             19.00     1
3           Aniseed Syrup                     10.00     1
4           Chef Anton's Cajun Seasoning      22.00     1
5           Chef Anton's Gumbo Mix            21.35     1
6           Grandma's Boysenberry Spread      25.00     1
7           Uncle Bob's Organic Dried Pears   30.00     2
8           Northwoods Cranberry Sauce        40.00     2
9           Mishi Kobe Niku                   97.00     2
10          Ikura                             31.00     2
11          Queso Cabrales                    21.00     2
12          Queso Manchego La Pastora         38.00     3
13          Konbu                             6.00      3
14          Tofu                              23.25     3
15          Genen Shouyu                      15.50     3
16          Pavlova                           17.45     3

"Zavaschi, gostei destas funções de ranking, mas esta última de NTILE me deu uma idéia... E se eu quiser aplicar (na mesma query) um ranking mas para grupos específicos dentre os resultados dessa query, é possível?"
Para tal utilizaremos o PARTITION BY.

PARTITION BY:
As funções de ranking pontem ser combinadas com funções de windowing (vou manter o termo em inglês, por questão de preferência). A função de windowing (janelamento ...) dividirá os registros retornados baseado no PARTITYON BY aliado à cláusula OVER utilizada na função de ranking. Com isso você obterá um ranking separado para cada partição. Vale lembrar que o PARTITION BY também pode ser usado com outras funções agragadoras, como MIN, MAX, etc.
Um exemplo disto pode ser visto a seguir (adaptando os exemplos anteriores para o seguinte exemplo):

SELECT ProductName, SupplierID, ROW_NUMBER() OVER (PARTITION BY SupplierID Order By SupplierID) AS PARTITIONBY FROM Products WHERE ProductID <= 15

Na query exemplo temos a aplicação da função de ranking ROW_NUMBER() sobre as partições geradas pelos registros onde cada Supplier é igual. Para facilitar a visualização estou ordenando também pelo SupplierID, mas não é necessário que o campo seja o mesmo.
A query exemplo nos retorna:

ProductName                              SupplierID  PARTITIONBY
---------------------------------------- ----------- ----------
Chai                                     1           1
Chang                                    1           2
Aniseed Syrup                            1           3
Chef Anton's Cajun Seasoning             2           1
Chef Anton's Gumbo Mix                   2           2
Grandma's Boysenberry Spread             3           1
Uncle Bob's Organic Dried Pears          3           2
Northwoods Cranberry Sauce               3           3
Mishi Kobe Niku                          4           1
Ikura                                    4           2
Queso Cabrales                           5           1
Queso Manchego La Pastora                5           2
Konbu                                    6           1
Tofu                                     6           2
Genen Shouyu                             6           3

No exemplo podemos ver que para janela (correspondida para os grupos onde os suppliers são iguais) temos a aplicação individual da nossa função de ranking.

---

Bom se você, leitor, chegou até aqui, parabéns! Além de mostrar que tem paciência em aguentar o que eu escrevi (hehe), ainda adquiriu um conhecimento bastante interessante sobre ranking no SQL Server.

Gostaria de reiterar que os exemplos são para fins didáticos e que quaisquer dúvidas e sugestões podem entrar em contato. :)

Abraços,
Thiago Zavaschi