terça-feira, 6 de julho de 2010

Funções Agregadas em janelas no SQL Server

Depois dos posts do Miguel procurei no SQL Server um recurso similar ao explicado por ele das janelas para funções agregadas. No SQL Server existe o OVER, entretanto com opções mais limitadas, não existindo a cláusula RANGE.
Para conseguir o mesmo resultado tive que utilizar sub-queries, o que tira toda a facilidade da operação. Para exemplificar o terceiro exemplo do Oracle apresentado pelo Miguel que tinha o seguinte SQL (http://sqlbrasil.blogspot.com/2010/07/funcoes-agregadas-em-janelas-no-oracle.html):

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;
 
tive que resolver da seguinte maneira no SQL Server:
 
Select as_of_Date, pais, num_users,
      (Select sum(num_users)
         from tst_Janela3 b
       where b.as_of_date >= a.as_of_date - DAY(a.as_of_date) + 1
          and b.as_of_date <= a.as_of_date
          and a.pais = b.pais) as acum_mes
from tst_janela3 a
order by 1, 2;

segunda-feira, 5 de julho de 2010

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.