第6章 窗口函数

Mr.Tong...
  • mysql
  • mysql
大约 11 分钟

本文主要介绍了MySQL窗口函数的定义和具体使用。

首先窗口函数是从MySQL8.0开始支持的,如果现在使用的是MySQL5.0或者8.0一下的版本,那么非常遗憾,建议搞个8.0版本试一试,哈哈~

📖一、窗口函数是什么

✅怎么理解窗口函数

搞清楚窗口代表着啥,才知道什么时候该用它。

窗口函数是相对于聚函数来说的。

  • 聚合函数是对一组数据计算后返回单个值(即分组)。
  • 非聚合函数一次只会处理一行数据。
  • 而窗口函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。

image-20221130132011999

image-20221130132024699

根据上面所说,准备如下员工表信息数据

-- 员工表
create table if not exists  `employee`
(
    `eid` int not null auto_increment comment '员工id' primary key,
    `ename` varchar(20) not null comment '员工名称',
    `dname` varchar(50) not null comment '部门名称',
    `hiredate` date not null comment '入职日期',
    `salary` double null comment '薪资'
) comment '员工表';
insert into  `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('傅嘉熙', '开发部', '2022-08-20 12:00:04', 9000);
insert into  `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('武晟睿', '开发部', '2022-06-12 13:54:12', 9500);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('孙弘文', '开发部', '2022-10-16 08:27:06', 9400);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('潘乐驹', '开发部', '2022-04-22 03:56:11', 9500);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('潘昊焱', '人事部', '2022-02-24 03:40:02', 5000);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('沈涛', '人事部', '2022-12-14 09:16:37', 6000);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('江峻熙', '人事部', '2022-05-12 01:17:48', 5000);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('陆远航', '人事部', '2022-04-14 03:35:57', 5500);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('姜煜祺', '销售部', '2022-03-23 03:21:05', 6000);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('邹明', '销售部', '2022-11-23 23:10:06', 6800);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('董擎苍', '销售部', '2022-02-12 07:54:32', 6500);
insert into `employee` (`ename`, `dname`, `hiredate`, `salary`) values ('钟俊驰', '销售部', '2022-04-10 12:17:06', 6000);

image-20221130132256975

我们举个例子:分别使用聚合函数sum()和窗口函数sum()来根据部门求和看下两者区别

select
dname,sum(salary) sum 
from employee group by dname;

image-20221130134506477

select
dname,salary,
sum(salary) over(partition by dname order by salary) sum
from employee;

image-20221130135204141

​ 通过观察,正如之前所说,窗口函数相对聚合函数,聚合函数是将一组数据计算后返回单个值,而窗口函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数,就好比如我们刚刚根据部门开窗求和salary薪资,每一行的sum数据是将前面范围内的数据都聚合到当前结果中。

​ 所以可见,窗口就是范围的意思,可以理解为一些记录(行)的集合;窗口函数也就是在满足某种条件的记录集合上执行计算的特殊函数。

image-20221130140256430

窗口函数也叫OLAP函数(Online Anallytical Processing),可以对数据进行实时分析处理。

✅基本语法

<窗口函数> OVER (PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名>);
-- over关键字用于指定函数的窗口范围,
-- partition by 用于对表分组,
-- order by子句用于对分组后的结果进行排序。

注意:窗口函数是对where或者group by子句处理后的结果再进行二次操作,因此会按照SQL语句的运行顺序,窗口函数一般放在select子句中(from前),例如上一条SQL,可以往上拖着看看~

✅使用场景

常用的使用场景如下:

  • 排名问题,例如:查包子铺利润月排名;
  • TOPN问题,例如:查每种包子利润最高的两个月;

✅常用的窗口函数

  • 专用窗口函数:rank()dense_rank()row_number()
  • 聚合函数:max()min()count()sum()avg()

窗口函数都有哪些?

todo:聚合函数可以在开创使用吗?

image-20221130162144455

image-20221130144643127

  • 序号函数:row_number() / rank() / dense_rank()
  • 分布函数:percent_rank() / cume_dist()
  • 前后函数:lag() / lead()
  • 头尾函数:first_val() / last_val()
  • 其他函数:nth_value() / nfile()

