窗口函數(shù)初識
窗口函數(shù)也叫OLAP函數(shù)（Online Anallytical Processing,聯(lián)機(jī)分析處理）桑嘶，可以對數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理。窗口函數(shù)和group by有類似之處呐粘，其區(qū)別在于窗口會對每個(gè)分組之后的數(shù)據(jù)進(jìn)行分別操作，而group by一般對分組之后的函數(shù)使用聚集函數(shù)匯總劫侧。
窗口函數(shù)進(jìn)階
mysql 8.0 之后的版本開始支持窗口函數(shù)的功能萌踱，窗口函數(shù)一般用于實(shí)現(xiàn)排名和Top N的問題，下面用過leetcode的幾個(gè)例子來說明一下槽地，窗口函數(shù)的用法迁沫。
Leetcode 178 （Medium）成績排名
這個(gè)題目需要實(shí)現(xiàn)對成績的排名芦瘾，并且有相同分?jǐn)?shù)時(shí)排名相同，但不占用下一個(gè)同學(xué)的名次集畅〗埽考慮使用窗口函數(shù)dense_rank()：
select score, dense_rank() over(order by score desc) as "Rank" from Scores;
Leetcode 184 （Medium）部門工資最高的員工
這個(gè)題目需要找出每個(gè)分組中的最大值，可以使用group by挺智，但是我推薦使用窗口函數(shù)dense_rank()：
select ed.Department, ed.Employee, ed.Salary from
(select d.Name as Department, e.Name as Employee, e.Salary,
dense_rank() over(partition by d.Name order by e.Salary desc) as ranking
from Employee as e inner join Department as d on e.DepartmentId = d.Id) as ed
where ed.ranking = 1 order by ed.Department;
除了使用排名函數(shù)dense_rank()以外祷愉，我們還使用分組操作partition by，在每個(gè)分組中構(gòu)造排名赦颇，并通過where篩選二鳄。
Leetcode 185 （Hard）部門工資前三高的所有員工
這個(gè)題目是一個(gè)Top N的問題，這里的N為3媒怯，因此我們還是使用窗口函數(shù)分組并在上一題的基礎(chǔ)上更改篩選條件：
select Department, Employee, Salary
from (select d.Name as Department,
e.Name as Employee,
e.Salary as Salary,
dense_rank() over(partition by d.Name order by e.Salary desc) as ranking
from Employee as e inner join Department as d on e.DepartmentId = d.Id) as ed
where ed.ranking <=3;
