分析函数(一)

--Rank()返回数据项的排名,排名相同时会出现名次跳跃的情况
--Dense_Rank()返回数据项的排名,排名相同不会出现跳跃的情况
select a.prd_type_id,
       sum(a.amount),
       rank() over(order by sum(a.amount) desc) /*rank函数*/,
       dense_rank() over(order by sum(a.amount) desc) /*dense_rank函数*/
  from all_sales a
 where a.year = 2003
   and a.amount is not null
 group by a.prd_type_id
 order by a.prd_type_id
 
--使用nulls firsr 和nulls last控制空值的排名
select a.prd_type_id,
       sum(a.amount),
       rank() over(order by sum(a.amount) desc nulls last),
       dense_rank() over(order by sum(a.amount) asc nulls first)
  from all_sales a
 where a.year = 2003
 group by a.prd_type_id
 order by a.prd_type_id
 
 --分析函数与partition by子句联合使用进行分组
 select b.prd_type_id,
        b.month,
        sum(b.amount),
        rank() over(partition by c.month order by sum(b.amount) desc nulls last)
   from all_sales b
  where b.year = 2003
    and b.amount is not null
  group by b.prd_type_id, b.month
  order by b.prd_type_id, b.month
  
--分析函数与ROLLUP CUBE GROUPING SETS等操作符的联合使用
--实例1
select c.prd_type_id,
       sum(c.amount),
       rank() over(order by sum(c.amount) desc nulls last) as rk
  from all_sales c
 where c.year = 2003
 group by rollup(c.prd_type_id)
 order by c.prd_type_id
--实例2
select d.prd_type_id,
       d.emp_id,
       sum(d.amount),
       rank() over(order by sum(d.amount) desc) as rk
  from all_sales d
 where d.year = 2003
 group by cube(d.prd_type_id, d.emp_id)
 order by d.prd_type_id, d.emp_id
 
 --实例3：仅返回小计信息
select d.prd_type_id,
       d.emp_id,
       sum(d.amount),
       rank() over(order by sum(d.amount) desc) as rk
  from all_sales d
 where d.year = 2003
 group by grouping sets(d.prd_type_id, d.emp_id)
 order by d.prd_type_id, d.emp_id