您现在的位置是：网站首页> 编程资料编程资料

MySQL窗口函数OVER()用法及说明_Mysql_

2023-05-26 410人已围观

简介 MySQL窗口函数OVER()用法及说明_Mysql_

MySQL窗口函数OVER()

下面的讲解将基于这个employee2表

mysql> SELECT * FROM employee2; +----+-----------+------+---------+---------+ | id | name | age | salary | dept_id | +----+-----------+------+---------+---------+ | 3 | 小肖 | 29 | 30000.0 | 1 | | 4 | 小东 | 30 | 40000.0 | 2 | | 6 | 小非 | 24 | 23456.0 | 3 | | 7 | 晓飞 | 30 | 15000.0 | 4 | | 8 | 小林 | 23 | 24000.0 | NULL | | 10 | 小五 | 20 | 4500.0 | NULL | | 11 | 张山 | 24 | 40000.0 | 1 | | 12 | 小肖 | 28 | 35000.0 | 2 | | 13 | 李四 | 23 | 50000.0 | 1 | | 17 | 王武 | 24 | 56000.0 | 2 | | 18 | 猪小屁 | 2 | 56000.0 | 2 | | 19 | 小玉 | 25 | 58000.0 | 1 | | 21 | 小张 | 23 | 50000.0 | 1 | | 22 | 小胡 | 25 | 25000.0 | 2 | | 96 | 小肖 | 19 | 35000.0 | 1 | | 97 | 小林 | 20 | 20000.0 | 2 | +----+-----------+------+---------+---------+ 16 rows in set (0.00 sec)

窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:

  • OVER()

这时候，是一个空子句，此时的效果和没有使用OVER()函数是一样的，作用的是这个表所有数据构成的窗口。

 mysql> SELECT -> name, -> salary, -> MAX(salary) OVER() AS max_salary -- 作用于一整个窗口，此时返回的是所有数据中的MAX(salary),表示所有员工的最大工资 -> FROM employee2; +-----------+---------+------------+ | name | salary | max_salary | +-----------+---------+------------+ | 小肖 | 30000.0 | 58000.0 | | 小东 | 40000.0 | 58000.0 | | 小非 | 23456.0 | 58000.0 | | 晓飞 | 15000.0 | 58000.0 | | 小林 | 24000.0 | 58000.0 | | 小五 | 4500.0 | 58000.0 | | 张山 | 40000.0 | 58000.0 | | 小肖 | 35000.0 | 58000.0 | | 李四 | 50000.0 | 58000.0 | | 王武 | 56000.0 | 58000.0 | | 猪小屁 | 56000.0 | 58000.0 | | 小玉 | 58000.0 | 58000.0 | | 小张 | 50000.0 | 58000.0 | | 小胡 | 25000.0 | 58000.0 | | 小肖 | 35000.0 | 58000.0 | | 小林 | 20000.0 | 58000.0 | +-----------+---------+------------+ 16 rows in set (0.00 sec) mysql> SELECT -> name, -> salary, -> MAX(salary) OVER() -- 获取部门为1的所有员工的name,salary以及这个部门的最大工资 -> FROM employee2 -> WHERE dept_id = 1; +--------+---------+--------------------+ | name | salary | MAX(salary) OVER() | +--------+---------+--------------------+ | 小肖 | 30000.0 | 58000.0 | | 张山 | 40000.0 | 58000.0 | | 李四 | 50000.0 | 58000.0 | | 小玉 | 58000.0 | 58000.0 | | 小张 | 50000.0 | 58000.0 | | 小肖 | 35000.0 | 58000.0 | +--------+---------+--------------------+ 6 rows in set (0.00 sec)
  • OVER(PARTITION BY yyy1,yyy2,yyy3)

含有了PARTITION BY 子句，此时就会根据yyy1,yyy2,yyy3这些列构成的整体进行划分窗口，只有这些列构成的整体相同，才会处在同一个窗口中。

 mysql> SELECT -> name, -> salary, -> MAX(salary) OVER(PARTITION BY dept_id) AS dept_max_salary -- 利用了PARTITION BY ，从而根据dept_id进行分组，然后获取每个分组的最大值 -> FROM employee2; +-----------+---------+-----------------+ | name | salary | dept_max_salary | +-----------+---------+-----------------+ | 小林 | 24000.0 | 24000.0 | --| 分组为NULL的 | 小五 | 4500.0 | 24000.0 | --| | 小肖 | 30000.0 | 58000.0 | -----| | 张山 | 40000.0 | 58000.0 | | 李四 | 50000.0 | 58000.0 | -- 分组为dept_id = 1的 | 小玉 | 58000.0 | 58000.0 | | 小张 | 50000.0 | 58000.0 | | 小肖 | 35000.0 | 58000.0 | -----| | 小东 | 40000.0 | 56000.0 | ---------| | 小肖 | 35000.0 | 56000.0 | | 王武 | 56000.0 | 56000.0 | | 猪小屁 | 56000.0 | 56000.0 | -- 分组为dept_id = 2的 | 小胡 | 25000.0 | 56000.0 | | 小林 | 20000.0 | 56000.0 | ---------| | 小非 | 23456.0 | 23456.0 | -- ------------| 分组为dept_id = 3的 | 晓飞 | 15000.0 | 15000.0 | -- --------------| 分组为dept_id = 4的 +-----------+---------+-----------------+ 16 rows in set (0.00 sec)
  • OVER(ORDER BY yyy1,yyy2,yyy3 ASC\DESC)

