Oracle分析函数实战

分析函数是什么？

分析函数是带over的，对每行都应用分析函数，然后分析函数根据排序规则（没有排序就是没有顺序的规则，order by就是起到一个分析函数在行上滑动方向的作用）按行向下滑动，直到全部行应用分析函数完毕则分析函数结束。分析函数的计算是在当前行所属的窗口上（这个是一个结果集，每行对应的窗口总是有一个结果集）进行的，每行对应的窗口范围是由partition,order by和window子句共同决定，分析函数就根据这个范围来计算当前行的值。分析函数计算的行是在order by之前的group by,having等之后的行，这个要注意。

分析函数主要掌握：

当前行的概念：因为分析函数总是对当前行所处于的窗口包含的结果集进行计算，从而返回当前行对应的分析函数值。当前行就是根据order by顺序，然后向下滑动，滑动到某行，某行就是当前行。

窗口的概念：逻辑窗口和物理窗口。对于逻辑窗口是计算出来的，这个要与物理窗口区分。

分区、排序、window子句等。

明确rows和range的区别。

对当前行应用分析函数实际上是对当前行所属的窗口应用分析函数，从而计算出当前行对应的分析函数的值。每行都处在滑动的窗口内，共同由order by,partition,window确定，对于parition,order by,window的规则见下面表示，因为这3个有的可以省略，有的有默认值。

如果order by的排序键值有相同值，window窗口用rows，那么相同的值对应的窗口是不能保证唯一性的，但是range可以保证唯一性，见下面的测试。

partition,order by都可以是列或表达式，这个要注意。

一些重要的概念：

1．分析函数应该是一个整体，也就是包含了over的函数,over关键字是分析函数的标志。有的函数只能作为分析函数，有的函数既能是分析函数又能是组函数，比如sum就是两者都行，而first_value只能是分析函数。

2．partition子句，partition子句是在order by之前的，也就是按指定键值对行分区，每个分区内应用order by,window等规则，确定每行所属的窗口，然后对每行用分析函数计算，如果没有partition那么所有行是一个分区。partition不一定要有，但是有很多函数必须要求有order by这个要注意，比如排名分析函数,window有的可以省略，可以有，有的不能带window。

所以规定一下：

当前分区：如果没有partition就是只所有的行，如果有partition就是在当前行所属分区内，一个行对应的窗口总是在此行所属的分区内。

2.rows/range关键字，一个是物理行，一个是逻辑行。对于rows，当前行对应的窗口就是可以根据partition,order by规则和window子句上确定的范围数出来的，而rows,当前行对应的窗口是根据partition,order by中的列和window子句上确定的范围以及以当前行中的order by列的值为参考计算出来的，如果不指定window,默认的是range,带order by对应窗口就是上面到当前行，否则是全部行。

物理行的order by排序键值可以是任何类型，也可以有多个排序键值。

但是逻辑行中的order by一般只能有一个排序键值，此排序键值只能是数字和日期（当然unbounded和current row可以，因为他们分别代表无限和当前行，当前行可以是0 following,0 preceding），对range,如果order by是数字，对应的窗口中的表达式必须是数字或current row或unbounded，如果是日期，可以对应数字、两个interval函数、current row、unbounded。当然Oracle可以自动类型转换,转换失败则报错。

如果range中的order by有多个排序键值，那么窗口中只能用unbounded,current row。

rows是物理行，如果order by中的排序键值不唯一，oracle不保证重复排序值的行分析函数返回的结果稳定，因为这种排序是不稳定的,order by中的列值相同，他们对应的窗口是不稳定的。但是range不同，order by里面的排序键值一样，对应的行的窗口范围是一样的，这必然导致他们的结果一样，对range的结果是稳定的。

注意range中的每行对应的窗口是计算出来的，是逻辑范围，因此和rows是有差别的，range能保证结果的稳定性，但是rows如果order by键值重复则不能保证。比如：

with 1 id, dual union all

select'b' name from 2 id, dual union all

select'd' name from 3 id, dual

)

