Oracle Obtendo performance com funções analíticas

As funções analíticas foram introduzidas no Oracle (a partir da versão 8.1.6) para facilitar as análises de negócios (Business Inteligence - BI) e dar suporte a tarefas analíticas. Sua aplicabilidade está bastante relacionada a sistemas de data warehouse e data mining, uma vez que estes utilizam dados agrupados e sumarizados para facilitar a análise. Entre outras coisas, as funções analíticas possibilitam gerar:

1.           Classificações de elementos com base em perguntas do tipo “quais os produtos mais vendidos?” ou “quais os setores mais lucrativos?”;

2.           Faixas de valores (partições), o que permite agrupá-las por número específico (por exemplo, pela categoria “cidades com desempenho semelhantes”);

3.           Agregados cumulativos ou centralizados nos quais o valor de uma linha depende do valor gerado por outra, respondendo a questões como “qual o valor acumulado de vendas deste ano com base no total de vendas mensal?” ou “qual o percentual de crescimento das vendas no último ano?”.

Em geral, as funções analíticas são pós-processadas (ou seja, são executadas sobre o resultado de um comando SELECT) e seguem este princípio de funcionamento:

1. Inicialmente, são resolvidas as associações e cláusulas WHERE, GROUP BY e HAVING, a fim de que sejam fornecidos os dados considerados base;

2. Uma fez feito isso, todos os cálculos serão resolvidos, inclusive as funções analíticas.

Embora seja possível criar funções semelhantes às analíticas por meio de visões auxiliares e subqueries, a eficiência não é a mesma, porque o conjunto da base de dados é resolvido primeiro e só depois são aplicadas as funções analíticas. Vejamos o exemplo da Listagem 1, na qual é calculada “a soma dos salários dos departamentos em função dos salários totais da empresa”.

Listagem 1 - Utilização de Função Analítica no Lugar de uma View

-- Sem pós processamento

-- Visão com o salário total

Create view sal_geral As Select SUM(sal) total_sal From Emp;

-- Comando Select Utilizando a View

Select deptno, (SUM(sal) / total_sal)

*100 From    Emp, sal_geral Group by deptno, total_sal

-- Comando Select Com pós processamento sem utilizar a view

Select deptno, (SUM(sal) / SUM(SUM(sal)) OVER ())

*100 From    Emp Group by deptno

Nota: As tabelas utilizadas neste exemplo pertencem ao esquema SCOTT (senha tiger), instalado automaticamente com o banco de dados e o qual fornece algumas tabelas já populadas.

O comando Select com pós-processamento da Listagem 1 apresenta a soma dos salários por departamento (sum(sal)) em relação a soma de todos os salários (SUM(sal) OVER()) da tabela de empregados. A cláusula OVER (sem parâmetros) especifica que serão somados todos os registros da tabela (sem restrições ou particionamentos).

O uso da cláusula OVER após uma função a caracteriza como função analítica. Essas funções seguem uma sintaxe padrão, que será explicada no decorrer da apresentação das funções existentes na Listagem 2.

Listagem 2

<nome_da_função>([parâmetrto(a)])

OVER ( [PARTITION BY <lista de itens para particionamento>]

[ORDER BY <lista de itens para ordenação>

[ASC ou DESC]]

[NULLS FIRST ou NULLS LAST] [BETWEEN

<valor inicial ou UNBOUNDED PRECEDING>

AND                     <valor final ou CURRENT ROW>])

Ordenação

A cláusula ORDER BY especifica a seqüência (ordenação) em que os dados serão agregados dentro de uma partição ou do conjunto total dos dados retornados. Assim como na cláusula ORDER BY do SQL padrão, os complementos ASC e DESC são utilizados para especificar o sentido da ordenação (ascendente ou descendente, respectivamente). As cláusulas NULLS FIRST e NULLS LAST especificam se os valores nulos dos itens a serem ordenados serão colocados no início ou no final da classificação. Caso esta cláusula seja omitida, os valores nulos serão considerados maiores que os outros.

O exemplo da Listagem 3 traz para cada mês do resumo de vendas o valor total vendido (SUM(vl_venda)) e o valor acumulado (SUM(SUM(vl_venda)) OVER (ORDER BY nr_mes)), ordenado pelo mês da venda. Por valor acumulado, entende-se o somatório dos valores para os meses menores ou iguais ao mês do registro atual. A cláusula ORDER BY nr_mes causa esse ‘efeito’ de acumulado. Por exemplo, se o item de ordenação fosse o valor da soma, o resultado acumulado seria 60, 180, 310, 460, 670 e 910.

Listagem 3 - Utilizando a Função Over com a Cláusula Order By

