MySQL学习八：窗口函数（一）

一、窗口函数

1.1 窗口函数定义

窗口限定一个范围，可以理解为满足某些条件的记录集合。窗口函数也就是在窗口范围内的执行的函数

1.2 窗口函数语法

窗口函数有 over 关键字，指定函数执行的范围。可分为三部分：分组字句 (partition by)，排序字句 (order by)，滑动窗口字句 (rows)

<函数名> over (partition by <分组的列名>, order by <排序的列名> rows between <起始行> and <终止行>)
1

1.3 演示表格一

create table gradeInfo1 (
    cid varchar(32),
    sname varchar(32),
    score int
);

insert into gradeInfo1 (cid, sname, score)
values
('001', '张三', 78),
('001', '李四', 82),
('002', '小明', 90),
('001', '王五', 67),
('002', '小红', 85),
('002', '小刚', 90);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.4 窗口的确定

1.4.1 例1：查询各班级总分

# 方法一
select *,
sum(score) over(partition by cid) as 班级总分
from gradeinfo1

# 方法二
select *, 
sum(score) over(partition by cid order by score rows between unbounded preceding and unbounded following) as 班级总分
from gradeinfo1
1
2
3
4
5
6
7
8
9

1.4.2 例2：查询各班级累计总分

# 方法一
select *,
sum(score) over(partition by cid order by score) as 班级总分
from gradeinfo1

# 方法二
select *,
sum(score) over(partition by cid order by score rows between unbounded preceding and current row) as 班级总分
from gradeinfo1
1
2
3
4
5
6
7
8
9

1.4.3 分区子句（partition by）

① 不分区可以写成 partition by null，或者直接不写
② 后面可以跟多个列，例如 partition by cid, sname
partition by 和 group by的区别： partition by 不将行数去重，group by 会将行数去重

1.4.4 排序子句（order by）

① 不排序可以写成 order by null，或者直接不写
② 后面可以跟多个列，例如 order by cid, sname

1.4.5 窗口子句（rows）

语法概述：
① 起始行：N preceding / unbounded preceding
② 当前行：current row
③ 终止行：N following / unbounded following
举例：
① 从分区内的前面所有行到当前行：rows between unbounded preceding and current row
② 从分区内的前面 2 行 到当前行：rows between 2 preceding and current row
③ 从分区内的当前行到后面所有行：rows between current row and unbounded following
④ 从分区内的当前行到后面 3 行：rows between current row and 3 following
注意：
① 若排序字句后缺少窗口字句，窗口规范默认为：rows between unbounded preceding and current row
② 若排序字句和窗口字句都缺少，窗口规范默认为：rows between unbounded preceding and unbounded following

1.4.6 总体执行流程

① 通过 partition by 个 order by 确定大窗口（定义出上界和下界）
② 通过 rows 字句针对每一行数据，确定小窗口（即滑动窗口）
③ 针对每行的小窗口内的数据，执行函数并生成新的列

1.5 函数分类

1.5.1 排序类函数

① row_number：序号不重复，且序号连续（即连续排名，不考虑并列）。例如：1，2，3 …
② rank：序号可重复，且序号不连续（即跳跃排名，考虑并列）。例如：1，2，2，4 …
③ dense_rank：序号可重复，且序号连续（即连续排名，考虑并列）。例如：1，2，2，3 …

# 对每个班级的学生成绩进行排名
select *,
row_number() over(partition by cid order by score desc) as 不并列排名,
rank() over(partition by cid order by score desc) as 跳跃可并列排名,
dense_rank() over(partition by cid order by score desc) as 连续可并列排名
from gradeinfo1
1
2
3
4
5
6

1.5.2 聚合类函数

sum，avg，count，max，min

1.5.3 跨行类函数

① lead (比较的列，N)：返回当前行的后面 N 行
② lag (比较的列，N)：返回当前行的前面 N 行

# 每个班级中，成绩比自己高一名的成绩是多少
select *,
lag(score, 1) over(partition by cid order by score desc) as 高一名的分数
from gradeinfo1
1
2
3
4

二、相关题目练习

2.1 演示表格二

create table gradeInfo2 (
    cid varchar(32),
    sname varchar(32),
    course varchar(32),
    score int
);

