MySQL——函数：窗口函数

窗口使用要在MySQL8.0以上，且比较重要。

1.准备数据

create table employee (
dname varchar(20) ,-- 部门名
eid varchar(20),
ename varchar(20),
hiredate date, -- 入职日期
salary double -- 薪资
);

insert into employee values('研发部','1001','刘备','2021-11-01',3000),
('研发部','1002','关羽','2021-11-02' ,5000),
( '研发部','1003','张飞','2021-11-03',7000),
('研发部','1004','赵云','2021-11-04',7000),
( '研发部','1005','马超','2021-11-05',4000),
( '研发部','1006','黄忠','2021-11-06',4900),
( '销售部','1007','曹操','2021-11-01',2000),
( '销售部','1008','许褚','2021-11-02',3000),
( '销售部','10091','典韦','2021-11-03',5000),
('销售部','1010','张辽','2021-11-04',6000),
( '销售部','1011','徐晃','2021-11-05',9000),
('销售部','1012','曹洪','2021-11-06',6000);

部分数据展示：

2.排序函数例子与区分

SELECT  
    dname,  
    ename,  
    salary,
    row_number() over (partition by dname order by salary desc) as rn1,
    dense_rank() over(partition by dname order by salary desc) as rn2,
    rank() over(partition by dname order by salary desc) as rn3
FROM  
    employee;

查询结果：

row_number()函数的序号不间断，每个序号出现的次数为1，出现相同数据的时候依照进行排序；

dense_rank()函数的序号不间断，每个序号出现的次数会重复，当数据相同的时候序号也相同，较为常用；

rank()函数的序号是间断的，每个序号出现的系数会重复。

注：提取排名时不能利用where进行筛选，需要利用子函数进行筛选

select * 
from(
SELECT  
    dname,  
    ename,  
    salary,
    dense_rank() over(partition by dname order by salary desc) as rn
FROM  
    employee) new_table
where rn=1 or rn=3  or rn=5;

3.聚合函数

sum函数

若没有order by 排序语句，默认把组内的所有数据进行sum操作。

SELECT  
    dname,  
    ename,  
    salary,
    sum(salary) over(partition by dname order by hiredate) as c1,
		sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c2,
		sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c3,
		sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c4,
		sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c5
FROM  
    employee;

c1:没有order by 排序语句，默认把组内的所有数据进行sum操作。alter
c2:从开头到当前行相加。
c3:从当前行再向上三行相加，一共四行。
c4:从当前行再向上三行再加向后一行相加，一共5行。
c5:从当前行到最后一行相加。

结果如下所示：

4.分布函数

sume_dist:分组内小于等于当前值的总数/分组内总行数

例子：查询小于等于当前薪资的比例

select dname,ename,salary,
ROUND(cume_dist() over(order by salary),3) as rn1,
ROUND(cume_dist() over(partition by dname order by salary),3) as rn2
from employee;

解析：总共有12个人，刘备的工资为3000,小于等于3000的总数为3，3/12=0.25。类似于累积分布函数

persent_rank:每行按照公式（rank-1)/(rows-1）不常用

select dname,ename,salary,
rank() over(PARTITION BY dname order by salary desc) as rk,
PERCENT_RANK() over(partition by dname order by salary desc) as rn2
from employee;

5.前后函数

LAG(expr,n)返回当前行的前n行或后n行的lead(expr,n) expr值
例子：将上一行的日期和本行的日期放在一行

select dname,ename,salary,hiredate,
lag(hiredate,1,'2000-01-01') over(PARTITION BY dname order by hiredate) as last_1_time,
LAG(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;

select dname,ename,salary,hiredate,
lead(hiredate,1,'2000-01-01') over(PARTITION BY dname order by hiredate) as last_1_time,
lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time
from employee;

6.头尾函数

FIRST_VALUE(expr)和last_value(expr)
例子：到本行时间位置，按照日期查询第一个入职和最后一个入职员工的薪资

select dname,ename,salary,hiredate,
FIRST_VALUE(salary) over(PARTITION BY dname order by hiredate) as first,
LAST_VALUE(salary) over(partition by dname order by hiredate) as last
from employee;

七.其它函数

NTILE(N):将分区中的有序数据分为n个等级，记录等级数
例子：将每个部门员工按照入职日期分成3组

select dname,ename,salary,hiredate,
NTILE(4) over(PARTITION BY dname order by hiredate) as rn
from employee;