Funções analíticas

Funções analíticas

1. GROUP BY

   • select column from table (where condition // group by /order by column) 
   • http://aserlorenzo.com/manSQL/Oracle/dml/funcoes/rollup.htm
   • Usando funções de grupo conseguimos determinar a soma de salários por função (JOB) mas, usando um único query, não conseguimos determinar também a soma total. Adicionando a cláusula ROLLUP ao GROUP BY conseguimos obter essa soma
   • * As function de grupo operam em grupo, portanto indicadas no group by
   • select column from table (where condition // group by /order by column)
   • ROLLUP
     • select deptno, sum(sal) as soma from emp group by rollup (deptno);
     • produz um conjunto de resultados com as linhas agrupadas normais e os valores dos subtotais. gera subtotais e valores cumulativos.
   • CUBE
     • elect deptno, job, sum(sal) as soma from emp group by cube (deptno, job);
     • produz resultados do rollup + valores da tabelas de referencia cruzada. permitindo todas as agregações/ combinações possiveis
     • GROUPING
       • é usada para: localizar os grupos que formam o subtotal em uma linha / e diferencia valores null armazenados de valores null criados por rollup ou cube
         • * retorna 0 ou 1
       • GROUPING SETS
         • group by grouping sets + desempenho
         • na mesma consulta vc consegue definir vários agrupamentos
           • todos agrupamentos são computados e os individuais são combinado na inst. union all
             • group by grouping sets
   • para combinar expressões de várias formas: colunas compostas, conjuntos de agrupamentos concatenados
   • Colunas compostas
     • tratadas com uma unidade. Deve vir entre (b,c) ex: group by rollup ( a,(b,c),d);
     • Agrupamentos concatenados
       • um conjunto de agrupamentos concatenados

2. OVER

   • SELECT empno, deptno, sal, AVG(sal) OVER () AS avg_sal FROM emp;
   • over ( partition by column )
     • Separa o resultado em partições ou grupos de dados
       • SELECT empno, deptno, sal, AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal FROM emp; 

3. LAG

   • select ename, hiredate, sal, lag (sal, 1, o ou sysdate) over ( order by hiredate) as prevsal from emp where job = ' CLERK';
   • LEAD
     • 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; 
     • Permite comparar uma linha com a seguinte ou com duas à frente
   • Retornar o valor da linha anterior
   • First_Value e Last_Value: Seleciona a primeira linha e a última linha de um grupo
     • asc ou desc

4. corr = correlação

   • cume_dist = mede-se a distancia
     • valor min = 0 valor max = 1
     • 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
     • percent_rank: mede-se a distanciia
       •  é semelhante a CUME_DIST( ) mas o valor da primeira linha é zero enquanto que o da última é um.
       • RATIO_TO_REPORT
         • permite determinar a percentagem que o valor de uma linha representa na soma dos valores do grupo
         • rank: ranqueamento
           • classifica as linhas do query de acordo com a expressã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.

Bilder und Dateien der Mindmap