目录
前言
最近发现自己sql语句比较差,就恶补了一下,发现了MySQL四大排名函数(8.0以上支持),做一下记录。
一、over 函数
在MySQL中,OVER关键字是用来定义窗口函数(Window functions)的规则。窗口函数是一种特殊类型的函数,它可以在查询结果集中的一个窗口(window)上执行计算,而不是对整个结果集进行计算。
<窗口函数>(<表达式>) OVER ([PARTITION BY <列名1>, <列名2>, ...]
[ORDER BY <列名> [ASC | DESC]], ...)
使用OVER关键字,可以对窗口函数指定以下规则:
-
PARTITION BY:用于定义窗口的分区方式。通过指定一个或多个列,可以将结果集划分为多个分区,每个分区都会单独执行窗口函数。
-
ORDER BY:用于定义窗口内数据的排序方式。可以指定一个或多个列,以确定窗口函数在窗口中计算时的顺序。
使用这些规则,可以很灵活地定义窗口函数的计算方式,并对不同的子集进行聚合、排名、累计等操作。 示例:使用COUNT函数和窗口函数来计算每个部门中员工的数量:
SELECT name, department, COUNT(*) OVER(PARTITION BY department) AS employee_count
FROM employees;
运行以上SQL语句,将会得到以下结果:
name | department | employee_count |
---|---|---|
John | HR | 3 |
Michael | HR | 3 |
Bob | HR | 3 |
Linda | Finance | 2 |
Alice | Finance | 2 |
以上结果显示了每个员工的姓名、所在部门以及每个部门中员工的数量。
二、ROW_NUMBER()函数
ROW_NUMBER()函数:为结果集中的每一行分配一个唯一的数字,表示该行在结果集中的排序位置,分区以后排序区内唯一。
示例:
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS ranking, name, salary
FROM employees;
效果:
ranking | name | salary |
---|---|---|
1 | John | 1000 |
2 | Linda | 900 |
3 | Michael | 700 |
4 | Alice | 700 |
三、RANK()函数
RANK()函数:为结果集中的每一行分配一个排名,相同值的行具有相同的排名,下一个排名是跳过前一个排名的个数。与ROW_NUMBER()相比,ROW_NUMBER()值相同时,序号不相同,而Rank()出现相同值时,排名是一样的。
示例:
SELECT RANK() OVER (ORDER BY salary DESC) AS ranking, name, salary
FROM employees;
效果:
ranking | name | salary |
---|---|---|
1 | John | 1000 |
2 | Linda | 900 |
3 | Michael | 700 |
3 | Alice | 700 |
5 | Bob | 600 |
四、DENSE_RANK()函数
DENSE_RANK()函数:为结果集中的每一行分配一个排名,相同值的行具有相同的排名,下一个排名是连续的。和RANK()区别在于,DENSE_RANK()排名是连续的,RANK()是跳跃的排名。
示例:
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking, name, salary
FROM employees;
效果图:
ranking | name | salary |
---|---|---|
1 | John | 1000 |
2 | Linda | 900 |
3 | Michael | 700 |
3 | Alice | 700 |
4 | Bob | 600 |
五、NTILE()函数
NTILE()函数:将结果集分成n个大小相等的组,并为每个组分配一个组号。 示例:
SELECT NTILE(3) OVER (ORDER BY salary DESC) AS group_number, name, salary
FROM employees;
效果:
group_number | name | salary |
---|---|---|
1 | John | 1000 |
1 | Linda | 900 |
2 | Michael | 800 |
3 | Alice | 700 |
3 | Bob | 700 |
总结
本文解释MySQL中四大排名函数的具体功能,并做了的使用示例。具体效果会根据实际数据和排序条件不同而有所差异。