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:
