Oracle评级函数

Oracle提供了一组评级函数,包括有rank().dense_rank(), cume_dist(),percent_rank(),ntile(n);

    其中rank()表示当前数值在结果集中的名次,如果有名次相同时,下一名次将留空,如第一行与第二行的数值相同,则这二行的rank()返回值均为1,而第三行的名次返回值则为3而非2;

而dense_rank()与rank()不同之处为,dense_rank()不留空,即如果第一行与第二行数值相同，则这二行返回值均为1,而第三行的名次值为2;

Cume_dist()返回当前名次在总名次中占的%分比,如果返回的结果集记录数为10,则第一行为1/10,第二行为2/10,第三行为3/10,最后一行的值一定为1;

Percent_rank()返回也是返回当前名次在总结果集中占的%分比,但该值从0开始计数，因此一个结果集为10的计算方法为:

第一行为0/10,第二行为1/(10-1),第三行为2/(10-1),最后一行值亦为1;

Ntile(n)即将结果集划分n片,计算当前值在n片中的那一个范围片中,如果n等于4则每一片的值为0.25,根据percent_rank()计算出当前行的值,如果值<=0.25则返回值为1,如果>0.25 and <=0.25*2,则返回值为2,如果>0.25*2 and <=0.75则值为3,其它值均为4;

1 下面我们首先创建一个测试表:

      create table test_group_table(

            t_year number,

           t_month number,

           quantity number

     );

     然后插入相应的测试数据:

       insert into test_group_table values(2012,1,29);

       insert into test_group_table values(2012,1,39);

       insert into test_group_table values(2012,2,11);

       insert into test_group_table values(2012,3,12);

       insert into test_group_table values(2013,1,10);

       insert into test_group_table values(2013,2,20);

       insert into test_group_table values(2013,3,30);

       insert into test_group_table values(2013,4,40);

评级函数需要与over关键字相合使用,我们执行以下sql:

SELECT T_YEAR,
       T_MONTH,
       sum(quantity) qty,
       rank() over (order by sum(quantity)) r_qty,
       dense_rank() over (order by sum(quantity)) d_qty,
       cume_dist() over  (order by sum(quantity)) c_qty,
       percent_rank() over  (order by sum(quantity)) q_qty,
       ntile(4) over (order by sum(quantity)) nqty
  FROM TEST_REPORT_TABLE
  group by rollup(t_year,t_month);
 得到的返回结果集为:

      2012 4 20 1 1 0.111111111111111              0                         1
      2012 1 30 2 2 0.222222222222222              0.125                 1
      2012 2 43.5 3 3 0.333333333333333           0.25                   1
      2012 3 55.5 4 4 0.444444444444444           0.375                 2
      2012  149 5 5 0.555555555555556               0.5                     2
      2013 1 808 6 6 0.666666666666667             0.625                3
      2013 5 907.54 7 7 0.777777777777778        0.75                  3
      2013  1715.54 8 8 0.888888888888889        0.875                4
                         1864.54  9  9  1                                  1                        4

     上面结果集中2012及2013年的所有数值在一起参与了评级,如果想针对不同的年份进行评级,即2012和2013年分开进行评级,可以将类似的  rank() over (order by sum(quantity)) r_qty,改为  rank() over (partition by t_yearorder by sum(quantity)) r_qty,这样排序返回值将只在每个年份中进行排序,如果想将同一月份不同年份的记录放在一块,即1月份2012和2013年的数据紧跟放在一块,可以使用rank() over (partition by t_monthsum(quantity));