select id,name,sum(id) 1 preceding) over(order by id range sx

from        ID NAME          S         SX

         1 a            1          2

         2 c             3          6

         3 e            5          7

因为这个分析函数按id升序，range决定每行所对应的逻辑窗口是比当前id<1到当前窗口，对于id=1来说，有两个id一样，那么计算id=1的所属窗口应该是第1行到第2行，所以他们的值一样都为2，计算id=2的，也有两个id一样，那么比id=2的小1的也有两行，那么id=2的两行对应的窗口是一样的，为前4行， 所以为6。当然id=3的类似分析。

range保证结果的确定性,因为相同排序键值所属的窗口是一致的，但是rows则不行，它是物理行，排序键值有重复，当前行对应的窗口是不确定的，如上面的，也许再运行就碰到name='c'的sum值为4，而name='d'的sum值为3了。

4.注意window子句。window子句有的分析函数没有，比如排名函数等，不准有window子句的那么每行的对应窗口就是当前分区内。但是允许window子句的，也可以不带window子句，如果要带window子句，则必须要有order by,另外注意range和window的关系，见第2点。允许带window的分析函数注意如下：

1.）没有window,如果也没有order by,那么每行对应的窗口就是分区内的所有行，相当于range between unbounded preceding and unbounded following(注意默认是range),分析函数对每行所属的窗口计算，应该每行都返回一样的值。

2．）没有window但是有order by,则表示每行对应的窗口是按照排序后，分区内的前面第1行到分区内的当前行结束。相当于range between unbounded preceding and current row (相同排序值范围一样).

3.)有window,也就必须有order by,如果没有between..and,在range/rows后只指定了一个值，那么这个值可以根据range或rows的规则计算出对应的行，这个行就是起点，也就是指指定一个值，则只是指定了起点，终点默认是current row(注意rows或range的current row概念不同，一个是物理当前行，一个是逻辑当前行，range是根据逻辑计算得到对应的窗口的)。比如rows 1 preceding相当于rows between 1 preceding and current row,range 1 preceding相当于range between 1 preceding and current row。

4.）0 following和0 preceding都相同于current row。

5.）可以显示指定窗口，用between start_point and end point,不管是显示的还是第3中说的只指定始点(会有默认终点当前行或逻辑当前行)，开始点到结束点的方向都是根据排序键值决定的顺序从上到下，如果顺序反了，则出错，比如rows 1 following，range 1 following,rows between 1 preceding and 2 preceding都是错的。

不可带window的函数，一般是排名函数，他们都是物理行，每行对应的窗口是当前分区内的所有行。

6）要特别注意一些分析函数的重点使用场合，比如first/last+keep的使用，first_value/last_value,特别用last_value填充缺失数据。要对比first/last与first_value/last_value,对比first/last中的order by desc的区别，他们不等价，同样first_value/last_value也是。

注意NULL的处理，各分析函数（要分类）列出典型使用：累计值，中间值，连续数问题等。

注意分析函数和组函数的区别，这个要开始就说，专门一节，组函数对每个组一般返回单行，但是分析函数是对每行计算的，所有每行都会返回一个分析函数的计算值。

明确分析函数的优点和缺点：分析函数的优点就是进行复杂的累计计算，中间计算，移动计算等，这样可以减少连接查询，比如找id,name找相同的name数目>1的，显示name,并且还要显示id和数目，那么可以用连接，子查询实现，用分析函数就简单多了，这个由分析函数的计算特点决定的。

分析函数的优点就是可以减少表的扫描，而且oracle内部有优化处理方式，提高性能，但是缺点就是很多分析函数可能会有排序什么的，这个要专门研究。

分析函数的其他规则：比如不能嵌套，参数规则，有的可以带参数有的不可以等，按照文档描述。

注意分析函数中如果带了order by虽然可以确定此分析函数的计算结果顺序（当然rows要保证排序键值唯一，range可以不保证），有的不能带window的排名函数很多都要求order by的唯一性。一个select里有多个分析函数都带了order by后面的就会破坏前面的排序结果，所以要想最终结果有序，必须最后对结果显示排序。

分析函数的缺点：经常需要有排序操作，很多就算无order by也需要内部排序，如果写多个分析函数，会产生很多排序，依赖于内存。当然也可以优化排序，比如通过索引消除排序。

分析函数的优点：代替复杂的子查询，join等，减少表的扫描次数，提高效率。

- End -