domingo, 27 de junho de 2010

Funções agregadas em janelas no Oracle - parte II

A janela pode ser especificada de diversas formas. Uma relação completa pode ser encontrada no manual da Oracle, a partir da versão. Aqui coloco uma lista parcial das alternativas:

BETWEEN x PRECEDING AND y FOLLOWING
BETWEEN x PRECEDING AND y PRECEDING
BETWEEN CURRENT ROW AND y FOLLOWING
BETWEEN x PRECEDING AND CURRENT ROW
BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
BETWEEN UNBOUNDED PRECEDING AND y FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
Por default, a última alternativa (em verde) é utilizada. Um detalhe, no exemplo que estou utilizando, há apenas um registro para cada dia. Assim, '10' PRECEDING representa os últimos 10 dias. Se houvesse um número de registros variável para cada dia, a janela deve ser especificada como um intervalo.
 
INTERVAL 'nn' DAY PRECEDING
INTERVAL 'nn' SECONDS FOLLOWING
INTERVAL 'nn' MONTH PRECEDING
 
Para exemplificar utilizo outra tabela com duzentos registros distribuídos aleatoriamente no mês de Janeiro.
 
create table tst_janela2 as
select to_date('31-12-2009', 'dd-mm-yyyy')+trunc(dbms_random.value(1,31)) as_of_date, trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 200;
 
O requisito é obter a média do número de usuários nos últimos 7 dias (6 precedentes e o dia corrente). A consulta é:
 
select distinct as_of_date,

sum(num_users) over (order by as_of_date
range interval '6' day preceding) media_semanal
from tst_janela2
order by 1;

Um último detalhe: digamos que, nesta resposta, é necessário obter o valor apenas para os sábados. A consulta acima deve ser uma visão (subconsulta) da principal. Se acrescentar um filtro (apenas sábados)  na clausula WHERE da consulta interna, as linhas dos demais dias da semana são retiradas do cálculo e o resultado final não será o desejado. Portanto, o correto é:

select as_of_date, media_semanal

from(
select distinct as_of_date,
sum(num_users) over (order by as_of_date
range interval '6' day preceding) media_semanal
from tst_janela2)
where to_char(as_of_date, 'D') = 7
order by 1;

Nenhum comentário:

Postar um comentário