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