Funções Analíticas em Oracle – DENSE_RANK e LAG/LEAD

 

Vamos explorar algumas novas funções de grupo que podem ser utilizadas em conjunto com as funções analíticas em Oracle.

 

DENSE_RANK

Esta função de grupo permite que se extraia a posição (ranking) de valores de uma lista em função dos demais. Para isto basta informar a ordem através do qual o valor está ordenado. Esta situação é muito comum para, por exemplo, determinar o raniking das maiores prestações que iremos receber em um determinado mês. Veja o exemplo:

 

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

       DENSE_RANK() OVER

        (PARTITION BY to_char(dtvenc,'YYYYMM')

         ORDER BY vlprest ) dense_rank

  FROM vRECEBER

 WHERE dtvenc > sysdate;

DTVENC

CDCONTR

VLPREST

DENSE_RANK

01-2007

56

695,09

1

01-2007

12

854,1

2

01-2007

62

3272,1

3

01-2007

33

11452,35

4

02-2007

74

50

1

02-2007

53

400

2

02-2007

56

695,09

3

02-2007

12

854,1

4

02-2007

76

1600

5

02-2007

5

2000

6

02-2007

125

2820,43

7

02-2007

62

3272,1

8

02-2007

7

3606,24

9

02-2007

124

4607,33

10

02-2007

33

11452,35

11

03-2007

74

50

1

03-2007

53

400

2

03-2007

56

695,09

3

03-2007

12

854,1

4

03-2007

76

1600

5

03-2007

5

2000

6

03-2007

125

2820,43

7

03-2007

62

3272,1

8

03-2007

7

3606,24

9

 

Note que a cada mês é criado um novo ranking (especificado no PARTITION) e é estabelecido a posição com base no valor das prestações (ORDER BY).

 

LAG

Esta função analítica é utilizada para se realizar uma busca dentro da mesma tabela sem que haja necessidade de realizar um self-join. A busca retornará uma linha que esteja anterior à posição atual do cursor (da busca). Você pode especificar quantas linhas atrás deverá ser retornada e, caso não haja, qual valor deve ser mostrado. Normalmente este comando é utilizado para verificar e comparar um valor com um valor anterior. No exemplo, vamos comparar o valor da prestação do cliente com o valor imediatamente anterior. Caso não haja valor anterior (na partição) então será retornado zero.

 

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

       LAG( vlprest,1,0 ) OVER

        ( PARTITION BY to_char( dtvenc, 'MM-YYYY')

          ORDER BY vlprest ) lag

  FROM vRECEBER

 WHERE dtvenc > sysdate;

 

DTVENC

CDCONTR

VLPREST

LAG

01-2007

56

695,09

0

01-2007

12

854,1

695,09

01-2007

62

3272,1

854,1

01-2007

33

11452,35

3272,1

01-2008

74

50

0

01-2008

53

400

50

01-2008

56

695,09

400

01-2008

12

854,1

695,09

01-2008

76

1600

854,1

01-2008

5

2000

1600

01-2008

125

2820,43

2000

01-2008

62

3272,1

2820,43

01-2008

7

3606,24

3272,1

01-2008

124

4607,33

3606,24

01-2008

33

11452,35

4607,33

01-2009

74

50

0

01-2009

53

400

50

01-2009

56

695,09

400

01-2009

12

854,1

695,09

01-2009

76

1600

854,1

01-2009

5

2000

1600

01-2009

62

3272,1

2000

01-2009

7

3606,24

3272,1

01-2009

124

4607,33

3606,24

 

Note que a cada novo valor particionado, o valor de comparação retorna ao zero.

 

LEAD

Atua como o LAG, mas fará a busca nas linhas seguintes. Veja o exemplo:

 

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

       LEAD( vlprest,1,0 ) OVER

        ( PARTITION BY to_char( dtvenc, 'MM-YYYY')

          ORDER BY vlprest ) lag

  FROM vRECEBER

 WHERE dtvenc > sysdate;

DTVENC

CDCONTR

VLPREST

LAG

01-2007

56

695,09

854,1

01-2007

12

854,1

3272,1

01-2007

62

3272,1

11452,35

01-2007

33

11452,35

0

01-2008

74

50

400

01-2008

53

400

695,09

01-2008

56

695,09

854,1

01-2008

12

854,1

1600

01-2008

76

1600

2000

01-2008

5

2000

2820,43

01-2008

125

2820,43

3272,1

01-2008

62

3272,1

3606,24

01-2008

7

3606,24

4607,33

01-2008

124

4607,33

11452,35

01-2008

33

11452,35

0

01-2009

74

50

400

01-2009

53

400

695,09

01-2009

56

695,09

854,1

01-2009

12

854,1

1600

01-2009

76

1600

2000

01-2009

5

2000

3272,1

01-2009

62

3272,1

3606,24

01-2009

7

3606,24

4607,33

01-2009

124

4607,33

11452,35

 

Há diversas funções de grupo e analíticas que podem ser utilizadas. Como todas têm o mesmo funcionamento, sugiro que você busque as alternativas diretamente na documentação da Oracle. Lembre-se: os bancos de dados, em função das necessidades de extração de dados gerenciais, dispõem de diversas funções, sejam elas estatísticas, financeiras ou de posição.

 

No próximo artigo, vamos continuar no tema de funções analíticas, mas com a abordagem do windowing.

 

Até lá!