什么是window functions
window functions（开窗函数），主要是用来解决聚合函数执行后丢失原有结果的问题。
通过使用window functions可以同时携带原有结果并且具有聚合后的结果。
目前，MySQL8.0已经支持开窗函数了。喜大普奔
知识点复习
group by是分组函数
group by则只保留参与分组的字段和聚合函数的结果
sum()等聚合函数
avg()、sum()、min()、max()
求平均、求和、求最大最小等
partition by是分析函数
partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序
样例数据准备
通过创建一个测试数据表，里面包含员工编号、员工姓名、部门和薪资数据。
CREATE TABLE `t_window_func` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dept` varchar(32) DEFAULT NULL,
`salary` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (1, '员工1', '部门1', 10000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (2, '员工2', '部门1', 11000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (3, '员工3', '部门1', 12000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (4, '员工4', '部门2', 13000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (5, '员工5', '部门2', 14000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (6, '员工6', '部门2', 15000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (7, '员工7', '部门3', 16000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (8, '员工8', '部门3', 17000);
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (9, '员工9', '部门3', 18000);
开窗函数与排名函数结合
窗口函数只要有以下几个：
row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()
下面将每种介绍一个场景进行测试
row_number()
显示结果集的行号，当遇到相同数据时，会直接顺序编号。
分别统计每个部门薪资排名
通过对每个部门分区，对结果集进行汇总，展示在salaryRank这一列，在没有开窗函数之前，实现此功能可是不简单。
select
name, dept, salary,
row_number() over (partition by dept order by salary desc ) salaryRank
from t_window_func
统计每个部门薪资排名最高的前两个人
select *
from (select name,
dept,
salary,
row_number() over (partition by dept order by salary desc ) salaryRank
from t_window_func
) tmp
where salaryRank <= 2
rank()、dense_rank()
区别
rank()用于对结果记录生成序号，dense_rank()和rank()功能相同，当遇到排序字段数据相同时，两者的处理方式不同。
- rank(): 跳跃排序，当第1、2行结果相同时，第1、2行排序为1，第3行排序为3
- dense_rank(): 不跳跃排序，当第1、2行结果相同时，第1、2行排序为1，第3行排序为2
数据准备
首先往表中插入一条数据，使部门3有两个薪资一样的员工。
INSERT INTO test_db.t_window_func (id, name, dept, salary) VALUES (10, '员工10', '部门3', 17000);
统计每个部门的排名
select name,
dept,
rank() over (partition by dept order by salary desc ) salaryRank,
dense_rank() over (partition by dept order by salary desc ) salaryDenseRank
from t_window_func;
ntile()
函数ntile(group_num)主要用于数据分组，将所有记录分成group_num个组，每组序号一样。
当数据不足以整除时，会把不够除的余数分给第一组。
普通ntile()分组
select
name, dept, ntile(3) over (order by salary desc)
from t_window_func;
结合partition by分区分组
针对部门，进行分区分2组。
select
name, dept, ntile(2) over (partition by dept order by salary desc)
from t_window_func;
cume_dist()
计算某个值在一组有序的数据中累计的分布
计算方法为：相对位置/总行数,返回值为(0,1]
注意：对于重复值，计算的时候，取重复值的最后一行的位置
select name,
dept,
salary,
round(cume_dist() over (order by salary ), 2) randSalary
from t_window_func;
percent_rank()
和cume_dist() 的不同点在于计算分布结果的方法
计算方法为：（相对位置-1）/（总行数-1）
注意：对于重复值，计算的时候，取重复值的第一行的位
select name,
dept,
salary,
round(percent_rank() over (order by salary ), 2) randSalary
from t_window_func;
同一个window被多次使用
select name,
dept,
salary,
row_number() over w salaryRowNumber,
rank() over w salaryRank,
dense_rank() over w salaryDenseRank
from t_window_func
window w as (partition by dept order by salary desc )
窗口函数和sum()等聚合函数结合
下面通过sum()函数演示具体的效果，对于avg()等聚合函数效果均相同。
sum()
分部门求和
select id,
name,
dept,
sum(salary) over (partition by dept )
from t_window_func;
和group by结果类似，只是展示形式不同。group by 结果：
select id,
name,
dept,
sum(salary)
from t_window_func
group by dept;
分部门阶段性累计求和
上述窗口函数当over中加入了order by之后，结果集大不相同。(order by DESC|ASC)也影响结果
- over(order by # desc)
select id,
name,
dept,
sum(salary) over (partition by dept order by salary desc )
from t_window_func;
- over(order by # asc)
select id,
name,
dept,
sum(salary) over (partition by dept order by salary asc )
from t_window_func;
求总和
over() 中不包含 partition by 和 order by 时，对所有结果集进行求和。
select id,
name,
dept,
sum(salary) over ()
from t_window_func;
