Artigos Banco de Dados 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:

imagem ilustrativa

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:

imagem ilustrativa

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.