Funções de Ranking
No SQL Server 2005 apareceram as funes de ranqueamento. Essas funções nos ajudam a realizar algumas tarefas com mais facilidade que anteriormente(SQL 2000) .Você pode criar um numero incremental em uma consulta(simular a propriedade identity) ,criar funcionalidade de paginação e etc. Aqui mostrarei alguns exemplos onde essas funções possam ser aplicadas: A função ROW_NUMBER() acrescenta um inteiro incremental em uma consulta. Imagine que você precise montar uma consulta com o valor incremental e a nossa tabela não existe uma coluna identity(auto incremento), você pode usar a função ROW_NUMBER() para simular esse comportamento.
use tempdb
GO
if OBJECT_ID(‘dbo.tb_incrementa’,’U’)isnot null
drop table dbo.tb_incrementa ;
create table dbo.tb_incrementa(cliente intnotnull,nome varchar(30)notnull)
INSERT INTO dbo.tb_incrementa(cliente,nome)
VALUES (10,’Kalen Delaney’),(15,’Paul Randal’),
(16,’Kimberly L. Tripp’),(19,’Itzik Ben Gan’),
(20,’Bill Gates’),(23,’Pinal’)
O select traz as informações dos clientes que tem código e nome, o codigo do mesmo no incremental se nós quisessemos realizar uma consulta dando o valor incremental para os valores usaremos o ROW_NUMBER().
SELECT * FROM dbo.tb_incrementa
Com a query abaixo possível criar um valor incremental para cada registro:
SELECT nome ASNomeCliente , ROW_NUMBER()OVER(ORDERBY cliente ASC)AS ValorInrementalFROM dbo.tb_incrementa:
Usando RANK e DENSE_RANK
Abaixo crio uma tabela chamada tb_corrida que armazena o nome de alguns pilotos e o tempo que os mesmos realizaram asprovas em uma competição.
if OBJECT_ID(‘dbo.tb_corrida’,’U’)is not null
drop table dbo.tb_corrida ;
create table dbo.tb_corrida
(idCorrida intidentitynotnull, nomeCorredor varchar(30)notnull,
dataCorrida datenotnull, tempoCorrida timenotnull )
INSERT INTO dbo.tb_corrida(nomeCorredor,dataCorrida,tempoCorrida)
VALUES (‘Sebastian Vettel’,CURRENT_TIMESTAMP,’01:35:12:333′ ),
(‘Lewis Hamilton’,CURRENT_TIMESTAMP,’01:45:01:123′),
(‘Mark Webber’,CURRENT_TIMESTAMP,’01:45:00:128′),
(‘Felipe Massa’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Rubens Barrichello’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Vitaly Petrov’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Sebastian Buemi’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Jenson Button’,CURRENT_TIMESTAMP,’02:30:00′),
(‘Fernando Alonso’,CURRENT_TIMESTAMP,’02:31:56′)
Imagine que precisaremos criar uma query com o ranking de chegada dos corredores baseando-se no menor tempo de corrida poderiamos usar a função RANK() ou DENSE_RANK(). A única diferença entre elas que a RANK deixa um “buraco” para as informações que tem o mesmo valor. Você pode observar que Felipe Massa e Rubinho tem o mesmo tempo de conclusão da prova, fazendo os dois ficarem na mesma posição. A próxima classifição que deveria ser o “5” foi ignorado para o SQL Server. Quando a função DENSE_RANK() é utilizada esse “gap” desaparece. Como pode ser visto o SQL Server realiza o Rank em cima do tempo de concluso da prova.
SELECT nomeCorredor AS NomePiloto, tempoCorrida AS Tempo, RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoRank, DENSE_RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoDenseRank FROM dbo.tb_corrida
Um outro exemplo que pode ser feito montar o rank mundial dos corredores tendo como parâmetro o número de vitórias/pontos do corredor. O corredor que tiver mais vitórias/ponto, ser “top” no pódio. Esse exemplo também poderia também ser aplicado para criar a tabela do campeonato brasileiro por exemplo. Abaixo crio a tabela tb_podio e populo com algumas informações ficticias.
if OBJECT_ID(‘dbo.tb_podio’,’U’)isnotnull
droptable dbo.tb_podio ;
createtable dbo.tb_podio(
idCorrida intidentitynotnull,
nomePiloto varchar(30)notnull,
Pais varchar(15)notnull,
totalPontos int,
Equipe varchar(40),
numeroVitorias int )
INSERT INTO dbo.tb_podio(nomePiloto,Pais,totalPontos,Equipe,numeroVitorias)
VALUES (‘Sebastian Vettel’,’ALE’,148,’RBR-Renault’, 4),
(‘Lewis Hamilton’,’ING’,85,’McLaren-Mercedes’,1),
(‘Mark Webber’,’AUS’,79,’RBR-Renault’,0),
(‘Rubens Barrichello’,’BRA’,2,’Williams-Cosworth’,0),
(‘Vitaly Petrov’,’ING’,21,’Renault-Lotus’,0),
(‘Sebastian Buemi’,’SUI’,7,’STR-Ferrari’,0),
(‘Jenson Button’,’ING’,76,’McLaren-Mercedes’,0),
(‘Fernando Alonso’,’ESP’,69,’Ferrari’,0 )
Abaixo a query e feita pelo o total de pontos acumulados pelos corredores. Montando o rank mundial por pontos.A query também poderia ser feita pelo numero de vitórias.
SELECT Equipe,nomePiloto as Piloto, numeroVitorias,TotalPontos ,
DENSE_RANK()OVER(ORDERBY totalPontos DESC)As Classificacao
FROM dbo.tb_podioORDERBYClassificacao
Usando o NTILE
O NTILE() usado para dividir um conjunto de resultados em grupos aproximadamente iguais. Imagine que você tem uma tabela de pedidos com o valor total dos pedidos. O departamento de negócios da empresa solicitou que você criasse uma query que trouxesse os produtos em grupo por valor.
O NTILE() recebe um inteiro como parâmetro, esse numero é usado para quebrar o total de registros em grupos. Quando o total de registros é divido em grupos pelo valor do parâmetros de entrada do NTILE() o SQL Server incrementa a quantidade de registros da divisao(que sobraram) para o primeiros grupos. Por exemplo: Se temos 32 registros e dividimos ele em 3 grupos, o SQL Server ir criar 3 grupos com 10 registros. Com os dois registros restantes o SQL Server ir colocar um no primeiro grupo e outro no segundo. A query abaixo cria tr categorias para os produtos tendo como base o valor dos mesmos, so elas: Baixo, Medio e Alto.
if OBJECT_ID(‘dbo.tb_produtos’) is not null
drop table dbo.tb_produtos ;
create table dbo.tb_produtos
(
codigo int identity not null,
descricao varchar(50) not null,
valor money not null
)
GO
INSERT INTO dbo.tb_produtos(descricao,valor)
VALUES (‘Lapis’,1.00), (‘Caneta’,1.50),(‘Celular’,130.00)
,(‘Livros PMO’,175.00), (‘Monitor’,450.00),(‘TV’,1200.00)
,(‘DVD’,450.00),(‘MP4’,250.00),(‘New Civic’,60000.00)
,(‘NoteBook’,2500.00),(‘Fogao’,176.00)
go
SELECT
descricao AS Produto,
valor AS Valor,
CASE NTILE(3) OVER(ORDER BY valor)
WHEN 1 THEN ‘Baixo’
WHEN 2 THEN ‘Médio’
WHEN 3 THEN ‘Alto’
ELSE ‘Sem Categoria’
END AS DescricaoTitulo
FROM dbo.tb_produtos
ORDER BY valor;
Abs Galera. Por enquanto é só.
