【数据库高级】Mysql窗口函数的使用和练习 |
您所在的位置：网站首页 › 开窗函数用法 › 【数据库高级】Mysql窗口函数的使用和练习 |
【数据库高级】Mysql窗口函数的使用和练习
|
Mysql窗口函数
🌾Mysql窗口函数🕊️一、什么是窗口函数🍃1、怎么理解窗口?🍃2、什么是窗口函数🍵1. 基本语法：🍵2. 窗口函数多用在什么场景？主要有以下两类：🍵3. 我们常见的窗口函数和聚合函数有这些：🍵4. 窗口函数和普通聚合函数的区别？
🕊️二、窗口函数的练习🍃1、序号函数🍃2、分布函数：🍃3、前后函数🍃4、首尾函数🍃5、其他函数
🕊️三、实战
🌾Mysql窗口函数
本文主要介绍了MySQL窗口函数的定义和具体使用。 首先窗口函数是从MySQL8.0开始支持的，如果现在使用的是MySQL5.0或者8.0一下的版本，那么非常遗憾，建议搞个8.0版本试一试，哈哈~ 🕊️一、什么是窗口函数 🍃1、怎么理解窗口?搞清楚窗口代表着啥，才知道什么时候该用它。 窗口函数是相对于聚函数来说的。 聚合函数是对一组数据计算后返回单个值（即分组）。非聚合函数一次只会处理一行数据。而窗口函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。
根据上面所说，准备如下员工表信息数据 -- 员工表 create table if not exists sql_niukewang.`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 sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('傅嘉熙', '开发部', '2022-08-20 12:00:04', 9000); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('武晟睿', '开发部', '2022-06-12 13:54:12', 9500); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('孙弘文', '开发部', '2022-10-16 08:27:06', 9400); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('潘乐驹', '开发部', '2022-04-22 03:56:11', 9500); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('潘昊焱', '人事部', '2022-02-24 03:40:02', 5000); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('沈涛', '人事部', '2022-12-14 09:16:37', 6000); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('江峻熙', '人事部', '2022-05-12 01:17:48', 5000); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('陆远航', '人事部', '2022-04-14 03:35:57', 5500); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('姜煜祺', '销售部', '2022-03-23 03:21:05', 6000); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('邹明', '销售部', '2022-11-23 23:10:06', 6800); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('董擎苍', '销售部', '2022-02-12 07:54:32', 6500); insert into sql_niukewang.`employee` (`ename`, `dname`, `hiredate`, `salary`) values ('钟俊驰', '销售部', '2022-04-10 12:17:06', 6000);
我们举个例子：分别使用聚合函数sum（）和窗口函数sum（）来根据部门求和看下两者区别 select dname,sum(salary) sum from employee group by dname;
通过观察，正如之前所说，窗口函数相对聚合函数，聚合函数是将一组数据计算后返回单个值，而窗口函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数，就好比如我们刚刚根据部门开窗求和salary薪资，每一行的sum数据是将前面范围内的数据都聚合到当前结果中。 所以可见，窗口就是范围的意思，可以理解为一些记录（行）的集合；窗口函数也就是在满足某种条件的记录集合上执行计算的特殊函数。
窗口函数也叫OLAP函数（Online Anallytical Processing），可以对数据进行实时分析处理。 🍵1. 基本语法： OVER (PARTITION BY ORDER BY ); -- over关键字用于指定函数的窗口范围， -- partition by 用于对表分组， -- order by子句用于对分组后的结果进行排序。注意：窗口函数是对where或者group by子句处理后的结果再进行二次操作，因此会按照SQL语句的运行顺序，窗口函数一般放在select子句中（from前），例如上一条SQL，可以往上拖着看看~ 🍵2. 窗口函数多用在什么场景？主要有以下两类： 排名问题，例如：每个部门的薪资排名；TOPN问题，例如：查每个部门薪资排名第一； 🍵3. 我们常见的窗口函数和聚合函数有这些： 专用窗口函数：rank()，dense_rank()，row_number()聚合函数：max()，min()，count()，sum()，avg()窗口函数都有哪些？
因为聚合函数也可以放在窗口函数中使用，因此窗口函数和普通聚合函数也很容易被混淆，二者区别如下： 聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。聚合函数也可以用于窗口函数中，这个我会举例说明。 🕊️二、窗口函数的练习还是使用上面的员工表信息完成下面的练习。 🍃1、序号函数序号函数有：ROW_NUMBER、RANK、DENSE_RANK，也就是序号排名的意思。 ROW_NUMBER()：顺序排序 —— 1、2、3 RANK()：并列排序，跳过重复序号 —— 1、1、3 DENSE_RANK()：并列排序，不跳过重复序号 —— 1、1、2 **应用场景：**求每个部门的员工薪资排名 ROW_NUMBER（）函数 select dname,salary, row_number() over(partition by dname order by salary) sum from employee;
总结： 上面针对同一个应用场景使用三种不同的序号函数，得到三种不同的结果，我们重点需要注意在三种结果的区别。 row_number()函数只是做一个顺序排序 —— 1、2、3…rank()函数做了顺序排序，但是做了并列排序，并跳过重复序号 —— 1、1、3dense_rank()函数 🍃2、分布函数：分布函数有：percent_rank() 、 cume_dist() cume_dist()：分组内小于、等于当前rank值的行数 / 分组内总行数 percent_rank()：每行按照公式(rank-1) / (rows-1)进行计算 CUME_DIST（）函数**应用场景：**查询小于等于当前薪资（salary）的比例 select dname,ename,salary, rank() 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;
这里使用了序号函数 row_number(),目的是为了更好的理解分布函数的cume_dist()函数。 cume_dist()函数作用是分组内小于、等于当前rank值的行数 / 分组内总行数，如上结果人事部的rank值为4行， |
【本文地址】