-
row_number是在每个分组窗口中给定唯一序号;
-
rank碰到相等值序号一样,会跳过之后的位次;
-
dense_rank碰到相等值序号也一致,但不跳过之后的位次;
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');
dense_rank
排名连续,且数值相同的排名序号也相同。
select *,dense_rank() over(order by Score desc) as rank1
from Scores
rank
排名不连续,且数值相同的排名序号也相同。
select *,rank() over(order by Score desc) as rank1
from Scores
row_number
排名连续,相同数值的排序会挨着且不同。注意:相同值大小的情况,会按照主键的值(这里是ID)来进一步排序,也就是3在前5在后,2在前6在后。
select *,row_number() over(order by Score desc) as rank1
from Scores
ntile函数
可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。 对于每一个行,ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理:
select *,ntile(3) over(order by Score desc) as ntile1
from Scores
select *,ntile(4) over(order by Score desc) as ntile1
from Scores
select *,ntile(5) over(order by Score desc) as ntile1
from Scores
select *,ntile(6) over(order by Score desc) as ntile1
from Scores
这种排名主要作用在于分桶,将排名压缩限制在一定范围内,但是对于相同数值的排名序号处理不够好, 慎用。