Fun��es Anal�ticas em Oracle � parte 2

Funç�es Anal�ticas em Oracle � parte 2

Na primeira parte deste artigo, resolvemos um problema usual que exigiu a utilizaç�o da cl�usula OVER para podermos calcular uma funç�o de grupo que extraiu informaç�o em um agrupamento de dados.

Neste artigo vamos explorar um pouco mais o que pode ser feito com esta part�cula. A primeira coisa � que pode-se extrair mais particionamentos em um �nico comando SELECT. Para isto basta utilizar quantas cl�usulas OVER forem necess�rias. Veja um exemplo:

Seu diretor adorou a qualidade a soluç�o que voc� apresentou, mas, como ele notou que pode extrair mais informaç�es do banco de dados, ele tamb�m gostaria de ver o maior valor de prestaç�o da empresa. Desta forma, al�m de voc� mostrar o maior valor de prestaç�o a cada m�s e o respectivo contrato, ele tamb�m quer saber qual o maior valor de prestaç�o de toda empresa.

Para resolver este problema, voc� ter� que repetir o comando anterior e acrescentar uma nova coluna com o novo particionamento da busca. Neste caso, voc� ainda utilizar a funç�o de grupo MAX, mas utilizar� a cl�usula OVER atuando na coluna CDEMPRESA. Veja como fica o comando:

SELECT dtvenc, cdempresa, cdcontr, vlprest, max_empresa
FROM (
SELECT to_char( dtvenc, 'MM-YYYY') dtvenc,
cdcontr, vlprest,
MAX( vlprest ) OVER
(PARTITION BY to_char(dtvenc,'YYYYMM')) max_prest,
MAX( vlprest ) OVER
(PARTITION BY cdempresa ) max_empresa
FROM vRECEBER
WHERE dtvenc > sysdate ) WHERE vlprest = max_prest;

O resultado da busca est� na figura 1:

Figura 1: resultado do comando com duas cl�usulas OVER

Isto foi muito f�cil... Agora o diretor gostaria de uma nova informaç�o: quais s�o as 3 maiores prestaç�es de cada m�s e os respectivos contratos?

Bem, agora n�o temos apenas uma funç�o de grupo envolvida. Precisamos estabelecer uma ordem no valor das prestaç�es e selecionar somente as 3 maiores. Isso � o que se chama de Ranking. A cl�usula que utilizaremos para resolver este problema tamb�m tem o mesmo nome: RANK. A utilizaç�o � bastante simples. Uma vez que sabemos utilizar o OVER j� estamos com quase tudo resolvido. Vamos simplesmente indicar que faremos um particionamento dos valores de prestaç�o por data (exatamente como fizemos anteriormente), mas vamos indicar que queremos o resultado em uma determinada ordem (ORDER BY). Para indicar ao Oracle que queremos estabelecer um ranking dos resultados, vamos acrescentar a cl�usula RANK antes do OVER. Assim:

SELECT dtvenc, cdcontr, prestacao, rank
FROM (
SELECT to_char( dtvenc,'MM-YYYY' ) dtvenc, cdcontr,
vlprest prestacao,
RANK() OVER (
PARTITION BY to_char( dtvenc,'YYYYMM' )
ORDER BY vlprest DESC ) rank
FROM vreceber
WHERE DTVENC > SYSDATE )
WHERE rank <= 3
ORDER BY rank DESC;

Nada muito complicado. Tivemos que utilizar uma tabela virtual criada na cl�usula FROM do primeiro comando SELECT para facilitar a localizaç�o e exposiç�o dos dados. Note que isso foi feito apenas para facilitar a seleç�o (filtro) das 3 maiores prestaç�es (cl�usula WHERE do primeiro SELECT). Como atribu�mos o nome �RANK� para a coluna criada a partir do RANK OVER, o filtro tornou-se poss�vel. O RANK OVER e o particionamento da busca foi criado da mesma forma que anteriormente. Cada linha do SELECT aninhado recebe uma numeraç�o que indica a seq��ncia (ou posiç�o) que ela ocupa na pesquisa. Note que o ORDER BY deste mesmo SELECT indica que se quer em ordem decrescente, ou seja, do maior para o menor. N�o foi colocar um argumento em RANK porque a funç�o � simplesmente indicar a ordem de classificaç�o do ranking.

O resultado da busca est� na figura 2:

Figura 2: resultado do ranking.

H� outras cl�usulas que podem ser exploradas, como DENSE_RANK e a dupla LAG/LEAD, que voc� conhecer neste artigo.

Fun��es Anal�ticas em Oracle � parte 2

Veja neste artigo a continuidade de funções analíticas em Oracle.

Funç�es Anal�ticas em Oracle � parte 2

Artigos relacionados