MySQL窗口函数OVER使用示例详细讲解-编程学习网

窗口函数

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窗口函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

文章详情

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

目录

窗口函数

测试数据表及数据

窗口函数

空窗口

窗口中只有 ORDER BY

窗口中只有 PARTITION BY 时

同时有 PARTITION BY 与 ORDER BY

软考中级精品资料免费领

相关文章

猜你喜欢

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

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

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

Vuerender函数使用详细讲解

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

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

GoLang函数栈的使用详细讲解

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

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

javascript浏览器的窗口被移动时触发此事件使用什么函数，详细讲解

javascript浏览器的窗口被移动时触发此事件使用什么函数，详细讲解

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

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

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

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

Python的函数使用示例详解

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

javascript当浏览器的窗口大小被改变时触发此事件使用什么函数，详细讲解

javascript一个外部对象被鼠标拖进当前窗口或者帧使用什么函数，详细讲解

javascript当浏览器的窗口大小被改变时触发此事件使用什么函数，详细讲解