Archive

Archive for June, 2011

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ó.

Advertisements

Removendo Cursor – Dica Rápida

Ola Galera, blz? Como o tempo está um pouco corrido essa vai ser uma dica rápida que pode ser usada no dia-a-dia.
Imaginem o cenário que é necessário criar uma string com diversos ID’s. E esses ID’s estão dentro de uma tabela. A idéia inicial seria realizar um loop para incrementar uma variavel e montar a nossa string.. Vamos ao exemplo:
O script abaixo cria uma tabela com os códigos do cliente que iremos usar para montar a nossa string.
use tempdb
go
if OBJECT_ID(‘dbo.tb_cliente’) is not null
drop table dbo.tb_cliente
go
create table dbo.tb_cliente(id int identity, codigoCliente int , nome varchar(50))
go
–Bloco que popula a tabela com informações dos clientes
set nocount on
declare @i int = 1
while @i <= 100
begin
insert into dbo.tb_cliente(codigoCliente,nome)values(@i,replicate(‘Thiago’,2))
set @i = @i + 1 *(2)
end
go

Declaramos as variaveis @count, @total para realizar o nosso loop. As variáveis @idCliente e @idClienteString serão usadas para armazenar os codigos concatenados. A tabela temporaria #CodigosCliente é usada para guardar as informações pertinentes ao predicado IN. O script abaixo preenche a tabela temporária com os registros que serão montados para o loop. Após a inserção, o total de registros e colocado em uma variável para que iniciemos a montagem da string.
declare @count int , @total int
declare @idCliente varchar(max), @idClienteString varchar(max)
create table #CodigosCliente(id int identity, codigoCliente int)
–Popula a temporaria com as informacoes que sera colocadas em uma string
insert into #CodigosCliente(codigoCliente)
select codigoCliente
from dbo.tb_cliente where codigoCliente between 10 and 50

set @idCliente = ‘ ‘
set @idClienteString = ‘ ‘
select  @total =count(id) from #CodigosCliente
set @count = 1
if @total > 0
begin
set @idClienteString = ‘codigoCliente IN ( ‘
end
else
begin
set @idClienteString = ‘codigoCliente IN (0 ‘
end
while @count <= @total
begin
if  @count > 1
begin
set @idClienteString = @idClienteString + ‘, ‘
end
select @idCliente=codigoCliente from #CodigosCliente where id=@count
set @idClienteString = @idClienteString + @idCliente
set @count = @count + 1
end
set @idClienteString = @idClienteString + ‘ )’
print @idClienteString
go
Após a execução do script acima, o SQL Server irá montar o predicado in com os valores, conforme imagem:

Conforme imagem acima, temos o resultado esperado, porém, operações linha-a-linha são mais custosas para o SQL Server. Nós poderíamos obter o mesmo resultado com o script abaixo:

declare @idCliente varchar(max )
declare @idClienteString varchar(max)
SET @idCliente =
SET @idClienteString =
SET @idClienteString = ‘codigoCliente IN (‘
SELECT @idCliente=@idCliente +‘,’+CONVERT(varchar(max),codigoCliente)from#CodigosCliente
SELECT @idCliente =substring(@idCliente,2,LEN(@idCliente))
SET @idClienteString = @idClienteString + @idCliente +‘)’
PRINT @idClienteString
Como podemos ver, temos o mesmo resultado com uma menor quantidade de linhas uma instrução única evitando o uso de um loop.

Créditos: Alexandre José Malachias….Valeu Boss