时间：2021-07-01 10:21:17 帮助过：17人阅读
分析函数带有一个开窗函数over() ，包含4个分析子句 : partittion by , order by ,rows , range 。使用形式over(partition by xxx order by yyy rows between zzz range between *** preceding and *** following)
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能随行变化而变化。举例如下：
3.1
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
3.2
over（order by salary range between 5 preceding and 5 following）
每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
例如:对于以下列 aa 1 2 2 2 3 4 5 6 7 9 sum(aa)over（order by aa range between 2 preceding and 2 following） 得出的结果是 AA SUM ---------------------- ------------------------------------------------------- 1 10 2 14 2 14 2 14 3 18 4 18 5 22 6 18 7 22 9 9 就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和 对于aa=2来说 ，sum=1+2+2+2+3+4=14 ； 又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；
3.3
over（order by salary rows between 2 preceding and 4 following）
每行对应的数据窗口是之前2行，之后4行
下面三条语句等效： over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following） 等效 over(partition by null)
参考：https://blog.csdn.net/zs064811/article/details/51979836
四 样例(在scott用户下模拟)
【在oracle livesql中测试，livesql.oracle.com
执行对应的sql脚本作为测试数据。
sql清单：
-- 创建部门表 create table dept( deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) ); --创建emp表 create table emp( empno number(4,0), ename varchar2(10), job varchar2(9), mgr number(4,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(2,0), constraint pk_emp primary key (empno), constraint fk_deptno foreign key (deptno) references dept (deptno) ); -- 插入dept数据 insert into DEPT (DEPTNO, DNAME, LOC) values(10, ‘ACCOUNTING‘, ‘NEW YORK‘); insert into dept values(20, ‘RESEARCH‘, ‘DALLAS‘); insert into dept values(30, ‘SALES‘, ‘CHICAGO‘); insert into dept values(40, ‘OPERATIONS‘, ‘BOSTON‘); -- 插入emp数据 insert into emp values( 7839, ‘KING‘, ‘PRESIDENT‘, null, to_date(‘17-11-1981‘,‘dd-mm-yyyy‘), 5000, null, 10 ); insert into emp values( 7698, ‘BLAKE‘, ‘MANAGER‘, 7839, to_date(‘1-5-1981‘,‘dd-mm-yyyy‘), 2850, null, 30 ); insert into emp values( 7782, ‘CLARK‘, ‘MANAGER‘, 7839, to_date(‘9-6-1981‘,‘dd-mm-yyyy‘), 2450, null, 10 ); insert into emp values( 7566, ‘JONES‘, ‘MANAGER‘, 7839, to_date(‘2-4-1981‘,‘dd-mm-yyyy‘), 2975, null, 20 ); insert into emp values( 7788, ‘SCOTT‘, ‘ANALYST‘, 7566, to_date(‘13-JUL-87‘,‘dd-mm-rr‘) - 85, 3000, null, 20 ); insert into emp values( 7902, ‘FORD‘, ‘ANALYST‘, 7566, to_date(‘3-12-1981‘,‘dd-mm-yyyy‘), 3000, null, 20 ); insert into emp values( 7369, ‘SMITH‘, ‘CLERK‘, 7902, to_date(‘17-12-1980‘,‘dd-mm-yyyy‘), 800, null, 20 ); insert into emp values( 7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, to_date(‘20-2-1981‘,‘dd-mm-yyyy‘), 1600, 300, 30 ); insert into emp values( 7521, ‘WARD‘, ‘SALESMAN‘, 7698, to_date(‘22-2-1981‘,‘dd-mm-yyyy‘), 1250, 500, 30 ); insert into emp values( 7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, to_date(‘28-9-1981‘,‘dd-mm-yyyy‘), 1250, 1400, 30 ); insert into emp values( 7844, ‘TURNER‘, ‘SALESMAN‘, 7698, to_date(‘8-9-1981‘,‘dd-mm-yyyy‘), 1500, 0, 30 ); insert into emp values( 7876, ‘ADAMS‘, ‘CLERK‘, 7788, to_date(‘13-JUL-87‘, ‘dd-mm-rr‘) - 51, 1100, null, 20 ); insert into emp values( 7900, ‘JAMES‘, ‘CLERK‘, 7698, to_date(‘3-12-1981‘,‘dd-mm-yyyy‘), 950, null, 30 ); insert into emp values( 7934, ‘MILLER‘, ‘CLERK‘, 7782, to_date(‘23-1-1982‘,‘dd-mm-yyyy‘), 1300, null, 10 );View Code
4.1 显示各部门员工的工资，并附带显示该部分的最高工资。
--显示各部门员工的工资，并附带显示该部分的最高工资。 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;
运行结果：
4.2 示例目的：按照deptno分组，然后计算每组值的总和
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal FROM SCOTT.EMP;
运行结果：
4.3 对各部门进行分组，并附带显示第一行至当前行的汇总
-- 对各部门进行分组，并附带显示第一行至当前行的汇总 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;
运行结果：
4.4 当前行至最后一行的汇总
-- 当前行至最后一行的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal FROM SCOTT.EMP;
运行结果
4.5 当前行的上一行(rownum-1)到当前行的汇总
-- 当前行的上一行(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;
运行结果
4.6 当前行的上一行(rownum-1)到当前行的下1行(rownum+1)的汇总
-- 当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总 SELECT EMPNO, ENAME, DEPTNO, SAL, --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下1行(rownum+1)的汇总 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 preceding AND 1 following) max_sal FROM SCOTT.EMP;
结果
参考：https://www.cnblogs.com/xlht/p/6261856.html
oracle分析函数
标签：log tin range acl sele var primary ada 清单
