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;

 

DTVENC

CDCONTR

VLPREST

AVG_PREST

01-2007

12

854,1

854,1

01-2007

33

11452,35

6153,225

01-2007

56

695,09

4333,84667

01-2007

62

3272,1

4068,41

02-2007

7

3606,24

3606,24

02-2007

76

1600

2603,12

02-2007

56

695,09

1967,11

02-2007

74

50

1487,8325

02-2007

5

2000

1590,266

02-2007

33

11452,35

3233,94667

02-2007

53

400

2829,09714

02-2007

62

3272,1

2884,4725

02-2007

12

854,1

2658,87556

02-2007

124

4607,33

2853,721

02-2007

125

2820,43

2850,69455

 

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;

 

DTVENC

CDCONTR

VLPREST

AVG_PREST

01-2007

12

854,1

6153,225

01-2007

33

11452,35

4333,84667

01-2007

56

695,09

5139,84667

01-2007

62

3272,1

1983,595

02-2007

7

3606,24

2603,12

02-2007

76

1600

1967,11

02-2007

56

695,09

781,696667

02-2007

74

50

915,03

02-2007

5

2000

4500,78333

02-2007

33

11452,35

4617,45

02-2007

53

400

5041,48333

02-2007

62

3272,1

1508,73333

02-2007

12

854,1

2911,17667

02-2007

124

4607,33

2760,62

02-2007

125

2820,43

3713,88

 

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;

 

DTVENC

CDCONTR

VLPREST

AVG_PREST

01-2007

12

854,1

854,1

01-2007

33

11452,35

6153,225

01-2007

56

695,09

4333,84667

01-2007

62

3272,1

4068,41

02-2007

5

2000

2000

02-2007

7

3606,24

2803,12

02-2007

12

854,1

2153,44667

02-2007

33

11452,35

4478,1725

02-2007

53

400

3662,538

02-2007

56

695,09

3167,96333

02-2007

62

3272,1

3182,84

02-2007

74

50

2791,235

02-2007

76

1600

2658,87556

02-2007

124

4607,33

2853,721

02-2007

125

2820,43

2850,69455

 

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á!