Fun��es Anal�ticas em Oracle

Temos tratado nas �ltimas colunas sobre a extraç�o de dados gerenciais no banco de dados Oracle. Uma boa forma de fazer esta extraç�o � utilizar funç�es anal�ticas que j� est�o dispon�veis h� algumas vers�es do banco de dados.

Imagine que voc� tenha um sistema de cobrança na sua empresa. Seus recebimentos s�o baseados em contratos, ou seja, todos os meses voc� realiza vendas e, como as vendas s�o financiadas, voc� tem um fluxo frequente de parcelas.

Voc� recebe a seguinte miss�o do diretor financeiro: �preciso saber quais s�o os contratos com os maiores valores de prestaç�o a cada m�s�. Seria relativamente simples oferecer esta resposta caso o diretor n�o tivesse inclu�do �contratos� na frase. Saber o valor das prestaç�es a cada m�s � muito simples.

Para resolver este problema, voc� precisar� utilizar a part�cula OVER do comando SELECT. Com esta opç�o � poss�vel, al�m de extrair dados sumarizados, mostrar as linhas anal�ticas (detalhe) ao mesmo tempo.

Ao utilizar o OVER voc� poder� estabelecer uma �quebra� (ou particionamento) para sua an�lise. Veja no comando a seguir:


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

O destaque mostra a atuaç�o da funç�o de grupo (MAX) e do particionamento da pesquisa (OVER). Neste caso, est� sendo mostrado o maior valor de prestaç�o de cada m�s. Este resultado ser� mostrado na coluna MAX_PREST. O resultado pode ser observado na figura 1.

Figura 1. Resultado do comando com OVER

Outro ponto que merece destaque � a possibilidade de mostrarmos os dados anal�ticos (VLPREST, CDCONTR e DTVENC) ao mesmo tempo que trabalhamos o maior valor do m�s (MAX_PREST). Desta forma � poss�vel observarmos quais s�o os contratos, os valores originais das prestaç�es e o maior valor daquele m�s.

Naturalmente n�o � exatamente isto que o diretor pediu... Ele quer apenas o maior valor do m�s e qual � o contrato. Podemos notar que o maior valor do m�s 10/2006 � o contrato 56 cujo valor � R$ 695,09 (ali�s, o �nico contrato deste m�s). J� nos meses seguintes nota-se que o maior valor do m�s 11/2006 � R$ 11.452,35 do contrato 33. E assim sucessivamente.

Para oferecermos apenas o resultado esperado pelo diretor, precisamos ir al�m no nosso comando:


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

Ao se utilizar a subquery na cl�usula FROM, pode-se extrair somente as linhas que interessam, ou seja, aqueles contratos e prestaç�es que t�m o maior valor de prestaç�o a cada m�s (comparaç�o do MAX_PREST e do VLPREST) na segunda cl�usula WHERE. O resultado pode ser visto na figura 2.

Figura 2. Resultado de acordo com a necessidade do diretor

Podem-se utilizar diversos particionamentos em uma �nica busca e tamb�m h� outras funç�es de grupo para se utilizar com a part�cula OVER.

Confira tamb�m

Curso de SQL

Curso

Modelagem de Bancos de Dados Relacionais

Curso

Curso de Oracle

Curso

Fun��es Anal�ticas em Oracle

Veja neste artigo uma de fazer a extração de dados gerenciais no banco de dados Oracle.

Confira tamb�m

Curso de SQL

Modelagem de Bancos de Dados Relacionais

Curso de Oracle

Artigos relacionados