ORACLE统计分析函数

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了ORACLE统计分析函数，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6963字，纯文字阅读大概需要10分钟。

内容图文

(  

  earnmonth varchar2(6), -- 打工月份  

  area varchar2(20), -- 打工地区  

  sno varchar2(10), -- 打工者编号  

  sname varchar2(20), -- 打工者姓名  

  times int, -- 本月打工次数  

  singleincome number(10,2), -- 每次赚多少钱  

  personincome number(10,2) -- 当月总收入  

)  

然后插入实验数据：

Sql代码  

1. insert into earnings values(‘200912‘,‘北平‘,‘511601‘,‘大魁‘,11,30,11*30);  
2. insert into earnings values(‘200912‘,‘北平‘,‘511602‘,‘大凯‘,8,25,8*25);  
3. insert into earnings values(‘200912‘,‘北平‘,‘511603‘,‘小东‘,30,6.25,30*6.25);  
4. insert into earnings values(‘200912‘,‘北平‘,‘511604‘,‘大亮‘,16,8.25,16*8.25);  
5. insert into earnings values(‘200912‘,‘北平‘,‘511605‘,‘贱敬‘,30,11,30*11);  
6.   
7. insert into earnings values(‘200912‘,‘金陵‘,‘511301‘,‘小玉‘,15,12.25,15*12.25);  
8. insert into earnings values(‘200912‘,‘金陵‘,‘511302‘,‘小凡‘,27,16.67,27*16.67);  
9. insert into earnings values(‘200912‘,‘金陵‘,‘511303‘,‘小妮‘,7,33.33,7*33.33);  
10. insert into earnings values(‘200912‘,‘金陵‘,‘511304‘,‘小俐‘,0,18,0);  
11. insert into earnings values(‘200912‘,‘金陵‘,‘511305‘,‘雪儿‘,11,9.88,11*9.88);  
12.   
13. insert into earnings values(‘201001‘,‘北平‘,‘511601‘,‘大魁‘,0,30,0);  
14. insert into earnings values(‘201001‘,‘北平‘,‘511602‘,‘大凯‘,14,25,14*25);  
15. insert into earnings values(‘201001‘,‘北平‘,‘511603‘,‘小东‘,19,6.25,19*6.25);  
16. insert into earnings values(‘201001‘,‘北平‘,‘511604‘,‘大亮‘,7,8.25,7*8.25);  
17. insert into earnings values(‘201001‘,‘北平‘,‘511605‘,‘贱敬‘,21,11,21*11);  
18.   
19. insert into earnings values(‘201001‘,‘金陵‘,‘511301‘,‘小玉‘,6,12.25,6*12.25);  
20. insert into earnings values(‘201001‘,‘金陵‘,‘511302‘,‘小凡‘,17,16.67,17*16.67);  
21. insert into earnings values(‘201001‘,‘金陵‘,‘511303‘,‘小妮‘,27,33.33,27*33.33);  
22. insert into earnings values(‘201001‘,‘金陵‘,‘511304‘,‘小俐‘,16,18,16*18);  
23. insert into earnings values(‘201001‘,‘金陵‘,‘511305‘,‘雪儿‘,11,9.88,11*9.88);  

然后看看刚刚建好的库：

Sql代码  

1. select * from earnings;  

 

(1)sum函数，统计总合
按照月份，统计每個地区的总收入

Sql代码  

1. select earnmonth, area, sum(personincome)  
2. from earnings  
3. group by earnmonth,area;  

 查看结果如下：

 

(2)rollup函数
按照月份，地区统计收入

Sql代码  

1. select earnmonth, area, sum(personincome)  
2. from earnings  
3. group by rollup(earnmonth,area);  

 查看结果如下：

 

(3)cube函数
按照月份，地区进行收入总汇总

Sql代码  

1. select earnmonth, area, sum(personincome)  
2. from earnings  
3. group by cube(earnmonth,area)  
4. order by earnmonth,area nulls last;  

 结果如下：

 

小结：sum是统计求和的函数。
group by 是分组函数，按照earnmonth和area先后次序分组。
以上三例都是先按照earnmonth分组，在earnmonth内部再按area分组，并在area组内统计personincome总合。
group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。
另外那个 nulls last 是把空值放在最后。

rollup和cube区别：
如果是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUP BY操作。

 

(4)grouping函数
在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认
该记录是由哪个字段得出来的
grouping函数用法，带一个参数，参数为字段名，如果当前行是由rollup或者cube汇总得来的，结果就返回1，反之返回0

Sql代码  

1. select decode(grouping(earnmonth),1,‘所有月份‘,earnmonth) 月份,  
2.        decode(grouping(area),1,‘全部地区‘,area) 地区, sum(personincome) 总金额  
3. from earnings  
4. group by cube(earnmonth,area)  
5. order by earnmonth,area nulls last;  

 查看结果如下：

 

(5)rank() over开窗函数:则是重复值所在行的序列值相同，但其后的序列值从重复行数开始递增
按照月份、地区，求打工收入排序

Sql代码  

1. select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
2.        rank() over (partition by earnmonth,area order by personincome desc) 排名  
3. from earnings;  

 查看结果：

 

(6)dense_rank() over开窗函数:在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增
按照月份、地区，求打工收入排序2

Sql代码  

1. select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
2.        dense_rank() over (partition by earnmonth,area order by personincome desc) 排名  
3. from earnings;  

 结果如下：

 

(7)row_number() over：不管是否有重复行，(分组内)序列值始终递增
按照月份、地区，求打工收入排序3

Sql代码      select earnmonth 月份,area 地区,sname 打工者, personincome 收入,          row_number() over (partition by earnmonth,area order by personincome desc) 排名     from earnings;  

 结果如下：

 

通过(5)(6)(7)发现rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number最牛，即使两个数据相同，排名也不一样。

 

(8)sum累计求和
根据月份求出各个打工者收入总和，按照收入由少到多排序

Sql代码  

1. select earnmonth 月份,area 地区,sname 打工者,   
2.        sum(personincome) over (partition by earnmonth,area order by personincome) 总收入  
3. from earnings;  

 查看结果如下：

 

(9)max，min，avg和sum函数综合运用
按照月份和地区求打工收入最高值，最低值，平均值和总额

Sql代码  

1. select distinct earnmonth 月份, area 地区,  
2.        max(personincome) over(partition by earnmonth,area) 最高值,  
3.        min(personincome) over(partition by earnmonth,area) 最低值,  
4.        avg(personincome) over(partition by earnmonth,area) 平均值,  
5.        sum(personincome) over(partition by earnmonth,area) 总额  
6. from earnings;  

 结果如下：

 

(10)lag和lead函数
求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

Sql代码  

1. select earnmonth 本月,sname 打工者,  
2.        lag(decode(nvl(personincome,0),0,‘没赚‘,‘赚了‘),1,0) over(partition by sname order by earnmonth) 上月,  
3.        lead(decode(nvl(personincome,0),0,‘没赚‘,‘赚了‘),1,0) over(partition by sname order by earnmonth) 下月  
4. from earnings;  

 

 

说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦，惭愧，我用子查询有的还查不出来呢。

 

语法如下：

lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。

 

再讲讲所谓的开窗函数，依本人遇见，开窗函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就用partition by，然后在组内根据什么什么进行内部排序，就用 order by。

ORACLE统计分析函数

标签：

本文系统来源：http://www.cnblogs.com/kkfish/p/5235615.html

内容总结

以上是互联网集市为您收集整理的ORACLE统计分析函数全部内容，希望文章能够帮你解决ORACLE统计分析函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---