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.

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.

2.3) COUNT

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

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.

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.

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

2.7) FIRST E LAST

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

2.8) FIRST_VALUE

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

2.9) LAG

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

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.