当前位置:Gxlcms > 数据库问题 > oracle分析函数

oracle分析函数

时间: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)overorder 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=99-1<=aa<=9+2 只有9一个数,所以sum=9

3.3

over(order by salary rows between 2 preceding and 4 following)
每行对应的数据窗口是之前2行,之后4行 

下面三条语句等效:           
     overorder by salary rows between unbounded preceding and unbounded following)
          每行对应的数据窗口是从第一行到最后一行,等效:
     overorder 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   清单   

人气教程排行