一、问题:
有一个薪水表salaries简况如下:
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
现要求对所有员工的薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列:
二、解决:
-- 插询语句
select emp_no,salary,
dense_rank() over(order by salary desc)as t_rank
from salaries
order by t_rank,emp_no asc
三、涉及到窗口函数:
1、窗口函数:也叫OLAP函数(Online Anallytical Processing,联机分析处理)可以对数据库数据进行实时分析处理。
2、窗口函数的基本语法: <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
3、窗口函数的类型:根据窗口函数的位置,可以放两种函数:
1)专用的窗口函数,rank,dense_rank,row_number 等专用串口函数。
2)聚合函数,如sum,avg,count,max,min等。
窗口函数中rank,dense_rank,row_number 的区别:
rank 是有相同的值排序后的序号会占用正常顺序,如:1224
dense_rank 则不会占用,如:1223,
row_number 就是序号了不管是否相同,如: 1234。
4、窗口函数使用场景
1)业务需求“在每组内排名”,比如:
排名问题:每个部门按业绩来排名
topN问题:找出每个部门排名前N的员工进行奖励
5、窗口函数特点:
1)由于窗口函数是对where 或者group by字句处理后的结果进行操作,所以窗口函数原则上只能写在select字句中。
2)同时具有分组和排序的功能,并且不减少原表的行数 。
6、注意 group by 和 partition by 的区别:
group by分组汇总后改变了表的行数,一行只有一个类别。而partition by和rank函数不会减少原表中的行数。