SELECT nr_mes, SUM(vl_venda) TotMes,

SUM(SUM(vl_venda))

OVER(ORDER BY nr_mes) Acumulado

FROM resumo_venda

WHERE nr_ano = 2002

GROUP BY nr_mes;

Particionamento de Dados

A cláusula PARTITION BY não é obrigatória. Seu uso significa que o cálculo (soma, rank, média etc) recomeçará a cada mudança no conteúdo dos campos listados na cláusula. Ou seja, o resultado da consulta é dividido em grupos (partições), com os quais a função analítica irá trabalhar. Caso não haja partição, a função analítica operará sobre o conjunto completo de dados retornado.

A Listagem 4 traz, para cada produto do resumo de vendas em todos os meses inferiores a maio, o valor total vendido (SUM(v.vl_venda)) e acumulado (SUM(SUM(v.vl_venda)) OVER (PARTITION BY p.nm_produto ORDER BY p.nm_produto, v.nr_mes)).

Listagem 4 - Exemplo da função Partition By

SELECT p.nm_produto, v.nr_mes,

SUM(v.vl_venda) TotMes,

SUM(SUM(v.vl_venda))

OVER(PARTITION BY p.nm_produto

ORDER BY p.nm_produto,

v.nr_mes) Acumulado

FROM produto p, resumo_venda v

WHERE p.cd_produto = v.cd_produto

AND    v.nr_ano = 2002

AND    v.nr_mes < 5

GROUP BY p.nm_produto, v.nr_mes;

Janelas

A cláusula de janela (windowing) é utilizada para quebrar a cláusula PARTITION BY em subconjuntos. A janela pode fazer referência a um número físico de linhas (janela física) ou a um intervalo, como por exemplo, de tempo ou de valores (janela lógica). Ou seja, quando uma janela física é definida, pode-se especificar a quantidade de linhas anteriores, posteriores ou ambas em relação à linha atual. Já na janela lógica, é possível valorar o limite inferior e/ou superior do conjunto de dados com base nas colunas que irão compor a janela (por exemplo, 2 meses antes com relação à data de venda ou 5000 a menos com relação ao valor da nota fiscal).

A Listagem 5 apresenta a sintaxe para a definição de janelas e as Listagens 6, 7 e 8 exemplificam a sua utilização.

Listagem 5 - Sintaxe utilizada para a definição de janelas.

ROWS / RANGE <[UNBOUNDED PRECEDING ou valor PRECEDING] /

BETWEEN           [UNBOUNDED PRECEDING ou valor PRECEDING]

AND                     [CURRENT ROW ou valor FOLLOWING]>

Onde:

ROWS: Janela física.

RANGE: Janela lógica.

BETWEEN ... AND ...: Especifica os pontos inicial e final da janela. Se BETWEEN for omitido e um ponto final for

especificado, este ponto será utilizado como ponto de inicio e o CURRENT ROW será utilizado como ponto final;

UNBOUNDED PRECEDING: Especifica que a janela começa na primeira linha da partição, ou na primeira linha do

conjunto de resultados, se não houver partição;

UNBOUNDED FOLLOWING: Especifica que a janela termina na última linha da partição, ou na última linha do conjunto

de resultados, se não houver partição.

CURRENT_ROW: Se for ponto de inicio para ROWS, faz com que a linha corrente seja o início da janela; para RANGE,

faz com que o valor corrente seja o inicio da janela. Como ponto de fim para ROWS, faz com que a linha corrente

seja o fim da janela; para RANGE, faz com que o valor corrente seja o fim da janela.

<n> FOLLOWING – Indica que as n linhas seguintes serão consideradas.

<n> PRECEDING – Indica que as n linhas anteriores serão consideradas.

A Listagem 6 calcula a média das vendas (arredondada em duas casas – ROUND(AVG(SUM(f.sales)) ..., 2)) por ano até o mês de junho (conforme restrição da cláusula WHERE), considerando os valores cumulativos dos meses anteriores. Vamos analisar a cláusula OVER desse comando dividindo-a em duas partes, grifadas em vermelho e azul:

ROUND( AVG( SUM(f.sales))

OVER (PARTITION BY

extract(year from t.transaction_date)

ORDER BY

extract(month from t.transaction_date)

ROWS 2 PRECEDING), 2) “Média Até”

Listagem 6 - Funções Windowing

Select extract(year from t.transaction_date) “Ano”,

extract(month from t.transaction_date) “Mês”,

SUM(f.sales) “Vendas”,

ROUND(

AVG(SUM(f.sales))

OVER (PARTITION BY

extract(year from t.transaction_date)

ORDER BY

extract(month from t.transaction_date)

ROWS 2 PRECEDING), 2) “Média Até”

