
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.
NOME | DESCRICAO_CARGO | SALARIO | RANK |
---|---|---|---|
Mariana Costa | Administrador de Banco de Dados | 7000,000 | 1 |
Carlos Silva | Analista de Sistemas | 7800,000 | 1 |
Lucas Oliveira | Desenvolvedor | 4500,000 | 1 |
Fernanda Santos | Desenvolvedor | 3000,000 | 2 |
Ana Souza | Desenvolvedor | 2500,000 | 3 |
Roberto Alves | Engenheiro de Software | 3000,000 | 1 |
Camila Pereira | Gerente de Projetos | 1950,000 | 1 |
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.
NOME | DESCRICAO_CARGO | SALARIO | RANK |
---|---|---|---|
Mariana Costa | Administrador de Banco de Dados | 7000,000 | 1 |
Carlos Silva | Analista de Sistemas | 7800,000 | 1 |
Lucas Oliveira | Desenvolvedor | 4500,000 | 1 |
Fernanda Santos | Desenvolvedor | 3000,000 | 2 |
Ana Souza | Desenvolvedor | 2500,000 | 3 |
Roberto Alves | Engenheiro de Software | 3000,000 | 1 |
Camila Pereira | Gerente de Projetos | 1950,000 | 1 |
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.
NOME | DESCRICAO_CARGO | SALARIO | DENSE_RANK |
---|---|---|---|
Mariana Costa | Administrador de Banco de Dados | 7000,000 | 1 |
Carlos Silva | Analista de Sistemas | 7800,000 | 1 |
Lucas Oliveira | Desenvolvedor | 4500,000 | 1 |
Fernanda Santos | Desenvolvedor | 3000,000 | 2 |
Ana Souza | Desenvolvedor | 2500,000 | 3 |
Roberto Alves | Engenheiro de Software | 3000,000 | 1 |
Camila Pereira | Gerente de Projetos | 1950,000 | 1 |
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.
NOME | SALARIO | SALARIO_ANTERIOR | SALARIO_PROXIMO |
---|---|---|---|
Camila Pereira | 1950,000 | 0 | 2500 |
Ana Souza | 2500,000 | 1950 | 3000 |
Roberto Alves | 3000,000 | 2500 | 3000 |
Fernanda Santos | 3000,000 | 3000 | 4500 |
Lucas Oliveira | 4500,000 | 3000 | 7000 |
Mariana Costa | 7000,000 | 4500 | 7800 |
Carlos Silva | 7800,000 | 7000 | 0 |
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.
NOME | SALARIO | QUARTIL |
---|---|---|
Carlos Silva | 7800,000 | 1 |
Mariana Costa | 7000,000 | 1 |
Lucas Oliveira | 4500,000 | 2 |
Roberto Alves | 3000,000 | 2 |
Fernanda Santos | 3000,000 | 3 |
Ana Souza | 2500,000 | 3 |
Camila Pereira | 1950,000 | 4 |
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.
NOME | SALARIO | DIST_CUM |
---|---|---|
Carlos Silva | 7800,000 | 0,142857142857143 |
Mariana Costa | 7000,000 | 0,285714285714286 |
Lucas Oliveira | 4500,000 | 0,428571428571429 |
Roberto Alves | 3000,000 | 0,714285714285714 |
Fernanda Santos | 3000,000 | 0,714285714285714 |
Ana Souza | 2500,000 | 0,857142857142857 |
Camila Pereira | 1950,000 | 1 |
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.
NOME | SALARIO | PERC_RANK |
---|---|---|
Carlos Silva | 7800,000 | 0 |
Mariana Costa | 7000,000 | 0,166666666666667 |
Lucas Oliveira | 4500,000 | 0,333333333333333 |
Roberto Alves | 3000,000 | 0,5 |
Fernanda Santos | 3000,000 | 0,5 |
Ana Souza | 2500,000 | 0,833333333333333 |
Camila Pereira | 1950,000 | 1 |
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.
NOME | SALARIO | PRIMEIRO_SALARIO | ULTIMO_SALARIO |
---|---|---|---|
Carlos Silva | 7800,000 | 7800 | 1950 |
Mariana Costa | 7000,000 | 7800 | 1950 |
Lucas Oliveira | 4500,000 | 7800 | 1950 |
Roberto Alves | 3000,000 | 7800 | 1950 |
Fernanda Santos | 3000,000 | 7800 | 1950 |
Ana Souza | 2500,000 | 7800 | 1950 |
Camila Pereira | 1950,000 | 7800 | 1950 |
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.
NOME | SALARIO | TERCEIRO_SALARIO |
---|---|---|
Carlos Silva | 7800,000 | |
Mariana Costa | 7000,000 | |
Lucas Oliveira | 4500,000 | 4500 |
Roberto Alves | 3000,000 | 4500 |
Fernanda Santos | 3000,000 | 4500 |
Ana Souza | 2500,000 | 4500 |
Camila Pereira | 1950,000 | 4500 |
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!