## oracle分析函数Rank, Dense_rank, row_number 转

源远流长-泉

http://www.blogjava.net/pengpenglin/archive/2008/06/27/211019.html

1.使用rownum为记录排名
2.使用分析函数来为记录排名
3.使用分析函数为记录进行分组排名

【1】测试环境：

SQL> desc user_order;
Name
Null?    Type

----------------------------------------- -------- ----------------------------
REGION_ID                                          NUMBER(2)
CUSTOMER_ID                                  NUMBER(
2)
CUSTOMER_SALES                          NUMBER

【2】测试数据：

SQL> select * from user_order order by customer_sales;

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
7          12           1182275
7          11           1190421
6          10           1196748
6           9            1208959
10          30          1216858
5             2                1224992
9             24              1224992
9             23              1224992

8
18           1253840
7          15           1255591
7          13           1310434
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 rows selected.

SQL> select rownum, t.*
2    from (select *

3            from user_order

4           order by customer_sales desc) t

5   where rownum <= 12
6   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
12             5                     2          1224992

12 rows selected.

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

ROW_NUMBER

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

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

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

SQL> select region_id, customer_id, sum(customer_sales) total,

2         rank() over(order by sum(customer_sales) desc) rank,

3         dense_rank() over(order by sum(customer_sales) desc) dense_rank,

4         row_number() over(order by sum(customer_sales) desc) row_number

5    from user_order

6   group by region_id, customer_id;

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

---------- ----------- ---------- ---------- ---------- ----------

8          18                1253840         11         11         11
5           2                 1224992         12         12         12
9          23                1224992         12         12         13
9          24                1224992         12         12         14
10          30               1216858         15           13            15

30 rows selected.

①对于第一条相同的记录，3种函数的排名都是一样的：12

②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录

③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

SQL> select region_id, customer_id,

sum(customer_sales) total,

2         rank() over(partition by region_id
order by sum(customer_sales) desc) rank,

3         dense_rank() over(partition by region_id
order by sum(customer_sales) desc) dense_rank,

4         row_number() over(partition by region_id
order by sum(customer_sales) desc) row_number

5    from user_order

6   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
6           6                1788836          1          1          1
6           9                1208959          2          2          2
6          10               1196748          3          3          3

30 rows selected.

### 源远流长-泉

Oracle开窗函数笔记及应用场景

smileNicky
2018/12/30
0
0
oracle分析函数Rank, Dense_rank, row_number

Hyacinth_Yuan
2015/12/25
192
0
oracle的分析函数‘over’

0o清风徐来o0
2012/10/22
377
0
SQL优化--使用分析函数

2016/09/13
17
0
oracle 函数之分析函数

1.分析函数有4个 over rownumber denserank rank 四个 不能单独使用2.select empno, sal ,deptno,sum(sal) over(order by empno) , sum(sal) over() from emp; 视图如下 按照工资进行累加3 s......

2017/12/30
0
0

java快递电子面单打印接口对接demo

29分钟前
4
0
fasjtjson文档

https://github.com/alibaba/fastjson/wiki/JSONField

jirak
29分钟前
4
0
Mybatis中插入多条记录

30分钟前
5
0

31分钟前
4
0

32分钟前
6
0