每个窗口中利用ORDER BY子句，这时候将按照yyy1进行对应的升序\降序的顺序进行排序,如果yyy1相同，将根据yyy2排序（和ORDER BY 的用法一样)，这时候不仅会进行排序操作，如果是SUM与其连用的话，同时进行了累加的操作，即值是当前行加上前一行对应的值。但是下面的例子中却发现ORDER BY 后面对应的值相同的时候，并不是当前这一行加上以前行的值，例如ORDER BY salary\ORDER BY name的时候。

 mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(ORDER BY salary) AS already_paid_salary -- 利用ORDER BY ，窗口中对应的行将按照salary进行升序排序，然后调用SUM聚集 函数，不同的窗口进行累计 -> FROM employee2; +-----------+---------+---------------------+ | 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 | -- -----| 这两行同处相同，此时这个窗口的already_paid_salary | 小肖 | 35000.0 | 211956.0 | -- -----| = (35000 * 2) (当前两行) + 141956(前面的行) | 小东 | 40000.0 | 291956.0 | -- ---| 这两行同处相同，此时这个窗口的already_paid_salary | 张山 | 40000.0 | 291956.0 | -- ---| = （40000 * 2）（当前两行） + 211956（之前行的） | 李四 | 50000.0 | 391956.0 | -- | 道理同上 | 小张 | 50000.0 | 391956.0 | -- | | 王武 | 56000.0 | 503956.0 | -- ------|道理同上 | 猪小屁 | 56000.0 | 503956.0 | -- ------| | 小玉 | 58000.0 | 561956.0 | +-----------+---------+---------------------+ 16 rows in set (0.00 sec) mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(ORDER BY name) -- 每个窗口的所有行将根据name进行升序排序这时候，然后不同name的行将会进行累计操作,直接是当前行+以嵌行的，相同的时候，是相同行的和加上之前行的值 -> FROM employee2; +-----------+---------+---------------------------------+ | name | salary | SUM(salary) OVER(ORDER BY name) | +-----------+---------+---------------------------------+ | 小东 | 40000.0 | 40000.0 | | 小五 | 4500.0 | 44500.0 | | 小张 | 50000.0 | 94500.0 | | 小林 | 24000.0 | 138500.0 | -- |这两组同处相同，所以对应的值为(24000 + 20000)（相同的两行） + 94500（之前的行） | 小林 | 20000.0 | 138500.0 | -- | | 小玉 | 58000.0 | 196500.0 | | 小肖 | 30000.0 | 296500.0 | -- ---|这两组同处相同，所以对应的值为(30000 + 35000 + 35000)（相同的三行） + 196500（之前的行） | 小肖 | 35000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | -- ---| | 小胡 | 25000.0 | 321500.0 | | 小非 | 23456.0 | 344956.0 | | 张山 | 40000.0 | 384956.0 | | 晓飞 | 15000.0 | 399956.0 | | 李四 | 50000.0 | 449956.0 | | 猪小屁 | 56000.0 | 505956.0 | | 王武 | 56000.0 | 561956.0 | +-----------+---------+---------------------------------+ 16 rows in set (0.00 sec)

同时值得注意的是，OVER()是一个全局函数，所以在使用ORDER BY 的时候，那么最后输出的时候也将是按照这个有序输出，但是仅仅在没有使用PARTITION BY的情况才是这样的.这个可以从PARTITION BY进行说明，没有使用PARTITION BY的时候，ORVER()中的ORDER BY将是针对整张表进行排序的，所以这时候如果FROM子句后面的ORDER BY后的字段和OVER()中ORDER BY后的字段相同的时候，就会显得多此一举了。

即

