MySQL 窗口函数详解:分析性查询的强大工具

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。

MySQL 窗口函数提供了一种灵活的方式来处理 SQL 查询中的数据,它们允许你在不需要对数据进行分组的情况下对行集进行分析。窗口函数最常用于分析性操作,比如计算排名、累计和、移动平均值等。MySQL 从版本 8.0 开始支持窗口函数。以下是窗口函数的几个关键概念和常见用法:

窗口函数的基本语法

窗口函数的基本语法结构如下:

sql

代码解读

复制代码

function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])
  • function_name: 窗口函数名,如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), 等。
  • PARTITION BY: 可选,用于定义窗口的分区,类似于 GROUP BY
  • ORDER BY: 用于定义窗口内的行顺序。
  • frame_clause: 可选,定义窗口框架,可以是 ROWSRANGE

常见窗口函数

  1. ROW_NUMBER()
  • 为每个分区的每一行分配唯一的行号。

sql

  1. 代码解读
  2. 复制代码
SELECT name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
  1. RANK()
  • 为每个分区的每一行分配排名,排名有重复,且后面排名会有跳跃。

sql

  1. 代码解读
  2. 复制代码
SELECT name, department,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
  1. DENSE_RANK()
  • 类似于 RANK(),但排名不跳跃。

sql

  1. 代码解读
  2. 复制代码
SELECT name, department,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
  1. NTILE(n)
  • 将分区中的行均匀分布到 n 个桶中,并为每一行分配桶号。

sql

  1. 代码解读
  2. 复制代码
SELECT name, department,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
  1. SUM(), AVG(), MIN(), MAX()
  • 在窗口内计算聚合值。

sql

  1. 代码解读
  2. 复制代码
SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

窗口框架

窗口框架用来定义在计算聚合值时哪些行应该被包含在窗口内。框架可以用 ROWSRANGE 来定义。

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:从分区开始到当前行。
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:从前一行到后一行。

示例:

sql

代码解读

复制代码

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM employees;

应用场景

窗口函数在以下场景中非常有用:

  • 计算每个部门内员工的工资排名。
  • 求某段时间内的累计销售额。
  • 计算移动平均值或累计和。
  • 分析和报告需要复杂数据操作的情况。

通过使用窗口函数,开发者可以在查询中轻松实现复杂的分析和报告功能,而不需要进行额外的数据处理。窗口函数提供了一种简洁、优雅的解决方案,可以提高 SQL 查询的表达能力和执行效率。


作者:stormsha

链接:https://juejin.cn/post/7438605834374823955

来源:稀土掘金

著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
11
11
0
171
分享
相关文章
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
join查询可以⽆限叠加吗?MySQL对join查询有什么限制吗?
大家好,我是 V 哥。本文详细探讨了 MySQL 中 `JOIN` 查询的限制及其优化方法。首先,`JOIN` 查询不能无限叠加,存在资源(CPU、内存、磁盘 I/O)、性能和语法等方面的限制。过多的 `JOIN` 操作会导致数据库性能急剧下降。其次,介绍了三种常见的 `JOIN` 查询算法:嵌套循环连接(NLJ)、索引嵌套连接(INL)和基于块的嵌套循环连接(BNL),并分析了它们的触发条件和性能特点。最后,分享了优化 `JOIN` 查询的方法,包括 SQL 语句优化、索引优化、数据库配置调整等。关注 V 哥,了解更多技术干货,点赞👍支持,一起进步!
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
131 7
MySQL事务日志-Undo Log工作原理分析
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志,可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施,可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考,帮助实现高效的数据库管理。
60 11
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
本文详细介绍了MySQL中的多表查询,包括多表关系、隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询及其实现方式,一文全面读懂多表联查!
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制,包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件,能显著提升数据库读写性能。默认大小为128MB,可根据服务器配置调整(如32GB内存可设为2GB)。它通过free链表管理空闲缓存页,flush链表记录脏页,并用LRU链表区分冷热数据以优化淘汰策略。此外,还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法,以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词:MySQL内存数据更新机制。
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
93 8
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
555 0
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
102 82
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等