首页 > 资讯 > 数据库 >MySQL窗口函数OVER使用示例详细讲解

806

分享到

MySQL窗口函数OVER使用示例详细讲解

MySQL 窗口函数窗口函数OVER MySQL OVER函数 2023-01-05 11:01:18 806人浏览八月长安

摘要

目录窗口函数测试数据表及数据窗口函数空窗口窗口中只有 ORDER BY窗口中只有 PARTITioN BY 时同时有 PARTITION BY 与 ORDER BY窗口函数 OVER （PARTITION BY xxx

窗口函数

OVER （PARTITION BY xxx ORDER BY xxx ASC/DESC）

测试数据表及数据

测试表 employee

CREATE TABLE employee (
    `id` int unsigned not null auto_increment primary key,
    `name` varchar(80),
    `age` int(11),
    `salary` DECIMAL(18,1),
    `dept_id` int(11)
) ENGINE=InnoDB default charset=utf8mb4;

插入测试数据

INSERT into employee values(3, '小肖', 29, 30000.0, 1);
INSERT into employee values(4, '小东', 30, 40000.0, 2);
INSERT into employee values(6, '小非', 24, 23456.0, 3);
INSERT into employee values(7, '晓飞', 30, 15000.0, 4);
INSERT into employee values(8, '小林', 23, 24000.0, null);
INSERT into employee values(10, '小五', 20, 4500.0, null);
INSERT into employee values(11, '张山', 24, 40000.0, 1);
INSERT into employee values(12, '小肖', 28, 35000.0, 2);
INSERT into employee values(13, '李四', 23, 50000.0, 1);
INSERT into employee values(17, '王武', 24, 56000.0, 2);
INSERT into employee values(18, '猪小屁', 2, 56000.0, 2);
INSERT into employee values(19, '小玉', 25, 58000.0, 1);
INSERT into employee values(21, '小张', 23, 50000.0, 1);
INSERT into employee values(22, '小胡', 25, 25000.0, 2);
INSERT into employee values(96, '小肖', 19, 35000.0, 1);
INSERT into employee values(97, '小林', 20, 20000.0, 2);

窗口函数

partition by 是分区，每个分区形成一个窗口，聚合等计算都在这个分区内完成；

order by 是排序，排完序的数据组成不同的窗口，不同值的数据组成不同的窗口；

空窗口

当窗口中为空时，就是对表中所有数据进行计算

Mysql> select name,salary,SUM(salary) over() AS already_paid_salary FROM employee e ;

name|salary |already_paid_salary|
----+-------+-------------------+
小肖  |30000.0|           561956.0|
小东  |40000.0|           561956.0|
小非  |23456.0|           561956.0|
晓飞  |15000.0|           561956.0|
小林  |24000.0|           561956.0|
小五  | 4500.0|           561956.0|
张山  |40000.0|           561956.0|
小肖  |35000.0|           561956.0|
李四  |50000.0|           561956.0|
王武  |56000.0|           561956.0|
猪小屁 |56000.0|           561956.0|
小玉  |58000.0|           561956.0|
小张  |50000.0|           561956.0|
小胡  |25000.0|           561956.0|
小肖  |35000.0|           561956.0|
小林  |20000.0|           561956.0|

窗口中只有 ORDER BY

当窗口中只有 order by 时候，对全表数据进行排序，其作用和 FROM 后面的 ORDER BY 一样，

1）当与 FROM 后面的 ORDER BY 字段相同时，相当于只有 OVER(ORDER BY xxx)

mysql> select name,salary,SUM(salary) over(ORDER BY salary) AS already_paid_salary FROM employee e ;

name|salary |already_paid_salary|
----+-------+-------------------+
小五  | 4500.0|             4500.0|
晓飞  |15000.0|            19500.0|
小林  |20000.0|            39500.0|
小非  |23456.0|            62956.0|
小林  |24000.0|            86956.0|
小胡  |25000.0|           111956.0|
小肖  |30000.0|           141956.0|
小肖  |35000.0|           211956.0|
小肖  |35000.0|           211956.0|
小东  |40000.0|           291956.0|
张山  |40000.0|           291956.0|
李四  |50000.0|           391956.0|
小张  |50000.0|           391956.0|
王武  |56000.0|           503956.0|
猪小屁 |56000.0|           503956.0|
小玉  |58000.0|           561956.0|

