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

接PART3：http://blog.chinaunix.net/uid-7655508-id-5834821.html

1.3 分析函数的使用

1.3.1 分析函数概览

 

我们浏览下所有的分析函数，其中带(*)的表示可以带window子句。其中黄色的表示常用分析函数。

--可以带window的

    AVG  *

    CORR *

    COVAR_POP *

    COVAR_SAMP *

    COUNT *

    FIRST_VALUE *

    LAST_VALUE *

    MAX *

    MIN *

    REGR(Linear Regressions) Functions *

    STDDEV *

    STDDEV_POP *

    STDDEV_SAMP *

    SUM *

    VAR_POP *

    VAR_SAMP *

    VARIANCE *

--不可以带window的

   CUME_DIST

    DENSE_RANK

    FIRST

    LAST

    LAG

    LEAD

    NTILE

    PERCENT_RANK

    PERCENTILE_COUNT

    PERCENTILE_DISC

    RANK

    ROW_NUMBER

    RATIO_TO_REPORT

常用分析函数使用说明

  分析函数可以分为四类：排名函数，聚合函数，行比较函数，统计函数。
  很多分析函数也可以当作聚合函数使用，这里我们只讨论当作分析函数的使用方法，聚合函数可以参考oracle sql指南，这里不另作说明。另外oracle还支持user-defined aggregate function,可以使用分析函数的语法，这样可以自定义扩展分析函数的功能，本章也会讨论如何写子句的分析函数。

1.3.2排名函数(Ranking)

  排名函数包括ROW_NUMBER, RANK ,DENSE_RANK, PERCENT_RANK, CUME_DIST和NTILE。(常用的是黄色标志的)，排名函数的最大用途就是做TOP-N分析和BOTTOM-N分析，常用于分页查询,当然row_number伪列也类似。

1.3.2.1 ROW_NUMBER, RANK ,DENSE_RANK
  其中row_number只能用于分析函数，rank和dense_rank可以用于组函数和分析函数。排名函数都没有windows子句，此3个分析函数必须使用order by,当然如果不要排序，也可以写order by null,order by 常量或多个常量，直接写NULL就行了，事实还会做一定sort动作的，只不过相当于增加几个伪列，都是一样的值。

用于分析函数他们的语法是：

Row_number() Over ([Partition by 

ROW_NUMBER,RANK,DENSE_RANK是在查询结果集排序的基础上，将结果集(必须包含order by子句，没有window子句，可以有partition子句，无参数)的每行从1到n标上行号，这个n小于等于查询出的行的数目。规则如下：

ROW_NUMBER：

Row_number函数为每行分配唯一值，首行为1，依次递增，当排序之后有相同数据时，排名任意分配(这时不保证排序,如果要排序，比如额外加排序键值，一般加rowid)。总体是递增顺序。

DENSE_RANK：
Dense_rank函数为每行分配一个唯一的值，首行为1，依次递增，除了排序之后遇到相同数据时，此时所有相同数据的排名都是一样的。但是总体是递增的，中间没有跳号。 

RANK：
Rank函数为每行分配一个唯一的值，首行为1，依次递增，除了排序之后遇到相同的数据时，此时所有相同数据的排名是一样的，并且相同的排名与下一个不同排名之间会产生跳号现象，但是总体递增。

  下面，我们看这个实例，比如要求查询出所有的2001年的订单，按照区域划分，计算总金额，分别用上面三种分析函数来计算排名：相同的排名相同，并且算上相同的名词，下一个不同的按排名数量递增(如排名是1 1,那么下一个是排名为3，使用rank实现。相同的排名相同，并且不算上相同的排名，如排名是1 1，那么下一个排名是2，用dense_rank实现。最后是依次递增，用row_number实现，语句如下：

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank,

DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 6;

由上图的结果我们可以看出：

1.order by后面的排序可以使用组函数（因为此处按group by分组），可以是一个指定列的表达式。

2.对于区分分析函数的执行结果，是按照order by后面指定的表达式值是否有重复，而不是行是否重复。如这里值要SUM(tot_sales)重复，就算重复值，然后应用规则。

     3.可以看出，是跳号的，sum一样的行号一样，而dense rank没有跳号，sum重复的行号一样。          Row_number是确定的。


  Rank和dense_rank可能会产生重复的排名，为了解决这个问题，需要在order by之后指定多个排序键值，如果没有重复的话，那么就是唯一。

只需要将上面的查询语句稍作修改，如下：

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC,CUST_NBR) sales_rank, --增加红色部分

  DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC,CUST_NBR ) sales_dense_rank,

  ROW_NUMBER(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr
ORDER BY 6;

可以看出，已经没有重复的等级号码了。
当然，我们也可以使用partition子句，在组内排名，把上面的语句稍作修改为：

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  RANK(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_rank,
--分析函数中可以使用组函数

  DENSE_RANK(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (PARTITION BY region_id ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr
ORDER BY 1;
--按照region_id排序。如果不指定这句，那么最终结果顺序会以sum为主，结果集不变。

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