首页 / ORACLE / ORACLE进阶之三:分析函数
ORACLE进阶之三:分析函数
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了ORACLE进阶之三:分析函数,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3117字,纯文字阅读大概需要5分钟。
内容图文
有时候我们需要从DB中提取一些很复杂的数据,而标准SQL却对此无能为力,或者是执行效率非常的低;比如我们需要提取如下数据: 逐行显示各个部门的累计工资,每行包括部门内前面所有人的工资总和; 查找各个部门工资最高的前N个人; 语法 Function名称([参数]
有时候我们需要从DB中提取一些很复杂的数据,而标准SQL却对此无能为力,或者是执行效率非常的低;比如我们需要提取如下数据:
逐行显示各个部门的累计工资,每行包括部门内前面所有人的工资总和;
查找各个部门工资最高的前N个人;
……
语法
Function名称([参数]) OVER ([partition 子句][ order 子句] [window 子句])
OVER为分析函数的关键字,用于区别普通的聚合函数;从语法格式上区分的话,没加over()即时聚合函数,加了over()就是分析函数。
Partition 子句:Partition by exp1[ ,exp2]...;
主要用于分组,可以理解成select中的group by;不过它跟select语句后跟的group by 子句并不冲突;指定该子句之后,前面的函数起效范围就是该分组内,若不指定,则Function的起效范围是全部结果集。
Order 子句:Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last];
其参数基本与select中的order by相同;Nulls first|last是用来限定nulls在分组序列中的所在位置的,我们知道oracle中对于null的定义是未知,所以默认order by的时候nulls总会被排在最前面。如果想控制值为null的行显示位置,nulls first|last参数就能派上用场了。
Window 子句:该子句的语法比较复杂,具体可以见下图;
该子句给出了一个定义变化或者固定的数据窗口方法,分析函数将对这些数据进行操作;默认情况下,一般用不上该子句,分析函数产生一个固定的窗口,影响的数据范围是从第一行到当前行,其效果和RANGE BETWEEN UNDOUNDED PRECEDING AND CURRENT ROW一样;若需要指定操作数据为当前行及其前两行,则可以用ROWS 2 PRECEDING来实现其效果;
其中用[]标注的子句都可以为空,一个最简单的分析函数可能是COUNT(*) OVER ();
样例
逐行显示各个部门的累计工资,每行包括部门内前面所有人的工资总和:
SELECT EMP_NO,
NAME,
DEPT_NO,
SUM(SAL) OVER(PARTITION BY DEPT_NO ORDER BY EMP_NO) DEPT_SAL_SUM
FROM EMP
ORDER BY DEPT_NO, EMP_NO;
查找各个部门工资最高的前N个人:
SELECT *
FROM (SELECT DEPT_NO,
NAME,
SAL,
DENSE_RANK() OVER(PARTITION BY DEPT_NO ORDER BY SAL DESC) DR
FROM EMP)
WHERE DR <= 3
ORDER BY DEPT_NO, SAL DESC;
注意点
1、分析函数与聚合函数非常相似,不同于聚合函数的地方在于它们每个分组序列均返回多行,而聚合函数返回一行;
2、带有分析函数的SQL列表中,除了order by子句之外,分析函数将在SQL语句中最后执行;因此,分析函数只能用于select的列或order by子句,而不能用于where、group by、having之类的语句中;
3、当分析函数中使用了distinct参数时,则只能使用partition子句,而不能指定order by子句;
4、SELECT语句中的ORDER BY子句与分析函数中的order by子句是互不影响的,但一般来说两者一致比较好,若两者不一致,则意味着分析函数需要对结果集进行多次排序,这将严重降低分析函数的执行效率;
5、dense_rank在做排序时如果遇到列有重复值,则重复值所在行的序列值相同,而其后的序列值依旧递增,rank则是重复值所在行的序列值相同,但其后的序列值从+重复行数开始递增,而row_number则不管是否有重复行,序列值始终递增;
函数列表
大致有26个函数可用,其中很多都是和聚合函数同名的,比如SUM、AVG、MIN、MAX……;其他是一些提供新功能的新函数;具体的函数列表如下:
内容总结
以上是互联网集市为您收集整理的ORACLE进阶之三:分析函数全部内容,希望文章能够帮你解决ORACLE进阶之三:分析函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。