Neste primeiro post, de uma série, falarei sobre as funções analíticas do Oracle, que podem ser úteis no dia-a-dia tanto de DBAs quando de desenvolvedores.

É importante mencionar que os exemplos abaixo são bem simples, apenas com o objetivo de mostrar o que pode ser feito e como fazer, usando funções analíticas

1) Conceitos iniciais

Funções analíticas computam um valor agregado baseado em um número de linhas. Funções agregadas retornam uma linha para cada grupo, já funções analíticas retornam multiplas linhas para cada grupo, ou seja, funções analíticas não agrupam resultados, ao contrário das funções agregadas.

A grande maioria das funções analíticas, também podem ser usadas como funções agregadas, e vice-versa.

Funções analíticas podem aparecer somente na lista do SELECT ou no ORDER BY

2) Até a versão 11.2 do Oracle, este dispunha das seguintes funções analíticas:

  • AVG
  • CORR
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST
  • LAST
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LISTAGG
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • RATIO_TO_REPORT
  • REGR_ (Linear Regression) Functions
  • ROW_NUMBER
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

Vamos seguir a ordem listada acima, falando primeiro das seguintes funções:

  • AVG
  • CORR
  • COUNT
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST
  • LAST
  • FIRST_VALUE
  • LAG

2.1) AVG

Retorna a média de valores de determinada coluna. Recebe um valor numérico, ou não-numérico, mas que possa ser implicitamente convertido. A cláusula OVER indica que está se usando a função de forma analítica.

 --exemplo de uso do avg como função agregada
 --mostra a média salarial, por departamento
 SELECT ID_DEPARTAMENTO,
 AVG(SALARIO) AS MEDIA_SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

 --exemplo de uso do avg como função analítica
 --mostra a media salarial, por idade do empregado
 SELECT ID_EMP,
 NOME_EMP,
 IDADE,
 SALARIO,
 AVG(SALARIO) OVER (PARTITION BY IDADE ORDER BY ID_EMP) MEDIA_SAL_POR_IDADE
 FROM CERTIFICACAO.TAB_EMPREGADOS

2.2) CORR

Mostra o coeficiênte de correlação de Pearson, medindo o grau de correlação entre 2 valores numéricos, ou não-numéricos, mas que possam ser implicitamente convertidos. Caso o coeficiente seja 1, há uma correlação perfeita positiva entre esses 2 valores, caso -1, há uma correlação perfeita negativa entre esses 2 valores, e caso 0, indica que as 2 variáveis não dependem linearmente uma da outra. Caso o resultado se aproxime de 1, ou -1, isso indica que o 1º valor possui certa correlação com o 2º valor, o que indica que o 2º valor influencia o 1º. Quanto mais perto de 1, ou -1, mais forte é essa correlação. A cláusula OVER indica que está se usando a função de forma analítica.

 --exemplo de uso do corr como funcao agregada
 --mostra se quanto mais tempo de empresa os funcionarios de cada 
 --departamento tem, mais o funcionario recebe, agrupando 
 --por departamento
 SELECT ID_DEPARTAMENTO,
 CORR(SYSDATE - DATA_ADMISSAO, SALARIO) AS CORRELACAO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO
 ORDER BY ID_DEPARTAMENTO

 --exemplo de uso do corr como funcao analitica
 --mostra se quanto mais tempo de empresa os funcionarios de cada 
 --departamento tem, mais o funcionario recebe, por empregado
 SELECT ID_EMP,
 TO_CHAR((SYSDATE - DATA_ADMISSAO) YEAR TO MONTH) TEMPO_DE_EMPRESA,
 CORR(SYSDATE - DATA_ADMISSAO, SALARIO) OVER (PARTITION BY ID_DEPARTAMENTO) AS CORRELACAO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_EMP

2.3) COUNT

Exibe o número de registros retornados por uma query.

 --exemplo de uso do count como função agregada
 --mostra o numero total de empregados, agrupados por departamento
 SELECT ID_departamento,
 COUNT(*) NUM_EMP_POR_DEPT
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO
 ORDER BY ID_DEPARTAMENTO

 --exemplo de uso do count como funcao analitica
 --mostra o empregado, seu departamento e a quantidade de 
 --empregados por departamento, para cada empregado
 SELECT ID_EMP,
 ID_DEPARTAMENTO,
 COUNT(*) OVER (PARTITION BY ID_departamento) AS TOTAL_DEPARTAMENTO
 FROM CERTIFICACAO.TAB_EMPREGADOS

 --exemplo de uso do count como funcao analitica
 --mostra informacoes sobre empregados, bem como a quantidade de 
 --empregados que recebem R$2000,00 a menos e R$10000,00 a mais, 
 --do que determinado empregado
 SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 COUNT(*) OVER (ORDER BY SALARIO RANGE BETWEEN 2000 PRECEDING AND 10000 FOLLOWING)
 FROM CERTIFICACAO.TAB_EMPREGADOS

2.4) COVAR_POP e COVAR_SAMP

Calculam a covariância da população e a covariância amostral, respectivamente, de 2 conjuntos de pares numéricos. Essas funções servem para verificar a relação entre 2 variáveis. Uma covariância positiva indica que as variáveis possuem uma relação direta, e uma covariância negativa indica que as variáveis possuem uma relação inversa.

 --exemplo de uso do covar_pop e covar_samp como funcoes agregadas
 SELECT ID_DEPARTAMENTO,
 TRUNC(COVAR_POP(SYSDATE - DATA_ADMISSAO, SALARIO), 2) AS COVAR_POP,
 TRUNC(COVAR_SAMP(SYSDATE - DATA_ADMISSAO, SALARIO), 2) AS COVAR_SAMP
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

 --exemplo de uso do covar_pop e covar_samp como funcoes analitica
 SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 TRUNC(COVAR_POP(SYSDATE - DATA_ADMISSAO, SALARIO) OVER (ORDER BY SALARIO), 2) AS COV_POPULACIONAL,
 TRUNC(COVAR_SAMP(SYSDATE - DATA_ADMISSAO, SALARIO) OVER (ORDER BY SALARIO), 2) AS COV_AMOSTRA
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_EMP

