分析函数(一)
1 --Rank()返回数据项的排名,排名相同时会出现名次跳跃的情况 2 --Dense_Rank()返回数据项的排名,排名相同不会出现跳跃的情况 3 select a.prd_type_id, 4 sum(a.amount), 5 rank() over(order by sum(a.amount) desc) /*rank函数*/, 6 dense_rank() over(order by sum(a.amount) desc) /*dense_rank函数*/ 7 from all_sales a 8 where a.year = 2003 9 and a.amount is not null 10 group by a.prd_type_id 11 order by a.prd_type_id 12 13 --使用nulls firsr 和nulls last控制空值的排名 14 select a.prd_type_id, 15 sum(a.amount), 16 rank() over(order by sum(a.amount) desc nulls last), 17 dense_rank() over(order by sum(a.amount) asc nulls first) 18 from all_sales a 19 where a.year = 2003 20 group by a.prd_type_id 21 order by a.prd_type_id 22 23 --分析函数与partition by子句联合使用进行分组 24 select b.prd_type_id, 25 b.month, 26 sum(b.amount), 27 rank() over(partition by c.month order by sum(b.amount) desc nulls last) 28 from all_sales b 29 where b.year = 2003 30 and b.amount is not null 31 group by b.prd_type_id, b.month 32 order by b.prd_type_id, b.month 33 34 --分析函数与ROLLUP CUBE GROUPING SETS等操作符的联合使用 35 --实例1 36 select c.prd_type_id, 37 sum(c.amount), 38 rank() over(order by sum(c.amount) desc nulls last) as rk 39 from all_sales c 40 where c.year = 2003 41 group by rollup(c.prd_type_id) 42 order by c.prd_type_id 43 --实例2 44 select d.prd_type_id, 45 d.emp_id, 46 sum(d.amount), 47 rank() over(order by sum(d.amount) desc) as rk 48 from all_sales d 49 where d.year = 2003 50 group by cube(d.prd_type_id, d.emp_id) 51 order by d.prd_type_id, d.emp_id 52 53 --实例3:仅返回小计信息 54 select d.prd_type_id, 55 d.emp_id, 56 sum(d.amount), 57 rank() over(order by sum(d.amount) desc) as rk 58 from all_sales d 59 where d.year = 2003 60 group by grouping sets(d.prd_type_id, d.emp_id) 61 order by d.prd_type_id, d.emp_id
简单做人 从容做事 快乐生活