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;
terça-feira, 6 de julho de 2010
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.
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.
Assinar:
Postagens (Atom)