Artigos Banco de Dados Introdução às Funções Analíticas no Oracle

1. Introdução

As funções analíticas podem ser utilizadas no SQL do Oracle para o cálculo e recuperação de resultados agregados em uma instrução SELECT. Elas podem ser empregadas como alternativa ao uso do SELECT com a cláusula “GROUP BY” em diversas situações práticas. A diferença consiste basicamente no fato de as funções analíticas serem capazes de retornar múltiplas linhas para cada grupo, algo que só é possível para o GROUP BY quando empregamos sub-consultas, tabelas virtuais e junções (técnicas que irão tornar a instrução SQL maior e mais complicada).

Nas seções a seguir, apresentamos alguns exemplos práticos introdutórios de utilização das funções analíticas. Utilizaremos como exemplo uma tabela de livros, cujo script de criação e inserção de dados é mostrado na Listagem 1.

Listagem 1: Script de criação para criar e popular a tabela exemplo (Livro)


CREATE TABLE LIVRO (
	ID NUMBER(2) PRIMARY KEY,
	TITULO VARCHAR2(30) NOT NULL,
	GENERO VARCHAR2(20) NOT NULL,
	PRECO NUMBER(5,2) NOT NULL
)
/

INSERT INTO LIVRO VALUES (1,'A Revolução dos Bichos', 'Ficção', 15);
INSERT INTO LIVRO VALUES (2,'Na Pior em Paris e Londres', 'Não-Ficção', 60);
INSERT INTO LIVRO VALUES (3,'Lutando na Espanha', 'Não-Ficção', 45);
INSERT INTO LIVRO VALUES (4,'A Flor da Inglaterra', 'Ficção', 45);
INSERT INTO LIVRO VALUES (5,'1984', 'Ficção', 40);

2. Sintaxe Básica

O modelo com a sintaxe básica para utilizar uma função analítica em uma consulta SQL é mostrado na Figura 1. Não se preocupe se você não entender precisamente o modelo neste momento, pois os exemplos apresentados na Seção 3 facilitarão este entendimento.

Sintaxe básica para utilizar funções analíticas em uma consulta SQL

Figura 1: Sintaxe básica para utilizar funções analíticas em uma consulta SQL

Na sintaxe apresentada:

  • Função_Analítica(argumentos): representa a função que será utilizada para computar resultados agregados ou valores de alguma medida estatística.
  • OVER / PARTITION BY: cláusulas utilizadas para definir as partições (grupos) para “quebrar” ou agregar os resultados.

Existem sintaxes mais complexas para o uso de funções analíticas, porém não as apresentaremos neste artigo, já que, por enquanto, o objetivo é passar apenas conceitos introdutórios.

3. Exemplos

Iniciamos a seção de exemplos, mostrando como utilizar a sintaxe básica do SQL com função analítica para escrever uma consulta que retorna o id, título, gênero e total de livros para o gênero retornado na linha corrente. Para tal, basta utilizar a conhecida função COUNT(*) em conjunto com as clausular OVER – PARTITION BY e realizar o particionamento dos resultados por gênero. A Listagem 2 mostra a consulta.

Listagem 2: Retornando Livros e Total por Gênero – SQL com função analítica


SELECT id, titulo, genero, 
COUNT(*) OVER (PARTITION BY genero) qtd_genero
FROM livro

O resultado da consulta é mostrado na Figura 2.

Resultados da listagem 2

Figura 2: Resultados da listagem 2

A cláusula PARTITION é responsável por configurar a faixa de registros que será utilizada por cada grupo. No exemplo apresentado, “qtd_genero” retorna o número de livros dentro do gênero de cada livro retornado pela consulta.

Sendo assim, com o modelo da função analítica (uso das cláusulas OVER – PARTITION BY), torna-se possível escrever uma consulta SQL que fornece resultados agregados que são retornados múltiplas vezes, uma vez para cada linha retornada pela consulta. A obtenção do mesmo tipo de resultado via “SQL tradicional” é bem mais trabalhosa. Nesse caso, é necessário elaborar uma consulta como a mostrada na Listagem 3.

Listagem 3: Retornando Livros e Total por Gênero – SQL com GROUP BY


SELECT a.id, a.titulo, a.genero, b.qtd_genero  
FROM livro a 
INNER JOIN (SELECT genero, count(*) qtd_genero FROM livro GROUP BY genero) b
ON (a.genero = b.genero)

No exemplo, foi preciso escrever um SQL com GROUP BY na cláusula FROM e fazer o INNER JOIN dos resultados desse SQL com a tabela “livro”.

A Listagem 4 mostra outro exemplo: um SQL que exibe o preço médio, o maior preço e o menor preço dos livros por gênero (o resultado é mostrado na Figura 3). Neste exemplo, utilizamos as tradicionais funções AVG, MAX e MIN da SQL.

Listagem 4: SQL com preço médio, máximo e mínimo dos livros por gênero


SELECT id, titulo, genero, preco, 
avg(preco) OVER (PARTITION BY genero) media_preco,
max(preco) OVER (PARTITION BY genero) maior_preco,
min(preco) OVER (PARTITION BY genero) menor_preco
FROM LIVRO
Resultados da listagem 4

Figura 3: Resultados da listagem 4

4. Comentários Finais

Finalizamos o artigo apresentando algumas considerações adicionais:

  • As funções analíticas são utilizadas para computar um valor agregado baseado em um grupo de linhas. Elas diferem do modelo tradicionalmente utilizado com a cláusula GROUP BY porque permitem que múltiplas linhas sejam retornadas para cada grupo (ou “janela” – nome utilizado nos manuais da Oracle). Elas só podem aparecer na lista associada à palavra SELECT nas consultas SQL.
  • Não apenas funções tradicionais como COUNT, SUM, AVG, MIN, MAX, VARIANCE e STDDEV, podem ser utilizadas no modelo analítico. Além delas, existem muitas outras funções mais novas e menos conhecidas pelos desenvolvedores Oracle, como CORR, DENSE_VAR, FIRST, LAG, LAST, RANK, DENSE_RANK e diversas outras.
  • As funções analíticas são computadas somente depois de todos os JOIN’s e das condições associadas à cláusula WHERE terem sido resolvidas (e também depois da resolução do GROUP BY e de condições especificadas na cláusula HAVING). A cláusula PARTITION BY é usada para quebrar o conjunto de resultados em grupos. Se ela for omitida – ou seja, se a cláusula OVER for utilizada sozinha – a função analítica irá atuar em todo o conjunto de resultados retornado pela cláusula WHERE.

Um pouco mais: