oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.
--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)
--所有人的总工资
select a.empno, a.ename, sum(a.sal) over() total from emp a;
EMPNO ENAME TOTAL
7369 SMITH 29025
7499 ALLEN 29025
7521 WARD 29025
7566 JONES 29025
7654 MARTIN 29025
7698 BLAKE 29025
7782 CLARK 29025
7788 SCOTT 29025
7839 KING 29025
7844 TURNER 29025
7876 ADAMS 29025
7900 JAMES 29025
7902 FORD 29025
7934 MILLER 29025
--2、over(partition by ...) 分组统计
--统计部门的平均工资
select a.empno,
a.ename,
b.dname,
to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg
from emp a, dept b
where a.deptno = b.deptno;
EMPNO ENAME DNAME DEPT_AVG
7934 MILLER ACCOUNTING $2,916.67
7839 KING ACCOUNTING $2,916.67
7782 CLARK ACCOUNTING $2,916.67
7876 ADAMS RESEARCH $2,175.00
7902 FORD RESEARCH $2,175.00
7566 JONES RESEARCH $2,175.00
7369 SMITH RESEARCH $2,175.00
7788 SCOTT RESEARCH $2,175.00
7521 WARD SALES $1,566.67
7844 TURNER SALES $1,566.67
7499 ALLEN SALES $1,566.67
7900 JAMES SALES $1,566.67
7698 BLAKE SALES $1,566.67
7654 MARTIN SALES $1,566.67
--查询出管理员工人数最多的人的名字和他管理的人的名字
select b.ename, t.ename, t.mgr, t.cnt
from (select a.empno,
a.ename,
a.mgr,
count(1) over(partition by a.mgr) cnt
from emp a) t,
emp b
where t.mgr = b.empno;
ENAME ENAME MGR CNT
JONES SCOTT 7566 2
JONES FORD 7566 2
BLAKE WARD 7698 5
BLAKE TURNER 7698 5
BLAKE ALLEN 7698 5
BLAKE JAMES 7698 5
BLAKE MARTIN 7698 5
CLARK MILLER 7782 1
SCOTT ADAMS 7788 1
KING BLAKE 7839 3
KING JONES 7839 3
KING CLARK 7839 3
FORD SMITH 7902 1
--3、over(order by ...) 排序统计
select a.empno,
a.deptno,
a.ename,
a.sal,
sum(a.sal) over(order by a.ename) sum
from emp a;
EMPNO DEPTNO ENAME SAL SUM
7876 20 ADAMS 1100.00 1100
7499 30 ALLEN 1600.00 2700
7698 30 BLAKE 2850.00 5550
7782 10 CLARK 2450.00 8000
7902 20 FORD 3000.00 11000
7900 30 JAMES 950.00 11950
7566 20 JONES 2975.00 14925
7839 10 KING 5000.00 19925
7654 30 MARTIN 1250.00 21175
7934 10 MILLER 1300.00 22475
7788 20 SCOTT 3000.00 25475
7369 20 SMITH 800.00 26275
7844 30 TURNER 1500.00 27775
7521 30 WARD 1250.00 29025
--4、over(partition by ... order by ...) 分组排序统计
--统计各部门薪水前三名的人员
select t.*
from (select rank() over(partition by b.dname order by a.sal desc) rk,
a.empno,
a.ename,
b.dname,
a.sal
from emp a, dept b
where a.deptno = b.deptno) t
where t.rk <= 3;
RK EMPNO ENAME DNAME SAL
1 7839 KING ACCOUNTING 5000.00
2 7782 CLARK ACCOUNTING 2450.00
3 7934 MILLER ACCOUNTING 1300.00
1 7902 FORD RESEARCH 3000.00
1 7788 SCOTT RESEARCH 3000.00
3 7566 JONES RESEARCH 2975.00
1 7698 BLAKE SALES 2850.00
2 7499 ALLEN SALES 1600.00
3 7844 TURNER SALES 1500.00
分享到:
相关推荐
ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧ORACLE的OVER函数使用技巧
oracle的分析函数over 及开窗函数
介绍ORACLE分析函数的一些例子,对开发报表非常有帮助
主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () ...
ORACLE常用分析函数说明,oracle优化
1 Oracle开发专题之:分析函数 OVER 2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题...
oracle分析函数,oracle分析函数over_及开窗函数用法。
文档详细介绍了oracle的分析函数,包括功能说明、sql示例等。分析函数功能强大,在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂,最好使用的时候,参考文档
oracle的分析函数汇总oracle的分析函数汇总
NULL 博文链接:https://yangkai.iteye.com/blog/754447
oracle 9i 分析函数参考手册.doc. 希望大家作为参考方便使用
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数,内置函数
ORACLE分析函数大全 包括大部分常用的分析函数
Oracle开发的over函数
关于ORACLE分析函数的教程,教程描述描述清晰
分析函数是 Oracle 专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计。本文总结了若干分析函数的使用方式,供大家学习参考,有...
Oracle自定义聚合函数,分析函数.
本文通过实例代码给大家介绍了oracle百分比分析函数RATIO_TO_REPORT() OVER(),代码简单易懂,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
Oracle分析函数,常用分析函数应有尽有
Oracle 9i 分析函数参考手册 Oracle 9i 分析函数参考手册 Oracle 9i 分析函数参考手册