什么是window functions

window functions（开窗函数），主要是用来解决聚合函数执行后丢失原有结果的问题。
通过使用window functions可以同时携带原有结果并且具有聚合后的结果。

目前，MySQL8.0已经支持开窗函数了。喜大普奔

MySQL8.0 - Window Functions

知识点复习

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;
order by desc
  • over(order by # asc)
select id,
       name,
       dept,
       sum(salary) over (partition by dept order by salary asc )
from t_window_func;
order by desc

求总和

over() 中不包含 partition by 和 order by 时，对所有结果集进行求和。

select id,
       name,
       dept,
       sum(salary) over ()
from t_window_func;
结果集