关闭

使用分析函数为记录进行分组排名(rank, dense_rank, row_number)——分析函数2

标签: insertusersqloracle测试table
1058人阅读 评论(0) 收藏 举报
分类:

一、使用rownum为记录排名:

如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下。

1、测试环境

create table user_order(
  EGION_ID NUMBER(2),
  CUSTOMER_ID NUMBER(2),
  CUSTOMER_SALES NUMBER
);

2、测试数据

insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,1,151162);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,29,903383);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,7,971585);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,28,986964);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,21,1020541);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,22,1036146);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,16,1068467);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,8,1141638);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,3,1161286);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,5,1169926);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,19,1174421);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,12,1182275);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,11,1190421);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,10,1196748);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,9,1208959);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,30,1216858);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,2,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,24,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,23,1224992);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,18,1253840);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,15,1255591);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,13,1310434);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,27,1322747);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,20,1413722);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(6,6,1788836);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(10,26,1808949);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(5,4,1878275);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(7,14,1929774);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(8,17,1944281);
insert into user_order(REGION_ID,CUSTOMER_ID,CUSTOMER_SALES) values(9,25,2232703);

SQL> select * from user_order;

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
         5           1         151162
        10          29         903383
         6           7         971585
        10          28         986964
         9          21        1020541
         9          22        1036146
         8          16        1068467
         6           8        1141638
         5           3        1161286
         5           5        1169926
         8          19        1174421

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
         7          12        1182275
         7          11        1190421
         6          10        1196748
         6           9        1208959
        10          30        1216858
         5           2        1224992  --注意这里有3条一样的数据。
         9          24        1224992  --注意这里有3条一样的数据。       
         9          23        1224992  --注意这里有3条一样的数据。
         8          18        1253840
         7          15        1255591
         7          13        1310434

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
        10          27        1322747
         8          20        1413722
         6           6        1788836
        10          26        1808949
         5           4        1878275
         7          14        1929774
         8          17        1944281
         9          25        2232703

已选择30行。
注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户,如果使用rownum会有什么样的后果呢?
SQL> select rownum, t.*
  2    from (select * from user_order order by customer_sales desc) t
  3   where rownum <= 12
  4   order by customer_sales desc;

    ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ---------- ----------- --------------
         1          9          25        2232703
         2          8          17        1944281
         3          7          14        1929774
         4          5           4        1878275
         5         10          26        1808949
         6          6           6        1788836
         7          8          20        1413722
         8         10          27        1322747
         9          7          13        1310434
        10          7          15        1255591
        11          8          18        1253840

    ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ---------- ----------- --------------
        12          5           2        1224992   --!

已选择12行。
很明显假如只是简单地按rownum进行排序的话,我们漏掉了另外两条记录(参考上面的结果)。


二、使用分析函数来为记录排名:
针对上面的情况,Oracle从8i开始就提供了3个分析函数:rank,dense_rank,row_number来解决诸如此类的问题,下面我们来看看这3个分析函数的作用以及彼此之间的区别:
Rank,Dense_rank,Row_number函数为每条记录产生一个从1开始至N的自然数,N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

ROW_NUMBERRow_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 

DENSE_RANKDense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。 

RANKRank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

这样的介绍有点难懂,我们还是通过实例来说明吧,下面的例子演示了3个不同函数在遇到相同数据时不同排名策略:

SQL> select region_id,
  2         customer_id,
  3         sum(customer_sales) total,
  4         rank() over(order by sum(customer_sales) desc) rank,
  5         dense_rank() over(order by sum(customer_sales) desc) dense_rank,
  6         row_number() over(order by sum(customer_sales) desc) row_number
  7    from user_order
  8   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         9          25    2232703          1          1          1
         8          17    1944281          2          2          2
         7          14    1929774          3          3          3
         5           4    1878275          4          4          4
        10          26    1808949          5          5          5
         6           6    1788836          6          6          6
         8          20    1413722          7          7          7
        10          27    1322747          8          8          8
         7          13    1310434          9          9          9
         7          15    1255591         10         10         10
         8          18    1253840         11         11         11

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         9          23    1224992         12         12         12    --!
         9          24    1224992         12         12         13    --!
         5           2    1224992         12         12         14    --!
        10          30    1216858         15         13         15    --!注意这里
         6           9    1208959         16         14         16
         6          10    1196748         17         15         17
         7          11    1190421         18         16         18
         7          12    1182275         19         17         19
         8          19    1174421         20         18         20
         5           5    1169926         21         19         21
         5           3    1161286         22         20         22

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         6           8    1141638         23         21         23
         8          16    1068467         24         22         24
         9          22    1036146         25         23         25
         9          21    1020541         26         24         26
        10          28     986964         27         25         27
         6           7     971585         28         26         28
        10          29     903383         29         27         29
         5           1     151162         30         28         30

已选择30行。
请注意上面的!部分,这里生动的演示了3种不同的排名策略:
①对于第一条相同的记录,3种函数的排名都是一样的:12
②当出现第二条相同的记录时,Rank和Dense_rank依然给出同样的排名12;而row_number则顺延递增为13,依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时,可以看到Rank函数在12和15之间空出了13,14的排名,因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增


三、使用分析函数为记录进行分组排名:
上面的排名是按订单总额来进行排列的,现在跟进一步:假如是为各个地区的订单总额进行排名呢?这意味着又多了一次分组操作:对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持,我们所要做的仅仅是在over函数中order by的前面增加一个分组子句:partition by region_id。

SQL> select region_id,
  2         customer_id,
  3         sum(customer_sales) total,
  4         rank() over(partition by region_id order by sum(customer_sales) desc) rank,
  5         dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,
  6         row_number() over(partition by region_id order by sum(customer_sales) desc) row_number
  7    from user_order group by region_id, customer_id;

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         5           4    1878275          1          1          1
         5           2    1224992          2          2          2
         5           5    1169926          3          3          3
         5           3    1161286          4          4          4
         5           1     151162          5          5          5
         6           6    1788836          1          1          1
         6           9    1208959          2          2          2
         6          10    1196748          3          3          3
         6           8    1141638          4          4          4
         6           7     971585          5          5          5
         7          14    1929774          1          1          1

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         7          13    1310434          2          2          2
         7          15    1255591          3          3          3
         7          11    1190421          4          4          4
         7          12    1182275          5          5          5
         8          17    1944281          1          1          1
         8          20    1413722          2          2          2
         8          18    1253840          3          3          3
         8          19    1174421          4          4          4
         8          16    1068467          5          5          5
         9          25    2232703          1          1          1
         9          24    1224992          2          2          2

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         9          23    1224992          2          2          3
         9          22    1036146          4          3          4
         9          21    1020541          5          4          5
        10          26    1808949          1          1          1
        10          27    1322747          2          2          2
        10          30    1216858          3          3          3
        10          28     986964          4          4          4
        10          29     903383          5          5          5

已选择30行。
现在我们看到的排名将是基于各个地区的,而非所有区域的了!Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分,这样排列函数就能够应用于这各个子集。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1038376次
    • 积分:16025
    • 等级:
    • 排名:第675名
    • 原创:568篇
    • 转载:91篇
    • 译文:2篇
    • 评论:68条