测试表如下:
create table sales_fact (
id_ number,
year number,
week number,
product varchar2(20),
country varchar2(20),
region varchar2(20),
sale number(10,2)
)
填充测试数据:
declare
v_id number :=1;
begin
for v_year in 2000..2003 loop
for v_week in 1..10 loop
for v_product in 1..10 loop
for v_country in 1..5 loop
for v_region in 1..6 loop
--dbms_output.put_line('year='||v_year||' week='||v_week||' product=product'||v_product||' );
insert into sales_fact values(
v_id,
v_year,v_week,'product'||v_product,'country'||v_country,'region'||v_region,
v_year+v_week+v_product+v_country+v_region*0.1);
v_id := v_id +1;
end loop;
end loop;
end loop;
end loop;
end loop;
commit;
end;
/
示例SQL如下:
select year,week,product,country,region,sale,
sum(sale) over( --获取某个产品每年从年初到当前日期的总sale
partition by product,country,region,year
order by week
rows between unbounded preceding and current row
) as runnign_sum_ytd,
sum(sale) over(--获取某个产品之前2周到后2周的总sale
partition by product,country,region,year
order by week
rows between 2 preceding and 2 following
) as r5_sum_ytd
from sales_fact
where 1=1
and country in ('country1')
and product in ('product1')
and region='region1'
order by product,country,region,year,week;
从上面的sql可以看到,相同country、product、region、year的数据为一个分区,
然后以week进行排序,
计算两个sum值:
某个产品每年从年初到当前日期的总sale
获取某个产品之前2周到后2周的总sale