from times t, sales_fact f

where t.time_key = f.time_key

and extract(month from t.transaction_date) <= 6

and extract(year from t.transaction_date) < 2000

GROUP BY (extract(year from t.transaction_date),

extract(month from t.transaction_date))

ORDER BY 1, 2; -- Ordenação por Ano e Mês, primeiro e segundo itens do SELECT

A primeira parte (em vermelho) é responsável pela partição dos cálculos, “resetando” (PARTITION BY) o cálculo a cada ano (extract(year from t.transaction_date)) e acumulando os valores por ordem (ORDER BY) de mês (extract(month from t.transaction_date)).

A segunda parte (em azul) indica que os dados analisados irão considerar as duas linhas anteriores a linha corrente (2 PRECEDING) da janela física (ROWS).

A Listagem 7 calcula a média das vendas por ano até o mês de junho, considerando os valores cumulativos dos três meses anteriores (RANGE 3 PRECEDING). A cláusula over desse comando trabalha de forma semelhante à da listagem 6, porém a janela utilizada para a análise não é mais a janela física de x linhas (definida por ROWS na Listagem 6) e sim a janela lógica dos 3 meses anteriores (independente da posição física em que as linhas referentes a esses três meses sejam retornadas), definida pela cláusula RANGE 3 PRECEDING.

Listagem 7 - Funções Windowing

Select extract(year from t.transaction_date) “Ano”,

extract(month from t.transaction_date) “Mês”,

SUM(f.sales) “Vendas”,

ROUND(

AVG(SUM(f.sales))

OVER (PARTITION BY

extract(year from t.transaction_date)

ORDER BY

extract(month from t.transaction_date)

RANGE 3 PRECEDING), 2)

“Média Até”

from times t, sales_fact f

where t.time_key = f.time_key

and extract(month from t.transaction_date) <= 6

and extract(year from t.transaction_date) < 2000

GROUP BY (extract(year from t.transaction_date),

extract(month from t.transaction_date))

ORDER BY 1, 2;

Os intervalos de tempo permitem analisar as mudanças nos dados no decorrer do tempo, o que facilita a identificação de tendências. Para definir os intervalos de data, é possível usar as seguintes expressões (indicando intervalos de n dias, meses ou anos - antes ou depois - com base em uma determinada data):

INTERVAL ‘n’ DAYS ou MONTHS ou YEARS /

PRECEDING ou FOLLOWING

A Listagem 8 apresenta a utilização de janelas com análises relacionadas a intervalos de tempo. Este exemplo mostra para determinada data de transação em uma tabela de movimentações de estoque os seguintes

valores: a quantidade movimentada na data, a média das quantidades movimentadas nos 30 dias anteriores (RANGE INTERVAL ‘30’ DAY PRECEDING), a média das quantidades movimentadas nos 30 dias seguintes a contar da data da linha atual (RANGE BETWEEN CURRENT ROW AND INTERVAL ‘30’ DAY FOLLOWING) e a média das quantidades movimentadas nos 60 dias (30 anteriores e 30 seguintes) (RANGE BETWEEN INTERVAL ‘30’ DAY PRECEDING AND INTERVAL ‘30’ DAY FOLLOWING).

Listagem 8 - Utilização de Função Analítica Aplicada num Intervalo de Tempo

SELECT dt_transacao, qt_movimento,

AVG(qt_movimento) OVER (ORDER BY dt_transacao

RANGE INTERVAL ‘30’ DAY PRECEDING) Ultimos30,

AVG(qt_movimento) OVER (ORDER BY dt_transacao

RANGE BETWEEN CURRENT_ROW

AND INTERVAL ‘30’ DAY FOLLOWING) Proximos30,

AVG(qt_movimento) OVER (ORDER BY dt_transacao

RANGE BETWEEN INTERVAL ‘30’ DAY PRECEDING

AND INTERVAL ‘30’ DAY FOLLOWING) Dias60

FROM movimento

Funções Analíticas de Agregação

As funções analíticas de agregação são as mesmas utilizadas para as agregações convencionais (SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE, VAR_SAMP), mas sua sintaxe é expandida para aceitar particionamento de dados. A Listagem 9 apresenta a sintaxe geral das funções analíticas de agregação.

Listagem 9 - Sintaxe Geral das Funções Analíticas de Agregação.

Função_Analítica_de_Agregação (item a ser calculado)

OVER ([PARTITION BY lista de itens para partição]

[ORDER BY lista de itens que ordenam o cálculo] [ASC ou DESC]

[BETWEEN <valor inicial ou UNBOUNDED PRECEDING>

AND <valor final ou CURRENT ROW>])

