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