O que são Funções Analíticas?

 

No Oracle, funções analíticas são utilizadas para realizar cálculos agregados em um conjunto de linhas, retornando múltiplas linhas para cada grupo. Elas são extremamente úteis para análises complexas e relatórios detalhados, permitindo cálculos avançados diretamente no SQL.

Vantagens de Usar Funções Analíticas

  • Cálculos Avançados: Permite realizar cálculos complexos, como médias móveis e percentuais acumulados, diretamente no SQL.
  • Eficiência e Precisão: Executa operações analíticas de forma eficiente e precisa, aproveitando a otimização do Oracle.
  • Flexibilidade: Suporta uma ampla variedade de funções e operações analíticas.

Principais Funções Analíticas

ROW_NUMBER

A função ROW_NUMBER atribui um número único sequencial a cada linha em uma partição de um conjunto de resultados. Veja um exemplo:

 

SELECT nome,
       descricao_cargo,
       salario,
       ROW_NUMBER() OVER(PARTITION BY descricao_cargo ORDER BY salario DESC) AS rank
  FROM vw_funcionarios;

Essa consulta retorna o número de cada funcionário dentro do seu cargo, ordenando pelo salário de forma decrescente.

 

NOMEDESCRICAO_CARGOSALARIORANK
Mariana CostaAdministrador de Banco de Dados7000,0001
Carlos SilvaAnalista de Sistemas7800,0001
Lucas OliveiraDesenvolvedor4500,0001
Fernanda SantosDesenvolvedor3000,0002
Ana SouzaDesenvolvedor2500,0003
Roberto AlvesEngenheiro de Software3000,0001
Camila PereiraGerente de Projetos1950,0001

RANK

A função RANK atribui um número de classificação a cada linha em uma partição de um conjunto de resultados, permitindo empates. Veja um exemplo:

 

SELECT nome,
       descricao_cargo,
       salario,
       RANK() OVER(PARTITION BY descricao_cargo ORDER BY salario DESC) AS rank
  FROM vw_funcionarios;

Essa consulta retorna a classificação de cada funcionário dentro do seu cargo, ordenando pelo salário de forma decrescente. Funcionários com salários iguais recebem a mesma classificação.

 

NOMEDESCRICAO_CARGOSALARIORANK
Mariana CostaAdministrador de Banco de Dados7000,0001
Carlos SilvaAnalista de Sistemas7800,0001
Lucas OliveiraDesenvolvedor4500,0001
Fernanda SantosDesenvolvedor3000,0002
Ana SouzaDesenvolvedor2500,0003
Roberto AlvesEngenheiro de Software3000,0001
Camila PereiraGerente de Projetos1950,0001

DENSE_RANK

A função DENSE_RANK é similar à RANK, mas não pula números de classificação em caso de empates. Veja um exemplo:

 

SELECT nome,
       descricao_cargo,
       salario,
       DENSE_RANK() OVER(PARTITION BY descricao_cargo ORDER BY salario DESC) AS dense_rank
  FROM vw_funcionarios;

Essa consulta retorna a classificação de cada funcionário dentro do seu cargo, ordenando pelo salário de forma decrescente. Funcionários com salários iguais recebem a mesma classificação, sem pular números.

 

NOMEDESCRICAO_CARGOSALARIODENSE_RANK
Mariana CostaAdministrador de Banco de Dados7000,0001
Carlos SilvaAnalista de Sistemas7800,0001
Lucas OliveiraDesenvolvedor4500,0001
Fernanda SantosDesenvolvedor3000,0002
Ana SouzaDesenvolvedor2500,0003
Roberto AlvesEngenheiro de Software3000,0001
Camila PereiraGerente de Projetos1950,0001

LAG e LEAD

As funções LAG e LEAD permitem acessar dados de linhas anteriores ou posteriores, respectivamente, sem a necessidade de um self join. Veja um exemplo:

 

SELECT nome,
       salario,
       LAG(salario, 1, 0) OVER(ORDER BY salario) AS salario_anterior,
       LEAD(salario, 1, 0) OVER(ORDER BY salario) AS salario_proximo
  FROM vw_funcionarios;

Essa consulta retorna o salário anterior e o próximo salário em relação ao salário atual para cada funcionário.

 

