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.

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.

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

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:

Introdu��o �s Fun��es Anal�ticas no Oracle

Veja neste artigo uma introdução ao uso de funções analíticas no SGBD Oracle através de exemplos práticos. Estas funções oferecem recursos poderosos para a obtenção de resultados agregados a partir de consultas SQL.

1. Introduç�o

2. Sintaxe B�sica

3. Exemplos

4. Coment�rios Finais

Artigos relacionados