分析函数实例

具体的分析函数的语法和函数列表见：http://blog.youkuaiyun.com/flighting_sky/article/details/9532663

/*数据准备：
 *以oracle样例的SH模式为实验数据
 *创建实验数据表：sales_fact
 */
create table sales_fact as
select country_name country,country_subRegion region,prod_name product,
calendar_year year,calendar_week_number week,
sum(amount_sold) sale,
sum(amount_sold *
   (case
       when mod(rownum,10)=0 then 1.4 
       when mod(rownum,5)=0 then 0.6
       when mod(rownum,2)=0 then 0.9 
       when mod(rownum,2)=1 then 1.2
       else 1 end)
   ) receipts
   from sales,times,customers,countries,products
   where sales.time_id =times.time_id and
   sales.prod_id =products.prod_id and
   sales.cust_id =customers.cust_id and
   customers.country_id=countries.country_id
   group by country_name, country_subregion,prod_name ,calendar_year, calendar_week_number;
   
   ---实验一：聚合函数
   /*1、获取到目前为止的累积消费额；
    *2、获取前后五周内的最大消费额；
    */
    ----获取到目前为止的累积消费额；
 select year,week,sale,
       sum(sale)over
       (partition by year
       order by week
       rows between unbounded preceding and current row) cumulate_sum---计算区间为年初到当前
from sales_fact
where product ='Xtend Memory' and country ='Australia' and week between 1 and 3 and year in (1998,1999)
order by year,week;

-----实验结果
-year-week-sale-cumulate_sum
1998	1	  58.15	58.15
1998	2	  29.39	87.54
1998	3	  29.49	117.03
1999	1	  53.52	53.52
1999	3	  94.6	148.12

---获取前后五周内的最大消费额；
select year,week,sale,
       sum(sale)over
       (partition by year
       order by week
       rows between 2 preceding and 2 following) cumulate_sum---计算区间为当前行前两周到当前行的后两周
from sales_fact
where product ='Xtend Memory' and country ='Australia' and week between 1 and 5 and year in (1998,1999)
order by year,week;

---实验结果
-year-week-sale-cumulate_sum
1998	1	   58.15	117.03
1998	2	   29.39	146.52
1998	3	   29.49	176.32
1998	4	   29.49	118.17
1998	5	   29.8	88.78
1999	1	   53.52	188.62
1999	3	   94.6	268.63
1999	4	   40.5	268.63
1999	5	   80.01	215.11

----实验二：定位函数：lead(),lag(),first_value(),last_value(),nth_value()
/*实验目的：
 *1、获取前一周的销售量【使用lag()】；
 *2、获取下一周的销售量【使用lead()】;
 *3、获取最大的销售额和最小的销售额【使用first_value()和last_value()】;
 *4、获取第二高的销售量【使用nth_value()】；
 ***/

select year,week,sale,
       lag(sale,1,sale)over
       (partition by year
       order by week) lag,----1、获取前一周的销售量；
       lead(sale,1,sale)over
       (partition by year
       order by week) lead, ---2、获取下一周的销售量;
       first_value(sale)over
       (partition by year
       order by sale desc 
       rows between unbounded  preceding and unbounded following) max_sale ,---3、最大的销售额
       last_value(sale)over
       (partition by year
       order by sale desc 
       rows between unbounded  preceding and unbounded following) min_sale,---3、最小的销售额
       nth_value(sale,2)  over
       (partition by year
       order by sale desc 
       rows between unbounded  preceding and unbounded following) second_sale---4、第二高的销售量
from sales_fact
where product ='Xtend Memory' and country ='Australia' and week between 1 and 3 and year in (1998,1999)
order by year,week;

---实验结果：
-year-week-sale---lag---lead---max_sale-min_sale-second_sale
1998	1	   58.15	58.15	29.39	   58.15	29.39	   29.49
1998	2	   29.39	58.15	29.49	   58.15	29.39	   29.49
1998	3	   29.49	29.39	29.49	   58.15	29.39	   29.49
1999	1	   53.52	53.52	94.6	   94.6	  53.52	   53.52
1999	3	   94.6	  53.52	94.6	   94.6	  53.52	   53.52