✅窗口函数和普通聚合函数的区别?

因为聚合函数也可以放在窗口函数中使用,因此窗口函数和普通聚合函数也很容易被混淆,二者区别如下:

  • 聚合函数是将多条记录聚合为一条;而窗口函数是每条记录都会执行,有几条记录执行完还是几条
  • 聚合函数也可以用于窗口函数中,这个我会举例说明。

📖二、窗口函数的练习

还是使用上面的员工表信息完成下面的练习。

✅序号函数

序号函数有如下三种:

  • ROW_NUMBER():顺序排序
  • RANK():并列排序,跳过重复序号
  • DENSE_RANK():并列排序,不跳过重复序号

**应用场景:**求每个部门的员工薪资排名

  1. ROW_NUMBER()函数
SELECT
	dname,
	salary,
	row_number() over ( PARTITION BY dname ORDER BY salary ) ranking 
FROM
	employee

image-20221130145806440

  1. RANK()函数
SELECT
	dname,
	salary,
	rank() over ( PARTITION BY dname ORDER BY salary  ) ranking 
FROM
	employee 

image-20221130150327353

  1. DENSE_RANK()函数
SELECT
	dname,
	salary,
	dense_rank() over ( PARTITION BY dname ORDER BY salary ) ranking 
FROM
	employee;

image-20221130150705049

总结:

上面针对同一个应用场景使用三种不同的序号函数,得到三种不同的结果,我们重点需要注意在三种结果的区别。

比如现在有99,99,98,97四位数,分别用不同函数进行排序:

  • row_number()函数只是做一个顺序排序,不在意是否重复 ,所以排名结果是: 1、2、3、4
  • rank()函数是做了并列排序,并跳过重复序号,所以排名结果是: 1、1、3、4
  • dense_rank()函数也是做了并列排序,但是不跳过重复序号:所以排名结果是: 1、1、2、3

✅分布函数:

分布函数有如下两种:

  • cume_dist()分组内小于、等于当前rank值的行数 / 分组内总行数
  • percent_rank()每行按照公式(rank-1) / (rows-1)进行计算
  1. CUME_DIST()函数

**应用场景:**查询小于等于当前薪资(salary)的比例

SELECT
	dname,
	ename,
	salary,
	row_number() over ( PARTITION BY dname ORDER BY salary ) ranking,
	cume_dist() over ( ORDER BY salary ) dist1,
	cume_dist() over ( PARTITION BY dname ORDER BY salary ) dist2 
FROM
	employee;

image-20221130153747811

  • 这里使用了序号函数 row_number(),目的是为了更好的理解分布函数的cume_dist()函数。

  • cume_dist()函数作用是分组内小于、等于当前rank值的行数 / 分组内总行数,如上结果人事部的rank值为4行,而<=5000的rank值行数为2,分组内总行数为4,所以cume_dist的值=(2/4)=0.5。以此类推,下面的结果都是这样的。

注意:没有使用partition by 分组的默认是所有数据为一组,比如上面的dist1列的数据

  1. percent_rank()函数

**用途:**用于计算某个值在已排序的行集合中的百分位排名。它返回一个介于 0 和 1 之间的值,表示当前行值在已排序行集中的相对位置。(不常用)

具体而言,PERCENT_RANK() 函数返回的值是当前行值在整个结果集中的相对位置(百分比形式)。如果有多个相同的值,则它们将被视为具有相同的排名,并且将返回相同的百分位排名。

计算的原理:每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数

SELECT
	dname,
	ename,
	salary,
	row_number() over ( PARTITION BY dname ORDER BY salary ) ranking,
	cume_dist() over ( ORDER BY salary ) dist1,
	cume_dist() over ( PARTITION BY dname ORDER BY salary ) dist2 
FROM
	employee;

image-20221130162654580

#分析
/*
 per:
  第一行: (1 - 1) / (4 - 1) = 0
  第二行: (1 - 1) / (4 - 1) = 0
  第三行: (3 - 1) / (4 - 1) = 0.6666666666666666
  第四行:(4 - 1) / (4 - 1) = 1
*/

由上面的注释我们可以理解percent_rank()函数的使用,我们可以把它转换为

  • 如果是升序排列,小于等于你的占比多少
  • 如果是降序排列,大于等于你的占比多少

