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 freqüente 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, mas isso ficará para as próximas colunas.

Abraços e até lá!