Funções agregadas em janelas no Oracle - parte III (final)

Para concluir esta pequena série de posts, vou utilizar uma terceira tabela de exemplo. Nela, há o número de acessos por usuário a cada dia, divididos por diferentes países (Brasil, EUA e Canadá):

create table tst_janela3 as

select 'BRASIL' pais,
        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
union all
select 'EUA' pais,
       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
union all
select 'CANADA' pais,
       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 requisito é praticamente o mesmo: obter o total de acessos acumulados a cada dia, contando sempre do dia primeiro do mês até o dia em questão. Ou seja, em 14/Março, deve-se obter a soma de acessos do dia 01/Março até 14/Março. No dia 15/Março, do dia 1o. até 15.  Em 01/Abril, "zera" o acumulador e começa a somar novamente.

Considerando apenas um país, a consulta adequada está abaixo. A janela utilizada é o dia-1. O "-1" é necessário para não incluir no cálculo o último dia do mês anterior, afinal no dia 15/Março precisamos somar os 14 dias precedentes e o corrente.

select as_of_date,

       num_users,
       sum(num_users) over(order by as_of_date
                           range extract(day from as_of_date)-1 preceding ) acum_mes
from tst_janela3
where pais = 'BRASIL';

Muito bem, mas o requisito não inclui o filtro para BRASIL. É necessário o valor para cada dia, para cada país. Nesta caso, deve-se utilizar a claúsula PARTITION BY, para manter os valores separados por país:

select as_of_date,
       pais,
       num_users,
       sum(num_users) over(partition by pais
                           order by as_of_date
                           range extract(day from as_of_date)-1 preceding ) acum_mes
from tst_janela3
order by 1,2;

Como todas funções analíticas, as consultas em janelas podem ser especialmente úteis em queries de relatórios e queries para alimentar sistemas de Data Warehouse. Até onde sei, são um recurso exclusivo do Oracle, mas o César, meu colega de blog, vai tentar provar que é simples fazer com o Microsoft SQL Server.