Oracle分析函数之开窗函数over()详解

分析函数是什么?
分析函数是Oracle专门用于 解决复杂报表统计需求 的功能强大的函数, 它可以在数据中进行分组然后计算基于组的某种统计值 ,并且每一组的每一行都可以返回一个统计值。

公司主营业务:成都网站建设、成都网站制作、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出成武免费做网站回馈大家。

          

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组, 每个分组 返回一个统计值,而分析函数采用partition by分组,并且 每组每行 都可以返回一个统计值。

              

分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

    

分析函数例子(在scott用户下模拟)

示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。

复制代码
--显示各部门员工的工资,并附带显示该部分的最高工资。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;
复制代码

运行结果:

               

示例目的:按照deptno分组,然后计算每组值的总和

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal  FROM SCOTT.EMP;

运行结果:

     

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

复制代码
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;
复制代码

运行结果:

   

示例目标:当前行至最后一行的汇总

复制代码
SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal  FROM SCOTT.EMP;
复制代码

运行结果:

   

 示例目标:当前行的上一行(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;
复制代码

运行结果:

    

示例目标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总     

复制代码
SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal  FROM SCOTT.EMP;
复制代码

运行结果:


分享名称:Oracle分析函数之开窗函数over()详解
本文网址:http://scyanting.com/article/jdejdj.html