Dando continuidade a série de posts relacionados às funções analíticas do Oracle, falarei neste post sobre mais algumas delas. As funções abordadas serão:

  • LAST_VALUE
  • LEAD
  • LISTAGG
  • MAX
  • MIN
  • NTILE

1) LAST_VALUE

Funciona como função analítica. Ela serve para retornar o último valor de uma série ordenada de valores.

 --exemplo de uso do last_value como função analitica
 --mostra o empregado com o maior salario e com o menor salario,
 --por departamento, para cada linha retornada 

 SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 DATA_ADMISSAO,
 ID_DEPARTAMENTO,
 LAST_VALUE(NOME_EMP) 
 OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO 
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMP_MAIOR_SAL,
 LAST_VALUE(NOME_EMP) 
 OVER (PARTITION BY ID_DEPARTAMENTO ORDER BY SALARIO DESC 
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMP_MENOR_SAL
 FROM CERTIFICACAO.TAB_EMPREGADOS
 ORDER BY ID_DEPARTAMENTO, SALARIO

2) LEAD

Funciona como uma função analítica. Permite acessar mais de uma linha da mesma tabela sem a necessidade de um SELF-JOIN.

 --exemplo de uso do lead como função analitica
 --mostra, para cada empregado, o empregado contratado logo 
 --após ele, por departamento

 SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 DATA_ADMISSAO,
 ID_DEPARTAMENTO,
 NVL(LEAD(NOME_EMP) OVER (PARTITION BY ID_DEPARTAMENTO 
   ORDER BY DATA_ADMISSAO), 'ULTIMO CONTRATADO DO SETOR') AS PROXIMO_CONTRATADO
 FROM CERTIFICACAO.TAB_EMPREGADOS

3) LISTAGG

Ordena os dados em cada grupo, especificado pelo ORDER BY, e posteriormente concatena esses dados. Serve para transformar uma coluna em linhas. É possivel usado tanto como função analítica, quanto como função agregada.

 --exemplo de uso do listagg como função agregada
 --mostra, por departamento, os empregados em ordem salarial, 
 --mas concatenados em uma linha por departamento
 --o segundo parametro da funcao LISTADD define o separador 
 --de campos

 SELECT ID_DEPARTAMENTO,
 LISTAGG(NOME_EMP, ' - ') WITHIN GROUP (ORDER BY SALARIO) AS EMPREGADOS
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO
 ORDER BY ID_DEPARTAMENTO

 --exemplo de uso do listagg como função analitica
 --mostra, para cada empregado, os empregados que recebem a mesma faixa salarial, entre R$0,00 e R$2000,00

 SELECT ID_EMP,
 ID_DEPARTAMENTO,
 NOME_EMP, 
 SALARIO,
 LISTAGG(NOME_EMP, ' - ') WITHIN GROUP (ORDER BY SALARIO) 
 OVER (PARTITION BY ID_DEPARTAMENTO) AS LISTA
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE SALARIO BETWEEN 0 AND 2000

4) MAX

Retorma o maior valor de determinada coluna. É possível ser usado tanto como função analítica, quanto como função agregada.

 --exemplo de uso do max como função agregada
 --exibe o maior salario, por departamento

 SELECT ID_DEPARTAMENTO,
 MAX(SALARIO) AS MAIOR_SAL
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

 --exemplo de uso do max como função analitica
 --mostra, por empregado, o salario empregado que recebe o 
 --maior salario, por faixa de comissao.
 --por exemplo, para os funcionarios que recebem comissao de 20%, 
 --mostra o maior salario entre eles

 SELECT ID_EMP,
 COMISSAO,
 NOME_EMP,
 SALARIO,
 ID_DEPARTAMENTO,
 MAX(SALARIO) OVER (PARTITION BY COMISSAO) AS MAX_SAL_POR_COMISSAO
 FROM CERTIFICACAO.TAB_EMPREGADOS

5) MIN

Retorna o menor valor de determinada coluna. É possível ser usado tanto como função analítica, quanto como função agregada.

 --exemplo de uso do min como função agregada
 --exibe o menor salario, por departamento

 SELECT ID_DEPARTAMENTO,
 MIN(SALARIO)
 FROM CERTIFICACAO.TAB_EMPREGADOS
 GROUP BY ID_DEPARTAMENTO

 --exemplo de uso do min como função analitica
 --mostra, por empregado, o salario do empregado que recebe 
 --o menor salario, por faixa de comissao.
 --por exemplo, para os funcionarios que recebem comissao de 30%, 
 --mostra o menor salario entre eles

 SELECT ID_EMP,
 COMISSAO,
 NOME_EMP,
 SALARIO,
 ID_DEPARTAMENTO,
 MIN(SALARIO) OVER (PARTITION BY COMISSAO) AS MIN_SAL_POR_COMISSAO
 FROM CERTIFICACAO.TAB_EMPREGADOS

6) NTILE

Função analítica usada para dividir um conjunto ordenado de valores em N partes iguais, e cada parte representa 1/N da população.

 --exemplo de uso do NTILE como função analitica
 --divide o conjunto de dados em 4 partes iguais (quartil)
 --SELECT ID_EMP,
 NOME_EMP,
 SALARIO,
 NTILE(4) OVER (ORDER BY SALARIO DESC) QUARTIL
 FROM CERTIFICACAO.TAB_EMPREGADOS

Até a próxima parte!

Para conferir o primeiro post da série Funções Analíticas, abaixo:

Funções Analíticas – Parte 1