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

1058人阅读 评论(0)

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



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   --！

Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

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

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

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

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

①对于第一条相同的记录，3种函数的排名都是一样的：12
②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

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

0
0

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