DENSE_RANK 窗口函数

钢铁的面条

已于 2023-07-21 15:46:46 修改

阅读量231

点赞数

文章标签： sql mysql 数据库后端

于 2023-07-21 15:45:32 首次发布

本文链接：https://blog.csdn.net/m0_53653818/article/details/131852867

版权

DENSE_RANK函数用于在SQL中实现分组的并列排名，尤其在处理薪资或成绩等排名场景时。它基于OVER子句中的ORDERBY排序，若使用PARTITIONBY，则按分组重置排名。例如，在员工表中，可以按部门分组并按薪水降序排列，得到每个部门薪资的前几名。示例查询展示了如何使用此函数来获取每个部门的前三高薪资员工。

摘要由CSDN通过智能技术生成

这个函数能干什么呢？先做个题练练手吧（参考sql在文末）力扣https://leetcode.cn/problems/department-top-three-salaries/

函数作用：

DENSE_RANK 窗口函数基于 OVER 子句中的 ORDER BY 表达式确定一组值中的一个值的排名。如果存在可选的 PARTITION BY 子句，则为每个行组重置排名。

说人话：实现了分组的并列排名

函数语法：

DENSE_RANK () OVER
(

#按照什么进行分组比如按照部门、班级等
[ PARTITION BY expr_list ]

#按照什么进行排名比如工资、成绩等
[ ORDER BY order_list ]
)

函数参数：

( )

该函数没有参数，但需要空括号。

OVER

适用于 DENSE_RANK 函数的窗口子句。

PARTITION BY expr_list

可选。一个或多个定义窗口的表达式。

ORDER BY order_list

可选。排名值基于的表达式。如果未指定 PARTITION BY，则 ORDER BY 使用整个表。如果省略 ORDER BY，则所有行的返回值为 1。

函数返回值：即order字段在partition分组内的排名，

函数返回值的规律：order字段的值相同则排名相同；排名每次加一或者减一

举例：

输入: 
Employee 表:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

sql：
select * ,dense_rank()over(partition by departmentId order by salary desc) as rk 
from employee as t1

输出结果：
+----+-------+--------+--------------+----+
| id | name  | salary | departmentId | rk |
+----+-------+--------+--------------+----+
| 4  | Max   | 90000  | 1            | 1  |
| 7  | Joe   | 85000  | 1            | 2  |
| 6  | Randy | 85000  | 1            | 2  |
| 1  | Will  | 70000  | 1            | 3  |
| 5  | Janet | 69000  | 1            | 4  |
| 2  | Henry | 80000  | 2            | 1  |
| 3  | San   | 60000  | 2            | 2  |
+----+-------+--------+--------------+----+
department 字段按照部门进行了分类
同一个部门的 salary 依次递减
rk 表示 salary 在部门中的并列排名

通过以上代码的演示，我们就拿到了分部门（department）的薪水（salary）排名，我们只需要选出薪水排名（rk）小于等于3的并且和department表连接查询就可以完成上题了。

参考sql：

select t3.name as Department,t2.name as Employee,t2.salary as Salary
from (
    select * ,dense_rank()over(partition by departmentId order by salary desc) as rk 
    from employee as t1
) t2
left join Department as t3
on t3.id = t2.departmentId
where t2.rk <= 3;