Oracle笔记 之 分组统计排名函数dense_rank/rank()-over()函数_dense_rank() over 怎么用

分组统计排名函数

解析

dense_rank/rank()-over()用于对数据进行统计排名。
rank-over()函数对相同的统计值的排名相同，不同的统计值之间的排名不连续（相同统计值的排名均为其前面不同统计值的数量+1）。
dense_rank-over()函数对相同的统计值的排名相同，不同的统计值之间的排名连续。
注意：如果over()中使用order by 进行排序后没有重复的值则dense_rank/rank()-over()统计排名结果一致且排名均连续。
dense_rank/rank()-over()可以在over()中使用Partition By指定进行排名的数据范围进行分组统计排名。
注意：这里的partition by进行排名的数据范围，而不是SQL语句里的分组函数。

示例

• 数据集
  这里使用Oracle笔记 之 偏移量分析函数lag/lead-over函数中的数据集，但是依次分别对2022,2024,2026,2028,2030后的数据使用Update test_lagover Set dvalue = dvalue + 1 Where ymdate >= 202201;对dvalue值进行追加。
• rank()-over()
  按每个ymdate的dvalue统计值倒序，ymdate升序进行非连续性排名

Select rank() over(Order By Sum(dvalue) Desc, ymdate) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate
# 结果
1	1	203001	310
2	2	203002	310
3	3	203003	310
4	4	203004	310
5	5	203005	310
6	6	203006	310
7	7	203007	310
8	8	203008	310
9	9	203009	310
10	10	203010	310
11	11	203011	310
12	12	203012	310
13	13	202801	290
14	14	202802	290
15	15	202803	290
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

按每个ymdate的dvalue统计值倒序进行非连续性排名

Select rank() over(Order By Sum(dvalue) Desc, ymdate) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate
# 结果
1	1	203001	310
2	1	203004	310
3	1	203002	310
4	1	203012	310
5	1	203011	310
6	1	203007	310
7	1	203010	310
8	1	203008	310
9	1	203003	310
10	1	203005	310
11	1	203006	310
12	1	203009	310
13	13	202909	290
14	13	202902	290
15	13	202801	290

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• dense_rank-over()
  按每个ymdate的dvalue统计值倒序，ymdate升序进行连续性排名
  注意：因为使用dvalue统计值倒序，ymdate升序进行排序后没有相同的统计结果，所有排序结果与’rank()-over()按每个ymdate的dvalue统计值倒序，ymdate升序进行非连续性排名’一致。

Select dense_rank() over(Order By Sum(dvalue) Desc, ymdate) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate 
# 结果
1	1	203001	310
2	2	203002	310
3	3	203003	310
4	4	203004	310
5	5	203005	310
6	6	203006	310
7	7	203007	310
8	8	203008	310
9	9	203009	310
10	10	203010	310
11	11	203011	310
12	12	203012	310
13	13	202801	290
14	14	202802	290
15	15	202803	290

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

按每个ymdate的dvalue统计值倒序进行连续性排名

Select dense_rank() over(Order By Sum(dvalue) Desc, ymdate) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate 
# 结果
1	1	203001	310
2	1	203004	310
3	1	203002	310
4	1	203012	310
5	1	203011	310
6	1	203007	310
7	1	203010	310
8	1	203008	310
9	1	203003	310
10	1	203005	310
11	1	203006	310
12	1	203009	310
13	2	202909	290
14	2	202902	290
15	2	202801	290

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• rank()-over(partition by)
  划定排序的数据范围为ymdate所在的年份，并按照每个ymdate的dvalue统计值倒序进行非连续性排名
  注意：因为人造数据的问题每年度的每个ymdate数值一致所以看不出统计排名效果

Select rank() over(Partition By trunc(ymdate / 100) Order By Sum(dvalue) Desc) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate
# 结果
1	1	202102	210
2	1	202106	210
3	1	202109	210
4	1	202105	210
5	1	202107	210
6	1	202108	210
7	1	202111	210
8	1	202101	210
9	1	202112	210
10	1	202103	210
11	1	202104	210
12	1	202110	210
13	1	202212	230
14	1	202201	230
15	1	202206	230

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• dense_rank-over(partition by)
  划定排序的数据范围为ymdate所在的年份，并按照每个ymdate的dvalue统计值倒序进行连续性排名
  注意：因为人造数据的问题每年度的每个ymdate数值一致所以看不出统计排名效果

Select dense_rank() over(Partition By trunc(ymdate / 100) Order By Sum(dvalue) Desc) rankid,
       ymdate,
       Sum(dvalue) dvalue
  From test_lagover
 Group By ymdate
# 结果
1	1	202102	210
2	1	202106	210
3	1	202109	210
4	1	202105	210
5	1	202107	210
6	1	202108	210
7	1	202111	210
8	1	202101	210
9	1	202112	210
10	1	202103	210
11	1	202104	210
12	1	202110	210
13	1	202212	230
14	1	202201	230
15	1	202206	230

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22