开发者社区> 游客tmj4hgt7bfvas> 正文

Oracle-分析函数_总结

简介: Oracle-分析函数_总结
+关注继续查看

一、分析函数是什么?



分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后基于组计算某种统计值,并且每一组的每一行都可以返回一个统计值。


说白了,分析函数就是 over([query_partition_clase] order_by_clause)。比如说,我采用sum求和,rank排序等等,根据什么来呢?over提供一个窗口,使用partition by进行分组,在组内使用order by进行排序。over不能单独使用,要和分析函数:

rank(),dense_rank(),row_number()等一起使用


二、Oracle分析函数与聚合函数的区别:



分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。


三、分析函数:



用于合计的函数:


  • sum()函数;
  • rollup()函数;
  • cube()函数;
  • grouping()函数;
  • max() over;
  • min() over;
  • avg() over


用于排列的函数:


  • rank() over 函数;
  • dense_rank() over 函数;
  • row_number() over 函数;


其他:


  • lag() over;
  • lead() over

1. sum()函数:

许多分析函数同时也是聚合函数,比如sum()函数,下面这样使用就是聚合函数。

--按照月份,统计每个地区的总收入
SELECT earnmonth 月份,area 地区,SUM(personincome) 总收入 FROM earnings GROUP BY earnmonth,area;


而这样使用就是分析函数:

SELECT DISTINCT earnmonth 月份,area 地区,
       sum(personincome) OVER (PARTITION BY earnmonth,area) 总收入
       FROM earnings;


它们得出的结果是相同的,都是:


image


image.png


请注意,这里我们用到了distinct 关键字,如果不用distinct,第2个查询将返回20行数据,即earnings表的每行记录都将返回一行总收入,因为不用distinct的含义是:针对每个打工者计算他/她所在的月份和地区的总收入。

SELECT earnmonth 月份,area 地区,
       sum(personincome) OVER (PARTITION BY earnmonth,area) 总收入
       FROM earnings;


image


image.png


在这个例子中,聚合函数是更好的选择,但在另外一些情形下,我们更应该使用分析函数。下面通过几个实例来介绍排序分析函数的用途。


问题:统计每个月份,不同地区工资最高的前3名。


2. rank()函数

语法:rank() over([query_partition_clause]order_by_clause)


利用我们传统的聚合函数max可以方便地取出工资最高的一个员工,但是取出多个就无能为力了,同样,如果不分组我们可以通过排序取出工资最高的前3名,但无法实现对多个月份和地区的分组。而采用rank()分析函数,可以方便地实现我们的要求。


完整的语句如下:

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  rank() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;


结果为:


image


image.png


我们在开窗函数over()中使用earnmonth(月份)和area(地区)作为分组标志,并按照personincome(收入)倒序排列。


注意:RANK()函数有3组,分别是rank(), dense_rank(), row_number(),它们的区别是:

RANK()如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如:当第2名和第3名的利润相同时,rank的结果是1,2,2,4;而dense_rank()则不会跳过这个排名,结果是1,2,2,3;而row_number()哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4.


3. dense_rank()

语法:  dense_rank() over([query_partition_clause] order_by_clause)


完整的语句如下:

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  dense_rank() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;


结果为:


image


image.png


4. row_number()

语法:row_number() over([query_partition_clause]order_by_clause)


完整的语句如下:

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  row_number() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;


结果为:


image


image.png


5. rollup()函数:

按照月份,地区统计收入

--rollup函数:(分组统计之后,再按照月份做一个汇总)
--按照月份,统计每个地区的总收入
SELECT earnmonth,area,SUM(personincome) FROM earnings GROUP BY ROLLUP(earnmonth,area);


结果为:


image


image.png


6. cube()函数:

按照月份,地区进行收入总汇总

--cube函数:(分组统计之后,按照月份做一个汇总,再按照地区做一个汇总,最后再来一个收入的总汇总)
SELECT earnmonth,area,sum(personincome)
FROM earnings
GROUP BY cube(earnmonth,area) ORDER by earnmonth,area NULLS last;


结果为:


image


image.png


7. grouping()函数:


在以上例子中,是用rollup()和cube()函数都会对结果集产生null,这时候可用grouping函数来确认该记录是由哪个字段得出来的.


grouping函数用法,带一个参数,参数为字段名,如果当前行是由rollup或者cube汇总得来的,结果就返回1,反之返回0.


完整语句如下:

SELECT decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,
decode(grouping(area),1,'所有地区',area) 地区,SUM(personincome)
FROM earnings
GROUP BY cube(earnmonth,area) ORDER by earnmonth,area NULLS last;


结果为:


image


image.png


8. max(),min(),avg()和sum()函数综合运用

安装月份和地区统计打工收入最高值,最低值,平均值和总额.

SELECT DISTINCT earnmonth 月份,area 地区,
       MAX(personincome) over(PARTITION BY earnmonth,area) 最高值,
       min(personincome) OVER(PARTITION BY earnmonth,area) 最低值,
       AVG(personincome) over(PARTITION BY earnmonth,area) 平均值,
       sum(personincome) over(PARTITION BY earnmonth,area) 总额
       FROM earnings;


以上语句统计结果和如下语句使用group by的查询结果一样:

SELECT earnmonth 月份,area 地区,
       MAX(personincome) 最高值,
       min(personincome) 最低值,
       AVG(personincome) 平均值,
       sum(personincome) 总额
       FROM earnings GROUP BY earnmonth,area;


9. lag( )和lead( )函数


说明: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就是用于处理这样的信息,默认为空。


统计每个打工者上个月和下个月有没有赚钱(personincome大于0即为赚钱):

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


结果为:


image


image.png




实验数据:



1.建表

create table earnings -- 打工赚钱表 
( 
  earnmonth varchar2(6), -- 打工月份 
  area varchar2(20), -- 打工地区 
  sno varchar2(10), -- 打工者编号 
  sname varchar2(20), -- 打工者姓名 
  times int, -- 本月打工次数 
  singleincome number(10,2), -- 每次赚多少钱 
  personincome number(10,2) -- 当月总收入 
) ;


2.插入实验数据

insert into earnings values('200912','北平','511601','大魁',11,30,11*30); 
insert into earnings values('200912','北平','511602','大凯',8,25,8*25); 
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25); 
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25); 
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11); 
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25); 
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67); 
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33); 
insert into earnings values('200912','金陵','511304','小俐',0,18,0); 
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88); 
insert into earnings values('201001','北平','511601','大魁',0,30,0); 
insert into earnings values('201001','北平','511602','大凯',14,25,14*25); 
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25); 
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25); 
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11); 
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25); 
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67); 
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33); 
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18); 
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88); 
commit;


3.查询展示全表

SELECT * FROM earnings;


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23538 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22248 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18832 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
14698 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
22066 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
36365 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
15520 0
131
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载