一.窗口函数应用场景:
在日常工作中,经常遇到需要分部门排序的问题。比如:
排名问题:每部门按照业绩排名;
topN问题:找出每部门排名前N的员工。
当我们不了解“窗口函数”神奇的存在时,我们使用“晦涩难懂”的自连接SQL解决该问题。而窗口函数的存在为我们解决问题提供了方便。
二.窗口函数简介:
MySQL从8.0开始支持窗口函数(OLAP函数),该功能在多数商业数据库和部分开源数据库中早已支持,用于快速解决SQL数据库多维分析处理问题。
窗口函数的基本语法如下:
over ( partition by
order by )
初学者经常混淆窗口函数与普通聚合函数的关系,二者区别如下:
聚合函数:将多条记录聚合为一条,窗口函数:每条记录都会执行,有几条记录执行完还是几条;
聚合函数可以用于窗口函数中。
三.窗口函数的使用:
窗口函数种类繁多,我们这里只介绍序号函数——row_number( )/rank( )/dense_rank( )。
三者区别如下图所示:
四.窗口函数的例子:
我们以leetcode面试题为例:
这是一道经典的topN问题,涉及先分组再排序,并且属于dense_rank( )问题。
面对这种问题,解题步骤如下:
按给定列名分组(partiotion by 分组列名),并按给定列名排序(order by 排序列名),套入dense_rank窗口函数:
select *,
dense_rank()over(partition by分组列名order by 排序列名 desc) asrkfrom 表名;
2. 筛选出topN,所以我们在上一步基础上加入where语句筛选符合条件的数据:
select *
from(select *,
row_number()over(
partitionby分组列名order by 排序列名 desc) asrkfrom 表名) asawhere rk <= N;
针对leetcode第185题,我的题解如下:
select d.Name asDepartment,
e.NameasEmployee,
e.SalaryasSalaryfrom(select *,
dense_rank()over(
partitionbyDepartmentIdorder by Salary desc) asrkfromEmployee) e, Department dwhere e.DepartmentId = d.Id and e.rk <= 3;