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
0
投稿猜你喜欢
- 如果我们的语句为：IF NOT EXISTS(SELECT 1 FROM sys.views WHERE name='Report_
- 无论是公司的同事还是外界的程序员朋友们，大部分人对JavaScript的高级应用不甚了解，已有的知识架构里会认为JavaScript仅仅是一
- 众所周知，python文件读取文件的时候所支持的newlines（即换行符），是指定的。这一点不管是从python的doucuments上还
- python爬虫模块selenium简介selenium主要是用来做自动化测试，支持多种浏览器，爬虫中主要用来解决JavaScript渲染问
时间库—arrow使用背景日期时间处理在实际应用场景中无处不在，所以这也成了编程语言中必不可少的模块，Python 也不例外。但是，你知道在- arguments.callee关于callee的解释：callee 是 arguments 对象的一个属性。可以用于引用该函数的函数体内当
- 一、创建测试项目1、新建GitHub仓库在GitHub上面新创建一个仓库，用来演示分支管理，如下图所示：点击“Creat
- 将el-switch值true、false改为number类型的1和0需求描述后端传回的status值为1（number类型）对应el-sw
- 最近看到市场上各种的文档格式转换软件，要么是收费、要么是有大量的广告。于是学习了一下 PyQt5 的页面操作，再加上了解 pandas 的使
- django可以在运行服务器时指定端口号python manage.py runserver 8080我们启动django开发服务器，输入命
- 一、前言B站上的漂亮的小姐姐真的好多好多，利用 you-get * 下载了一个 B 站上跳舞的小姐姐视频，利用视频中的弹幕来制作一个漂亮小姐
- 安装通过 pip 安装 Beautiful Soup 模块：pip install beautifulsoup4 。 还可以使用 PyCha
- 一般我们是利用Session对象来防止通过“刷新”增加计数器的访问量的，看看下面的做法；<％If IsEmpty(Sessi
- 在MySQL中可以使用IF()、IFNULL()、NULLIF()、ISNULL()函数进行流程的控制。1、IF()函数的使用IF(expr
- 现在正在搞三层开发，用ASP和VB6.0，但是现在苦于没有找到合适的方法来调试自己写的DLL文件，效率相当低。 &n
- 本文实例讲述了Python爬取国外天气预报网站的方法。分享给大家供大家参考。具体如下：crawl_weather.py如下：#encodin
- 一、加载库首先加载torch库，进入python后加载库使用import导入【import 库名】二、sin值计算方法pytorch中的si
- 1.API接口:hello world 案例from flask import Flaskfrom flask_restful import
- 对于Linux用户而言，命令行操作我们已经非常熟悉了。与其他流行的操作系统不同，在Linux社区中，使用命令行与使用图形用户界面执行类似任务
- 一、配置webdriver下载谷歌浏览器驱动，并配置好import timeimport randomfrom PIL import Ima
