数据库
搜索
分类

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

作者：开发老张 时间：2024-01-16 15:56:56　

窗口函数

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|

来源：https://blog.csdn.net/zhy0414/article/details/128545846

标签：MySQL,窗口函数,OVER
0
投稿

猜你喜欢

  • mysql数据库优化总结(心得)

    2024-01-17 17:50:37

  • python中实现词云图的示例

    2021-08-04 22:11:33

  • python通过Seq2Seq实现闲聊机器人

    2021-09-02 13:39:15

  • 可以拖动的div 实现代码第1/2页

    2024-03-19 17:46:20

  • GoFrame框架gcache的缓存控制淘汰策略实践示例

    2023-07-22 06:41:19

  • SQLServer2008新实例远程数据库链接问题(sp_addlinkedserver)

    2024-01-19 23:44:22

  • ASP中RegExp对象正则表达式语法及相关例子

    2007-08-12 17:46:00

  • Python 如何利用ffmpeg 处理视频素材

    2022-05-31 19:25:17

  • Python中__init__和__new__的区别详解

    2023-09-24 13:14:17

  • PyTorch如何创建自己的数据集

    2022-10-17 05:22:17

  • 微信小程序转化为uni-app项目的方法示例

    2024-03-23 19:34:39

  • Python实现模拟时钟代码推荐

    2023-08-03 05:26:09

  • php完全过滤HTML,JS,CSS等标签

    2023-10-09 08:07:34

  • 如何基于Python + requests实现发送HTTP请求

    2022-04-17 09:27:09

  • python重要函数eval多种用法解析

    2023-02-08 20:16:46

  • Python数学建模PuLP库线性规划实际案例编程详解

    2021-04-29 19:12:56

  • CSS绝对定位在宽屏分辨率下错位

    2009-07-28 12:24:00

  • Python编程实现正则删除命令功能

    2022-10-19 16:45:08

  • Vue3+Vite实现动态路由的详细实例代码

    2023-07-02 16:58:37

  • 经验几则 推荐

    2024-04-22 12:46:14
    • asp之家 网络编程 m.aspxhome.com