2）当与 FROM 后面的 ORDER BY 字段不同时，FROM 子句的 ORDER BY 会覆盖 OVER() 中的 ORDER BY，FROM 子句中 ORDER BY 后值相同的才会按照 OVER() 子句中的 ORDER BY 排序；

mysql> select id,name,salary,SUM(salary) over(ORDER BY salary) AS already_paid_salary FROM employee e ORDER BY name;

id|name|salary |already_paid_salary|
--+----+-------+-------------------+
 4|小东  |40000.0|           291956.0|
10|小五  | 4500.0|             4500.0|
21|小张  |50000.0|           391956.0|
97|小林  |20000.0|            39500.0|
 8|小林  |24000.0|            86956.0|
19|小玉  |58000.0|           561956.0|
 3|小肖  |30000.0|           141956.0|
12|小肖  |35000.0|           211956.0|
96|小肖  |35000.0|           211956.0|
22|小胡  |25000.0|           111956.0|
 6|小非  |23456.0|            62956.0|
11|张山  |40000.0|           291956.0|
 7|晓飞  |15000.0|            19500.0|
13|李四  |50000.0|           391956.0|
18|猪小屁 |56000.0|           503956.0|
17|王武  |56000.0|           503956.0|

窗口中只有 PARTITION BY 时

此时的聚合函数会按照分组进行计算，分组内的所有行的数据都是这个分组中所有数据计算后的值；

mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id) AS already_paid_salary FROM employee;

id|name|salary |dept_id|already_paid_salary|
--+----+-------+-------+-------------------+
 8|小林  |24000.0|       |            28500.0|
10|小五  | 4500.0|       |            28500.0|
 3|小肖  |30000.0|      1|           263000.0|
11|张山  |40000.0|      1|           263000.0|
13|李四  |50000.0|      1|           263000.0|
19|小玉  |58000.0|      1|           263000.0|
21|小张  |50000.0|      1|           263000.0|
96|小肖  |35000.0|      1|           263000.0|
 4|小东  |40000.0|      2|           232000.0|
12|小肖  |35000.0|      2|           232000.0|
17|王武  |56000.0|      2|           232000.0|
18|猪小屁 |56000.0|      2|           232000.0|
22|小胡  |25000.0|      2|           232000.0|
97|小林  |20000.0|      2|           232000.0|
 6|小非  |23456.0|      3|            23456.0|
 7|晓飞  |15000.0|      4|            15000.0|

同时有 PARTITION BY 与 ORDER BY

ORDER BY 对 PARTITION BY 窗口中的数据进行排序，当 PARTITION BY 与 ORDER BY 列名不同时，聚合函数是根据排序进行逐个聚合计算的，当碰到 ORDER BY 相同的两个值时，同时计算两个值，并两行数据一致；当 PARTITION BY 与 ORDER BY 的列一致时，相当于只有 PARTITION BY；FROM 后面的 ORDER BY 是对整个表的数据进行排序，与 OVER 子句中的不同；当两者的字段不同时，先按照 OVER() 子句进行聚合计算，然后按照 FROM 子句的进行排序输出；

mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY name) AS already_paid_salary FROM employee e ;

id|name|salary |dept_id|already_paid_salary|
--+----+-------+-------+-------------------+
10|小五  | 4500.0|       |             4500.0|
 8|小林  |24000.0|       |            28500.0|
21|小张  |50000.0|      1|            50000.0|
19|小玉  |58000.0|      1|           108000.0|
 3|小肖  |30000.0|      1|           173000.0|
96|小肖  |35000.0|      1|           173000.0|
11|张山  |40000.0|      1|           213000.0|
13|李四  |50000.0|      1|           263000.0|
 4|小东  |40000.0|      2|            40000.0|
97|小林  |20000.0|      2|            60000.0|
12|小肖  |35000.0|      2|            95000.0|
22|小胡  |25000.0|      2|           120000.0|
18|猪小屁 |56000.0|      2|           176000.0
17|王武  |56000.0|      2|           232000.0|
 6|小非  |23456.0|      3|            23456.0|
 7|晓飞  |15000.0|      4|            15000.0|

