over partition by order by rows|range（2）之聚合函数

测试表如下:

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