设为首页 收藏本站
查看: 1579|回复: 0

[经验分享] Oracle over()函数实例分析

[复制链接]

尚未签到

发表于 2018-9-9 08:57:17 | 显示全部楼层 |阅读模式
  over() 函数 是对 分析函数的一种条件解释,直接点就是 给分析函数加条件吧。
  在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分析下两种函数结合over的用法。
  以下测试使用的orace默认的 scott用户下的emp表数据。
  1.sum()结合over()
  Sql代码
  select a.empno as 员工编号
  ,a.ename as 员工姓名
  ,a.deptno as 部门编号
  ,a.sal as 薪酬
  ,sum(sal) over (partition by deptno) 按部门求薪酬总和
  from scott.emp a;
  此段sql 执行的结果为:
  部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和
  7934 MILLER 10 1300 8750
  7782 CLARK 10 2450 8750
  7839 KING 10 5000 8750
  7369 SMITH 20 800 10875
  7876 ADAMS 20 1100 10875
  7566 JONES 20 2975 10875
  7788 SCOTT 20 3000 10875
  7902 FORD 20 3000 10875
  7900 JAMES 30 950 9400
  7654 MARTIN 30 1250 9400
  7521 WARD 30 1250 9400
  7844 TURNER 30 1500 9400
  7499 ALLEN 30 1600 9400
  7698 BLAKE 30 2850 9400
  可以从结果上看到sum()函数对部门区分进行了求和统计。其中“partition by”官方点的说法叫做"分区",其实就是统计的范围条件。
  下面在给把上面的sql语句改造下 给 over() 函数加上 “order by sal” 会看到一个更过瘾的效果:
  Sql代码
  select a.empno as 员工编号
  ,a.ename as 员工姓名
  ,a.deptno as 部门编号
  ,a.sal as 薪酬
  ,sum(sal) over (partition by deptno) 按部门求薪酬总和
  ,sum(sal) over (partition by deptno order by sal) 按部门累计薪酬
  from scott.emp a;
  结果为:
  部门编号 员工姓名 员工编码 薪酬 按部门求薪酬总和  按部门累计薪酬
  7934 MILLER 10 1300 8750 1300
  7782 CLARK 10 2450 8750 3750
  7839 KING 10 5000 8750 8750
  7369 SMITH 20 800 10875 800
  7876 ADAMS 20 1100 10875 1900
  7566 JONES 20 2975 10875 4875
  7788 SCOTT 20 3000 10875 10875
  7902 FORD 20 3000 10875 10875
  7900 JAMES 30 950 9400 950
  7654 MARTIN 30 1250 9400 3450
  7521 WARD 30 1250 9400 3450
  7844 TURNER 30 1500 9400 4950
  7499 ALLEN 30 1600 9400 6550
  7698 BLAKE 30 2850 9400 9400
  从结果中可以看的 加了 “order by”后对 统计进行一个累加,这里个人理解为对统计范围规定了个统计顺利,一步一步的统计。
  注:此sql语句结尾处不要加“order by”,应为使用的分析函数的(partition by deptno order by sal)
  里已经有排序的语句了,如果再在句尾添加排序子句,一致倒罢了,不一致,结果就令人费解了。
  2.rank()结合over()
  rank函数是分级函数,这个函数必须与 over 函数使用,否则会报一个"缺少窗口函数的错"。我测试sql如下:
  Sql代码
  select a.empno as 员工编号,
  a.sal as 薪资,
  a.job as 岗位,
  rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级
  from scott.emp a;
  查询结果为:
  员工编号 薪资 岗位 等级
  7902 3000 ANALYST 1
  7788 3000 ANALYST 1
  7934 1300 CLERK 1
  7876 1100 CLERK 2
  7900 950 CLERK 3
  7369 800 CLERK 4
  7566 2975 MANAGER 1
  7698 2850 MANAGER 2
  7782 2450 MANAGER 3
  7839 5000 PRESIDENT 1
  7499 1600 SALESMAN 1
  7844 1500 SALESMAN 2
  7654 1250 SALESMAN 3
  7521 1250 SALESMAN 3


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.iyunv.com/thread-568508-1-1.html 上篇帖子: oracle加固 下篇帖子: oracle查询数据库名、实例名等
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表