Analytic Functions: O que são e como utilizá-las no Oracle Database

Analytic Functions

Funções analíticas calculam um valor agregado com base em um grupo de linhas, elas diferenciam funções de agregação e podem retornar várias linhas para cada grupo. Para cada linha que retornar na consulta, a função analítica irá buscar um valor agregado do grupo e mostrar na consulta.

As funções analíticas podem ser de 0 à 3 argumentos, que por sua vez podem ser qualquer tipo de dados NUMERIC ou qualquer tipo de dados não NUMERIC que pode ser convertido para um tipo de dado NUMERIC. Na documentação da Oracle, o argumento determinado é com a maior precedência numérica e a conversão implícita dos argumentos restante para esse tipo de dados. O tipo de retorno é também esse tipo de dados (NUMERIC).

As restrições quanto as funções analíticas são: que não pode ser especificado qualquer função analítica em qualquer parte da clausula analítica, ou seja, você não pode aninhar funções analíticas. Também não pode especificar uma função analítica com funções definidas pelo usuário. (LORENTZ, 2005)

[ANALYTIC_FUNCTION] (ARGUMENTS) OVER (ANALYTIC_CLAUSE)

As ANALYTICS FUNCTIONS são muito uteis em casos em que o desenvolvedor deseja efetuar uma busca do resultado de uma soma, por exemplo, e lista-la de forma analítica, ou seja, mostrando todos os registros (agrupando os repetidos) de uma função de agregação.

Estas funções são normalmente utilizadas em ambientes para Data Warehousing, para isso utiliza-se a clausula do OVER e em seguida o tipo da análise de dados que poderá ser feita, poderá ser em modo de partição (PARTITION BY) ou então em modo de ordenação (ORDER BY), pode-se utilizar ambos o modo na mesma clausula do OVER. Porém antes da clausula do OVER deve-se utilizar alguma função de agregação, pode-se utilizar algumas das seguintes funções abaixo para escrever uma função analítica: (LORENTZ, 2005)

AVG, CORR, COUNT, COVAR_POP, COVAR_SAMP, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, LISTAGG, MAX, MEDIAN, MIN, NTH_VALUE, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, ROW_NUMBER, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

Para demonstrar do que as funções analíticas são capazes, há um exemplo que será executado na tabela EMPLOYEES do usuário HUMAN RESOURCES, padrão para os treinamentos do Oracle.

SELECT  E.MANAGER_ID, E.FIRST_NAME || ‘ ‘ ||
       E.LAST_NAMENAME_EMPLOYEE, SUM(E.SALARY) SALARY,
       SUM(E.SALARY) OVER (PARTITION BY E.MANAGER_ID ORDER BY E.SALARY) SUMGER 
FROM    EMPLOYEES E 
GROUP BY E.MANAGER_ID, E.FIRST_NAME || ‘ ‘ || E.LAST_NAME, E.SALARY

Resultado:

O exemplo a acima calcula, para cada gestor (campo MANAGER_ID) na tabela de exemplo EMPLOYEES, um total cumulativo de salários dos empregados que respondem ao gerente que são iguais ou menos do que o salário atual. Você pode ver que Raphaely e Cambrault têm o mesmo total cumulativo. Isso ocorre porque Raphaely e Cambrault têm os salários idênticos, de modo Oracle Database adiciona juntos os seus valores salariais e aplica o mesmo total acumulado a ambas as linhas.

Referências

LORENTZ, Diana. ORACLE® DATABASE: SQL REFERENCE 10g RELEASE 2 (10.2) – B14200-02. 2005.

Agradecimentos

FABIO PELICER

ALEXANDRE PIETROBELLI