Oracle分析函数学习


0、建表及插入测试数据

--CREATE TEST TABLE AND INSERT TEST DATA.   

  1. create table students   
  2. (id number(15,0),   
  3. area varchar2(10),   
  4. stu_type varchar2(2),   
  5. score number(20,2));   
  6.   
  7. insert into students values(1, '111', 'g', 80 );   
  8. insert into students values(1, '111', 'j', 80 );   
  9. insert into students values(1, '222', 'g', 89 );   
  10. insert into students values(1, '222', 'g', 68 );   
  11. insert into students values(2, '111', 'g', 80 );   
  12. insert into students values(2, '111', 'j', 70 );   
  13. insert into students values(2, '222', 'g', 60 );   
  14. insert into students values(2, '222', 'j', 65 );   
  15. insert into students values(3, '111', 'g', 75 );   
  16. insert into students values(3, '111', 'j', 58 );   
  17. insert into students values(3, '222', 'g', 58 );   
  18. insert into students values(3, '222', 'j', 90 );   
  19. insert into students values(4, '111', 'g', 89 );   
  20. insert into students values(4, '111', 'j', 90 );   
  21. insert into students values(4, '222', 'g', 90 );   
  22. insert into students values(4, '222', 'j', 89 );   
  23. commit;   
  24.   
  25. col score format 999999999999.99  

1、GROUP BY子句的增强

A、GROUPING SETS 

 
  1. select id,area,stu_type,sum(score) score    
  2. from students   
  3. group by grouping sets((id,area,stu_type),(id,area),id)   
  4. order by id,area,stu_type;  

--------理解grouping sets

select a, b, c, sum( d ) from t

group by grouping sets ( a, b, c )

等效于

select * from (

select a, null, null, sum( d ) from t group by a

union all

select null, b, null, sum( d ) from t group by b

union all

select null, null, c, sum( d ) from t group by c

)

B、ROLLUP

   

  1. select id,area,stu_type,sum(score) score    
  2. from students   
  3. group by rollup(id,area,stu_type)   
  4. order by id,area,stu_type;  

--------理解rollup

select a, b, c, sum( d )

from t

group by rollup(a, b, c);

等效于

select * from (

select a, b, c, sum( d ) from t group by a, b, c

union all

select a, b, null, sum( d ) from t group by a, b

union all

select a, null, null, sum( d ) from t group by a

union all

select null, null, null, sum( d ) from t

)

C、CUBE 

 
  1. select id,area,stu_type,sum(score) score    
  2. from students   
  3. group by cube(id,area,stu_type)   
  4. order by id,area,stu_type;  

--------理解cube

select a, b, c, sum( d ) from t

group by cube( a, b, c)

等效于

select a, b, c, sum( d ) from t

group by grouping sets(

( a, b, c ),

( a, b ), ( a ), ( b, c ),

( b ), ( a, c ), ( c ),

() )

D、GROUPING函数

从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列! 

 
  1. select decode(grouping(id),1,'all id',id) id,   
  2. decode(grouping(area),1,'all area',to_char(area)) area,   
  3. decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,   
  4. sum(score) score   
  5. from students   
  6. group by cube(id,area,stu_type)   
  7. order by id,area,stu_type;   

2、OVER()函数的使用

A、RANK()、DENSE_RANK() 、ROW_NUMBER()、CUME_DIST()、MAX()、AVG() 

 
  1. break on id skip 1   
  2. select id,area,score from students order by id,area,score desc;   
  3.   
  4. select id,rank() over(partition by id order by score desc) rk,score from students;   
  5.   
  6. --允许并列名次、名次不间断   
  7. select id,dense_rank() over(partition by id order by score desc) rk,score from students;   
  8.   
  9. --即使SCORE相同，ROW_NUMBER()结果也是不同   
  10. select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;   
  11.   
  12. select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number    
  13. row_number() over (order by id) rn,id,area,score from students;   
  14.   
  15. select id,max(score) over(partition by id order by score desc) as mx,score from students;   
  16.   
  17. select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别   
  18.   
  19. --按照ID求AVG   
  20. select id,avg(score) over(partition by id order by score desc rows between unbounded preceding    
  21. and unbounded following ) as ag,score from students;  

B、SUM() 

 
  1. select id,area,score from students order by id,area,score desc;   
  2.   
  3. select id,area,score,   
  4. sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和   
  5. sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)   
  6. 100*round(score/sum(score) over (),4) "份额(%)"  
  7. from students;   
  8.   
  9. select id,area,score,   
  10. sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和   
  11. sum(score) over (partition by id) id总和, --各id的分数总和   
  12. 100*round(score/sum(score) over (partition by id),4) "id份额(%)",   
  13. sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)   
  14. 100*round(score/sum(score) over (),4) "份额(%)"  
  15. from students;  

C、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据 

 
  1. select id,lag(score,1,0) over(order by id) lg,score from students;   
  2.   
  3. select id,lead(score,1,0) over(order by id) lg,score from students;  

D、FIRST_VALUE()、LAST_VALUE() 

 
  1. select id,first_value(score) over(order by id) fv,score from students;   
  2. select id,last_value(score) over(order by id) fv,score from students;    
  3.   
  4. --而对于last_value() over(order by id),结果是有问题的，因为我们没有按照id分区，所以应该出来的效果应该全部是90（最后一条）   
  5. --再看个例子   
  6. select id,last_value(score) over(order by rownum),score from students;   
  7.   
  8. --当使用last_value分析函数的时候，缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前行向前进行比较，所以会出现上边的结果。加上如下的参数，结果就正常了。呵呵。默认窗口范围为所有处理结果。   
  9. select id,last_value(score) over(order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students;  

相关内容