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

接PART2：http://blog.chinaunix.net/uid-7655508-id-5834764.html

1.2.2 分析函数语法结构
  要使用分析函数，首先必须了解分析函数的语法结构，基本语法结构如下图所示：

  从上图可以看出，分析函数是由函数名，参数，over关键字，over后面的括号内指定分析子句，分析子句可能由partition子句，order by子句，window子句按顺序排列组成。

分析函数的语法含义和使用注意点：

 

参数：

 

1.分析函数的参数：0-3个参数，是指分析函数接受数字类型或可以隐性转为数字类型的非数字类型。自动转为数字类型的规则根据数字类型的优先级确定，oracle数字类型的优先级别依次是binary doubleàbinary floatànumber。所以最先转为binary double类型。另外分析函数的返回值也是数字类型。(除个别接受其他类型参数，如first_value和last_value)

分析子句：

 

2.通过over关键字来区分分析函数是操作一个查询结果集。也就是说，分析函数是在from，where，group by，having之后才开始工作的。出现在最后order by和select之前，分析函数也可以用于子查询，用于过滤父查询的查询结果。分析函数只允许出现在order by和select中，只是针对同一级查询。

3.分析函数不能嵌套。意思是在同一个分析函数中，分析子句中不能再使用分析函数，但是可以将分析函数放到子查询中，然后父查询的分析函数操作这个子查询的分析函数的返回结果。

4.分析子句顺序是partition子句order by子句window子句。其中有的分析函数必须有order by子句，另外有window子句必须要有order by子句。还有其他的一些限制，后面详细介绍。当然有的分析函数也可以不需要分析子句，但是意义不大。如：
select id,last_name,salary,dept_id,sum(salary) over() from s_emp;--正确的
相当于所有行为一组，没有顺序，窗口范围是首行到末行，所以分析函数对所有行的处理结果一致，等于于sum(salary)。

5.用户自定义分析函数user-defined aggregate funtion，也可以通过over关键字确定。实现更强大的自定义处理功能。本章将举例分析。

6.通过partition子句来将查询结果集分组，可以通过多个value_exp来确定，如果不指定partition，则把查询结果集当成单个组，相当于partition null，分析函数中使用partition by后面不能有括号，带括号的是在model查询和outer join中使用，多个partition表达式之间用逗号隔开
Partition表达式可以是常量，表列，非分析函数，函数表达式或前面的任意组合。

7.若被查询的对象具有并行性，并且分析函数包含partition子句，那么分析函数的计算也是并行的。

8.order by子句，有的分析函数必须要有order by，如row_number函数，有的不需要order by，order by如没有window子句，则表示对当前行计算的窗口范围是组的首行到当前行，相当于between unbounded preceding and current row。如果没有order by，则对当前行的计算范围是组的首行到末行，相当于between unbounded preceding and unbouned following,同组的行计算结果一样，当然，如果有window子句，必须要有order by,默认为range,注意不是rows,这样有重复排序的键值窗口是不同的。

Order by子句确定了组内的排序情况，如果没有指定partition，则是所有行的排序情况，如果有partition，则是partition之后的每个组内部的排序情况，order by可以接受多个排序键值，除了PERCENTILE_CONT 和 PERCENTILE_DISC(他们只能取唯一的键值排序)。如果最后查询结果需要一定的顺序，则在最后显示指定order by，因为分区子句的order by只保证组内有序，特别是有多个分析函数的时候，从左到右，后面的会覆盖前面的排序结果，所以最后要显示order by，当然具体情况具体考虑。

当order by使用多个排序表达式的时候，对于排名函数尤其重要。因为order by有键值相同的话不保证排序，我们常多加一个rowid，这样能保证组内有序。

如果使用了order by之后，仍然存在相同的行，那么分析函数对于相同行的计算结果一致(大部分函数，如rank函数，但是对有些函数比如row_number函数会对相同行任意分配递增值，是唯一的，参考后面介绍)。

ORDER BY后面可以是列或表达式。

Order by限制：

 

9.order by之后必须是表达式，对于sibling关键字是非法的，只对层次查询有效。位置指定和别名指定也是非法的，其他和普通查询一致。

10.如果在windows子句中使用range逻辑划分窗口范围，并且order by中是多个键值表达式排序，那么窗口的范围必须是下列三种情况：

a. between unbounded preceding and current row  --相当于没有写window，因为order by默认就是组的首行到当前行

b.between current row and unbounded following 

c.between unbounded preceding and unbounded following –相当于没有写order by，表示是组的首行到组的末行

    
  为什么有这个限制？因为range是按照排序键值和后面的窗口范围确定计算范围的，如果有多个排序键值的话，不知道根据什么计算，所以不能有具体的范围,比如1 preceding等。

  rows没有这个限制。使用range，对于order by是单个键值表达式排序，也没有这个限制。asc和desc指定排序规则是升序还是降序，默认升序，nulls first和nulls last是指定对null是出现在首行还是末行，默认升序情况是nulls last，把null当成最大的处理。这个对于普通的order by也是有效的

Window子句：

 

11.只能在order by之后指定window子句，有的分析函数允许有window子句，有的不允许，有windows子句，必须要有order by子句，后面具体说明。

12.window的范围通过Rows或range关键字指定。Rows表示物理偏移量，range表示逻辑偏移量。用rows或range划分窗口，按照起点在上，终点在下的原则，如果违反这个原则，则分析函数的计算结果为null。然后对窗口中的每一行应用分析函数计算结果。对于range的限制可以查看规则10。

当使用range的时候，根据order by中的value确定的逻辑偏移量来计算。Range对于分析函数的计算结果总是确定的，而rows有可能产生不确定值。

如果是rows，order by之后的value如果有重复，有可能产生不确定值，因为有可能需要多个value保证排序唯一。（在排名(ranking)函数中要特别注意）。

---

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