详解Oracle分析函数

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

Oracle分析函数的学习有以下几个关键点：

1.对一组数据进行计算，返回多行。

2.不需要进行多表联合，提高性能。

3.在所有表连接和所有WHERE, GROUP BY和HAVING字句之后处理，在ORDER BY子句之前处理。

4.只能位于SELECT或者ORDER BY子句。

分析函数语法:

FUNCTION_NAME(,...)

OVER

()

例:

sum(sal) over (partition by deptno order by ename) new_alias

sum就是函数名

(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)

over是一个关键字，用于标识分析函数，否则查询分析器不能区别sum()聚集函数和sum()分析函数。

partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区。

order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的。

1)FUNCTION子句

ORACLE提供了26个分析函数,按功能分5类

分析函数分类

等级(ranking)函数:用于寻找前N种查询

开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上。

例:

sum(t.sal) over (order by t.deptno,t.ename) running_total,

sum(t.sal) over (partition by t.deptno order by t.ename) department_total

制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列

例:

sum(t.sal) over () running_total2,

sum(t.sal) over (partition by t.deptno ) department_total2

制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!

LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值，为了避免数据的自连接，它们是非常用用的。

VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值

2)PARTITION子句

按照表达式分区(就是分组)，如果省略了分区子句,则全部的结果集被看作是一个单一的组。

3)ORDER BY子句

分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时，默认的窗口是全部的分区。在Order by子句后可以添加nulls last，如:order by comm desc nulls last   表示排序时忽略comm列为空的行。  

4)WINDOWING子句

用于定义分析函数将在其上操作的行的集合

Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作

默认的窗口是一个固定的窗口，仅仅在一组的第一行开始，一直继续到当前行,要使用窗口，必须使用ORDER BY子句。

根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量。

5)Rang窗口

Range 5 preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合

ANGE窗口仅对NUMBERS和DATES起作用，因为不可能从VARCHAR2中增加或减去N个单元。

另外的限制是ORDER BY中只能有一列，因而范围实际上是一维的，不能在N维空间中。

例:

avg(t.sal) over(order by t.hiredate asc range 100 preceding) 统计前100天平均工资

6)Row窗口

利用ROW分区，就没有RANGE分区那样的限制了，数据可以是任何类型，且ORDER BY 可以包括很多列。

7)Specifying窗口

UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始，并结束于正在处理的当前行。

CURRENT ROW:该窗口从当前行开始(并结束)

Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始，对RANGE来说，从从行序值小于数字表达式的当前行的值开始。

Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)

range between 100 preceding and 100 following:当前行100前,当前后100后

注意:分析函数允许你对一个数据集进排序和筛选，这是SQL从来不能实现的。除了最后的Order by子句之外，分析函数是在查询中执行的最后的操作集，这样的话，就不能直接在谓词中使用分析函数，即不能在上面使用where或having子句。

Oracle分析函数实例(在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;

运行结果：

结合上述的Oracle分析函数的应用实例，我们不难看出Oracle分析函数的作用，在提高了运算效率的同时也提高了Oracle数据库的查询效率。在本站的Oracle教程中，对Oracle数据库中的其他函数也有详细的讲解，想要深入学习的小伙伴可以结合教程学习，使我们更好更方便地使用Oracle数据库。