一、Oracle分析函数入门

一、Oracle分析函数入门

分析函数是什么？

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

分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

注：窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

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

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

--显示各部门员工的工资，并附带显示该部分的最高工资。

SELECTE.DEPTNO,

E.EMPNO,

E.ENAME,

E.SAL,

LAST_VALUE(E.SAL)

OVER(PARTITIONBYE.DEPTNO

ORDERBYE.SAL ROWS

--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录

--unbounded：不受控制的，无限的

--preceding：在...之前

--following：在...之后

BETWEENUNBOUNDED PRECEDINGANDUNBOUNDED FOLLOWING) MAX_SAL

FROMEMP E;

运行结果：

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

SELECTEMPNO,

ENAME,

DEPTNO,

SAL,

SUM(SAL)OVER(PARTITIONBYDEPTNOORDERBYENAME) max_sal

FROMSCOTT.EMP;

运行结果：

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

SELECTEMPNO,

ENAME,

DEPTNO,

SAL,

--注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总

SUM(SAL)OVER(PARTITIONBYDEPTNO

ORDERBYENAME

ROWSBETWEENUNBOUNDED PRECEDINGANDCURRENTROW) max_sal

FROMSCOTT.EMP;

运行结果：

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

SELECTEMPNO,

ENAME,

DEPTNO,

SAL,

--注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总

SUM(SAL)OVER(PARTITIONBYDEPTNO

ORDERBYENAME

ROWSBETWEENCURRENTROWANDUNBOUNDED FOLLOWING) max_sal

FROMSCOTT.EMP;

运行结果：

示例目标：当前行的上一行(rownum-1)到当前行的汇总

SELECTEMPNO,

ENAME,

DEPTNO,

SAL,

--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总

SUM(SAL)OVER(PARTITIONBYDEPTNO

ORDERBYENAME ROWS

BETWEEN1PRECEDINGANDCURRENTROW) max_sal

FROMSCOTT.EMP;

运行结果：

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

SELECTEMPNO,

ENAME,

DEPTNO,

SAL,

--注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

SUM(SAL)OVER(PARTITIONBYDEPTNO

ORDERBYENAME

ROWSBETWEEN1PRECEDINGAND2FOLLOWING) max_sal

FROMSCOTT.EMP;

运行结果：

---------------------------------------------------------------------------------------

Blog：http://www.cnblogs.com/linjiqin/

J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(满)