MySQL（四）：使用SQL实现排名

大家好，我是欧阳方超，可以扫描下方二维码关注我的公众号“欧阳方超”，后续内容将在公众号首发。

1、概述

排名的需求总是很常见的，今天就介绍一下MySQL中的rank()函数，如何应用它以将排名分配给结果集的分区中的每一行。

2、rank()函数

rank()函数为结果集每个分区中的每一行分配一个排名，其语法如下：

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

在这个语法中：
PARTITION BY子句对结果集进行分区。RANK()功能在分区内执行；ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。
注意，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

2.1、rank()使用

假如有下面一张表，员工所在部门以及工资情况。

id	name	department	salary
1	Alice	Sales	70000.00
2	Bob	Sales	80000.00
3	Charlie	HR	60000.00
4	David	HR	70000.00
5	Eve	Engineering	95000.00
6	Frank	Engineering	95000.00
9	Ala	Engineering
11	Grace	Marketing	50000.00
12	Hannah	Marketing	55000.00
13	Tom	Engineering	55000.00

假如我要查出每个部门工资最高的员工（可能会出现多人并列第一的情况），可以使用下面的SQL实现：

   select
	name,
	department,
	rk
from
	(
	select
		name,
		department,
		rank() over(partition by department
	order by
		salary desc) rk
	from
		employees e 
   ) as temp
where
	rk = 1

运行结果为：

name	department	rk
Eve	Engineering	1
Frank	Engineering	1
David	HR	1
Hannah	Marketing	1
Bob	Sales	1

2.2、rank()的问题

从上面的结果来看没有问题，确实能查出来每个部门工资最高的员工，其中Engineering部门Eve和Frank工资并列第一高。但是SQL职工的子查询存在问题，只是在这场景中没有外现，不妨单独执行一个子查询：

select
		name,
		department,
		rank() over(partition by department
	order by
		salary desc) rk
	from
		employees e

执行结果为：

name	department	rk
Eve	Engineering	1
Frank	Engineering	1
Tom	Engineering	3
Ala	Engineering	4
David	HR	1
Charlie	HR	2
Hannah	Marketing	1
Grace	Marketing	2
Bob	Sales	1
Alice	Sales	2

不难看出Engineering部门的Tom被排在了第三名，这在逻辑上显然是不对的（当然如果需求如此那就使用rank()函数），因为Eve和Frank是并列第一名，Tom显然应该是第二名。

2.3、更符合逻辑的dense_rank()

为了使查询结果更合理一些，可以使用dense_rank()函数进行排名，dense有“密集”之意，这也传递出了使用该方法的排名其排名值中没有间隙——排名值是连续增长的。下面是SQL及执行结果：

select
		name,
		department,
		dense_rank() over(partition by department
	order by
		salary desc) rk
	from
		employees e

name	department	rk
Eve	Engineering	1
Frank	Engineering	1
Tom	Engineering	2
Ala	Engineering	3
David	HR	1
Charlie	HR	2
Hannah	Marketing	1
Grace	Marketing	2
Bob	Sales	1
Alice	Sales	2

本次查询，部门Engineering中的Tom员工“更合理”地出现在了第二名中。

2.4、有些数据不需要参与排名

在查询排名的示例中，如果需求再“奇葩”一些，salary为空的员工不参与排名，此时SQL可以调整为如下内容：

select
		name,
		department,
		CASE 
			when salary is null then null
			else dense_rank() over(partition by department order by salary desc)
		END as rk
	from
		employees e

查询结果为：

name	department	rk
Eve	Engineering	1
Frank	Engineering	1
Tom	Engineering	2
Ala	Engineering
David	HR	1
Charlie	HR	2
Hannah	Marketing	1
Grace	Marketing	2
Bob	Sales	1
Alice	Sales	2

使用了case when语法使得salary为空的员工不再参与排名。

2.5、只设置行号的函数——row_number()

与rank()、dense_rank()相关的还有一个函数row_number()，该函数再MySQL8以后的版本中才引入，从它的名字也可以看出其作用——为每一行分配唯一的排名。可以从下面的SQL及查询结果看出其运行效果：

select
		name,
		department,
		CASE 
			when salary is null then null
			else row_number() over(partition by department order by salary desc)
		END as rk
	from
		employees e

name	department	rk
Eve	Engineering	1
Frank	Engineering	2
Tom	Engineering	3
Ala	Engineering
David	HR	1
Charlie	HR	2
Hannah	Marketing	1
Grace	Marketing	2
Bob	Sales	1
Alice	Sales	2

严格来讲，row_number()已经不是设置排名了，它是在设置行号。

3、总结

RANK()函数为结果集中的行分配一个唯一的排名，排名基于指定的排序条件。当出现重复值时，RANK()会为这些重复的行分配相同的排名，并在后续排名中跳过相应的名次。例如，如果有两个并列第一的项，下一项将被标记为第三。RANK()与ROW_NUMBER()和DENSE_RANK()的区别在于，ROW_NUMBER()为每一行分配唯一的排名，而DENSE_RANK()则不会跳过排名。通过正确使用ORDER BY和PARTITION BY，可以灵活地对数据进行排名，满足不同的业务需求。
我是欧阳方超，把事情做好了自然就有兴趣了，如果你喜欢我的文章，欢迎点赞、转发、评论加关注。我们下次见。