NOMESALARIOSALARIO_ANTERIORSALARIO_PROXIMO
Camila Pereira1950,00002500
Ana Souza2500,00019503000
Roberto Alves3000,00025003000
Fernanda Santos3000,00030004500
Lucas Oliveira4500,00030007000
Mariana Costa7000,00045007800
Carlos Silva7800,00070000

NTILE

A função NTILE distribui as linhas de um conjunto de resultados em um número especificado de grupos. Veja um exemplo:

 

SELECT nome, salario, NTILE(4) OVER(ORDER BY salario DESC) AS quartil
  FROM vw_funcionarios;

Essa consulta divide os funcionários em quatro grupos, ou quartis, baseando-se no salário de forma decrescente.

 

NOMESALARIOQUARTIL
Carlos Silva7800,0001
Mariana Costa7000,0001
Lucas Oliveira4500,0002
Roberto Alves3000,0002
Fernanda Santos3000,0003
Ana Souza2500,0003
Camila Pereira1950,0004

CUME_DIST

A função CUME_DIST calcula a distribuição cumulativa de um valor em um conjunto de valores. Veja um exemplo:

 

SELECT nome, salario, CUME_DIST() OVER(ORDER BY salario DESC) AS dist_cum
  FROM vw_funcionarios;

Essa consulta retorna a distribuição cumulativa do salário de cada funcionário em relação ao conjunto de salários.

 

NOMESALARIODIST_CUM
Carlos Silva7800,0000,142857142857143
Mariana Costa7000,0000,285714285714286
Lucas Oliveira4500,0000,428571428571429
Roberto Alves3000,0000,714285714285714
Fernanda Santos3000,0000,714285714285714
Ana Souza2500,0000,857142857142857
Camila Pereira1950,0001

PERCENT_RANK

A função PERCENT_RANK calcula o percentual de classificação de um valor em um conjunto de valores. Veja um exemplo:

 

SELECT nome,
       salario,
       PERCENT_RANK() OVER(ORDER BY salario DESC) AS perc_rank
  FROM vw_funcionarios;

Essa consulta retorna o percentual de classificação do salário de cada funcionário em relação ao conjunto de salários.

 

NOMESALARIOPERC_RANK
Carlos Silva7800,0000
Mariana Costa7000,0000,166666666666667
Lucas Oliveira4500,0000,333333333333333
Roberto Alves3000,0000,5
Fernanda Santos3000,0000,5
Ana Souza2500,0000,833333333333333
Camila Pereira1950,0001

FIRST_VALUE e LAST_VALUE

As funções FIRST_VALUE e LAST_VALUE retornam o primeiro e o último valor em uma partição de um conjunto de resultados, respectivamente. Veja um exemplo:

 

SELECT nome,
       salario,
       FIRST_VALUE(salario) OVER(ORDER BY salario DESC) AS primeiro_salario,
       LAST_VALUE(salario) OVER(ORDER BY salario DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ultimo_salario
  FROM vw_funcionarios;

Essa consulta retorna o primeiro e o último salário em relação ao salário atual para cada funcionário.

 

NOMESALARIOPRIMEIRO_SALARIOULTIMO_SALARIO
Carlos Silva7800,00078001950
Mariana Costa7000,00078001950
Lucas Oliveira4500,00078001950
Roberto Alves3000,00078001950
Fernanda Santos3000,00078001950
Ana Souza2500,00078001950
Camila Pereira1950,00078001950

NTH_VALUE

A função NTH_VALUE retorna o enésimo valor em um conjunto de valores. Veja um exemplo:

 

SELECT nome,
       salario,
       NTH_VALUE(salario, 3) OVER(ORDER BY salario DESC) AS terceiro_salario
  FROM vw_funcionarios;

Essa consulta retorna o terceiro salário em relação ao salário atual para cada funcionário.

 

NOMESALARIOTERCEIRO_SALARIO
Carlos Silva7800,000
Mariana Costa7000,000
Lucas Oliveira4500,0004500
Roberto Alves3000,0004500
Fernanda Santos3000,0004500
Ana Souza2500,0004500
Camila Pereira1950,0004500

Considerações Finais

 

As funções analíticas no Oracle são ferramentas poderosas para realizar análises avançadas e cálculos complexos diretamente no SQL. Elas são essenciais para uma ampla gama de aplicações, desde relatórios simples até análises de dados complexas.

 

Se você tiver alguma dúvida ou sugestão, deixe um comentário abaixo!

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Rolar para cima