阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle分析函数入门

202次阅读
没有评论

共计 1893 个字符,预计需要花费 5 分钟才能阅读完成。

一、Oracle 分析函数入门

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

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用 group by 分组,每个分组 返回一个统计值,而分析函数采用 partition by 分组,并且 每组每行 都可以返回一个统计值。

分析函数的形式
分析函数带有一个开窗函数 over(),包含三个分析子句: 分组 (partition by), 排序(order by), 窗口(rows),他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里我只说 rows 方式的窗口,range 方式和滑动窗口也不提

分析函数例子(在 scott 用户下模拟)

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
LAST_VALUE(E.SAL)
OVER(PARTITION BY E.DEPTNO
ORDER BY E.SAL ROWS
--unbounded preceding and unbouned following 针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
--unbounded:不受控制的,无限的
--preceding:在... 之前
--following:在... 之后
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
FROM EMP E;

运行结果:

Oracle 分析函数入门 

示例目的:按照 deptno 分组,然后计算每组值的总和

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
FROM SCOTT.EMP;

运行结果:

Oracle 分析函数入门

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意 ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
FROM SCOTT.EMP;

运行结果:

Oracle 分析函数入门

示例目标:当前行至最后一行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意 ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
FROM SCOTT.EMP;

运行结果:

Oracle 分析函数入门

 示例目标:当前行的上一行 (rownum-1) 到当前行的汇总

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意 ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行 (rownum-1) 到当前行的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
FROM SCOTT.EMP;
运行结果:

Oracle 分析函数入门

示例目标:当前行的上一行 (rownum-1) 到当前行的下辆行 (rownum+2) 的汇总     

SELECT EMPNO,
ENAME,
DEPTNO,
SAL,
--注意 ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行 (rownum-1) 到当前行的下辆行 (rownum+2) 的汇总
SUM(SAL) OVER(PARTITION BY DEPTNO
ORDER BY ENAME
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
FROM SCOTT.EMP;

运行结果:

Oracle 分析函数入门

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-01/139579.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计1893字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中