数据库排名函数(rank() over/dense_rank() over/row_number() over/ntile)

    sqlserver数据库有四种排名方式，本文逐一说明。

1. RANK() OVER
2. DENSE_RANK() OVER
3. ROW_NUMBER() OVER
4. NTILE

测试数据准备：

CREATE TABLE rank_over_test(
stuName VARCHAR(32),
subjectName VARCHAR(32),
score FLOAT
);
INSERT INTO rank_over_test VALUES ('张三', '语文', 88.5);
INSERT INTO rank_over_test VALUES ('张三', '数学', 78);
INSERT INTO rank_over_test VALUES ('张三', '英语', 92);

INSERT INTO rank_over_test VALUES ('李四', '语文', 84);
INSERT INTO rank_over_test VALUES ('李四', '数学', 78);
INSERT INTO rank_over_test VALUES ('李四', '英语', 92.5);

INSERT INTO rank_over_test VALUES ('王五', '语文', 87);
INSERT INTO rank_over_test VALUES ('王五', '数学', 80);
INSERT INTO rank_over_test VALUES ('王五', '英语', 88.5);

INSERT INTO rank_over_test VALUES ('赵六', '语文', 87);
INSERT INTO rank_over_test VALUES ('赵六', '数学', 86);
INSERT INTO rank_over_test VALUES ('赵六', '英语', 77);

一、RANK() OVER

1.1、查询整表数据

SELECT * FROM rank_over_test;

 

1.2、整个结果集进行分组，以stuName进行排名

SELECT stuName, subjectName, score, RANK() OVER (ORDER BY stuName) rank1 FROM rank_over_test; 

1.3、整个结果集进行分组，以subjectName进行排名

SELECT stuName, subjectName, score, RANK() OVER (ORDER BY subjectName) rank2 FROM rank_over_test;

1.4、用partition by分组

partition by：用于给结果集分组，如果没有指定，则是对整个结果集作为一个分组。

rank()：用于在分组内部进行排序。需要注意的是，rank()是跳跃排序，即：如果有两个第1名，则接下来就是第3，忽略第2的情况。

SELECT stuName, subjectName, score, RANK() OVER (PARTITION BY subjectName ORDER BY score desc) rank3 FROM rank_over_test;

 

1.5、RANK() OVER注意点

问题：使用rank() over的时候，空值是最大的，如果排序字段为null,可能造成null字段排在最前面，影响排序结果。

解决办法：可以这样用rank() over(partition by subjectName order by score desc )来规避这个问题。

 

二、DENSE_RANK() OVER

上一步1.4提到RANK()是跳跃式排序，如果不需要跳跃式排序该如何实现呢？这就是DENSE_RANK() OVER的妙用了：

和RANK() OVER区别是：当两名学生的成绩并列以后，下一位同学并不空出所占的名次。

SELECT stuName, subjectName, score, DENSE_RANK() OVER (PARTITION BY subjectName ORDER BY score desc) rank3 FROM rank_over_test;

 

三、ROW_NUMBER() OVER

这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名。

SELECT stuName, subjectName, score, ROW_NUMBER() OVER (ORDER BY stuName) AS RN FROM rank_over_test;

 

四、NTILE()排名分组

括号里面是几，就会分成几组。

可以看作是把有序的数据集合平均分配到指定的数量的桶中,将桶号分配给每一行。

例如5行数据，分成2组，就会前面3个，后面2个，默认把多余的行分给前面；分成3组，第一组就会2个，第二组就会2个，第三组就会1个。

应用：按成绩分三组(上中下)。

SELECT stuName, subjectName, score, NTILE(3) OVER (ORDER BY score desc) AS NTILE_RN FROM rank_over_test;

关于NTILE的其它妙用：

数据的并发处理，多线程思想：https://www.cnblogs.com/lonelyxmas/p/7503389.html

 

五、 完整代码

-- 排序函数：
-- 1、rank() over
-- 2、dense_rank() over
-- 3、row_number() over
-- 4、NTILE()排序
/*
CREATE TABLE rank_over_test(
stuName VARCHAR(32),
subjectName VARCHAR(32),
score FLOAT
);
INSERT INTO rank_over_test VALUES ('张三', '语文', 88.5);
INSERT INTO rank_over_test VALUES ('张三', '数学', 78);
INSERT INTO rank_over_test VALUES ('张三', '英语', 92);

INSERT INTO rank_over_test VALUES ('李四', '语文', 84);
INSERT INTO rank_over_test VALUES ('李四', '数学', 78);
INSERT INTO rank_over_test VALUES ('李四', '英语', 92.5);

INSERT INTO rank_over_test VALUES ('王五', '语文', 87);
INSERT INTO rank_over_test VALUES ('王五', '数学', 80);
INSERT INTO rank_over_test VALUES ('王五', '英语', 88.5);

INSERT INTO rank_over_test VALUES ('赵六', '语文', 87);
INSERT INTO rank_over_test VALUES ('赵六', '数学', 86);
INSERT INTO rank_over_test VALUES ('赵六', '英语', 77);
*/
-- 1、RANK() OVER
SELECT * FROM rank_over_test;
SELECT stuName, subjectName, score, RANK() OVER (ORDER BY stuName) rank1 FROM rank_over_test; ----整个结果集进行分组，以stuName进行排名
SELECT stuName, subjectName, score, RANK() OVER (ORDER BY subjectName) rank2 FROM rank_over_test; ----整个结果集进行分组，以subjectName进行排名
SELECT stuName, subjectName, score, RANK() OVER (PARTITION BY subjectName ORDER BY score desc) rank3 FROM rank_over_test;
-- partition by：用于给结果集分组，如果没有指定，则是对整个结果集作为一个分组
-- rank()：用于在分组内部进行排序。需要注意的是，ran()是跳跃排序，即：如果有两个第1名，则接下来就是第3，忽略第2的情况。

-- 注意：使用rank() over的时候，空值是最大的，如果排序字段为null,可能造成null字段排在最前面，影响排序结果。
-- 可以这样：rank() over(partition by course order by score desc nulls last)来规避这个问题。

-- 2、DENSE_RANK() OVER
-- 和RANK() OVER区别是：当两名学生的成绩并列以后，下一位同学并不空出所占的名次。
SELECT stuName, subjectName, score, DENSE_RANK() OVER (PARTITION BY subjectName ORDER BY score desc) rank3 FROM rank_over_test;

-- 3、ROW_NUMBER() OVER
-- 这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名
SELECT stuName, subjectName, score, ROW_NUMBER() OVER (ORDER BY stuName) AS RN FROM rank_over_test;

-- 4、NTILE()排序
-- 括号里面是几，就会分成几组
-- 解释：可以看作是把有序的数据集合平均分配到指定的数量的桶中,将桶号分配给每一行。
-- 例如5行数据，分成2组，就会前面3个，后面2个，默认把多余的行分给前面；分成3组，第一组就会2个，第二组就会2个，第三组就会1个
-- 应用：成绩上中下三组、
SELECT stuName, subjectName, score, NTILE(2) OVER (ORDER BY score) AS NTILE_RN FROM rank_over_test;