添加 FROM 子句的 ORDER BY

mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY name) AS already_paid_salary FROM employee e ORDER BY name;

id|name|salary |dept_id|already_paid_salary|
--+----+-------+-------+-------------------+
 4|小东  |40000.0|      2|            40000.0|
10|小五  | 4500.0|       |             4500.0|
21|小张  |50000.0|      1|            50000.0|
 8|小林  |24000.0|       |            28500.0|
97|小林  |20000.0|      2|            60000.0|
19|小玉  |58000.0|      1|           108000.0|
 3|小肖  |30000.0|      1|           173000.0|
96|小肖  |35000.0|      1|           173000.0|
12|小肖  |35000.0|      2|            95000.0|
22|小胡  |25000.0|      2|           120000.0|
 6|小非  |23456.0|      3|            23456.0|
11|张山  |40000.0|      1|           213000.0|
 7|晓飞  |15000.0|      4|            15000.0|
13|李四  |50000.0|      1|           263000.0|
18|猪小屁 |56000.0|      2|           176000.0|
17|王武  |56000.0|      2|           232000.0|

PARTITION BY 与 ORDER BY 字段一致时，相当于只有 PARTITION BY：

mysql> select id,name,salary,dept_id,SUM(salary) over(PARTITION BY dept_id ORDER BY dept_id) AS already_paid_salary FROM employee;

id|name|salary |dept_id|already_paid_salary|
--+----+-------+-------+-------------------+
 8|小林  |24000.0|       |            28500.0|
10|小五  | 4500.0|       |            28500.0|
 3|小肖  |30000.0|      1|           263000.0|
11|张山  |40000.0|      1|           263000.0|
13|李四  |50000.0|      1|           263000.0|
19|小玉  |58000.0|      1|           263000.0|
21|小张  |50000.0|      1|           263000.0|
96|小肖  |35000.0|      1|           263000.0|
 4|小东  |40000.0|      2|           232000.0|
12|小肖  |35000.0|      2|           232000.0|
17|王武  |56000.0|      2|           232000.0|
18|猪小屁 |56000.0|      2|           232000.0
22|小胡  |25000.0|      2|           232000.0|
97|小林  |20000.0|      2|           232000.0|
 6|小非  |23456.0|      3|            23456.0|
 7|晓飞  |15000.0|      4|            15000.0|

到此这篇关于MySQL窗口函数OVER使用示例详细讲解的文章就介绍到这了,更多相关MySQL窗口函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL窗口函数OVER使用示例详细讲解

本文链接: https://www.lsjlt.com/news/176788.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

MySQL窗口函数OVER使用示例详细讲解

目录

窗口函数

测试数据表及数据

窗口函数

空窗口

窗口中只有 ORDER BY

窗口中只有 PARTITION BY 时

同时有 PARTITION BY 与 ORDER BY

本篇文章演示代码以及资料文档资料下载

MySQL窗口函数OVER使用示例详细讲解

Sql Server 开窗函数Over()的使用实例详解

窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE

Vuerender函数使用详细讲解

使用OpenGL创建窗口的示例详解

C++ 超详细示例讲解list的使用

GoLang函数栈的使用详细讲解

C语言函数声明以及函数原型超详细讲解示例

MySQL数据库基础篇SQL窗口函数示例解析教程

C语言详细讲解qsort函数的使用

Python中 join() 函数的使用示例讲解

MySQL中find_in_set()函数用法示例详解

C语言超详细讲解getchar函数的使用

Kotlin作用域函数使用示例详细介绍

Python的函数使用示例详解

使用Pandas实现MySQL窗口函数的解决方法

C语言基础函数用法示例详细解析

零基础学MySQL（五）-- 详细讲解数据库中的常用函数

【从删库到跑路】详细讲解MySQL的函数和约束作用

SpringMVC拦截器和异常处理器使用示例超详细讲解

oracle中从右往左截取的函数是什么

oracle中count函数与什么函数一样

oracle中date和timestamp区别

oracle中Regr_SLOPE的用法

oracle中on怎么用

oracle中case的用法

oracle中call调用过程输出参数怎么写

oracle中sum和count的区别

oracle中replace函数的使用方法

oracle中date类型可以为空吗