分组统计排名函数
解析
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
按每个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
- 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
按每个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
- 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
- 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