SQL em Oracle > DML > Funções > Funções analíticas
Funções analíticas logotipo.gif

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.


ROW_NUMBER( )

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

bthome.gifTopo


RANK( )

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

bthome.gifTopo


DENSE_RANK( )

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

bthome.gifTopo


CUME_DIST( )

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

bthome.gifTopo


PERCENT_RANK( )

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

bthome.gifTopo


RATIO_TO_REPORT( )

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

bthome.gifTopo


NTILE( )

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

bthome.gifTopo


LEAD( )

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

bthome.gifTopo


LAG( )

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

bthome.gifTopo


FIRST_VALUE( )

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

bthome.gifTopo


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

bthome.gifTopo


Realizado por Jose Aser Lorenzo. Última alteração em 2011-02-26