程序员の小满

第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
0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.14.1