Oracle分析函数

create table orders_tmp
( CUST_NBR  NUMBER(5)   NOT NULL, 
  REGION_ID   NUMBER(5)  NOT NULL, 
  SALESPERSON_ID   NUMBER(5)   NOT NULL,  
  YEAR  NUMBER(4) NOT NULL,  
  MONTH  NUMBER(2)  NOT NULL,  
  TOT_ORDERS  NUMBER(7)  NOT NULL, 
  TOT_SALES  NUMBER(11,2) NOT NULL 
);



--按区域查找上一年度订单总额占区域订单总额20%以上的大客户,每个大客户所占的订单比例
 select all_sales.*,100*round(cust_sales/region_sales,2)||'%' percent
 from
 (select o.cust_nbr customer, o.region_id region,sum(o.tot_sales) cust_sales,
 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
 from orders_tmp o
 where o.year = 2001
 group by o.region_id, o.cust_nbr)all_sales
 where all_sales.cust_sales > all_sales.region_sales * 0.2;


 
--找出订单总额排名前12位的客户(一般)
 select rownum, t.* from 
 (select *  from user_order  order by customer_sales desc) t
  where rownum <= 12
  order by customer_sales desc;
  
 
 
 
 create table user_order
 ( REGION_ID    NUMBER(2),
  CUSTOMER_ID   NUMBER(2),
  CUSTOMER_SALES   NUMBER);
  
  
  
--Rank，Dense_rank，Row_number函数区别
--对所有客户按订单总额进行排名
select region_id, customer_id, sum(customer_sales) total,
 rank() over(order by sum(customer_sales) desc) rank,
 dense_rank() over(order by sum(customer_sales) desc) dense_rank,
 row_number() over(order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;



--按区域和客户订单总额进行排名
select region_id, customer_id, sum(customer_sales) total,
 rank() over(partition by region_id order by sum(customer_sales) desc) rank,
 dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,
 row_number() over(partition by region_id order by sum(customer_sales) desc) row_number
from user_order
group by region_id, customer_id;


--对于空值的排名，使用 NULLS LAST，NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
 select region_id, customer_id,
 sum(customer_sales) cust_total,
 sum(sum(customer_sales)) over(partition by region_id) reg_total,
 rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank
 from user_order
 group by region_id, customer_id;
 
 
 
 
--找出所有订单总额排名前3的大客户：
select * from 
(select region_id,
 customer_id,
 sum(customer_sales) cust_total,
 rank() over(order by sum(customer_sales) desc NULLS LAST) rank
 from user_order
 group by region_id, customer_id
 )
 where rank <= 3;
 
 
--找出每个区域订单总额排名前3的大客户
select * from 
(select region_id, customer_id,
 sum(customer_sales) cust_total,
 sum(sum(customer_sales)) over(partition by region_id) reg_total,
 rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank
 from user_order
 group by region_id, customer_id
 )
 where rank <= 3;


--找出订单总额最高、最低的客户
select min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first,
  min(customer_id)  keep (dense_rank last order by sum(customer_sales) desc) last
from user_order
group by customer_id;
 



--找出订单总额排名前25%的客户,ntile(4)表示所有记录被分为4个等级，nt.til=1取1/4的记录
select * from
(select region_id,customer_id,
 ntile(4) over(order by sum(customer_sales) desc) til
 from user_order
  group by region_id, customer_id)nt
  where nt.til=1;







create table orders
(MONTH  NUMBER(2),
  TOT_SALES  NUMBER
  );
  
 select * from orders for update;
 commit;

-------------------------------------------------------------------
--窗口函数示例－全统计
-------------------------------------------------------------------
--列出每月的订单总额以及全年的订单总额

 select month,
 sum(tot_sales) month_sales,
 sum(sum(tot_sales)) over (order by month
 rows between unbounded preceding and unbounded following) total_sales
 from orders
 group by month;
 
-------------------------------------------------------------------
--窗口函数－滚动统计(累积/均值)
-------------------------------------------------------------------
--列出每月的订单总额以及截至到当前月的订单总额
--也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month
rows between unbounded preceding and current row) current_total_sales
from orders
group by month;

--统计
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month
rows between unbounded preceding and current row) current_total_sales,
sum(sum(tot_sales)) over(order by month
rows between unbounded preceding and unbounded following) total_sales
from orders
group by month;

--平均值
select month,
sum(tot_sales) month_sales,
avg(sum(tot_sales)) over(order by month
rows between unbounded preceding and current row) current_total_sales,
avg(sum(tot_sales)) over(order by month
rows between unbounded preceding and unbounded following) total_sales
from orders
group by month;