# 下面两个代码是一样的，但是仅仅OVER()只使用ORDER BY子句的时候，并且才这样 # 两个ORDER BY后面的字段是相同才可以保证效果一样 # 如果使用了PARTITION BY子句，那么OVER()中的ORDER BY将是针对每一个窗口 # 中的所有行进行排序的，而在FROM子句后面的ORDER BY将是针对整张表，所以 # 导致结果不同 SELECT name, SUM(salary) OVER(ORDER BY NAME) FROM employee2; SELECT name, SUM(salary) OVER(ORDER BY NAME) FROM employee2 ORDER BY name; mysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2; +-----------+---------+---------------------------------+ | name | salary | SUM(salary) OVER(ORDER BY name) | +-----------+---------+---------------------------------+ | 小东 | 40000.0 | 40000.0 | | 小五 | 4500.0 | 44500.0 | | 小张 | 50000.0 | 94500.0 | | 小林 | 24000.0 | 138500.0 | | 小林 | 20000.0 | 138500.0 | | 小玉 | 58000.0 | 196500.0 | | 小肖 | 30000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小胡 | 25000.0 | 321500.0 | | 小非 | 23456.0 | 344956.0 | | 张山 | 40000.0 | 384956.0 | | 晓飞 | 15000.0 | 399956.0 | | 李四 | 50000.0 | 449956.0 | | 猪小屁 | 56000.0 | 505956.0 | | 王武 | 56000.0 | 561956.0 | +-----------+---------+---------------------------------+ 16 rows in set (0.00 sec) # 两个ORDER BY后面的字段相同时，作用就会相当只使用SUM(salary) OVER(ORDER BY name) mysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2 -> ORDER BY name; +-----------+---------+---------------------------------+ | name | salary | SUM(salary) OVER(ORDER BY name) | +-----------+---------+---------------------------------+ | 小东 | 40000.0 | 40000.0 | | 小五 | 4500.0 | 44500.0 | | 小张 | 50000.0 | 94500.0 | | 小林 | 24000.0 | 138500.0 | | 小林 | 20000.0 | 138500.0 | | 小玉 | 58000.0 | 196500.0 | | 小肖 | 30000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小胡 | 25000.0 | 321500.0 | | 小非 | 23456.0 | 344956.0 | | 张山 | 40000.0 | 384956.0 | | 晓飞 | 15000.0 | 399956.0 | | 李四 | 50000.0 | 449956.0 | | 猪小屁 | 56000.0 | 505956.0 | | 王武 | 56000.0 | 561956.0 | +-----------+---------+---------------------------------+ 16 rows in set (0.00 sec) # 两个ORDER BY后的字段不同，那么FROM 子句后的ORDER BY将会覆盖OVER()中的ORDER BY mysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2 -> ORDER BY salary; +-----------+---------+---------------------------------+ | name | salary | SUM(salary) OVER(ORDER BY name) | +-----------+---------+---------------------------------+ | 小五 | 4500.0 | 44500.0 | | 晓飞 | 15000.0 | 399956.0 | | 小林 | 20000.0 | 138500.0 | | 小非 | 23456.0 | 344956.0 | | 小林 | 24000.0 | 138500.0 | | 小胡 | 25000.0 | 321500.0 | | 小肖 | 30000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | | 小东 | 40000.0 | 40000.0 | | 张山 | 40000.0 | 384956.0 | | 小张 | 50000.0 | 94500.0 | | 李四 | 50000.0 | 449956.0 | | 猪小屁 | 56000.0 | 505956.0 | | 王武 | 56000.0 | 561956.0 | | 小玉 | 58000.0 | 196500.0 | +-----------+---------+---------------------------------+ 16 rows in set (0.00 sec) # OVER()中的ORDER BY针对的窗口中的所有行进行排序的，而下面的FROM子句中的 # ORDER BY是针对整个表的，所以此时两者的作用并不相同 mysql> SELECT -> name,salary, -> SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) -> FROM employee2; +-----------+---------+------------------------------------------------------+ | name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) | +-----------+---------+------------------------------------------------------+ | 小五 | 4500.0 | 4500.0 | | 小林 | 24000.0 | 28500.0 | | 小张 | 50000.0 | 50000.0 | | 小玉 | 58000.0 | 108000.0 | | 小肖 | 30000.0 | 173000.0 | | 小肖 | 35000.0 | 173000.0 | | 张山 | 40000.0 | 213000.0 | | 李四 | 50000.0 | 263000.0 | | 小东 | 40000.0 | 40000.0 | | 小林 | 20000.0 | 60000.0 | | 小肖 | 35000.0 | 95000.0 | | 小胡 | 25000.0 |
                
                

                

                                            
上一篇：适合新手的mysql日期类型转换实例教程_Mysql_

                                                                
下一篇：MySQL视图的概念、创建、查看、删除和修改详解_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网