| Funções analíticas | |
As funções analíticas calculam um valor para cada linha que varia com os valores das outras linhas do grupo. Diferem das funções de grupo pelas seguintes razões:
O conjunto de operações que permitem o cálculo do valor da função analítica é o último a ser efectuado na execução de um query. Primeiro são feitas as junções, filtros horizontais (WHERE), agrupamento (GROUP BY) e filtro de grupos (HAVING). Depois é feito o cálculo da função analítica e só em último lugar a ordenção (ORDER BY).
Estas funções são muito úteis para os ambientes de data wharehouse.
A função ROW_NUMBER() actua depois da extração dos dados, numerando as linhas segundo o critério indicado na expressão da função analitica. Torna possível que no mesmo comando haja diferentes critérios de ordenação.
O exemplo abaixo mostra o nome de empregado e o respectivo salário. A coluna ROWNUM representa a sequência em que as linhas são retiradas antes da ordenação. A coluna SAL_ASC representa a ordenação ascendente por SAL. A coluna SAL_DESC representa a sequência de ordenação descendente por SAL. A coluna HIREDATE_ASC representa a sequência de ordenação crescente por HIREDATE.
select ename, sal, hiredate, rownum, row_number() over (order by sal asc) as sal_asc, row_number() over (order by sal desc) as sal_desc, row_number() over (order by hiredate asc) as hiredate_asc from emp; |
ENAME SAL HIREDATE ROWNUM SAL_ASC SAL_DESC HIREDATE_ASC ---------- ---------------------- ------------------------- ---------------------- ---------------------- ---------------------- ---------------------- SMITH 800 80.12.17 1 1 14 1 JAMES 950 81.12.03 12 2 13 11 ADAMS 1100 83.01.12 11 3 12 14 WARD 1250 81.02.22 3 4 10 3 MARTIN 1250 81.09.28 5 5 11 8 MILLER 1300 82.01.23 14 6 9 12 TURNER 1500 81.09.08 10 7 8 7 ALLEN 1600 81.02.20 2 8 7 2 CLARK 2450 81.06.09 7 9 6 6 BLAKE 2850 81.05.01 6 10 5 5 JONES 2975 81.04.02 4 11 4 4 FORD 3000 81.12.03 13 12 2 10 SCOTT 3000 82.12.09 8 13 3 13 KING 5000 81.11.17 9 14 1 9 14 rows selected |
A função RANK() classifica as linhas do query de acordo com a empressão do "ORDER BY". Quando duas linhas têm a mesma classificação recebem o mesmo número. Isto provoca que o número abaixo não seja atribuído.
Na coluna RANK_SAL do exemplo abaixo é atribuída uma classificação às linhas de acordo com a ordenação crescente do valor de SAL. Repare que WARD e MARTIN têm o mesmo salário, pelo que ficam em 4º lugar. MILLER é o concorrente seguinte, que recebe o 6º. O 5º lugar não foi atribuído.
Na coluna RANK_MOD a ordenação é feita segundo o resultado da função MOD, que neste caso determina o resto da divisão entre o salário e 1000. Neste critério WARD e MILLER ficam classificados noutra posição.
select ename, sal, mod(sal,1000), rank() over (order by sal) as rank_sal, rank() over (order by mod(sal,1000)) as rank_mod from emp order by sal; |
ENAME SAL MOD(SAL,1000) RANK_SAL RANK_MOD ---------- ---------------------- ---------------------- ---------------------- ---------------------- SMITH 800 800 1 11 JAMES 950 950 2 13 ADAMS 1100 100 3 4 MARTIN 1250 250 4 5 WARD 1250 250 4 5 MILLER 1300 300 6 7 TURNER 1500 500 7 9 ALLEN 1600 600 8 10 CLARK 2450 450 9 8 BLAKE 2850 850 10 12 JONES 2975 975 11 14 FORD 3000 0 12 1 SCOTT 3000 0 12 1 KING 5000 0 14 1 14 rows selected |
A função DENSE_RANK() é semelhante a RANK() mas não deixa números em branco. No exemplo abaixo repare nas linhas WARD, MARTIN e MILLER e compare com o exemplo anterior.
select ename, sal, mod(sal,1000), dense_rank() over (order by sal) as dense_rank_sal, dense_rank() over (order by mod(sal,1000)) as dense_rank_mod from emp order by sal; |
ENAME SAL MOD(SAL,1000) DENSE_RANK_SAL DENSE_RANK_MOD ---------- ---------------------- ---------------------- ---------------------- ---------------------- SMITH 800 800 1 8 JAMES 950 950 2 10 ADAMS 1100 100 3 2 MARTIN 1250 250 4 3 WARD 1250 250 4 3 MILLER 1300 300 5 4 TURNER 1500 500 6 6 ALLEN 1600 600 7 7 CLARK 2450 450 8 5 BLAKE 2850 850 9 9 JONES 2975 975 10 11 FORD 3000 0 11 1 SCOTT 3000 0 11 1 KING 5000 0 12 1 14 rows selected |
A função CUME_DIST( ) (Cumulative Distribution) determina a posição relativa de uma linha dentro do conjunto de linhas. Considerando uma ordenação ascendente, o valor da linha "i" dentro de um total de "n" linhas será a divisão entre o número de linhas com valor menor ou igual a "i" e "n". A primeira linha tem valor maior que zero. A última tem valor menor ou igual a 1. Valores iguais têm sempre a mesma classificação.
O valor da primeira linha é 1/14. O valor da linha 5 é 5/14.
select ename, sal, cume_dist() over (order by sal) from emp; |
EENAME SAL CUME_DIST()OVER(ORDERBYSAL) ---------- ---------------------- --------------------------- SMITH 800 0,0714285714285714285714285714285714285714 JAMES 950 0,1428571428571428571428571428571428571429 ADAMS 1100 0,2142857142857142857142857142857142857143 WARD 1250 0,3571428571428571428571428571428571428571 MARTIN 1250 0,3571428571428571428571428571428571428571 MILLER 1300 0,4285714285714285714285714285714285714286 TURNER 1500 0,5 ALLEN 1600 0,5714285714285714285714285714285714285714 CLARK 2450 0,6428571428571428571428571428571428571429 BLAKE 2850 0,7142857142857142857142857142857142857143 JONES 2975 0,7857142857142857142857142857142857142857 SCOTT 3000 0,9285714285714285714285714285714285714286 FORD 3000 0,9285714285714285714285714285714285714286 KING 5000 1 14 rows selected |
A função PERCENT_RANK( ) é semelhante a CUME_DIST( ) mas o valor da primeira linha é zero enquanto que o da última é um. Para isso o valor da linha "i" é (i-1)/(n-1).
select ename, sal, percent_rank() over (order by sal) from emp; |
ENAME SAL PERCENT_RANK()OVER(ORDERBYSAL) ---------- ---------------------- ------------------------------ SMITH 800 0 JAMES 950 0,0769230769230769230769230769230769230769 ADAMS 1100 0,1538461538461538461538461538461538461538 WARD 1250 0,2307692307692307692307692307692307692308 MARTIN 1250 0,2307692307692307692307692307692307692308 MILLER 1300 0,3846153846153846153846153846153846153846 TURNER 1500 0,4615384615384615384615384615384615384615 ALLEN 1600 0,5384615384615384615384615384615384615385 CLARK 2450 0,6153846153846153846153846153846153846154 BLAKE 2850 0,6923076923076923076923076923076923076923 JONES 2975 0,7692307692307692307692307692307692307692 SCOTT 3000 0,8461538461538461538461538461538461538462 FORD 3000 0,8461538461538461538461538461538461538462 KING 5000 1 14 rows selected |
A função RATIO_TO_REPORT( ) permite determinar a percentagem que o valor de uma linha representa na soma dos valores do grupo.
No exemplo abaixo é possível ver a percentagem do salário de cada vendedor sobre o total de salários pagos aos vendedores (SALESMAN).
select ename, sal, ratio_to_report(sal) over () as sal_percent from emp where job='SALESMAN'; |
ENAME SAL SAL_PERCENT ---------- ---------------------- ---------------------- ALLEN 1600 0,2857142857142857142857142857142857142857 WARD 1250 0,2232142857142857142857142857142857142857 MARTIN 1250 0,2232142857142857142857142857142857142857 TURNER 1500 0,2678571428571428571428571428571428571429 4 rows selected |
A função NTILE( ) permite dividir as linhas do query por conjuntos. O número de conjuntos é o parâmetro da função. As linhas são ordenadas e distribuídas sequencialmente pelos conjuntos. Alguns podem ficar com mais uma linha que outros.
select ename, sal, ntile(4) over (order by sal desc) as grupo from emp; |
ENAME SAL GRUPO ---------- ---------------------- ---------------------- KING 5000 1 FORD 3000 1 SCOTT 3000 1 JONES 2975 1 BLAKE 2850 2 CLARK 2450 2 ALLEN 1600 2 TURNER 1500 2 MILLER 1300 3 WARD 1250 3 MARTIN 1250 3 ADAMS 1100 4 JAMES 950 4 SMITH 800 4 14 rows selected |
A função LEAD( ) é das mais poderosas, pois permite o acesso a duas linhas da mesma tabela sem recorrer a uma junção da tabela com ela própria. Permite comparar uma linha com a seguinte ou com duas à frente.
No exemplo abaixo podemos comparar a data de contratação de um empregado com a data em que imediatamente a seguir foi contratado outro (NEXT_1). A coluna NEXT_2 mostra a data em que foi contratado o segundo. Repare nos valores NULL no fim da tabela. Podem ser substituidos por outro valor.
select ename, hiredate, lead(hiredate,1) over (order by hiredate) as next_1, lead(hiredate,2) over (order by hiredate) as next_2 from emp; |
ENAME HIREDATE NEXT_1 NEXT_2 ---------- ------------------------- ------------------------- ------------------------- SMITH 80.12.17 81.02.20 81.02.22 ALLEN 81.02.20 81.02.22 81.04.02 WARD 81.02.22 81.04.02 81.05.01 JONES 81.04.02 81.05.01 81.06.09 BLAKE 81.05.01 81.06.09 81.09.08 CLARK 81.06.09 81.09.08 81.09.28 TURNER 81.09.08 81.09.28 81.11.17 MARTIN 81.09.28 81.11.17 81.12.03 KING 81.11.17 81.12.03 81.12.03 JAMES 81.12.03 81.12.03 82.01.23 FORD 81.12.03 82.01.23 82.12.09 MILLER 82.01.23 82.12.09 83.01.12 SCOTT 82.12.09 83.01.12 ADAMS 83.01.12 14 rows selected |
A função LAG( ) faz o mesmo que LEAD( ) mas contando para trás.
select ename, hiredate, lag(hiredate,1) over (order by hiredate) as previus_1, lag(hiredate,2) over (order by hiredate) as previus_2 from emp; |
ENAME HIREDATE PREVIUS_1 PREVIUS_2 ---------- ------------------------- ------------------------- ------------------------- SMITH 80.12.17 ALLEN 81.02.20 80.12.17 WARD 81.02.22 81.02.20 80.12.17 JONES 81.04.02 81.02.22 81.02.20 BLAKE 81.05.01 81.04.02 81.02.22 CLARK 81.06.09 81.05.01 81.04.02 TURNER 81.09.08 81.06.09 81.05.01 MARTIN 81.09.28 81.09.08 81.06.09 KING 81.11.17 81.09.28 81.09.08 JAMES 81.12.03 81.11.17 81.09.28 FORD 81.12.03 81.12.03 81.11.17 MILLER 82.01.23 81.12.03 81.12.03 SCOTT 82.12.09 82.01.23 81.12.03 ADAMS 83.01.12 82.12.09 82.01.23 14 rows selected |
A função FIRST_VALUE( ) vai buscar a coluna da linha com o valor mais pequeno dentro do conjunto de linhas, tendo em conta o critério de ordenação escolhido. Evita o uso de sub-selects.
No exemplo abaixo a coluna "low sal" tem o salário do empregado que menos ganha, enquanto "low_name" tem o nome desse empregado, isto porque a ordenação foi por salário e crescente. A coluna "high sal" tem o maior salário, enquanto "high_name" tem o nome do empregado com o maior ordenado, já que a ordenação foi decrescente por salário.
select ename, sal, first_value(ename) over (order by sal) as "low ename", first_value(sal) over (order by sal) as "low sal", first_value(ename) over (order by sal desc) as "high ename", first_value(sal) over (order by sal desc) as "high sal" from emp; |
ENAME SAL low ename low sal high ename high sal ---------- ---------------------- ---------- ---------------------- ---------- ---------------------- KING 5000 SMITH 800 KING 5000 SCOTT 3000 SMITH 800 KING 5000 FORD 3000 SMITH 800 KING 5000 JONES 2975 SMITH 800 KING 5000 BLAKE 2850 SMITH 800 KING 5000 CLARK 2450 SMITH 800 KING 5000 ALLEN 1600 SMITH 800 KING 5000 TURNER 1500 SMITH 800 KING 5000 MILLER 1300 SMITH 800 KING 5000 WARD 1250 SMITH 800 KING 5000 MARTIN 1250 SMITH 800 KING 5000 ADAMS 1100 SMITH 800 KING 5000 JAMES 950 SMITH 800 KING 5000 SMITH 800 SMITH 800 KING 5000 14 rows selected |
Funções estatísticas combinadas com cláusula OVER
As funções estatísticas podem ser combinadas com as funções analíticas.
No exemplo abaixo a coluna "SOMA_ENAME" acumula a soma de salários por ordem alfabética do nome, enquanto a coluna "SOMA_HIREDATE" acumula salários por HIREDATE.
select ename, sal, hiredate, sum(sal) over (order by ename) soma_ename, sum(sal) over (order by hiredate) soma_hiredate from emp order by ename |
ENAME SAL HIREDATE SOMA_ENAME SOMA_HIREDATE ---------- ---------------------- ------------------------- ---------------------- ---------------------- ADAMS 1100 83.01.12 1100 29025 ALLEN 1600 81.02.20 2700 2400 BLAKE 2850 81.05.01 5550 9475 CLARK 2450 81.06.09 8000 11925 FORD 3000 81.12.03 11000 23625 JAMES 950 81.12.03 11950 23625 JONES 2975 81.04.02 14925 6625 KING 5000 81.11.17 19925 19675 MARTIN 1250 81.09.28 21175 14675 MILLER 1300 82.01.23 22475 24925 SCOTT 3000 82.12.09 25475 27925 SMITH 800 80.12.17 26275 800 TURNER 1500 81.09.08 27775 13425 WARD 1250 81.02.22 29025 3650 14 rows selected |
Realizado por Turtle Learning ®. Última alteração em 2011-02-26
Topo