-------------------------------------------------------------------
--窗口函数－根据时间范围统计
-------------------------------------------------------------------
--查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。
select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null 
and order_dt between to_date('2011-09-01','yyyy-mm-dd') and to_date('2011-09-31','yyyy-mm-dd');




-------------------------------------------------------------------
--窗口函数进阶－first_value/last_value
-------------------------------------------------------------------
--显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值
select month,
first_value(sum(tot_sales)) over (order by month 
rows between 1 preceding and 1 following) prev_month,
sum(tot_sales) monthly_sales,
last_value(sum(tot_sales)) over (order by month 
 rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month 
 rows between 1 preceding and 1 following) rolling_avg
from orders
group by month 
order by month;


-------------------------------------------------------------------
--窗口函数－比较相邻记录
-------------------------------------------------------------------
--显示当月的销售额和上个月的销售额
--lag(sum(tot_sales),1)中的1表示以1月为基准。
 select  month,            
 sum(tot_sales) monthly_sales,
 lag(sum(tot_sales), 1) over (order by month) prev_month_sales
 from orders
 group by month
 order by month;






--rank、dense_rank、row_number
①对所有客户按订单总额进行排名
②按区域和客户订单总额进行排名
③找出订单总额排名前13位的客户
④找出订单总额最高、最低的客户
⑤找出订单总额排名前25%的客户
--窗口
①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额

--分析函数的应用场合
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作

--等级函数应用场合
①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险
②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录


--窗口函数应用场合：

①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔：例如在交易日之前的前30天
③通过指定一个范围值：例如所有占到当前交易量总额5%的记录



--报表函数（over函数的空括号表示该记录集的所有记录都应该被列入统计的范围）
select month,
sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month
rows between unbounded preceding and unbounded following) win_sales,
sum(sum(tot_sales)) over() rpt_sales
from orders
group by month;


--列出上一年度每个月的销售总额、年底销售额以及每个月的销售额占全年总销售额的比例
--方法一
 select all_sales.*,
            100 * round(cust_sales / region_sales, 2) || '%' Percent
  from (select o.cust_nbr customer,
                         o.region_id region,
                        sum(o.tot_sales) cust_sales,
                        sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
                from orders_tmp o
             where o.year = 2001
              group by o.region_id, o.cust_nbr) all_sales
  where all_sales.cust_sales > all_sales.region_sales * 0.2;
  
  
--方法二
 select region_id, salesperson_id, 
            sum(tot_sales) sp_sales,
            round(sum(tot_sales) / sum(sum(tot_sales)) 
                      over (partition by region_id), 2) percent_of_region
   from orders
 where year = 2001
  group by region_id, salesperson_id
  order by region_id, salesperson_id;
  
  
  
--方法三（Ratio_to_report函数允许我们计算每条记录在其对应记录集或其子集中所占的比例）
 select region_id, salesperson_id, 
             sum(tot_sales) sp_sales,
             round(ratio_to_report(sum(tot_sales)) 
                           over (partition by region_id), 2) sp_ratio
    from orders
 where year = 2001
 group by region_id, salesperson_id
 order by region_id, salesperson_id;
 
 
 
 -------------------------------------------------------------------------------------------
 --总结
 -------------------------------------------------------------------------------------------
 --统计方面： 
 Sum( ) Over ([Partition by  ] [Order by  ])
 
 Sum( ) Over ([Partition by  ] [Order by  ]  
      Rows Between   Preceding And   Following)
        
 Sum( ) Over ([Partition by  ] [Order by  ]
      Rows Between   Preceding And Current Row)
 
 Sum( ) Over ([Partition by  ] [Order by  ]
      Range Between Interval ' ' day Preceding And Interval ' ' day Following )
      
--排列方面： 
 Rank() Over ([Partition by  ] [Order by  ] [Nulls First/Last])
 
 Dense_rank() Over ([Partition by  ] [Order by  ] [Nulls First/Last])
    
 Row_number() Over ([Partition by  ] [Order by  ] [Nulls First/Last])
    
 Ntile( ) Over ([Partition by  ] [Order by  ])
 
--最大值/最小值查找方面： 
 Min( )/Max( ) Keep (Dense_rank First/Last [Partition by  ] [Order by  ])
 
--首记录/末记录查找方面：  
 First_value / Last_value(Sum( )) Over ([Partition by  ] [Order by  ]
  Rows Between   Preceding And   Following  )

--相邻记录之间比较方面： 
 Lag(Sum( ), 1) Over([Patition by  ] [Order by  ])