MySQL窗口函数——分组排序函数:number_rank(),rank(),dense_rank()

简介: MySQL窗口函数——分组排序函数:number_rank(),rank(),dense_rank()

🎲概念

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数,与Oracle 窗口函数类似,属于MySQL的一大特点.

非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。

窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。

当你实际操作之后,你会发现窗口函数真的是非常nice!

image.png


那么窗口函数到底有哪些呢?这些窗口函数的功能到底有多么的强大呢?让我们带着这样的疑问去学习吧!

image.png


另外还有开窗聚合函数: SUM,AVG,MIN,MAX

🎯语法结构

1. window_function ( expr ) OVER ( 
2. PARTITION BY ... 
3. ORDER BY ... 
4.   frame_clause 
5. )

image.png


🎬序号函数

序号函数有三个:ROW_NUMBER()、RANK()、DENSE_RANK(),可以用来实现分组排序,并添加序号。

这三种排序方式,我们需要根据自己的业务需求进行选择

row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
) 

下面我们通过实际的案例操作来理解这三个方法,到底有什么异同


🎨准备数据

create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

🎸ROW_NUMBER() 直接排序

select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;


image.png

row_number() :将那些数值相同也按照顺序排序了,但是有时候我们需要将这些相同的放在一起排名,这个时候我们这个方法就显得不够好了。


over:里面第一个参数是,按照什么分组;第二个是按照什么排序,升序还是降序


💄rank()相同的并列排序

select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

image.png

只需要将上述的函数名,改成rank即可,在这里可以很好的避免之前的情况发生,将一样的数值放在了一起,也就是并列排名,在现实中具有很多的推广。


那么有时候,我们虽然想并列排名,但是又不想中间空排名次序,也就是连续的排名,那么此时我们又该如何去做呢?


🍤dense_rannk()连续并列的排序

-- 对每个部门的员工按照薪资排序,并给出排名 dense-rank
select 
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

image.png


在现实的场景当中,我们可以根据自己的场景去选择我们的排序函数,当然前提是你必须要基础它的语法:


number_rank()|rank()| dense_rank() over(partition by 按照什么字段分组 order by 按照什么字段排序 desc)as 重命名字段


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
SQL 关系型数据库 MySQL
MySQL 常用函数
我们这次全面梳理 MySQL 中的常用函数,涵盖 聚合函数、字符串函数、日期时间函数、数学函数 和 控制流函数 等五大类。每类函数均配有语法说明与实用示例,帮助读者提升数据处理能力,如统计分析、文本处理、日期计算、条件判断等。文章结尾提供了丰富的实战练习,帮助读者巩固和应用函数技巧,是进阶 SQL 编程与数据分析的实用工具手册。
1154 2
|
SQL 关系型数据库 MySQL
【YashanDB知识库】MySQL field 函数的改写方法
【YashanDB知识库】MySQL field 函数的改写方法
|
SQL 关系型数据库 MySQL
【MySQL基础篇】盘点MySQL常用四大类函数
本文介绍了MySQL中的四大类常用函数:字符串函数、数值函数、日期函数和流程函数。
【MySQL基础篇】盘点MySQL常用四大类函数
|
SQL 关系型数据库 MySQL
Mysql-常用函数及其用法总结
以上列举了MySQL中一些常用的函数及其用法。这些函数在日常的数据库操作中非常实用,能够简化数据查询和处理过程,提高开发效率。掌握这些函数的使用方法,可以更高效地处理和分析数据。
579 19
|
SQL 关系型数据库 MySQL
【YashanDB知识库】MySQL field 函数的改写方法
本文来自YashanDB官网,介绍将MySQL的FIELD函数改写到YashanDB的方法。MySQL中,FIELD函数用于自定义排序;而在YashanDB中,可使用DECODE或CASE语句实现类似功能。示例展示对表`t1`按指定顺序排序的过程,提供两种改写方式,结果均符合预期。
|
关系型数据库 MySQL
MySQL常见函数第一期,你都用过哪些呢 ?
本期介绍10个常用的MySQL函数:字符串连接(CONCAT)、提取子字符串(SUBSTRING)、获取字符串长度(LENGTH)、转换大小写(UPPER、LOWER)、去除空格(TRIM)、替换字符串(REPLACE)、查找子字符串位置(INSTR)、带分隔符的字符串连接(CONCAT_WS)以及获取当前日期时间(NOW)。
524 8
|
SQL 关系型数据库 MySQL
MySQL常见函数第二期,你都用过哪些呢 ?
本期介绍了20个常用的MySQL函数,涵盖日期处理(如CURDATE()、DATE_FORMAT())、数学运算(如ABS()、ROUND())、统计分析(如COUNT()、SUM())等,帮助提高SQL查询效率和数据处理能力。希望对大家的学习有所帮助。
552 7
|
算法
Leetcode 313. Super Ugly Number
题目翻译成中文是『超级丑数』,啥叫丑数?丑数就是素因子只有2,3,5的数,7 14 21不是丑数,因为他们都有7这个素数。 这里的超级丑数只是对丑数的一个扩展,超级丑数的素因子不再仅限于2 3 5,而是由题目给定一个素数数组。与朴素丑数算法相比,只是将素因子变了而已,解法还是和朴素丑数一致的。
412 1
|
存储 SQL 算法
LeetCode 题目 65:有效数字(Valid Number)【python】
LeetCode 题目 65:有效数字(Valid Number)【python】
|
存储 算法
【LeetCode力扣】单调栈解决Next Greater Number(下一个更大值)问题
【LeetCode力扣】单调栈解决Next Greater Number(下一个更大值)问题
353 0

推荐镜像

更多