MySQL窗口函数OVER()

最新推荐文章于 2024-06-28 03:39:01 发布
最新推荐文章于 2024-06-28 03:39:01 发布
阅读量834 收藏 2
点赞数
分类专栏： 面试 学习路线 阿里巴巴 文章标签： mysql 数据库 java tomcat mapreduce
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_67402235/article/details/126803622
版权
本文详细介绍了MySQL的窗口函数OVER()，包括其与PARTITION BY和ORDER BY子句的结合使用，以及如何与SUM()等聚合函数配合，提供实例展示了在数据库查询中的应用。同时还提到了ORDER BY在窗口函数中的作用，以及与FORMAT、CAST和CONVERT函数在格式化输出和排序上的差异。文章末尾还提及了窗口函数与其他排序函数如ROW_NUMBER()、DENSE_RANK()和RANK()的结合使用。
摘要由CSDN通过智能技术生成

下面的讲解将基于这个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 ASCDESC):每个窗口中利用ORDER BY子句，这时候将按照yyy1进行对应的升序降序的顺序进行排序,如果yyy1相同，将根据yyy2排序（和ORDER BY 的用法一样)，这时候不仅会进行排序操作，如果是SUM与其连用的话，同时进行了累加的操作，即值是当前行加上前一行对应的值。但是下面的例子中却发现ORDER BY 后面对应的值相同的时候，并不是当前这一行加上以前行的值，例如ORDER BY salaryORDER 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 |
| 小肖
最低0.47元/天 解锁文章
m0_67402235
关注
专栏目录
Mysql_窗口函数_OVER()的简单使用
feizuiku0116的博客
08-21 3126
一.OVER() 描述 OVER()的意思就是所有的数据都在窗口中 实例 OVER() 意思是所有的数据都在窗口中 # 计算所有员工的平均工资 SELECT *, avg(salary) OVER () FROM employee; OVER()用于将当前行与一个聚合值进行比较 # 计算员工与平均薪水之差 SELECT *, salary - avg(salary) OVER () as diffent FROM employee; OVER()和CO
mysql中的lead函数和over函数
xzy的博客
06-21 1062
over函数是用来分组加排序的，然后等over函数分好组排好序之后，再使用lead函数去找当前分组内的下一行数据的某个字段值，具体是哪个字段由over函数指定，如果当前分组内没有下一条数据的话，那么返回null空值。我们有一个销售表，有很多类型的产品，我们想要根据产品id分组，然后再根据产品销售时间排序，我们想要显示下个月的销售额，以及下个月增加的销售额。lead函数的功能：取出当前分组的下一条数据的某个字段的值，如果当前分组内已经是最后一条数据了，那么会返回null。
【MySQL】深度解析：双字段分区（OVER(PARTITION BY A,B)）的艺术
最新发布
master_chenchen的博客
06-28 1162
窗口函数允许在一组相关的行（窗口）上执行计算，而子句则定义了窗口的范围。当涉及到时，意味着数据将根据字段A和B的组合值被划分为多个独立的分区，在每个分区内进行计算。核心特性和优势：数据分组：灵活地对数据进行细分，实现复杂统计分析。性能优化：减少全表扫描，提高查询效率。代码简洁：在单个查询中完成复杂的数据处理，减少子查询和临时表的使用。代码示例SELECTFROMyour_table;这段代码计算了每一对(A, B)值的value总和。双字段分区（
SQL语句中OVER函数的用法
weixin_30512043的博客
03-11 1002
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。其参数：over（partition by columnname1 order by columnname2）含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。例如：employees表中，有两个部门的记录：department_id...
MySQL学习-窗口函数over（）
认真学编程的原始人的博客
10-20 744
窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
MySQL 的窗口函数 OVER
01-04 1613
ORDER BY 对 PARTITION BY 窗口中的数据进行排序，当 PARTITION BY 与 ORDER BY 列名不同时，聚合函数是根据排序进行逐个聚合计算的，当碰到 ORDER BY 相同的两个值时，同时计算两个值，并两行数据一致；2）当与 FROM 后面的 ORDER BY 字段不同时，FROM 子句的 ORDER BY 会覆盖 OVER() 中的 ORDER BY，FROM 子句中 ORDER BY 后值相同的才会按照 OVER() 子句中的 ORDER BY 排序；
MySQL-窗口函数【重点】
独自优秀的数据分析师
04-21 1011
对一个成熟的数据分析师来说，窗口函数可以大幅提高查询效率，且SQL代码优雅。 窗口函数学起来，炫飞同行~ 本文内容导航： 一、定义 窗口可以理解为记录集合，窗口函数就是在满足某种条件的记录集合上执行的特殊函数。 即：应用在窗口内的函数。 静态窗口：每条记录都要在此窗口内执行函数，窗口大小都是固定的。 动态窗口：不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。 二、语法格式 函数名(字段名) over(子句) over()括号内若不写，则意味着窗口函数基于满足where条件的所有行进行计算。
MySQL 8.0之窗口函数
weixin_45773632的博客
05-25 1046
文章目录简介如何使用？1. rank() 专用窗口函数2. 其他专业窗口函数3. 聚合函数作为窗口函数 简介 原文链接：https://zhuanlan.zhihu.com/p/92654574 此部分官方文档：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html 注意：从版本8.0开始，MySQL支持窗口函数。窗口函数允许您以新的，更简单的方式解决查询问题，并具有更好的性能。 窗口函数的基本语法如下： <窗
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
mysql over函数_SQL进阶排序和窗口函数
weixin_33734005的博客
01-23 749
在使用数据库制作各种统计数据的时候，需要对数据进行排序，比如按照「分数、销量、人数」等数值进行排序，通常排序的方法有两种：跳过之后的位次排序不跳过之后的位次排序窗口函数窗口函数只在最新的MySQL版本中才支持的！窗口函数只在最新的MySQL版本中才支持的！窗口函数只在最新的MySQL版本中才支持的！参考资料：https://zhuanlan.zhihu.com/p/92654574什么是...
mysql 之分析函数 over参考 （partition by ...order by ...）
ahmcwt的专栏
12-01 9693
mysql 之分析函数 over 转自:https://www.cnblogs.com/moyijian/p/9905803.html 一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 1、分析函数和聚合函数的不同之处： 分析函数和聚合函数很多是同名的，意思也一样，只是聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每
mysqlover函数
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 185
各位大侠，请教一个问题，MySQL怎么实现row_number() over(partition by ) 对重复字段只取包括其中一个的记录的方法就这样，还可以通过唯一id(如果存在的话，没有的话，用row_number)比较如select*fromcb_rqrj_djclhsawhereIDin(selectmin(i...
MySQL窗口函数 OVER()函数介绍
feizuiku0116的博客
08-02 3776
OVER()的意思就是所有的数据都在窗口中。
MySQL 窗口函数
王林的博客
06-08 2420
MySQL8 新特性——窗口函数
mysql 窗口函数_窗口函数_解决SQL累加问题
weixin_39781209的博客
12-05 1420
业务场景1：求出每月数量（amount）的累计值这个需求在Excel里面是非常好实现的，一个求和公式直接搞定，但Excel处理的数据量毕竟有限，这个需求在SQL里，怎么来实现呢？——窗口函数。select *,sum(amount) over(order by month) from table group by month;窗口函数1、累计求和基础结构：sum(...) over(...)复杂结...
mysql 实现over函数_mysql实现over()开窗函数功能
weixin_32558527的博客
02-01 964
Mysql实现开窗函数功能：参考：http://stackoverflow.com/questions/3333665/rank-function-in-mysqlCREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));INSERT INTO person VALUES (1, 'Bob', 25,...
mysql窗口函数（over()、排序函数、with语句）
ljh18885466426的博客
08-21 1044
窗口函数： seleect 聚合函数 over(partition by_______ order by ________)from table; 基本语法: <window_function>over(…) 优点： 简单 窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。 快速 这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。 多功能性 最重要的是，窗口函数具
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值