ORACLE分析函数实战(Analytical Functions)_PART2

接PART1：

1.1 背景知识

分析函数是oracle8.1.6加入的，oracle后续版本扩展了分析函数的数目。分析函数可以分为四类：排名函数(ranking)，聚合函数(aggregate)，行比较函数(row comparison)，统计函数(statistical)。本节内容主要围绕这四个方面讲解分析函数的原理，特点，使用场合，注意点等。对常用分析函数重点分析，一般的分析函数作为了解简单介绍。

使用分析函数的好处很多,可以解决复杂问题的分析，代替复杂的查询操作，比如没有分析函数之前，我们要解决一个复杂的问题，可能需要用到自连接，子查询或内嵌视图，甚至可能要使用存储过程，但是使用了分析函数之后，一句简单的sql就可以解决这些问题，而且oracle对分析函数做了内部的优化处理，性能比使用复杂查询有很大的提升。

1.2 分析函数基础

 1.2.1 分析函数基本原理

Oracle分析函数(Analytic Function)依赖于对行的分组，是对行进行分组之后，计算这些分组的值。所以，分析函数是对查询的结果集按照分析子句和分析函数的规则来进行进一步的操作。首先按照分析子句对行进行分组(大组)，然后扫描各个分组，被扫描到的当前行会对应于一个分组内的滑动的窗口(小组)之内，当前行对应的这个滑动的窗口范围由分析子句决定，这个范围可以是物理度量的也可以是逻辑度量的，最后对当前行对应的窗口使用分析函数，计算这个组(小组)的值。所以分析函数对于各个组可以产生多个分组的值，而聚合函数对于各个分组只能产生唯一值，这是两者之间的典型区别。

  名词：
  分析子句(analytic clause),最多由三个部分组成，依次顺序是partition子句，order by子句，window子句。
  partition子句根据分区键确定如何对行进行分区，如果没有partition子句，那么所有行为一个分区。
  order by子句确定每个分区内的排序规则。
  Window子句，确定当前行对应的窗口范围，从而用分析函数计算当前行对应的窗口的值，如果没有window子句（只允许有window的分析函数），而有order by那么默认窗口范围是当前分区内的首行到当前行，如果没有order by，那么默认是当前分区内的首行到末行。没有window的分析函数讨论window没有意义，规则不同。

如：
select id,last_name,salary,dept_id,row_number() over(partition by dept_id order by id) from s_emp;

  这是一个计算排名的分析函数，这里使用了partition子句对行进行分区(大组,如果没有partition子句，那么整个行是一个大组),partition子句按照部门编号分为多个大组。Order by子句确定组内的排序,然后对各个大组扫描，row_number没有window子句，那么当前行的窗口范围就是首先在一个分区之内，然后当前行的分析函数计算范围是该组第一行到当前行。对这个范围使用分析函数，计算组的值。结果如下：

可以看出，id为4的组只有一行，所以分析函数的结果为1，id为3的和id为11的同组。Id=3的计算为1，id=11的窗口对应范围在id=3到当前行，所以计算结果为2。

分析函数用于计算排名，累积值，移动值，中间值，平均值，输出集合报表等。常用于复杂的分析，比如在数据仓库系统中进行OLAP(On-Line Analytical Processing)操作，使用分析函数可以更好地提供对决策的支持。

分析函数限制和注意点：
  分析函数是在一个查询中最后计算的，除了order by(在语句最后order by之前)，所以在同一个查询层次中,分析函数只可能出现在order by，select中，其他where,group by,having等处不能出现分析函数。当然分析函数还有其他的限制，比如不能嵌套，以及其他的使用，比如可以用于子查询等，这将在后面详细分析。

另外要注意一点，分析函数是在一个查询中最后计算的，除了order by。所以分析函数是在order by之前和select显示结果之前(from之后)计算好的，在同一个查询层次中只能出现在order by和select中，不能出现在where，group by，having等其他地方，但是如果一个查询含有嵌套查询，比如where中有子查询，那么这个子查询我们可以使用分析函数。所以说上述限制只是在同一层次的查询中，对子查询不使用这个限制。还要注意一点，分析函数既然是最后计算的，在order by之前，如果一个select中有多个分析函数，最后没有order by显示排序的话，可能前面的分析函数会出现乱序的情况，从左到右后面的那个分析函数会重新组织前面分析函数的结果顺序，但是不改变其查询的结果,所以对于多个分析函数的处理结果，一般还需要显示order by一下，另外分析子句中的order by只是组内排序，而不是最终结果排序，当然没有partition的时候是对最终结果排序，另外分析子句中的order by如果出现键值相同的话不保证排序，详细见后面说明。

例如：

select id,last_name, salary,sum(salary) over(order by id,last_name),sum(salary) over(order by last_name,id) from s_emp;

这条语句使用了两个看似相同的分析函数，但是内部排序规则不一样，第2个分析函数的结果会对整个结果重新排序，先按照last_name，再按照id排序，所以第1个是先按id，再按last_name排序的结果不变，但是位置发生了变化，由第2个指定了排序规则，下面看这个结果：

由上面的图可以看出，最终结果按照第2个分析函数排序，这两个分析函数的最大值都是31377，第1个分析函数的最大值id为25，因为先按照id排序，id为25也是所有id最大的，第2个按last_name先排序，那么velasquez这个last_name也是最大的。如果显示指定order by，则最终按照order by的排序情况重新组织结果。

下面先用一个实例来说明一下分析函数的基本工作情况：

SQL> desc employees

Name      Type         Nullable Default Comments

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

MANAGE_ID NUMBER(10)   Y                  上级经理ID    

LAST_NAME VARCHAR2(10) Y                   last_name

HIRE_DATE DATE         Y                   雇佣日期

SALARY    NUMBER(10,2) Y                   薪水值

  问题：将雇员employees按照对应的经理分组，并且按照hire_date从小到大排序，前面加上序号，每个对应经理的初始序号为1，然后递增？
  解决：使用分析函数row_number()，按照条件分组排序确定计算范围的window，然后对window使用分析函数计算组值。

SQL> select row_number() over(partition by manage_id order by hire_date) rm,manage_id,last_name,hire_date,salary from employees;

 

        RM   MANAGE_ID LAST_NAME  HIRE_DATE         SALARY

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

         1           1 dj4        2008/6/3         3000.00

         2           1 dj2        2008/6/4         4000.00

         3           1 dj1        2008/6/5         3000.00

         1           2 wj4        2008/6/3         3000.00

         2           2 wj2        2008/6/4         4000.00

         3           2 wj1        2008/6/5         3000.00

下面简单分析一下这句sql：

未完待续，见PART3：http://blog.chinaunix.net/uid-7655508-id-5834821.html