2.5) CUME_DIST

Calcula a distribuição cumulativa de um valor, dentro de um grupo de valores, ou seja, calcula a posição relativa de um valor específico, dentro de um grupo de valores.

 --exemplo de uso do cume_dist como funcao agregada
 --calcula a distribuicao cumulativa de determinado 
 --funcionario entre todos os funcionarios d2 departamento 2
 SELECT CUME_DIST(2000, 2) WITHIN GROUP
 (ORDER BY SALARIO, ID_dEPARTAMENTO) AS CUME_DIST
 FROM CERTIFICACAO.TAB_EMPREGADOS

 --exemplo de uso do cume_dist como funcao analitica
 --mostra quem tem os menores e maiores salarios, por departamento
 --no caso abaixo, quanto mais proximo de 1, menor e o 
 --salario por departamento
 SELECT ID_EMP,
 ID_DEPARTAMENTO,
 NOME_EMP,
 SALARIO,
 TRUNC(CUME_DIST() OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC), 2)
 FROM CERTIFICACAO.TAB_EMPREGADOS

2.6) DENSE_RANK

Calcula o rank de uma linha dentro de um grupo de linhas, ou seja, retorna a posição (rank) dessa linha dentro do grupo de linhas especificado pela query

 --exemplo de uso do dense_rank como funcao agregada
 --dado um valor, a query retorna a posicao (rank) 
 --desse valor, dentro da tabela
 SELECT DENSE_RANK(10000) WITHIN GROUP (ORDER BY SALARIO DESC) POSICAO
 FROM CERTIFICACAO.TAB_EMPREGADOS

 --exemplo de uso do dense_rank como funcao analitica
 --mostra o rank de salario de cada funcionario, por departamento
 SELECT ID_EMP,
 NOME_EMP,
 ID_DEPARTAMENTO,
 SALARIO,
 DENSE_RANK() OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC) AS RANK
 FROM CERTIFICACAO.TAB_EMPREGADOS

2.7) FIRST e LAST

Retornam o primeiro e o último valores de uma sequência ordenada.

 --exemplo de uso do first e last como funcoes agregadas
 --lista, por departamento, o funcionario mais velho que tem o 
 --pior salario e o funcionario mais novo que tem o melhor salario
 SELECT ID_DEPARTAMENTO,
 MIN(SALARIO) KEEP (DENSE_RANK LAST ORDER BY IDADE) AS PIOR_SALARIO_EMP_MAIS_VELHO,
 MAX(SALARIO) KEEP (DENSE_RANK FIRST ORDER BY IDADE) AS MELHOR_SALARIO_EMP_MAIS_NOVO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO
 ORDER BY ID_DEPARTAMENTO

 --exemplo de uso do first e last como funcoes analiticas
 --lista, por funcionario, o funcionario mais velho que tem 
 --o pior salario e o mais novo que tem o melhor salario
 SELECT ID_EMP, NOME_EMP, IDADE, SALARIO,
 MIN(SALARIO) KEEP (DENSE_RANK LAST ORDER BY IDADE) 
   OVER (PARTITION BY ID_DEPARTAMENTO) AS PIOR_SALARIO_EMP_MAIS_VELHO,
 MAX(SALARIO) KEEP (DENSE_RANK FIRST ORDER BY IDADE) 
   OVER (PARTITION BY ID_DEPARTAMENTO) AS MELHOR_SALARIO_EMP_MAIS_NOVO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_EMP, NOME_EMP, IDADE, SALARIO

2.8) FIRST_VALUE

Funciona como função analíca apenas. Retorna o primeiro valor em uma sequencia ordenada de valores.

 --exemplo de uso do first_value como funcao analitica
 --exibe o nome do empregado que recebe o menor salario, por 
 --departamento
 SELECT id_emp,
 nome_emp,
 salario,
 FIRST_VALUE(NOME_EMP) OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO) AS MENOR_SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS

 --exemplo de uso do first_value como funcao analitica
 --exibe o nome do empregado que recebe o menor salario, 
 --por departamento.
 --perceba que, para mudar a exibicao do menor salario, para o 
 --maior salario, basta colocar o desc no final do partition by
 SELECT id_emp,
 nome_emp,
 salario,
 FIRST_VALUE(NOME_EMP) OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC) AS MENOR_SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS

2.9) LAG

Funciona como função analítica apenas. Permite acessar informações de uma linha anterior à posição atual.

 --exemplo de uso do lag como funcao analitica
 --exibe a diferenca entre o salario dos empregados, ordenado 
 --pela idade, ou seja, dado um funcionario, calcula a diferenca 
 --entre esse funcionario, e primeiro mais novo do que ele
 SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 IDADE,
 LAG(SALARIO, 1, 0) OVER (ORDER BY IDADE) AS SALARIO_ANTERIOR,
 SALARIO - LAG(SALARIO, 1, 0) OVER (ORDER BY IDADE) AS DIFERENCA
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY IDADE

OBS1: O segundo parâmetro da cláusula LAG indica N linhas anteriores, ou seja, no exemplo acima, eu especifico 1, que significa a diferença entre o registro da linha atual e o registro n – 1. Caso eu coloque 2, a query retornará a diferença entre o registro atual e o registro n – 2, e assim por diante.

Até o próximo post.