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

É bastante conhecida a utilização de funções agregadas em SQL, como MIN, MAX, SUM e AVG, mas o Oracle oferece um recurso interessante e bastante poderoso: definir um intervalo (janela) para a função. Começo com um exemplo bem simples, baseado em uma tabela que contém um registro para cada dia do ano:

create table tst_janela as

select to_date('31-12-2009', 'dd-mm-yyyy')+level as_of_date, trunc( dbms_random.VALUE(100, 200) ) num_users
from dual
connect by level <= 365;

O objetivo é obter para cada dia, a média dos últimos 10 dias. Ou seja, em 01/Jan, o resultado é a média dos valores entre 1 e 10/Jan. Já em 25/Jan, a média entre valores de 16 a 25/Jan. A função a ser utilizada é AVG. O problema é como definir as linhas que devem ser consideradas no cálculo, já que elas variam a  cada dia. Esta é a "janela" (intervalo, conjunto de linhas) da função. A consulta abaixo resolve o problema:

select as_of_date,

sum(num_users) over (order by as_of_date
range between '10' preceding and current row) media_10dias
from tst_janela;

Fundamental é compreender bem como a segunda coluna é calculada. Sum(num_users) dispensa comentários, mas a claúsula seguinte é chave da solução. Order by as_of_date é o critério como as linhas devem ser ordenadas. Range between '10' preceding and current row estabelece a janela começando 10 linhas (dias) atrás e terminando na linha corrente.