Funções Analíticas em Oracle - Windowing

Neste artigo vamos explorar um pouco mais as funções analíticas no Oracle. Desta vez vamos explorar um pouco o recurso de windowing. Na realidade toda utilização de funções analíticas está baseada neste conceito. Uma cláusula analítica define o acesso a um grupo de linhas e a este grupo de linhas dá-se o nome de janela (window). Esta janela será utilizada para realizar os cálculos com base na linha especificada. Desta forma, nota-se que é possível especificar uma janela para cada linha de pesquisa.

Algumas das funções analíticas podem ser utilizadas para estabelecer uma janela de cálculo no Oracle. São elas: AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP e VARIANCE. Funções de regressão linear também podem ser utilizadas.

Estrutura

A cláusula de windowing é colocada após o OVER, depois de se especificar o particionamento e a ordem. Note que é necessário, quando se utiliza o windowing especificar uma ordem para que se possa estabelecer o intervalo de linhas que serão utilizadas como base para extrair os dados da janela.

Pode-se optar por utilizar RANGE (quando for um padrão lógico) ou ROWS (quando for físico). Estas cláusulas determinarão a forma e abrangência para cálculo do resultado. A grande vantagem está em se determinar “regiões” de busca. Desta forma, deve-se utilizar a cláusula BETWEEN para determinar o intervalo de ação do cálculo. Define-se o limite inferior e superior para se realizar o cálculo e pode-se utilizar expressões como:

- UNBOUNDED PRECEDING: desde o início da partição

- UNBOUNDED FOLLOWING: até o final da partição

- CURRENT ROW: linha atual

A combinação destas expressões com o BETWEEN determinarão a janela de consulta.

Quando trabalhamos com ROWS não há preocupação sobre o tipo de dados que se utiliza para a cláusula ORDER BY. O cálculo será realizado no grupo de linhas físico retornado na consulta.

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr, vlprest,

       AVG( vlprest ) OVER

        (PARTITION BY to_char(dtvenc,'YYYYMM')

         ORDER BY to_char(dtvenc,'YYYYMM')

         ROWS BETWEEN

          UNBOUNDED PRECEDING AND CURRENT ROW ) avg_prest

  FROM vRECEBER

 WHERE dtvenc > sysdate;

Neste caso a média (AVG_PREST) é extraída sempre a partir da primeira linha até a linha atual.

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr, vlprest,

       AVG( vlprest ) OVER

       (PARTITION BY to_char(dtvenc,'YYYYMM')

         ORDER BY to_char(dtvenc,'YYYYMM')

         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) avg_prest

  FROM vRECEBER

 WHERE dtvenc > sysdate;

Observe que a média da primeira linha (AVG_PREST) é extraída das duas primeiras linhas porque não há linha anterior a esta. A partir da segunda linha, a média é extraída sempre entre os valores de uma linha anterior, a linha atual e a linha posterior.

Para utilizarmos analisarmos o RANGE, temos que modificar a cláusula ORDER BY para um valor numérico, visto que nosso resultado é numérico (poderia ser em ordem de data também). Caso nosso resultado fosse um intervalo de datas, a cláusula ORDER BY deveria conter obrigatoriamente uma data. Isso deve ser feito porque trabalharmos com um grupo lógico de dados. As comparações não são realizadas somente sobre as linhas que estamos vendo.

SELECT to_char( dtvenc, 'MM-YYYY') dtvenc, cdcontr, vlprest,

       AVG( vlprest ) OVER

        (PARTITION BY to_char(dtvenc,'YYYYMM')

          ORDER BY cdcontr

          RANGE BETWEEN

           UNBOUNDED PRECEDING AND CURRENT ROW) avg_prest

  FROM vRECEBER

 WHERE dtvenc > sysdate;

Com isso finalizamos esta parte do estudo sobre as funções analíticas do Oracle. O próximo assunto deve continuar explorando recursos utilizados em Data Warehouse.

Até lá!