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

最新推荐文章于 2022-10-11 18:17:26 发布

事后诸葛亮

最新推荐文章于 2022-10-11 18:17:26 发布

阅读量1.5k

点赞数 1

分类专栏：分析函数文章标签： insert user sql oracle 测试 table

本文链接：https://blog.csdn.net/zq9017197/article/details/7402180

版权

分析函数专栏收录该内容

5 篇文章 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_NUMBER：Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

②DENSE_RANK：Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

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

这样的介绍有点难懂，我们还是通过实例来说明吧，下面的例子演示了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 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。

事后诸葛亮

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
使用分析函数为记录进行分组排名（rank, dense_rank, row_number）——分析函数2

一、使用rownum为记录排名：如果需要排名的话那么只需要简单地加上rownum不就行了吗？事实情况是否如此想象般简单，我们来实践一下。1、测试环境create table user_order( EGION_ID NUMBER(2), CUSTOMER_ID NUMBER(2), CUSTOMER_SALES NUMBER);2、测试数据insert
复制链接

扫一扫