Welcome 微信登录

首页 / 数据库 / MySQL / 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 setsselect a, b, c, sum（ d ） from tgroup by grouping sets （ a, b, c ）等效于select * from （select a, null, null, sum（ d ） from t group by aunion allselect null, b, null, sum（ d ） from t group by b union allselect 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;  
--------理解rollupselect a, b, c, sum（ d ）from tgroup by rollup（a, b, c）;等效于select * from （select a, b, c, sum（ d ） from t group by a, b, c union allselect a, b, null, sum（ d ） from t group by a, bunion allselect a, null, null, sum（ d ） from t group by aunion allselect 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;  
--------理解cubeselect a, b, c, sum（ d ） from tgroup by cube（ a, b, c）等效于select a, b, c, sum（ d ） from tgroup 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;  
Oracle中LIKE语句优化在Oracle中得到分页数据相关资讯      Oracle教程 
  • Oracle中纯数字的varchar2类型和  （07/29/2015 07:20:43）
  • Oracle教程：Oracle中查看DBLink密  （07/29/2015 07:16:55）
  • [Oracle] SQL*Loader 详细使用教程  （08/11/2013 21:30:36）
  • Oracle教程：Oracle中kill死锁进程  （07/29/2015 07:18:28）
  • Oracle教程：ORA-25153 临时表空间  （07/29/2015 07:13:37）
  • Oracle教程之管理安全和资源  （04/08/2013 11:39:32）
本文评论 查看全部评论 （0）
表情： 姓名： 字数