insert into gradeInfo2 (cid, sname, course, score)
values
('001', '张三', '语文', 78),
('002', '小刚', '语文', 71),
('001', '李四', '数学', 56),
('001', '王五', '数学', 97),
('002', '小明', '数学', 54),
('002', '小刚', '数学', 67),
('002', '小红', '数学', 82),
('001', '王五', '语文', 80),
('001', '张三', '数学', 77),
('002', '小明', '语文', 58),
('002', '小红', '语文', 87),
('001', '李四', '语文', 60),
('001', '张三', '英语', 66),
('002', '小刚', '英语', 50),
('001', '李四', '地理', 59),
('001', '王五', '地理', 88),
('002', '小明', '地理', 45),
('002', '小刚', '地理', 66),
('002', '小红', '地理', 82),
('001', '王五', '英语', 81),
('001', '张三', '地理', 77),
('002', '小明', '英语', 55),
('002', '小红', '英语', 87),
('001', '李四', '英语', 61)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

题目 1：查询每个学生成绩最高的三个学科（分组内 Top N 问题）

select *
from(
	select *,
	row_number() over(partition by sname order by score desc) as ranking
	from gradeinfo2
) a
where ranking <= 3
1
2
3
4
5
6
7

题目 2：查询每个学科都高于班级学科平均分的学生（汇总分析问题）

select sname
from(
	select *, score - course_avg_score as del
    from(
    	select *,
		avg(score) over(partition by cid, course) as course_avg_score
		from gradeinfo2
    ) t1
) t2
group by sname
having min(del) > 0 
1
2
3
4
5
6
7
8
9
10
11

2.2 演示表格三

create table employee(
		empno varchar(32),
        ename varchar(32),
        hire_date varchar(32),
        salary int,
        dept_no varchar(32)
);

insert into employee(empno, ename, hire_date, salary, dept_no)
values
('001', 'Adam', '2018-03-01', 1000, 'A'),
('002', 'Bill', '2021-03-01', 1200, 'A'),
('003', 'Cindy', '2016-03-01', 1500, 'A'),
('004', 'Danney', '2020-03-01', 5000, 'A'),
('005', 'Eason', '2020-03-01', 4000, 'B'),
('006', 'Fred', '2018-03-01', 3500, 'B'),
('007', 'Gary', '2017-03-01', 1800, 'B'),
('008', 'Hugo', '2020-03-01', 4500, 'B')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

题目 1：查询每个部门工资最高的前三个员工信息（分组内 Top N 问题）

select *
from(
	select *,
	rank() over(partition by dept_no order by salary desc) as ranking
	from employee
) a
where ranking <= 3
1
2
3
4
5
6
7

题目 2：查询员工工资占所属部门总工资的百分比（汇总分析问题）

select *, 
concat(round(salary/total_salary*100, 3), '%') as salary_percent
from(
	select *,
	sum(salary) over(partition by dept_no) as total_salary
	from employee
) a

1
2
3
4
5
6
7
8

题目 3：对各部门员工的工资进行升序排列，排名前 30% 为底层，30% - 80% 为中层，80% 以上为高层，并打上标签

select *,
case
	when ranking_percent < 0.3 then '底层'
	when ranking_percent between 0.3 and 0.8 then '中层'
	else '高层'
	end as label
from(
	select *, round(ranking/total_employee, 2) as ranking_percent
	from(
		select *,
		rank() over(partition by dept_no order by salary) as ranking,
		count(empno) over(partition by dept_no) as total_employee
		from employee
	) a
) b
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

题目 4：查询每年入职总数以及截至本年累计入职总人数（本年总入职人数 + 本年之前所有年的入职人数之和）

# 方法一
# 创建临时表
with
a as(
	select year(hire_date) as 入职年份, count(empno) as 入职员工数 from employee
	group by 入职年份
	order by 入职年份
)

select *,
sum(入职员工数) over(rows between unbounded preceding and current row) as 累计入职总人数
from a


# 方法二
select *,
sum(入职员工数) over(rows between unbounded preceding and current row) as 累计入职总人数
from(
	select year(hire_date) as 入职年份, count(empno) as 入职员工数 from employee
	group by 入职年份
	order by 入职年份
) a
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

三、窗口函数问题与技巧总结

① 本文用窗口函数解决的 2 个主要问题：分区内 Top N 问题，汇总分析问题

② 分区内 Top N 公式

select *
from(
	select *,
	row_number() over(partition by 分区列 order by 比较列) as ranking
	from gradeinfo2
) a
where ranking <= N
1
2
3
4
5
6
7

③ 窗口函数 → 生成辅助列（用于计算百分比等）

④ 重要：with 子句 → 生成临时表，把复杂的问题拆分成多个子问题，再用临时表表达