O exemplo da Listagem 10 permite localizar a região em que cada produto foi mais vendido com a ajuda da função analítica MAX, particionada (agrupada) por produto em uma subquery na cláusula FROM.

Listagem 10 - Utilizando Funções de Agregação

SELECT nm_produto, nm_regiao, sum_qtd

FROM (SELECT p.nm_produto, r.nm_regiao, SUM(v.qt_produto) sum_qtd,

MAX(SUM(v.qt_produto)) OVER (PARTITION BY p.nm_produto) max_sum_qtd

FROM           venda_produto v, regiao r, produto p

WHERE         v.cd_regiao = r.cd_regiao

AND             v.cd_produto = p.cd_produto

GROUP BY    p.nm_produto, r.nm_regiao)

WHERE sum_qtd = max_sum_qtd

A subquery apresentada na cláusula FROM da Listagem 10 retornará o nome do produto (p.nm_produto), o nome da região (r.nm_região), a quantidade total vendida deste produto nesta região (SUM(v.qt_produto) sum_qtd) e a quantidade vendida na região em que o produto vendeu melhor (MAX(SUM(v.qt_produto)) OVER (PARTITION BY p.nm_produto) max_sum_qtd). Veja a listagem 11.

Listagem 11

SELECT p.nm_produto, r.nm_regiao, SUM(v.qt_produto) sum_qtd,

MAX(SUM(v.qt_produto)) OVER (PARTITION BY p.nm_produto) max_sum_qtd

FROM            venda_produto v, regiao r, produto p

WHERE         v.cd_regiao = r.cd_regiao

AND               v.cd_produto = p.cd_produto

GROUP BY    p.nm_produto, r.nm_regiao

A partir dessa análise, os registros que apresentarem o somatório da quantidade vendida (sum_qtd) igual ao da coluna analítica que traz o valor máximo de vendas do produto para determinada região (max_sum_qtd), satisfarão a cláusula WHERE sum_qtd = max_sum_qtd da consulta principal e apresentarão a região onde cada produto é mais vendido. Ou seja, na listagem 12 temos o conjunto resultante do código apresentado na listagem 10.

Listagem 12

Cuidados com o ORDER BY em funções analíticas

A utilização do ORDER BY para obter valores acumulados em funções analíticas deve ser feita com cuidado, pois o resultado pode variar de acordo com as colunas utilizadas nessa cláusula. Observe as consultas e os resultados mostrados nas listagens A e B. No primeiro comando SELECT (listagem A), a cláusula de ordenação da soma foi especificada apenas por ordem decrescente de valor de salário (OVER PARTITION BY job ORDER BY sal DESC). Assim, no caso de salários iguais, estes eram agregados de uma só vez (Scott e Ford com salário de 3000 e Martim e Ward com 1250), o que pode ser observado na coluna de valores acumulados. Isso acontece por que a cláusula ORDER BY especifica qual a ordem em que os valores serão agregados e, neste exemplo, a ordem especificada foi apenas “ordem decrescente de valor de salário”. Como não há nenhum critério de desempate estabelecido nesta cláusula, os valores iguais são agregados ao mesmo tempo. No segundo comando (listagem B), o campo ename foi incluído como diferenciador ou critério de desempate para a ordenação na seqüência de agregação (OVER PARTITION BY job ORDER BY sal DESC, ename).

Listagem A

SELECT

RPAD(ename,20) “Nome do Funcionário”,

RPAD(job, 20) “Cargo”, sal “Salário”,

SUM(sal) OVER (PARTITION BY job ORDER BY sal DESC)

“Acumulado”

FROM emp

ORDER BY 2,3 DESC;

Listagem B

SELECT

RPAD(ename,20) “Nome do Funcionário”,

RPAD(job, 20) “Cargo”, sal “Salário”,

SUM(sal) OVER (PARTITION BY job ORDER BY sal DESC, ename)

“Acumulado”

FROM emp

ORDER BY 2,3 DESC;

Conclusões

Este artigo apresentou algumas das funções analíticas disponíveis no Oracle. Este é um assunto bastante vasto, mas espero que esta introdução tenha apresentado alguns pontos que muitas vezes são subutilizados por desconhecimento. Nas próximas edições, daremos continuidade à discussão de outras funcionalidades.

Por fim, vale mencionar o Oracle Discoverer, ferramenta OLAP da Oracle (também assunto para um próximo artigo). Ela permite usar as funções apresentadas aqui bem como outras funções de forma gráfica e visual, proporcionando alta disponibilidade, tempos de resposta reduzidos e alto grau de interatividade com o usuário.