应用场景不是很多,但是咱们也可以学习一下,万一以后遇到了也知道之前好像接触过~

✅前后函数

前后函数有如下两种:

LAG(expr,n):返回位于当前行的前n行

LEAD(expr,n):后n行的expr的值

  • 返回当前行的前n行(本组)的expr值
  • lag允许你在每一个分组内, 从当前行向前看n行数据
  • n(也叫offset)是从当前行偏移的行数,以获取值。offset必须是一个非负整数。如果offset为零,则LAG()函数计算当前行的值。如果省略 offset,则LAG()函数默认使用n=1, 向前看一个数据。
  1. LAG(expr,n)前函数

应用场景:求各部门内部相邻组员的薪资差

第一步:先使用前函数查出前一个员工薪资

select dname,ename,salary,
lag(salary,1) over(partition by dname order by salary) presalary  from employee;

image-20221130165306561

第二步:把第一步的结果作为表进行查询,并做-运算

select * ,(salary-presalary)  from (
	select dname,ename,salary,
	lag(salary,1) over(partition by dname order by salary) presalary  from employee
) pre_table;

image-20221130165558976

  1. LEAD(expr,n)后函数

应用场景:求各部门内部相邻组员的薪资差

其实和上面的LAG函数一样,只不过就是一个是向上偏移,一个是向下偏移。

第一步:先使用前函数查出后一个员工薪资

SELECT
	dname,
	ename,
	salary,
	lead( salary, 1 ) over ( PARTITION BY dname ORDER BY salary ) presalary 
FROM
	employee;

第二步:把第一步的结果作为表进行查询,并做-运算

SELECT *,( salary - presalary ) 
FROM
	( SELECT dname, ename, salary, lead( salary, 1 ) over ( PARTITION BY dname ORDER BY salary ) presalary FROM employee ) pre_table;

image-20221130170037212

总结:对于前后函数,向前还是向后偏移大家可以灵活使用。

✅首尾函数

首尾也叫头尾函数,有如下两张:

  • first_value(expr)返回在指定排序顺序下的第一个行值。

  • last_value(expr)返回在指定排序顺序下的最后一个行值

需求:截止到当前,按照日期排序查询第1个入职和最后1个入职员工的薪资

first_value(expr)函数

SELECT
	*,
	FIRST_VALUE( salary ) over ( PARTITION BY dname ORDER BY hiredate ) firsts 
FROM
	employee;

image-20221130171801840

再比如求每个部门入职最早的人姓名:

SELECT
	d.dname,
	d.firsts 
FROM
	( SELECT *, LAST_VALUE( ename ) over ( PARTITION BY dname ORDER BY hiredate ) firsts FROM employee ) d 
GROUP BY
	d.dname

image-20240207191920747

last_value(expr)函数和first_value(expr)函数是一样的,这里就不再模拟演示了。

✅其他函数

NTH_VALUE(expr, n)、NTILE(n)

NTILE(n):将分区中的有序数据分为n个等级,记录等级数

NTH_VALUE(expr, n):返回窗口中第n个expr的值。expr可以是表达式,也可以是列名

  1. ntile(n)函数

需求:将每个部门员工按照入职日期分成2组

select * , 
ntile(2) over(partition by dname order by hiredate) `group` 
from employee;

image-20221130172604982

  1. nth_value(expr,n)函数

需求:截止到当前薪资,显示每个员工的薪资中排名第2或者第3的薪资

select * , 
nth_value(salary,2) over(partition by dname order by hiredate) twoSalary ,
nth_value(salary,3) over(partition by dname order by hiredate) threeSalary 
from employee;

image-20221130173623362

📖三、实战

  1. 牛客网:[SQL33 找出每个学校GPA最低的同学](找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)open in new window)

https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tqId=1980672&ru=%2Fpractice%2F26c8715f32e24d918f15db69518f3ad8&qru=%2Fta%2Fsql-quick-study%2Fquestion-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199open in new window

image-20221130174019796

你认为这篇文章怎么样?

  • 0
  • 0
  • 0
  • 0
  • 0
  • 0
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.14.1