【SQL】MySQL中的窗口函数（开窗函数）

窗口函数是MYSQL8.0新增的

聚合函数： 多行变一行，常见的sum，count，max，min
窗口函数： 行数不变，常见的row_number,rank

语法格式：

窗口函数(表达式) over (partition by … order by … frame_clause)
partition by是分区，类似于group by，如去掉相当于对所有数据进行计算
order by排序
frame_clause用于在分区内指定窗口大小，指定计算的区域

---

表employee
字段
dname 部门
ename 员工
hiredate 入职日期
salary 薪水
使用场景：
一、分组排序

-- 每个部门的员工按薪水由高到低排序并添加序号
select 
dname,
ename,
salary,
row_number() over (partition by dname order by salary desc) as 序号
from employee 
1
2
3
4
5
6
7

二、分组累加

-- 每个部门的员工薪水从高到低排序，每个部门薪水累加
select 
dname,
ename,
salary,
sum(salary) over (partition by dname order by salary desc) as 累计值
from employee 
1
2
3
4
5
6
7

三、分组求和

-- 每个部门薪水总和，相比group by dname求和来说，这里返回的条数不会变少
select 
dname,
ename,
salary,
sum(salary) over (partition by dname) as 部门薪水总和
from employee 
1
2
3
4
5
6
7

四、向前取向后取

-- lag(salary,1,100)返回上一行的salary值，上一行没值默认为100
-- lag(salary,5,100)返回上五行的salary值，上一行没值默认为100
-- lead()返回下n行的值，用法与lag相同
select 
dname,
ename,
salary,
lag(salary,1,0) over(partition by dname order by salary) as 前一名的薪水,
lag(salary,2) over(partition by dname order by salary) as 前两名的薪水
from employee
1
2
3
4
5
6
7
8
9
10

五、组内第一个/最后一个的值

-- 头尾函数first_value、last_value
-- first_value取分组内第一个的值
-- last_value取分组内最后一个的值（由于默认计算窗口是从首行到当前行，因此直接使用的话就是取当前行的值，需要指定窗口为首行到最后一行，才能真正实现取组内最后一个值）
select 
dname,
ename,
hiredate,
salary,
first_value(salary) over(partition by dname order by hiredate) as 部门首次入职员工薪资
from employee
1
2
3
4
5
6
7
8
9
10

六、组内第n个的值

-- nth_value取分组内第n个值（未指定窗口大小则为首行截止到当前行的第n个，不够n个就是null）
select 
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname order by hiredate) as 部门内第二个入职员工薪资
from employee
1
2
3
4
5
6
7
8

七、平均分组

-- ntile平均分组
select 
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate) as 部门内的组号
from employee
1
2
3
4
5
6
7
8

---

指定窗口大小（计算区域）:

-- 控制开窗函数的计算区域
-- 指定范围首行到当前行（默认是这个）
rows between unbounded preceding and current row
-- 指定范围前3行到当前行
rows between 3 preceding and current row 
-- 指定范围前3行到后1行
rows between 3 preceding and 1 following
-- 指定范围当前行到最后行
rows between current row and unbounded following
/*
unbounded preceding  首行 
current row          当前行
unbounded following  最后行
n preceding          前n行
n following          后n行
*/ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16