首页 > 问答 > 关键词 > mysql最新资讯 > 正文

MySQL8.0窗口函数做数据排名统计详细教程

2020-06-29 09:06 · 稿源:数据库干货铺

MySQL8.0新增了窗口函数,大大的方便了做数据排名统计的人,很多朋友还不清楚怎么用MySQL8.0做数据统计排名,下面就来为大家分享一篇心得文章。

程序员 代码 黑客

MySQL8.0之前,做数据排名统计等相当痛苦,因为没有像Oracle、SQL SERVER 、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后,针对这类统计就再也不是事了,本文就以常用的排序实例介绍MySQL的窗口函数。

1、准备工作

创建表及测试数据

mysql> use testdb;Database changed/*创建表 */mysql> create  table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1),key idx_stuNo_course(stu_no,course));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| tb_score         |+------------------+/* 新增一批测试数据 */mysql> insert into tb_score(stu_no,course,score)values('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> insert into tb_score(stu_no,course,score)values('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

2、统计每门课程分数的排名

根据每门课程的分数从高到低进行排名,此时,会出现分数相同时怎么处理的问题,下面就根据不同的窗口函数来处理不同场景的需求

ROW_NUMBER

由结果可以看出,分数相同时按照学号顺序进行排名

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn-> from tb_score;+---------+---------+-------+----+| stu_no  | course  | score | rn |+---------+---------+-------+----+| 2020005 | C++     |  96.0 |  1 || 2020013 | C++     |  96.0 |  2 || 2020006 | C++     |  90.0 |  3 || 2020001 | C++     |  85.0 |  4 || 2020012 | C++     |  85.0 |  5 || 2020003 | C++     |  81.0 |  6 || 2020010 | C++     |  76.0 |  7 || 2020002 | C++     |  70.0 |  8 || 2020008 | C++     |  69.0 |  9 || 2020007 | C++     |  66.0 | 10 || 2020009 | C++     |  66.0 | 11 || 2020004 | C++     |  60.0 | 12 || 2020003 | English | 100.0 |  1 || 2020004 | English | 100.0 |  2 || 2020002 | English |  99.0 |  3 || 2020013 | English |  88.0 |  4 || 2020008 | English |  86.0 |  5 || 2020009 | English |  86.0 |  6 || 2020011 | English |  84.0 |  7 || 2020010 | English |  81.0 |  8 || 2020003 | English |  80.0 |  9 || 2020007 | English |  76.0 | 10 || 2020012 | English |  75.0 | 11 || 2020005 | English |  70.0 | 12 || 2020006 | English |  70.0 | 13 || 2020005 | mysql   |  98.0 |  1 || 2020001 | mysql   |  90.0 |  2 || 2020008 | mysql   |  90.0 |  3 || 2020011 | mysql   |  90.0 |  4 || 2020004 | mysql   |  80.0 |  5 || 2020003 | mysql   |  78.0 |  6 || 2020010 | mysql   |  75.0 |  7 || 2020009 | mysql   |  70.0 |  8 || 2020006 | mysql   |  60.0 |  9 || 2020002 | mysql   |  50.0 | 10 || 2020007 | mysql   |  50.0 | 11 |+---------+---------+-------+----+36 rows in set (0.00 sec)

DENSE_RANK

为了让分数相同时排名也相同,则可以使用DENSE_RANK函数,结果如下:

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn 
-> fromtb_score;
+---------+---------+-------+----+| stu_no  | course  | score | rn |+---------+---------+-------+----+| 2020005 | C++     |  96.0 |  1 || 2020013 | C++     |  96.0 |  1 || 2020006 | C++     |  90.0 |  2 || 2020001 | C++     |  85.0 |  3 || 2020012 | C++     |  85.0 |  3 || 2020003 | C++     |  81.0 |  4 || 2020010 | C++     |  76.0 |  5 || 2020002 | C++     |  70.0 |  6 || 2020008 | C++     |  69.0 |  7 || 2020007 | C++     |  66.0 |  8 || 2020009 | C++     |  66.0 |  8 || 2020004 | C++     |  60.0 |  9 || 2020003 | English | 100.0 |  1 || 2020004 | English | 100.0 |  1 || 2020002 | English |  99.0 |  2 || 2020013 | English |  88.0 |  3 || 2020008 | English |  86.0 |  4 || 2020009 | English |  86.0 |  4 || 2020011 | English |  84.0 |  5 || 2020010 | English |  81.0 |  6 || 2020003 | English |  80.0 |  7 || 2020007 | English |  76.0 |  8 || 2020012 | English |  75.0 |  9 || 2020005 | English |  70.0 | 10 || 2020006 | English |  70.0 | 10 || 2020005 | mysql   |  98.0 |  1 || 2020001 | mysql   |  90.0 |  2 || 2020008 | mysql   |  90.0 |  2 || 2020011 | mysql   |  90.0 |  2 || 2020004 | mysql   |  80.0 |  3 || 2020003 | mysql   |  78.0 |  4 || 2020010 | mysql   |  75.0 |  5 || 2020009 | mysql   |  70.0 |  6 || 2020006 | mysql   |  60.0 |  7 || 2020002 | mysql   |  50.0 |  8 || 2020007 | mysql   |  50.0 |  8 |+---------+---------+-------+----+36 rows in set (0.00 sec)

RANK

DENSE_RANK的结果是分数相同时排名相同了,但是下一个名次是紧接着上一个名次的,如果 2 个并列的第 1 之后,下一个我想是第 3 名,则可以使用RANK函数实现

mysql> select stu_no,course,score, rank()over(partition by course order by score desc ) rn 
-> fromtb_score;+---------+---------+-------+----+| stu_no  | course  | score | rn |+---------+---------+-------+----+| 2020005 | C++     |  96.0 |  1 || 2020013 | C++     |  96.0 |  1 || 2020006 | C++     |  90.0 |  3 || 2020001 | C++     |  85.0 |  4 || 2020012 | C++     |  85.0 |  4 || 2020003 | C++     |  81.0 |  6 || 2020010 | C++     |  76.0 |  7 || 2020002 | C++     |  70.0 |  8 || 2020008 | C++     |  69.0 |  9 || 2020007 | C++     |  66.0 | 10 || 2020009 | C++     |  66.0 | 10 || 2020004 | C++     |  60.0 | 12 || 2020003 | English | 100.0 |  1 || 2020004 | English | 100.0 |  1 || 2020002 | English |  99.0 |  3 || 2020013 | English |  88.0 |  4 || 2020008 | English |  86.0 |  5 || 2020009 | English |  86.0 |  5 || 2020011 | English |  84.0 |  7 || 2020010 | English |  81.0 |  8 || 2020003 | English |  80.0 |  9 || 2020007 | English |  76.0 | 10 || 2020012 | English |  75.0 | 11 || 2020005 | English |  70.0 | 12 || 2020006 | English |  70.0 | 12 || 2020005 | mysql   |  98.0 |  1 || 2020001 | mysql   |  90.0 |  2 || 2020008 | mysql   |  90.0 |  2 || 2020011 | mysql   |  90.0 |  2 || 2020004 | mysql   |  80.0 |  5 || 2020003 | mysql   |  78.0 |  6 || 2020010 | mysql   |  75.0 |  7 || 2020009 | mysql   |  70.0 |  8 || 2020006 | mysql   |  60.0 |  9 || 2020002 | mysql   |  50.0 | 10 || 2020007 | mysql   |  50.0 | 10 |+---------+---------+-------+----+36 rows in set (0.01 sec)

这样就实现了各种排序需求。

NTILE

NTILE函数的作用是对每个分组排名后,再将对应分组分成N个小组,例如

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group  fromtb_score;+---------+---------+-------+----+----------+| stu_no  | course  | score | rn | rn_group |+---------+---------+-------+----+----------+| 2020005 | C++     |  96.0 |  1 |        1 || 2020013 | C++     |  96.0 |  1 |        1 || 2020006 | C++     |  90.0 |  3 |        1 || 2020001 | C++     |  85.0 |  4 |        1 || 2020012 | C++     |  85.0 |  4 |        1 || 2020003 | C++     |  81.0 |  6 |        1 || 2020010 | C++     |  76.0 |  7 |        2 || 2020002 | C++     |  70.0 |  8 |        2 || 2020008 | C++     |  69.0 |  9 |        2 || 2020007 | C++     |  66.0 | 10 |        2 || 2020009 | C++     |  66.0 | 10 |        2 || 2020004 | C++     |  60.0 | 12 |        2 || 2020003 | English | 100.0 |  1 |        1 || 2020004 | English | 100.0 |  1 |        1 || 2020002 | English |  99.0 |  3 |        1 || 2020013 | English |  88.0 |  4 |        1 || 2020008 | English |  86.0 |  5 |        1 || 2020009 | English |  86.0 |  5 |        1 || 2020011 | English |  84.0 |  7 |        1 || 2020010 | English |  81.0 |  8 |        2 || 2020003 | English |  80.0 |  9 |        2 || 2020007 | English |  76.0 | 10 |        2 || 2020012 | English |  75.0 | 11 |        2 || 2020005 | English |  70.0 | 12 |        2 || 2020006 | English |  70.0 | 12 |        2 || 2020005 | mysql   |  98.0 |  1 |        1 || 2020001 | mysql   |  90.0 |  2 |        1 || 2020008 | mysql   |  90.0 |  2 |        1 || 2020011 | mysql   |  90.0 |  2 |        1 || 2020004 | mysql   |  80.0 |  5 |        1 || 2020003 | mysql   |  78.0 |  6 |        1 || 2020010 | mysql   |  75.0 |  7 |        2 || 2020009 | mysql   |  70.0 |  8 |        2 || 2020006 | mysql   |  60.0 |  9 |        2 || 2020002 | mysql   |  50.0 | 10 |        2 || 2020007 | mysql   |  50.0 | 10 |        2 |+---------+---------+-------+----+----------+36 rows in set (0.01 sec)

3、窗口函数小结

类别函数说明
排序ROW_NUMBER为表中的每一行分配一个序号,可以指定分组(也可以不指定)及排序字段
DENSE_RANK根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时,序号相同,序号中没有间隙(1,1,2, 3 这种)
RANK根据排序字段为每个分组中的每一行分配一个序号。 排名值相同时,序号相同,但序号中存在间隙(1,1,3, 4 这种)
NTILE根据排序字段为每个分组中根据指定字段的排序再分成对应的组
分布PERCENT_RANK计算各分组或结果集中行的百分数等级
CUME_DIST计算某个值在一组有序的数据中累计的分布
前后LEAD返回分组中当前行之后的第N行的值。如果不存在对应行,则返回NULL。比如N= 1 时,第一名对应的值是第二名的,最后一名结果是NULL
LAG返回分组中当前行之前的第N行的值。如果不存在对应行,则返回NULL。比如N= 1 时,第一名对应的值是是NUL,最后一名结果是倒数第 2 的值
首尾中FIRST_VALUE返回每个分组中第一名对应的字段(或表达式)的值,例如本文中可以是第一名的分数、学号等任意字段的值
LAST_VALUE返回每个分组中最后一名对应的字段(或表达式)的值,例如本文中可以是最后一名的分数、学号等任意字段的值
NTH_VALUE返回每个分组中排名第N的对应字段(或表达式)的值,但小于N的行对应的值是NULL

MySQL中主要的窗口函数先总结这么多,建议还是得动手实践一番。另外,MySQL5. 7 及之前版本的排序方式的实现很多人已总结,也建议实操一番。

本文转载自微信公众号【数据库干货铺】

  • 相关推荐
  • 大家在看
  • MySQL怎么连接 全部连接方式汇总

    1. 连接方式1.1 方式1 /usr/local/mysql5.7/bin/mysql -p此方法默认采用root@localhost用户登录,1.2 方式2/usr/local/mysql5.7/bin/mysql -uroot -p -S /app/data/mysql3307/tmp/mysql.sock1.3 方式3/usr/local/mysql5.7/bin/mysql -uroot -p -h 127.0.0.1 -P3307此方式的用户和方式 2 的不同,如下 root@localhost 和root@127.0.0.1是不同的用户1.4 方式4 /usr/local/mysql5.7/bin/mysql -uroot -p -h localhost -P3307此方式和?

  • MySQL怎么删除#sql开头的临时表

    巡检时发现服务器磁盘空间不足,通过查看大文件进行筛选是发现有几个#sql开头的文件,且存在超过100G及10G以上的文件。

  • MySQL一直自动重启解决办法

    近期,测试环境出现了一次MySQL数据库不断自动重启的问题,导致的原因是强行kill -9 杀掉数据库进程导致,报错信息如下:2019-07-24T01:14:53.769512Z 0 [Note] Executing SELECT * FROM INFORMATION_SCHEMA.TABLES; to get a list of tables using the deprecated partition engine. You may use the startup option --disable-partition-engine-check to skip this check.2019-07-24T01:14:53.769516Z 0 [Note] Beginning of

  • MYSQL的ibtmp1文件太大怎么处理

    1. 啥情况呀测试环境机器磁盘空间不足的告警打破了下午的沉寂,一群人开始忙活着删数据。但是,不久前刚清理了一波数据,测试环境在没做压测的情况下不至于短短一个月不到就涨了200G数据,于是,我悄悄的进入数据目录下,发现一个不寻常的点,ibtmp1 文件有192G ll -hibtmp1 -rw-r----- 1 mysql mysql 192G Aug 12 16:20 ibtmp12. 怎么处理2.1 简单说明ibtmp1 是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_pat

  • MySQL忘记密码怎么办 密码重置详细教程

    如果忘记密码,对于MySQL而言处理起来也相对比较简单。但需要修改配置,重启数据库。可以按照如下步骤处理。

  • MySQL敏感数据怎么加密 数据加密解密教程

    大数据时代的到来,数据成为企业最重要的资产之一,数据加密的也是保护数据资产的重要手段。本文主要在结合学习通过MySQL函数及Python加密方法来演示数据加密的一些简单方式。

  • 为避免种族歧视,MySQL宣布删除黑名单白名单等术语

    MySQL数据库的开发人员日前宣布,计划在数据库源代码和文档中停止使用并替换 master、 slave、 blacklist(黑名单)和whitelist(白名单)等术语。

  • MySQL传统点位复制在线转为GTID模式复制

    1. GTID优缺点MySQL传统点位复制在5. 7 版本前是主要的主从复制模式,而随着MySQL5. 6 版本引入GTID,并且MySQL5. 7 进行各方面的优化以后,在mySQL5.7(尤其是MySQL5.7.6)版本后GTID模式的主从复制方式成为一个新的选择方式。要使用GTID模式,首先也需知其优缺点,其主要的优缺点如下:1.1 优点a) 更简单的实现failover,无需找log_file和log_Pos。b) 更简单的搭建主从复制。c) 复制集群有一个统一的方式识别复制位置,给集群管理

  • 黑名单、白名单完全不能用了!MySQL、Twitter等纷纷删除

    作为全球第二大流行数据库,MySQL也受到了众所周知事件的影响,不得不做出一些调整。MySQL开发人员今天宣布,计划在数据库源文件、文档中停止使用master(主)、slave(从)、blacklist(黑名单)、

  • MySQL数据备份及恢复教程 巧用xtrabackup工具备份恢复

    本文将通过应用更为普遍的物理备份工具xtrabackup来演示数据备份及恢复的第二篇内容。

  • 妈妈网CEO杨刚:在线母婴不是风口而是窗口

    在线母婴行业,曾有高光时刻,随后多年低调前行,终于在 2020 年 4 月重获市场热情。正在接受上市辅导的美柚冲击科创板,老牌互联网巨头网易推出亲子相册。 不过,在老牌在线母婴企业妈妈网CEO杨刚眼里,这些都不意外。 杨刚表示,“母婴与O2O、垂直电商不同,它不算风口,而是窗口。因为每年都会有新流量,按 2019 年新生儿数量,在线母婴行业的 2019 年,仅妈妈群体就有将近 1500 万新流量”。 根据艾媒咨询 3 月下旬发布的《 2

  • 币安何一:区块链行业里面存在一个巨大的窗口期

    2020年,新冠肺炎疫情在世界各地蔓延。截止4月28日下午,全球累计确诊新冠肺炎病例已突破300万例。新冠肺炎疫情的爆发对世界各国的经济和社会造成了很大的冲击。纵观历史长河,世界经历了多次金融危机,而新冠肺炎疫情对经济的冲击是外生冲击。疫情之后的全球化和全球价值链将会呈现新的态势和发展趋势。很多人说,新一轮的疫情可能会带来全球的大萧条或者经济体系的崩溃。币安何一如何看待未来世界的变化呢?以下是Odaily星球日报

  • 美国火星车今晚发射!只有2小时窗口期

    北京时间7月20日凌晨5时58分14秒,阿联酋希望号火星轨道器发射升空。北京时间7月23日12时41分,中国天问一号火星探测器发射升空。现在,就等美国的毅力号了。根据NASA(美国航空航天局)的最

  • 万兴科技旗下思维导图软件MindMaster8.0登陆飞书应用商店

    近日万兴科技旗下产品MindMaster8.0版正式登陆飞书应用商店,牵手大平台加码ToB业务,在各大企业逐渐由传统纸质办公向云和互联网时代转型的这一重要时期,抢占移动办公新风口。 飞书作为国内领先的全方位企业服务平台,在整合即时沟通、日历、在线文档、云盘、应用中心等功能,为企业提供品质卓越的云协作体验方面颇有成果。自2017年问世,2019年正式推向海外和国内市场。对比钉钉和企业微信,飞书并不完全趋同,它想做的是系统办

  • docker怎么安装MySQL docker安装MySQL教程

    把MySQL放进Docker,总共需要几步?本次就通过社区版容器安装2个mysql实例,看一下部署有多简单。

  • MySQL数据库怎么升级 MySQL数据库升级教程

    当前不少系统的数据库依旧是MySQL5.6,由于MySQL5. 7 及MySQL8. 0 在性能及安全方面有着很大的提升,因此需要升级数据库。本文通过逻辑方式、物理方式原地升级来介绍MySQL5.6 升级至MySQL5. 7 的方法,并介绍其使用场景。

  • 为什么MySQL默认隔离级别是RR

    曾多次听到“MySQL为什么选择RR为默认隔离级别”的问题,其实这是个历史遗留问题,当前以及解决,但是MySQL的各个版本沿用了原有习惯。历史版本中的问题是什么,本次就通过简单的测试来说明一下。

  • MySQL统计库表大小都是多少

    统计每个库每个表的大小是数据治理的其中最简单的一个要求,本文将从抽样统计结果及精确统计结果两方面来统计MySQL的每个库每个表的数据量情况。

  • MySQL怎么按指定字符合并及拆分 详细实例分析

    按照指定字符进行合并或拆分是经常碰到的场景,MySQL在合并的写法上比较简单,但是按指定字符拆分相对比较麻烦一点(也就是要多写一些字符)。本文将举例演示如何进行按照指定字符合并及拆分。

  • mysql怎么处理大表在不停机的情况下增加字段

    MySQL中给一张千万甚至更大量级的表添加字段一直是比较头疼的问题,遇到此情况通常该如果处理?本文通过常见的三种场景进行案例说明。

  • 参与评论
文明上网理性发言,请遵守新闻评论服务协议
  • 热门标签